bitmap indexes - performance
in [PgSql]
|
Prev: [HACKERS] Planner deficiencies with views that use windowing functions
Next: [HACKERS] superfluous copydir() prototype in pg_upgrade.h
From: Mark Kirkwood on 2 Jul 2010 04:30 On 02/07/10 13:31, Bruce Momjian wrote: > Leonardo F wrote: > >> I'm trying to find more docs that explain the "improvements" of >> bitmap indexes in other products... but most of what I've found >> talks about bitmapAND/OR.... which is something that is very >> cool, but that postgres already does even with btree indexes... >> or index creation time/size, which are, for the moment, the only >> things that I'm pretty confident the patch would actually provide. >> > I think a real limitation of on-disk bitmap indexes is that they are > only feable for low cardinality columns, while btree handles all column > types. > > 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). regards Mark
From: Mark Kirkwood on 2 Jul 2010 22:00 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Josh Berkus on 8 Jul 2010 01:09 >> 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... Well, if you can fix the more basic missing stuff, I think we could live with the performance issues. Bitmaps would still be a huge win for relatively static tables with lots of low-cardinality columns (basic data warehouse case). If I recall correctly, the old patch was still missing both WAL and VACUUM support. These would be required before tradeoffs of space vs. update performance would be worth talking about. >> 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) Have you tested this? The bitmap AND/OR for btrees in current postgres isn't exactly cost-free, especially the recheck. It seems like there could be room for better performance with bitmap indexes. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 22 Jul 2010 06:54
On Thu, 2010-07-01 at 16:30 +0000, Leonardo F wrote: > To sum up: IMHO nor improvements in memory usage nor > in startup time would be good reasons to switch to bitmap > indexes... but bulk index creation time (10 minutes to index > what it takes 60 minutes with btree... and maybe more if tables > are bigger) and (maybe) index disk space might be... > but I'm not 100% convinced... Are you intending to work on this for 9.1? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |