Thread

Re: Use exact nullingrels matches for NestLoopParams

Richard Guo <guofenglinux@gmail.com> — 2025-11-17T07:28:45Z

On Tue, Nov 11, 2025 at 3:58 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Here is a patch that makes that change.  It also removes the
> NRM_SUBSET enum value, along with all remaining checks for it, since
> it is no longer used.

With the changes in 0001, we should now be able to use exact
nullingrels matches in all cases when fixing up expressions of
upper-level plan nodes that are not joins.  Therefore, I think we can
remove the nrm_match parameter from fix_upper_expr(), along with the
corresponding field in fix_upper_expr_context.  I've done that in 0002
(which will be squashed into 0001 when committed).

> (I'm wondering if we have a way to identify the nullingrels added by
> an outer join when fixing up its targetlist and qpqual.  If so, we
> might be able to switch to exact nullingrels matches for them and
> thereby get rid of NRM_SUPERSET too.)

After looking into this, I think it's quite challenging to determine
the effects of an outer join when the join has been commuted with
another one per outer join identity 3.  In such cases, the Vars/PHVs
in the join's targetlist and qpqual should have the same nullingrels
they would have if the two joins had been done in syntactic order.
Unfortunately, in setrefs.c we do not have enough information (such as
the relevant SpecialJoinInfos) to determine that.

For example, consider when we transform

    (A leftjoin B on (Pab)) leftjoin C on (Pbc)

to

    A leftjoin (B leftjoin C on (Pbc)) on (Pab)

For the now-upper A/B join, whose ojrelids is {3, 5}, the Vars from B
in its targetlist and qpqual have nullingrels = {3}, and the Vars from
C have nullingrels = {5}.  In its inner plan's targetlist, however,
both B and C Vars have empty nullingrels.  The problem is that we have
no reliable way in setrefs.c to determine which of the join's ojrelids
should apply to which Vars.

On the other hand, if we perform the transformation in the reverse
order, then for the B/C join whose ojrelids is {4}, the C Vars in its
targetlist and qpqual have nullingrels = {4, 5}, while in its inner
plan's targetlist the C Vars have empty nullingrels.  We have no
reliable way to determine that {5} should also be applied to the C
Vars.

However, we can tighten the check somewhat.  Currently, we check
whether the jointype is JOIN_INNER and use NRM_SUPERSET if it is not.
We can improve this by checking whether the Join node has non-empty
ojrelids and using NRM_SUPERSET only in that case.  This allows us to
perform exact matches in more situations, such as the pushed-down B/C
join in the first case.

0003 implements this.  To support it, we record the outer-join relids
in Join plan nodes (the related changes for adding ojrelids are
adapted from the patch in [1]).  This may seem like overengineering to
have a new field just for this check, but the field also improves
EXPLAIN (RANGE_TABLE) output by showing which outer-join relids are
completed by each Join plan node.  I expect that we will find other
uses for the ojrelids information in the future.

[1] https://postgr.es/m/3200728.1758662857@sss.pgh.pa.us

- Richard