Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 966A063398D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri,  5 Jun 2009 12:25:39 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 65559-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri,  5 Jun 2009 12:25:38 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 24EEB6335A2
	for <pgsql-sql@postgresql.org>; Fri,  5 Jun 2009 12:25:38 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n55FPbFv004695;
	Fri, 5 Jun 2009 11:25:37 -0400 (EDT)
To: chester c young <chestercyoung@yahoo.com>
cc: sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: do unneeded outer joins cost? 
In-reply-to: <886808.30289.qm@web54302.mail.re2.yahoo.com> 
References: <886808.30289.qm@web54302.mail.re2.yahoo.com>
Comments: In-reply-to chester c young <chestercyoung@yahoo.com>
	message dated "Fri, 05 Jun 2009 08:01:51 -0700"
Date: Fri, 05 Jun 2009 11:25:37 -0400
Message-ID: <4694.1244215537@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.105 tagged_above=0 required=5 tests=AWL=0.105
X-Spam-Level: 
X-Archive-Number: 200906/24
X-Sequence-Number: 32629

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