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

bitmap indexes - performance

From: Leonardo F <m_lists(at)yahoo(dot)it>
To: pgsql-hackers(at)postgresql(dot)org
Subject: bitmap indexes - performance
Date: 2010-07-01 13:23:49
Message-ID: 800923.27831.qm@web29010.mail.ird.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Using as a starting point the old bitmap patch in:

http://archives.postgresql.org/message-id/20081101000154.GO27872@fune


I re-applied and re-worked the patch to see what kind of improvements over
btrees bitmaps actually provided.

Using a 20M rows table of 10/100/1000 random values, I've found that:

1) bulk index creation time is roughly 6 times better
2) index size is 6-15 times smaller (depending on column cardinality)
3) there's almost no difference in query times (but I have to make more
tests)
4) I can't say anything about the insertion performance, but I guess
bitmap will perform way worse than btree

Are these improvements (index creation time, index size) worth enough
to keep on working on this?

I mean: given that bitmaps don't give any benefits in query times, but
only benefits related to disk size and bulk index creation times, and
will have horrible performance for insertions/deletions: would this job be
worthed?

In case it is: I will try to clean up the patch and post it...


As a side note: I guess that most of the bitmap indexes performance 
improvements in the SELECT area are already implemented in postgres
in the bitmapand/or and bitmap scan stuff? I couldn't find any docs that
say that bitmap indexes are faster for selects, unless of course they
are ANDed/ORed together (which is something postgres already does
for regular btree indexes)


      

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2010-07-01 14:16:09
Subject: Re: bitmap indexes - performance
Previous:From: Greg StarkDate: 2010-07-01 11:27:57
Subject: Re: Proposal for 9.1: WAL streaming from WAL buffers

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