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

Re: bitmap indexes - performance

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: pgsql-hackers(at)postgresql(dot)org
Cc: m_lists(at)yahoo(dot)it
Subject: Re: bitmap indexes - performance
Date: 2010-07-03 02:00:26
Message-ID: 4C2E99BA.3080009@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-hackers
On 02/07/10 20:30, Mark Kirkwood wrote:
>
> I recall that for (some/most? of) those low cardinality cases, (on 
> disk) bitmap indexes would perform better too. I think the size saving 
> alone is a huge win for serious data warehousing situations. On the 
> other hand problems I recall are possibly reduced UPDATE/DELETE 
> performance and issues with CREATE INDEX CONCURRENTLY and also 
> complications with VACUUM (altho these last two may have been sorted - 
> I've lost touch with what was in the most recent patches).
>
>

Sorry, missed the message earlier where Bruce mentioned VACUUM.

Re Performance, I definitely recall some pretty serious performance 
improvements on some of the TPC D (or H) queries when the dataset was 
large . However I am wondering if most of the improvement might have 
been because the bitmap index(es) fitted in memory and the corresponding 
btree ones did not.

Leonardo - maybe try larger datasets (20M rows probably means table and 
btree indexes can all fit in memory). Also might be worth experimenting 
with the TPC D,H dataset and query generator and seeing if any of those 
queries tickle any bitmap sweet spot.


Cheers

Mark


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-07-03 02:02:59
Subject: Re: _bt_parent_deletion_safe() isn't safe
Previous:From: Igor KryltsovDate: 2010-07-03 01:55:38
Subject: Table partitioning - is anything coming?

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