Hello,
I'm running version 8.2 with the bitmap index patch posted on pgsql-hackers.
While selection queries with equality predicates (col = value) are able to make
use of the bitmap index, those with IS NULL predicates (col IS NULL) are not
able to use the bitmap index. The online manuals seem to indicate that IS NULL
predicates by default do not use indices but they can be forced to do so by
setting enable_seqscan to off. Even after setting enable_seqscan to off, the
optimizer still chooses sequential scan over bitmap index scan. Below shows
various queries with plans showing use (and lack of) the bitmap index on a
table containing 1500 rows.
I also checked that if I create a btree index on col and set enable_seqscan to
off, the optimizer correctly chooses the btree index for IS NULL queries. So my
question is whether there is something fundamentally different about the bitmap
index that precludes its use in IS NULL queries? Does the bitmap index not
store a bit vector for the NULL value (i.e. a bit vector that contains a 1 for
each row with a NULL value and 0 for other rows) ?
Thanks,
Jason
my_db=# explain analyze select * from some_values where col=98;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on some_values (cost=5.01..94.42 rows=97 width=8) (actual
time=0.493..0.923 rows=100 loops=1)
Recheck Cond: (col = 98)
-> Bitmap Index Scan on some_values_idx (cost=0.00..4.98 rows=97 width=0)
(actual time=0.475..0.475 rows=0 loops=1)
Index Cond: (col = 98)
Total runtime: 1.321 ms
(5 rows)
my_db=# explain analyze select * from some_values where col is null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on some_values (cost=0.00..184.00 rows=1 width=8) (actual
time=0.102..1.966 rows=1 loops=1)
Filter: (col IS NULL)
Total runtime: 2.014 ms
(3 rows)
my_db=# set enable_seqscan to off;
SET
my_db=# explain analyze select * from some_values where col is null;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on some_values (cost=100000000.00..100000184.00 rows=1 width=8)
(actual time=0.100..1.934 rows=1 loops=1)
Filter: (col IS NULL)
Total runtime: 1.976 ms
(3 rows)
---------------------------------
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search.
