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
