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
(SELECT * FROM test ORDER BY first_name LIMIT (SELECT lim_a FROM l))
(SELECT * FROM test ORDER BY first_name DESC LIMIT (SELECT lim_d FROM l));
One of both
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.
Don't use reserved key words as identifier (as you found yourself already):
ordr instead of
Don't bloat your table with redundant values. If you cannot avoid storing
ordr for every row, at least make it small. Basic layout:
CREATE TABLE test (
, 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);
limit, or this is just an intermediary result of another query?
limitstored. 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 =
descfor example, but with other records ).