Paal, > 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. Can you provide the schema and queries here please? > 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. Postgres currently lacks a bitmap index, though 8.1 has a bitmap "predicate merge" in 8.1 We have recently completed an Oracle-like bitmap index that we will contribute shortly to Postgres and it performs very similarly to the "other commercial databases" version. > 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. This sounds like a very good case for bitmap index, please forward the schema and queries. > 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.) See above. - Luke
