[Bizgres-general] Re: Non equality operators and bitmap indexes (was : A bitmap index access method is available...)

[Jie Zhang]

On 12/28/05 3:49 PM, "Mark Kirkwood" <markir at paradise.net.nz> wrote:

> I noticed that queries like:
> 
> bitmap=# EXPLAIN SELECT * FROM bitmaptest WHERE val0 <= 1;
>                                QUERY PLAN
> ----------------------------------------------------------------------
>   Seq Scan on bitmaptest  (cost=0.00..218458.08 rows=4633337 width=40)
>     Filter: (val0 <= 1)
> (2 rows)
> 
> will not use the available (bitmap) index on val0, whereas, an
> equivalent query recoded to use equality operators will happily do so:
> 
> bitmap=# EXPLAIN SELECT * FROM bitmaptest WHERE val0 in (0,1);
>                                            QUERY PLAN
> 
> ------------------------------------------------------------------------------
> ----------------
>   Bitmap Heap Scan on bitmaptest  (cost=24689.68..187647.73 rows=4138460
> width=40)
>     Recheck Cond: ((val0 = 0) OR (val0 = 1))
>     ->  BitmapOr  (cost=24689.68..24689.68 rows=4633337 width=0)
>           ->  Bitmap Index Scan on bitmaptest_val0  (cost=0.00..15790.67
> rows=2963336 width=0)
>                 Index Cond: (val0 = 0)
>           ->  Bitmap Index Scan on bitmaptest_val0  (cost=0.00..8899.00
> rows=1670001 width=0)
>                 Index Cond: (val0 = 1)
> (7 rows)
> 
> 
> I'm guessing it is a TODO item, but thought I may as well mention it :-)

There are three known encoding schemes for the bitmap index: the equality
scheme (each bitmap is associated with each one of all distinct values), the
interval scheme (each bitmap is associated with several distinct values that
lie within an interval), and the range scheme (each bitmap is associated
with distinct values that are less/greater than a given value). Each of
these schemes has different performance advantages for different queries.
Currently, we only support the equality scheme. I think that the better way
is to let users decide which encoding scheme to use. Any comments on this?

Best,
Jie