Обсуждение: Experiences of PostgreSQL on-disk bitmap index patch
Hi!
I'm working on building a PostgreSQL based data warehouse, and I'm thus very interested in any experiences and usage of the PostgreSQL bitmap index patches (which I've found on pgsql-patches).
Anyone using these patchese on production systems?
Anyone know if the patches run on latest stable 8.2?
Issues applying the patch to latest 8.2 source?
Other experiences of the patches?
If there's any PgSQL developer reading this - when can on-disk bitmap indexes be expected to be included in stable PostgreSQL versions?
Thank you folks!
Best regards,
Christian
Christan Josefsson wrote: > Hi! > > I'm working on building a PostgreSQL based data warehouse, and I'm thus > very interested in any experiences and usage of the PostgreSQL bitmap > index patches (which I've found on pgsql-patches). > > Anyone using these patchese on production systems? > Anyone know if the patches run on latest stable 8.2? > Issues applying the patch to latest 8.2 source? > Other experiences of the patches? They work against -HEAD. They are not finished. They have not been tested. They do not support Vacuum. Joshua D. Drake > > If there's any PgSQL developer reading this - when can on-disk bitmap > indexes be expected to be included in stable PostgreSQL versions? > > Thank you folks! > > Best regards, > Christian -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
On 6/20/07, Christan Josefsson <cjosefsson@gmail.com> wrote: > Anyone using these patchese on production systems? If these are the same patches that were made for Bizgres, then they are bound to be in use in some current production systems of that version of PostgreSQL. > If there's any PgSQL developer reading this - when can on-disk bitmap > indexes be expected to be included in stable PostgreSQL versions? It's scheduled for inclusion in 8.3, which is close to getting a beta release: http://www.postgresql.org/developer/roadmap Alexander.
"Alexander Staubo" <alex@purefiction.net> writes: > On 6/20/07, Christan Josefsson <cjosefsson@gmail.com> wrote: >> If there's any PgSQL developer reading this - when can on-disk bitmap >> indexes be expected to be included in stable PostgreSQL versions? > It's scheduled for inclusion in 8.3, Not any more --- we gave up waiting for a finished patch to be submitted. http://archives.postgresql.org/pgsql-patches/2007-05/msg00013.php http://archives.postgresql.org/pgsql-patches/2007-05/msg00299.php http://developer.postgresql.org/index.php/Todo:PatchStatus regards, tom lane
Alexander Staubo wrote: > On 6/20/07, Christan Josefsson <cjosefsson@gmail.com> wrote: >> Anyone using these patchese on production systems? > > If these are the same patches that were made for Bizgres, then they > are bound to be in use in some current production systems of that > version of PostgreSQL. > >> If there's any PgSQL developer reading this - when can on-disk bitmap >> indexes be expected to be included in stable PostgreSQL versions? > > It's scheduled for inclusion in 8.3, which is close to getting a beta > release: > > http://www.postgresql.org/developer/roadmap Just so there is no confusion. These WILL NOT be in 8.3: http://developer.postgresql.org/index.php/Todo:PatchStatus Joshua D. Drake > > Alexander. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
On 6/20/07, Joshua D. Drake <jd@commandprompt.com> wrote: > Just so there is no confusion. These WILL NOT be in 8.3: > > http://developer.postgresql.org/index.php/Todo:PatchStatus Apologies. I didn't know they had been put on hold. Alexander.
Ok.
Big thanks for the information.
You mentioned Bizgres, do you have any more information in that direction, or do you know who to contact regarding information on Bizgres bitmap indexes. If there is a bitmap index patch in Bizgres which can be applied to the latest stable source of PostgreSQL then I have a solution until 8.4 (which I according to your answers is the assumed release for introducing on-disk bitmap indexes).
Any guess when 8.4 could be production ready? A year or more?
Regards,
Christian
2007/6/21, Alexander Staubo <alex@purefiction.net>:
On 6/20/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> Just so there is no confusion. These WILL NOT be in 8.3:
>
> http://developer.postgresql.org/index.php/Todo:PatchStatus
Apologies. I didn't know they had been put on hold.
Alexander.
On Thu, Jun 21, 2007 at 10:39:29AM +0200, Christan Josefsson wrote: > Any guess when 8.4 could be production ready? A year or more? "In the future" is what I'd be willing to state out loud ;-) 8.3 hasn't finished development yet. I wouldn't hold my breath. You can find out more about bizgres at http://bizgres.org/home.php. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner
Christan Josefsson wrote: > Any guess when 8.4 could be production ready? A year or more? Why don't you just use Bizgres? Right, they don't release that often, and 0.9 misses various fixes that went into PostgreSQL. But if it has what you are after and works for you.. -- Best regards, Hannes Dorbath
On Thursday 21 June 2007 04:39, Christan Josefsson wrote: > Ok. > > Big thanks for the information. > > You mentioned Bizgres, do you have any more information in that direction, > or do you know who to contact regarding information on Bizgres bitmap > indexes. If there is a bitmap index patch in Bizgres which can be applied > to the latest stable source of PostgreSQL then I have a solution until 8.4 > (which I according to your answers is the assumed release for introducing > on-disk bitmap indexes). > If you really want to see on-disk bitmaps in, you might want to study the patches and the missing vacuum related bits and then think about submitting an updated version. My take on the future of that patch is the original developers aren't terribly motivated to finish it, in much part because some of the testing people have done vs. 8.3 shows it solves an even smaller number of issues than originally hoped. my .02 anyway. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Thanks Robert!
So you indicate that the so called bitmap index scan, a.k.a in-memory bitmap indexes (right?), already adds such an improvement when it comes to optimized response time on large query sets (having the characteristics as normally used to identify cases where bitmap indexes improves performance like: low cardinality keys, large data volumes etc), so that the on-disk indexes are not really needed or atleast not worth wile implementing?
Regards,
Christian
2007/6/25, Robert Treat <xzilla@users.sourceforge.net>:
On Thursday 21 June 2007 04:39, Christan Josefsson wrote:
> Ok.
>
> Big thanks for the information.
>
> You mentioned Bizgres, do you have any more information in that direction,
> or do you know who to contact regarding information on Bizgres bitmap
> indexes. If there is a bitmap index patch in Bizgres which can be applied
> to the latest stable source of PostgreSQL then I have a solution until 8.4
> (which I according to your answers is the assumed release for introducing
> on-disk bitmap indexes).
>
If you really want to see on-disk bitmaps in, you might want to study the
patches and the missing vacuum related bits and then think about submitting
an updated version. My take on the future of that patch is the original
developers aren't terribly motivated to finish it, in much part because some
of the testing people have done vs. 8.3 shows it solves an even smaller
number of issues than originally hoped. my .02 anyway.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
cjosefsson@gmail.com ("Christan Josefsson") writes: > So you indicate that the so called bitmap index scan, a.k.a > in-memory bitmap indexes (right?), already adds such an > improvement when it comes to optimized response time on large > query sets (having the characteristics as normally used to > identify cases where bitmap indexes improves performance like: > low cardinality keys, large data volumes etc), so that the > on-disk indexes are not really needed or atleast not worth wile > implementing? It looks very much like that may be the case... Bitmap index scans have a somewhat different set of functionality, but there is enough overlap that the cases where on-disk bitmap indexes are useful (and in-memory bitmap scans aren't) look like rare edge cases. There may be users that see those "rare edge cases" all the time; they'll find on-disk bitmap indexes worth having, and, possibly, worth implementing. But to be sure, there used to be a lot of "burning interest" in on-disk bitmap indexes, and in-memory bitmap index scans have quenched many of the flames... -- "cbbrowne","@","cbbrowne.com" http://linuxfinances.info/info/advocacy.html ">WindowsNT will not accept fecal matter in its diet... it's that simple. I suppose that is a good ward against cannibalism." -- Nick Manka
Chris Browne <cbbrowne@acm.org> writes: > But to be sure, there used to be a lot of "burning interest" in > on-disk bitmap indexes, and in-memory bitmap index scans have quenched > many of the flames... Well, we had in-memory bitmaps already in 8.1, and the bitmap index work happened since that. I think the main argument for bitmap indexes is the potential to make the index smaller. A btree index requires a minimum of 16 bytes per entry (20 if MAXALIGN=8), whereas a bitmap index can in principle get down to a few bits per entry for a high-cardinality column value. So you could hope for a 10x smaller index and corresponding reduction in index search time. The fly in the ointment is that if the column value is so high cardinality as all that, it's questionable whether you want an index search at all rather than just seqscanning; and it's definite that the index access cost will be only a fraction of the heap access cost. So the prospects for actual net performance gain are a lot less than the index-size argument makes them look. There doubtless are gains on some workloads, but how much and on how wide a range of workloads is still an open question. regards, tom lane
On 06/25/07 09:58, Tom Lane wrote: [snip] > > The fly in the ointment is that if the column value is so high > cardinality as all that, it's questionable whether you want an index > search at all rather than just seqscanning; and it's definite that > the index access cost will be only a fraction of the heap access cost. > So the prospects for actual net performance gain are a lot less than > the index-size argument makes them look. Well they definitely are for data warehouses, in which many high-cardinality columns each have an index. Because of their small disk size, ANDing them is fast and winnows down the result set. That's the theory, of course. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good!