From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: TB-sized databases
Date: Mon, 17 Mar 2008 22:48:35 -0700
Message-ID: <47DF57B3.9090206@cheapcomplexdevices.com> (raw)
In-Reply-To: <4005.1205800130@sss.pgh.pa.us>
References: <200803172347.m2HNlCD29489@momjian.us>
<4005.1205800130@sss.pgh.pa.us>
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> I have _not_ added a TODO for this item. Let me know if one is needed.
>
> Please do, I think it's an open issue.
>
> * Consider Cartesian joins when both relations are needed to form an
> indexscan qualification for a third relation
>
Would another possible condition for considering
Cartesian joins be be:
* Consider Cartesian joins when a unique constraint can prove
that at most one row will be pulled from one of the tables
that would be part of this join?
In the couple cases where this happened to me it was
in queries on a textbook star schema like this:
select * from fact
join dim1 using (dim1_id)
join dim2 using (dim2_id)
where dim1.value = 'something'
and dim2.valuex = 'somethingelse'
and dim2.valuey = 'more';
and looking up all the IDs before hitting the huge
fact table. Often in these cases the where clause
on the dimension tables are on values with a unique
constraint.
If I understand right - if the constraint can prove
it'll return at most 1 row - that means the cartesian
join is provably safe from blowing up.
Not sure if that's redundant with the condition you
mentioned, or if it's yet a separate condition where
we might also want to consider cartesian joins.
Ron M
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: rm_pg@cheapcomplexdevices.com, tgl@sss.pgh.pa.us, bruce@momjian.us
Subject: Re: TB-sized databases
In-Reply-To: <47DF57B3.9090206@cheapcomplexdevices.com>
* 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