Hi list,
This simple query shouldn't cause two bitmap index scans:
EXPLAIN select * from test where b='0';
Bitmap Heap Scan on test (cost=1056.68..8200.12 rows=29839 width=314) Recheck Cond: ((b = 0) AND (b = 0::smallint))
-> BitmapAnd (cost=1056.68..1056.68 rows=5237 width=0) -> Bitmap Index Scan on test_i_idx (cost=0.00..485.45
rows=29839 width=0) -> Bitmap Index Scan on test_b_c_idx (cost=0.00..556.06
rows=29839 width=0) Index Cond: (b = 0::smallint)
One of the indexes is a partial index, and the other is just a simple index.
Apparently, for some reason, the '0' is expanded into both an integer
and a smallint literal and the planner thinks it can reduce rows by
checking the condition twice?
This is how I reproduced the issue:
set enable_indexscan=off;
create table test as select i, (i/30000)::smallint as b, 0::int as c,
repeat('x', 300) as filler from generate_series(1,170000) i;
create index test_i_idx on test (i) where b=0;
create index test_b_c_idx on test (b,c);
analyze test;
explain select * from test where b='0';
Reproduced on PostgreSQL 8.3.15, 8.4.8, 9.0.4, 9.1rc1 and 9.2devel.
However, this issue does NOT occur on 8.2.21
When I write the literal without quotes, I get a more sensible plan:
EXPLAIN select * from test where b=0;
Bitmap Heap Scan on test (cost=493.79..8260.88 rows=30007 width=314) Recheck Cond: (b = 0) -> Bitmap Index Scan on
test_i_idx (cost=0.00..486.29 rows=30007 width=0)
Also, *before* analyzing the table, I get a good plan:
EXPLAIN select * from test where b='0';
Bitmap Heap Scan on test (cost=18.86..2450.01 rows=850 width=42) Recheck Cond: (b = 0::smallint) -> Bitmap Index
Scanon test_b_c_idx (cost=0.00..18.64 rows=850 width=0) Index Cond: (b = 0::smallint)
Regards,
Marti Raudsepp
