bitmap index scan problem? - Mailing list pgsql-bugs

hi
we have a table with some 30M records.
running PG8.1.4. on linux.

when we run with enable_bitmapscan true, PG begins by doing a bitmap index scan plus a BitmapAnd.

when we run with enable_bitmapscan false, PG finds a better index directly and chooses a much better plan.


below is some data, the query and the plans.
as you can see, when using the Index Scan PG finds 62 rows.
but when using Bitmap Index Scan it finds 2563790 + 506 rows which should never be better!?

the question is simply why the planner is not smart enough to skip the bitmap scan if normal operation is faster.




stat=# \d stats
                      Table "public.stats"
   Column   |            Type             |     Modifiers
-----------+-----------------------------+--------------------
  id        | bigint                      | not null default 0
  timestamp | timestamp without time zone |
  aid       | integer                     |
  i         | integer                     |
  ct        | integer                     |
  total     | bigint                      |
  bid       | integer                     | not null default 0
Indexes:
     "id_idx" btree (id)
     "bid_index" btree (bid)
     "ct_index" btree (ct)



--GOOD PLAN FIRST--
stat=# set enable_bitmapscan to false;
SET
Time: 0.645 ms
stat=# explain analyze select aid, ct, sum(total) from stats where ct='90' and bid=17675 GROUP BY aid, ct;
                                                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=28713.95..28714.63 rows=54 width=16) (actual time=1.072..1.080 rows=3 loops=1)
    ->  Index Scan using bid_index on stats  (cost=0.00..28709.92 rows=538 width=16) (actual time=0.100..0.804 rows=62
loops=1)
          Index Cond: (bid = 17675)
          Filter: (ct = 90)
  Total runtime: 1.163 ms
(5 rows)

Time: 2.692 ms




--NOW THE BAD PLAN--
stat=# set enable_bitmapscan to true;
SET
Time: 2.775 ms
stat=# explain analyze select aid, ct, sum(total) from stats where ct='90' and bid=17675 GROUP BY aid, ct;
                                                                      QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=15848.76..15849.44 rows=54 width=16) (actual time=13210.811..13210.818 rows=3 loops=1)
    ->  Bitmap Heap Scan on stats  (cost=13754.80..15844.73 rows=538 width=16) (actual time=13206.714..13210.525
rows=62loops=1) 
          Recheck Cond: ((bid = 17675) AND (ct = 90))
          ->  BitmapAnd  (cost=13754.80..13754.80 rows=538 width=0) (actual time=13206.659..13206.659 rows=0 loops=1)
                ->  Bitmap Index Scan on bid_index  (cost=0.00..44.51 rows=7576 width=0) (actual time=0.137..0.137
rows=506loops=1) 
                      Index Cond: (bid = 17675)
                ->  Bitmap Index Scan on ct_index  (cost=0.00..13710.04 rows=2409440 width=0) (actual
time=13206.216..13206.216rows=2563790 loops=1) 
                      Index Cond: (ct = 90)
  Total runtime: 13210.918 ms
(9 rows)

Time: 13212.121 ms




/stig