Re: How do I bump a row to the front of sort efficiently

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.