Andres Freund <andres@anarazel.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