Re: 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(at)gmail(dot)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(at)purefiction(dot)net> writes:
> On 6/20/07, Christan Josefsson <cjosefsson(at)gmail(dot)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(at)gmail(dot)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(at)commandprompt(dot)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(at)purefiction(dot)net>:
>
> On 6/20/07, Joshua D. Drake <jd(at)commandprompt(dot)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(at)crankycanuck(dot)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(at)users(dot)sourceforge(dot)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(at)gmail(dot)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(at)acm(dot)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!