How are star joins different from what we do now? --------------------------------------------------------------------------- Simon Riggs wrote: > On Thu, 2005-12-08 at 12:26 +0100, P?l Stenslet wrote: > > I'm currently benchmarking several RDBMSs with respect to analytical > > query performance on medium-sized multidimensional data sets. The data > > set contains 30,000,000 fact rows evenly distributed in a > > multidimensional space of 9 hierarchical dimensions. Each dimension > > has 8000 members. > > > I have established similar conditions for the query in PostgreSQL, and > > it runs in about 30 seconds. Again the CPU utilization is high with no > > noticable I/O. The query plan is of course very different from that of > > Oracle, since PostgreSQL lacks the bitmap index merge operation. It > > narrows down the result one dimension at a time, using the > > single-column indexes provided. It is not an option for us to provide > > multi-column indexes tailored to the specific query, since we want > > full freedom as to which dimensions each query will use. > > > Are these the results we should expect when comparing PostgreSQL to > > Oracle for such queries, or are there special optimization options for > > PostgreSQL that we may have overlooked? (I wouldn't be suprised if > > there are, since I spent at least 2 full days trying to trigger the > > star optimization magic in my Oracle installation.) > > 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. > > 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!) > > [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.] > > Best Regards, Simon Riggs > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Bruce Momjian | http://candle.pha.pa.us pgman@xxxxxxxxxxxxxxxx | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
