Re: Should Oracle outperform PostgreSQL on a complex

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux