Re: Planner creating ineffective plans on LEFT OUTER joins

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Planner creating ineffective plans on LEFT OUTER joins
Date: 2008-06-27 04:12:55
Message-ID: 19977.1214539975@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Andres Freund <andres(at)anarazel(dot)de> writes:
>> The only way it could do that would be by interchanging the order of the
>> left and inner joins, ie (ab left join bc) join cd; which would change
>> the results.

> My knowledge about the implementation side of relational databases is quite
> limited, so my ideas may be quite flawed:
> The planner already recognizes that the left side of the join is quite small
> and the right side will be very big.
> Why cant it optimize the query the same way it does for a inner join, namely
> doing an index lookup on bc?
> I dont see the fundamental problem?

The only correct join order for this query is to join bc to cd, then
left-join ab to that result.

Now, if we make ab the outer side of a nestloop over the lower join's
result, it would indeed be theoretically possible to pass down the
value of ab.b through the lower join to the scan on bc and use it to
constrain the scan. The problem is that finding plans that work like
this would increase the planner's runtime exponentially, compared to
the current situation where we only check for indexscan constraints
coming from the immediate join partner.

(There might be some executor issues too, but I think those would be
relatively easily solved, compared to the plan search time problem.)

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Smith 2008-06-27 04:22:42 Re:
Previous Message Hiroshi Saito 2008-06-27 03:28:24 Re: MSVC 2003 compile error with pg8.3.3