In make_outerjoininfo, I think we can additionally check a property
that's needed to apply OJ identity 3: the lower OJ in the RHS cannot be
a member of inner_join_rels because we do not try to commute with any of
lower inner joins.
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -1593,7 +1593,8 @@ make_outerjoininfo(PlannerInfo *root,
}
else if (jointype == JOIN_LEFT &&
otherinfo->jointype == JOIN_LEFT &&
- otherinfo->lhs_strict)
+ otherinfo->lhs_strict &&
+ !bms_is_member(otherinfo->ojrelid, inner_join_rels))
{
/* Identity 3 applies, so remove the ordering restriction */
min_righthand = bms_del_member(min_righthand,
This check will help to avoid bogus commute_xxx bits in some cases, such
as in query
explain (costs off)
select * from a left join
(b left join c on b.i = c.i inner join d on true)
on a.i = b.i;
It will help us know that the b/c join and the join of a cannot commute
and thus save us from generating cloned clauses for 'b.i = c.i'. Plus,
it is very cheap. So I think it's worth doing. Any thoughts?
Thanks
Richard