Skip site navigation (1) Skip section navigation (2)

bitmap index and IS NULL predicate

From: Jason Pinnix <pinnixjason(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: bitmap index and IS NULL predicate
Date: 2007-05-15 15:16:04
Message-ID: 83589.1406.qm@web57312.mail.re1.yahoo.com (view raw or flat)
Thread:
Lists: 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.

Responses

pgsql-performance by date

Next:From: Alexander StauboDate: 2007-05-15 15:22:17
Subject: Re: bitmap index and IS NULL predicate
Previous:From: Heikki LinnakangasDate: 2007-05-15 14:17:23
Subject: Re: Many to many join seems slow?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group