Re: [HACKERS] Fixing matching of boolean index columns to sort ordering

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