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
similarlyto 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