Re: plan shape work

On Wed, Sep 10, 2025 at 3:16 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> Hmm, this isn't quite what I had in mind. What I was thinking is that
> the outer join relids included in joinrel->relids can also be found
> from its outer or inner. For example, consider a query like:
>
> (A leftjoin B on (Pab)) leftjoin C on (Pbc)
>
> For the join with joinrel->relids being {1, 2, 3, 4, 5}, {1, 2, 3}
> comes from the outer side, {4} comes from the inner side, and {5} is
> the outer join being calculated at this join. So the Assert I
> proposed earlier becomes:
>
> {1, 2, 3} U {4} U {5} == {1, 2, 3, 4, 5}

Makes sense.

> However, if we have transformed it to:
>
> A leftjoin (B leftjoin C on (Pbc)) on (Pab)
>
> For this same join, {1} comes from the outer side, {2, 4} comes from
> the inner side, and {3, 5} are the outer joins being calculated at
> this join. So the Assert becomes:
>
> {1} U {2, 4} U {3, 5} == {1, 2, 3, 4, 5}

Hmm. As I understood it, Tom was proposing a single, optional ojrelid
for each join, with all outer joins having a value and no inner join
having one. What you are proposing seems to be a very similar concept,
but as I understand it, you're saying that each join would carry a
*set* of ojrelids, which might be empty and might contain more than
one element.

Experimenting with this example, it looks like you're correct and Tom,
or my understanding of Tom, is incorrect. What I see is that I get a
structure like this:

{MERGEPATH
:parent_relids (b 1 2 3 4 5)
:jpath.outerjoinpath
{PATH
:parent_relids (b 1)
:jpath.innerjoinpath
{MERGEPATH
:parent_relids (b 2 4)
:jpath.outerjoinpath
{PATH
:parent_relids (b 2)
}
:jpath.innerjoinpath
{PATH
:parent_relids (b 4)
}
}
}

So, for the assertion to pass, the more deeply nested merge join would
need to have ojrelids = {} and the less-deeply nested one would need
ojrelids={3,5}.

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