Thread: do unneeded outer joins cost?

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
t2used?
 

(the question goes as to how much generated sql needs to be cleaned to be efficient)


     

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
joint2 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