The following is a remark added to /src/backend/optimizer/README by commit 8703059c6b55c427100e00a09f66534b6ccbfaa1, and IIUC, I think "LHS" in the last sentence "We prevent that by forcing the min LHS for the upper join to include B." should be "RHS".
The use of minimum Relid sets has some pitfalls; consider a query like A leftjoin (B leftjoin (C innerjoin D) on (Pbcd)) on Pa where Pa doesn't mention B/C/D at all. In this case a naive computation would give the upper leftjoin's min LHS as {A} and min RHS as {C,D} (since we know that the innerjoin can't associate out of the leftjoin's RHS, and enforce that by including its relids in the leftjoin's min RHS). And the lower leftjoin has min LHS of {B} and min RHS of {C,D}. Given such information, join_is_legal would think it's okay to associate the upper join into the lower join's RHS, transforming the query to B leftjoin (A leftjoin (C innerjoin D) on Pa) on (Pbcd) which yields totally wrong answers. We prevent that by forcing the min LHS for the upper join to include B. Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers