From: Tom Lane <tgl@sss.pgh.pa.us>
To: chester c young <chestercyoung@yahoo.com>
Cc: sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: do unneeded outer joins cost?
Date: Fri, 05 Jun 2009 11:25:37 -0400
Message-ID: <4694.1244215537@sss.pgh.pa.us> (raw)
In-Reply-To: <886808.30289.qm@web54302.mail.re2.yahoo.com>
References: <886808.30289.qm@web54302.mail.re2.yahoo.com>
chester c young <chestercyoung@yahoo.com> writes:
> for example:
> select t1.*
> from t1
> left join t2 on( <expensive condition> );
> since t2 is not used in the result nor is it used in determining the result, is the <expensive condition> to left join t2 used?
It's not correct that the t2 join is irrelevant to the result. If any
row of t1 joins to more than one row of t2 then the join would affect
the result.
In some cases it is possible to prove that no row of t1 can join to more
than one row of t2, in which case the join to t2 could be dropped.
Postgres does not currently implement such an optimization (although
I believe someone is working on it for 8.5). It seems likely though
that an "expensive" join condition would be too complex to be amenable
to such a proof anyway.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, chestercyoung@yahoo.com
Subject: Re: do unneeded outer joins cost?
In-Reply-To: <4694.1244215537@sss.pgh.pa.us>
* 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