Andres Freund <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers