Re: Fixing matching of boolean index columns to sort ordering

Michael Paquier <michael(dot)paquier(at)gmail(dot)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