Re: plan shape work

On Wed, Sep 10, 2025 at 3:16 AM Richard Guo <guofenglinux@gmail.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