Subject: Re: Should Oracle outperform PostgreSQL on a complex
From: Simon Riggs <simon@2ndquadrant.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>,
	=?ISO-8859-1?Q?P=E5l?= Stenslet <paal.stenslet@exie.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <43A4E08D.2050708@paradise.net.nz>
References: <200512170428.jBH4S2W23715@candle.pha.pa.us>
	<1134810953.2964.117.camel@localhost.localdomain>
	<8627.1134843222@sss.pgh.pa.us>  <43A4E08D.2050708@paradise.net.nz>
Content-Type: text/plain
Date: Sun, 18 Dec 2005 17:53:27 +0000
Message-Id: <1134928407.2964.177.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Sun, 2005-12-18 at 17:07 +1300, Mark Kirkwood wrote:
> Tom Lane wrote:
> 
> >>2. transform joins into subselects, then return subselect rows via an
> >>index bitmap. Joins are performed via a bitmap addition process.
> 
> Looks like 8.1 pretty much does this right now:

Good analysis.

8.1 doesn't do:
- the transforms sufficiently well (you just performed them manually)
- doesn't AND together multiple bitmaps to assist with N-way joins

Those aren't criticisms, just observations. Pal's original example was a
9-dimension join, so I think PostgreSQL does very well on making that
run in 30 seconds. That's a complex example and I think upholds just how
good things are right now. 

Anyway, back to the starting point: IMHO there is an additional
optimisation that can be performed to somehow speed up Single large
table-many small table joins. And we have some clues as to how we might
do that.

Best Regards, Simon Riggs