4

I have this table in a Postgres 9.3 database:

enter image description here

I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name.

The final result will be as in the following picture:

enter image description here

Note: Sorry if it is simple for you, but I can't solve this. All mail addresses were generated by www.mockaroo.com. If your address is in this list, don't blame me.

order and limit columns will always have same data. order may be asc or desc and limit may be any integer value (but all rows will be the same value. It came from a grouping query.

2
  • Do you really store order and limit, or this is just an intermediary result of another query?
    – dezso
    Sep 24 '15 at 14:44
  • This table have all order and limit stored. A previous query make this selection all the same for this situation. You can have other values from a different query from the same big table ( order = desc for example, but with other records ).
    – Magno C
    Sep 24 '15 at 14:55
8

Query

While your solution is clever, it's poison for performance because the value to order by has to be computed separately for every row. More importantly, your query cannot use a plain index.

I suggest to move the trick to the LIMIT clause and use UNION ALL. This way the query gets cheaper overall and can use an index (which nukes competitors that can't).

WITH l AS (
   SELECT CASE WHEN ordr = 'a' THEN lim ELSE 0 END AS lim_a
        , CASE WHEN ordr = 'd' THEN lim ELSE 0 END AS lim_d
   FROM   test
   LIMIT  1
   )
(SELECT * FROM test ORDER BY first_name      LIMIT (SELECT lim_a FROM l))
UNION ALL
(SELECT * FROM test ORDER BY first_name DESC LIMIT (SELECT lim_d FROM l));

One of both SELECT gets LIMIT 0 and is never executed. You'll see "never executed" in the EXPLAIN ANALYZE output.

About twice as fast without index in a quick test on Postgres 9.4 (35 rows out of 50k), but several orders of magnitude faster with index. The difference grows with the size of the table, obviously.

Table layout

Don't use reserved key words as identifier (as you found yourself already): lim and ordr instead of limit and order.

Don't bloat your table with redundant values. If you cannot avoid storing lim and ordr for every row, at least make it small. Basic layout:

CREATE TABLE test (
  id         int
, lim        int
, ordr       "char" CHECK (ordr IN ('a', 'd'))
, first_name text
, email      text
);

"char" is perfect as a simplistic enumeration type and occupies just 1 byte.
Create an index:

CREATE INDEX test_first_name_idx ON test(first_name);

Details:

SQL Fiddle.

2
  • Many thanks. I agree with you on avoid storing repeated rows but trust me: I can't avoid this becaus of system nature. If you want to know more, go to eic-cefet-rj.github.io/sagitarii. Its a user-custom and system-flow data table.
    – Magno C
    Sep 25 '15 at 22:51
  • @MagnoC: My answer already addresses the case that redundant storage can't be avoided ... Sep 25 '15 at 22:57
3

It was solved by a friend with this statement:

select * from test 
    order by 
    case when (ordr='asc') then first_name end asc, 
    case when (ordr='desc') then first_name end desc 
limit ( select lim from test limit 1 );

NOTE: We changed the name of columns order and limit to ordr and lim.

3
  • Not bad, not bad :) The only thing I have to add is that storing all those ordr and lim values is a waste of space. Storing them in a separate table (just one row) would be enough.
    – dezso
    Sep 24 '15 at 21:24
  • Agree. You're right. Its part of a data science workflow execution and is just one row at start. At some point occour a data split and becomes several rows, but I must to preserve this information in just one tabe ( no joins ) because the nature of the system.
    – Magno C
    Sep 25 '15 at 1:45
  • 2
    For all upvoters: my solution is simple and easy to read, but note I accepted @Erwin Brandstetter's answer (not too hard to understand) because of performance improvements. The answer's core is the same and it is well explained. Please upvote his answer to lead others users to the right way
    – Magno C
    Sep 28 '15 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.