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

Re: bitmap indexes - performance

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Leonardo F <m_lists(at)yahoo(dot)it>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: bitmap indexes - performance
Date: 2010-07-01 14:16:09
Message-ID: 201007011416.o61EG9l02763@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Leonardo F wrote:
> 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)

Great report, thanks.  The other big problem with on-disk bitmap indexes
is removing expired values via vacuum.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

In response to

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-07-01 14:18:40
Subject: Re: Cannot cancel the change of a tablespace
Previous:From: Leonardo FDate: 2010-07-01 13:23:49
Subject: bitmap indexes - performance

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