Re: plan shape work

On Thu, Sep 11, 2025 at 2:19 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I do not think this is correct, or at least it's not the most useful
> way to think about it. As I stated earlier, each join plan node that
> is doing a non-inner join has a unique corresponding outer-join RTE
> that describes what it's doing. The point that you are making is
> that if we've applied identity 3 to swap the order of two outer
> joins, then we can't claim that the output of the lower plan node is a
> fully-correct representation of the semantics of the join that it's
> doing: there may be values that should have gone to NULL but won't
> until after the upper plan node processes the rows.
>
> If we're going to attach more labeling to the plan nodes, I'd
> prefer to do what I suggested and label the nodes with the specific
> outer join that they think they are implementing. With Richard's
> proposal it will remain impossible to tell which node is doing what.

Conceptually, I prefer your idea of one RTI per join node, but I don't
understand how to make it work. Let's say that, as in Richard's
example, the query is written as (A leftjoin B on (Pab)) leftjoin C on
(Pbc) but we end up with a plan tree that looks like this:

Something Join (RTIs: 1 2 3 4 5)
-> Scan on A (RTI: 1)
-> Whatever Join (RTIs: 2 4)
-> Scan on B (RTI: 2)
-> Scan on C (RTI: 4)

RTE 3 is the join between A and B, and RTI 5 is the join between A-B
and C. It makes plenty of sense to associate RTI 5 with the Something
Join, so your model seems to require us to associate RTI 3 with the
Whatever Join, because there's no place else for it to go. That seems
to create two problems.

First, RTI 3 is for an A-B join, and the Whatever Join is for a B-C
join, and it sounds wrong to associate an RTI with a join when not all
of the rels being joined are present at that level. Can we really say
that the Whatever Join is implementing RTI 3 given that RTI 3 includes
A?

Second, even ignoring that problem, if we now try to assert that the
RTIs of a joinrel are the union of the RTIs we see in the plan tree,
the assertion is going to fail, because now the Whatever Join sees
RTIs 2 and 4 through its children and RTI 3 through its own ojrelid,
but the joinrel's RTIs are {2,4}, not {2,3,4}.

--
Robert Haas
EDB: http://www.enterprisedb.com