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
Btreeindexes. Also, the new bitmap index has a fast compressed bitmap storage and access that make the AND operations
speedywith 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
experiencefrom 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
canget it in Bizgres CVS now.
- Luke