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