Michael Paquier <michael.paquier@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=10width=8) -> Sort (cost=15.10..15.13 rows=11 width=8) Sort Key: b -> Bitmap Heap Scan on foo
(cost=4.24..14.91rows=11 width=8) Recheck Cond: (a = 1) -> Bitmap Index Scan on foo_a_b_idx
(cost=0.00..4.24rows=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