bitmap index scan problem?

From: stig erikson <stigerikson_nospam_(at)yahoo(dot)se>
To: pgsql-bugs(at)postgresql(dot)org
Subject: bitmap index scan problem?
Date: 2006-09-08 17:11:00
Message-ID: eds88c$p6e$1@floppy.pyrenet.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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=62 loops=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=506 loops=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.216 rows=2563790 loops=1)
Index Cond: (ct = 90)
Total runtime: 13210.918 ms
(9 rows)

Time: 13212.121 ms

/stig

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message skirsten 2006-09-08 17:38:54 Strange behavior of incremented_by
Previous Message Paul Suckling 2006-09-08 16:18:09 BUG #2611: 2 instalation probs: invalid directory and user account creation