Date: Sat, 17 Dec 2005 18:21:50 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Should Oracle outperform PostgreSQL on a complex
In-reply-to: <200512170428.jBH4S2W23715@candle.pha.pa.us>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Simon Riggs <simon@2ndquadrant.com>,
	=?ISO-8859-1?Q?P=E5l_Stenslet?= <paal.stenslet@exie.com>,
	pgsql-performance@postgresql.org
Message-id: <43A3A06E.2090900@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <200512170428.jBH4S2W23715@candle.pha.pa.us>

Bruce Momjian wrote:
> How are star joins different from what we do now?
> 
> ---------------------------------------------------------------------------
> 

Recall that a "star" query with n tables means a query where there are 
(n - 1) supposedly small tables (dimensions) and 1 large table (fact) - 
which has foreign keys to each dimension.

As I understand it, the classic "tar join" is planned like this:

1) The result of the restriction clauses on each of the (small) 
dimension tables is computed.
2) The cartesian product of all the results of 1) is formed.
3) The fact (big) table is joined to the pseudo relation formed by 2).

 From what I have seen most vendor implementations do not (always) 
perform the full cartesion product of the dimensions, but do some of 
them, join to the fact, then join to the remaining dimensions afterwards.

There is another join strategy called the "star transformation" where 
some of the dimension joins get rewritten as subqueries, then the above 
method is used again! This tends to be useful when the cartesion 
products would be stupidly large (e.g. "sparse" facts, or few 
restriction clauses)

regards

Mark

P.s : Luke or Simon might have a better definition... but thought I'd 
chuck in my 2c... :-)