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.
The test query selects about one half of the members from each dimension, and calculates fact sums grouped by 5 high-level members from each dimensional hierarchy. (There are actually some additional complications that makes the query end up listing 20 table aliases in the from-clause, 18 of which are aliases for 2 physical tables.)
On Oracle the query runs in less than 3 seconds. All steps have been taken to ensure that Oracle will apply star schema optimization to the query (e.g. having loads of single-column bitmap indexes). The query plan reveals that a bitmap merge takes place before fact lookup.
There's a lot of RAM available, and series of queries have been run in advance to make sure the required data resides in the cache. This is confirmed by a very high CPU utilization and virtually no I/O during the query execution.
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.)
