bitmap index and IS NULL predicate - Mailing list pgsql-performance

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.