From: Simon Riggs <simon@2ndquadrant.com>
To: Pål Stenslet <paal.stenslet@exie.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Should Oracle outperform PostgreSQL on a complex
Date: Tue, 13 Dec 2005 23:28:49 +0000
Message-ID: <1134516529.27873.133.camel@localhost.localdomain> (raw)
In-Reply-To: <43D5D5C1F10C384B9AB4AE34A0510EC12B2B66@e1.exie.no>
References: <43D5D5C1F10C384B9AB4AE34A0510EC12B2B66@e1.exie.no>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, paal.stenslet@exie.com
Subject: Re: Should Oracle outperform PostgreSQL on a complex
In-Reply-To: <1134516529.27873.133.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox