[Bizgres-general] A bitmap index access method is available in bizgres CVS tip

[Mark Kirkwood]

Jie Zhang wrote:

>>What I am seeing is that vanilla Pg 8.1.1 with btree indexes on several
>>low cardinality columns seems to be considerably faster than Bizgres
>>with on-disk bitmaps (for my test case). I would have thought that it
>>was an ideal candidate to show the advantages of on-disk representation
>>(several low cardinality columns) - any ideas?
> 
> 
> This should be an ideal candidate for an on-disk bitmap index. However, in
> your test case, current implementation works better when you create a single
> bitmap index for those three columns. That is,
> 
>   create index bitmaptest_012 on bitmaptest using bitmap(val0, val1, val2);
> 
> Then you should get much better query performance on your test query. This
> is because the "AND" operations are done inside this single bitmap index,
> while the "AND" operations in your test case are done by Tom Lane's bitmap
> scan.
> 
> This is one limitation of current implementation. I am working on improving
> this.

Ah - that makes sense, I've rerun the index setup like:

--
-- Index the table with a single bitmap this time.
--
CREATE INDEX bitmaptest_vals ON bitmaptest USING bitmap (val0, val1, val2);

Performance:

Bizgres current 33s (slower!)


EXPLAIN ANALYZE:
                                                                  QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
------------------
  Aggregate  (cost=3818.04..3818.04 rows=1 width=0) (actual 
time=33734.646..33734.651 rows=1 loops=1)
    ->  Bitmap Heap Scan on bitmaptest  (cost=21.75..3815.47 rows=1030 
width=0) (actual time=26546.031..33722.914 rows=1019
loops=1)
          Recheck Cond: ((val0 = 1) AND (val1 = 4) AND (val2 = 79))
          ->  Bitmap Index Scan on bitmaptest_vals  (cost=0.00..21.75 
rows=1030 width=0) (actual time=26517.566..26517.566 r
ows=1019 loops=1)
                Index Cond: ((val0 = 1) AND (val1 = 4) AND (val2 = 79))
  Total runtime: 33735.867 ms
(6 rows)


The cost estimate sure does look better - but somehow the actual time is 
    2 times as slow again. I tried with the columns in a different order 
as well (most selective one first), with no noticeable effect.

I'm wondering about os and hardware at this point - FreeBSD on an 
(older) SMP machine (dual PIII 1G) - could that be significant?

If you guys want to try out the test, I've put an archive at:

http://homepages.paradise.net.nz/markir/download/bitmaptest.tar.gz

Cheers

Mark