Re: Should Oracle outperform PostgreSQL on a complex

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Pål Stenslet <paal(dot)stenslet(at)exie(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Should Oracle outperform PostgreSQL on a complex
Date: 2005-12-17 04:28:02
Message-ID: 200512170428.jBH4S2W23715@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


How are star joins different from what we do now?

---------------------------------------------------------------------------

Simon Riggs wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Kirkwood 2005-12-17 05:21:50 Re: Should Oracle outperform PostgreSQL on a complex
Previous Message Bruce Momjian 2005-12-17 02:44:19 Re: How much expensive are row level statistics?