Thread: Tighten a little bit the computation of potential commutator pairs

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