Michael Paquier <michael.paqu...@gmail.com> writes: > And actually, contrary to what is mentioned upthread, the planner is > not able to avoid a sort phase if other data types are used, say: > =# create table foo (a int, b int); > CREATE TABLE > =# create index on foo (a, b); > CREATE INDEX > =# explain (costs off) select * from foo where a = 1 order by b limit 10;
No, there's a difference between "not able to" and "chooses not to". In this example case, it just thinks a bitmap scan is cheaper than an ordered scan: regression=# explain select * from foo where a = 1 order by b limit 10; QUERY PLAN --------------------------------------------------------------------------------------- Limit (cost=15.10..15.13 rows=10 width=8) -> Sort (cost=15.10..15.13 rows=11 width=8) Sort Key: b -> Bitmap Heap Scan on foo (cost=4.24..14.91 rows=11 width=8) Recheck Cond: (a = 1) -> Bitmap Index Scan on foo_a_b_idx (cost=0.00..4.24 rows=11 width=0) Index Cond: (a = 1) (7 rows) regression=# set enable_bitmapscan to 0; SET regression=# explain select * from foo where a = 1 order by b limit 10; QUERY PLAN ------------------------------------------------------------------------------------ Limit (cost=0.15..33.06 rows=10 width=8) -> Index Only Scan using foo_a_b_idx on foo (cost=0.15..36.35 rows=11 width=8) Index Cond: (a = 1) (3 rows) The problem with the boolean-column case is it fails to recognize that the index matches at all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers