Use column values in ORDER BY and LIMIT clauses

Question

I have this table in a Postgres 9.3 database:

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:

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.

Answer 1

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:

• Configuring PostgreSQL for read performance
• Measure the size of a PostgreSQL table row

SQL Fiddle.

Answer 2

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.