From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18953: Planner fails to build plan for complex query with
 LATERAL references
In-reply-to: 
 <CAMbWs49XTfPD9OCw9xWsRtbiNYgQ-Nau_hHy9UbbGP-w2V=ANQ@mail.gmail.com>
References: <18953-1c9883a9d4afeb30@postgresql.org>
 <CAMbWs49XTfPD9OCw9xWsRtbiNYgQ-Nau_hHy9UbbGP-w2V=ANQ@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Wed, 11 Jun 2025 17:33:54 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1268644.1749765012.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 12 Jun 2025 17:50:12 -0400
Message-ID: <1268645.1749765012@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1268645.1749765012%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> Thanks for the report.  Here's a simplified repro.
> ...
> In this query, the join between t3 and s is placed into a separate
> join sub-problem due to the from_collapse_limit.  This join is deemed
> not legal by join_is_legal(), as have_dangerous_phv() thinks the PHV
> could pose a hazard as described in that function's comment.  As a
> result, no join could be built for this sub-problem.

Bleah.

> No idea how to fix this though.  Any thoughts?

My thought is that have_dangerous_phv() was never more than a
quick-n-dirty kludge, and what we really ought to do is remove it.
That means cleaning up the technical debt mentioned in 85e5e222b:

    In principle we could allow such a PlaceHolderVar to be evaluated at t=
he
    lower join node using values passed down from the upper relation along=
 with
    values from the join's own outer relation.  However, nodeNestloop.c on=
ly
    supports simple Vars not arbitrary expressions as nestloop parameters.
    createplan.c is also a few bricks shy of being able to handle such cas=
es;
    it misplaces the PlaceHolderVar parameters in the plan tree, which is =
why
    the visible symptoms of this bug are "plan should not reference subpla=
n's
    variable" and "failed to assign all NestLoopParams to plan nodes" plan=
ner
    errors.

    Adding the necessary complexity to make this work doesn't seem like it
    would be repaid in significantly better plans, because in cases where =
such
    a PHV exists, there is probably a corresponding join order constraint =
that
    would allow a good plan to be found without using the star-schema exce=
ption.
    Furthermore, adding complexity to nodeNestloop.c would create a run-ti=
me
    penalty even for plans where this whole consideration is irrelevant.
    So let's just reject such paths instead.

I think that the argument about executor complexity might be a red
herring: if we can get the PHV to be evaluated in the tlist of the
nestloop's outer relation, then the reference to it will still just be
an outer Var in the NestLoopParam structure.  I'm still poking at what
we'd have to do to the planner to get that to happen, but my initial
impression is that it might not be very complicated after all.

			regards, tom lane