Re: 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=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