From: Mark Kirkwood <markir@paradise.net.nz>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Pål Stenslet <paal.stenslet@exie.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Should Oracle outperform PostgreSQL on a complex
Date: Sat, 17 Dec 2005 18:21:50 +1300
Message-ID: <43A3A06E.2090900@paradise.net.nz> (raw)
In-Reply-To: <200512170428.jBH4S2W23715@candle.pha.pa.us>
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... :-)
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: markir@paradise.net.nz, pgman@candle.pha.pa.us, simon@2ndquadrant.com, paal.stenslet@exie.com
Subject: Re: Should Oracle outperform PostgreSQL on a complex
In-Reply-To: <43A3A06E.2090900@paradise.net.nz>
* 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