Note: I still consider this a bug/missing feature of sorts since the
planner could do better here, and there is no real clean way of
structuring a query to perform efficiently here, which is why I
erroneously cross posted this to hacker initially:
# create table testing(id serial primary key, data varchar);
# insert into testing(data) select 'test' from pg_tables a,pg_tables
b,pg_tables c,pg_tables d limit 100000
# explain select * from testing order by id limit 30;
QUERY PLAN
------------------------------------------------------------------------------------------
Limit (cost=0.29..1.24 rows=30 width=9)
-> Index Scan using testing_pkey on testing (cost=0.29..3148.29
rows=100000 width=9)
(2 rows)
# explain select * from testing where id = 1000;
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using testing_pkey on testing (cost=0.29..8.31 rows=1 width=9)
Index Cond: (id = 1000)
(2 rows)
# explain select * from testing order by case when id = 1000 then 0
else 1 end, id limit 30;
QUERY PLAN
---------------------------------------------------------------------------
Limit (cost=4744.45..4744.52 rows=30 width=9)
-> Sort (cost=4744.45..4994.45 rows=100000 width=9)
Sort Key: (CASE WHEN (id = 1000) THEN 0 ELSE 1 END), id
-> Seq Scan on testing (cost=0.00..1791.00 rows=100000 width=9)
(4 rows)
Cost goes through the roof for a query that pg could have have done
better with if it were able to better "understand" the case statement.
