Re: Should Oracle outperform PostgreSQL on a complex

Simon,

> Yes, I'd expect something like this right now in 8.1; the
> numbers stack up to PostgreSQL doing equivalent join speeds,
> but w/o star join.

I do expect a significant improvement from 8.1 using the new bitmap index because there is no need to scan the full Btree indexes. Also, the new bitmap index has a fast compressed bitmap storage and access that make the AND operations speedy with no loss like the bitmap scan lossy compression, which may enhance the selectivity on very large datasets.

> You've confused the issue here since:
> - Oracle performs star joins using a bit map index transform.
> It is the star join that is the important bit here, not the
> just the bitmap part.
> - PostgreSQL does actually provide bitmap index merge, but
> not star join
> (YET!)

Yes, that is true, a star join optimization may be a big deal, I'm not sure. I've certainly talked to people with that experience from RedBrick, Teradata and Oracle.

> [I've looked into this, but there seem to be multiple patent
> claims covering various aspects of this technique, yet at
> least other 3 vendors manage to achieve this. So far I've not
> dug too deeply, but I understand the optimizations we'd need
> to perform in PostgreSQL to do this.]

Hmm - I bet there's a way.

You should test the new bitmap index in Bizgres - it rocks hard. We're prepping a Postgres 8.1.1 patch soon, but you can get it in Bizgres CVS now.

- Luke