From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
Cc: exclusion@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18953: Planner fails to build plan for complex query with LATERAL references
Date: Thu, 12 Jun 2025 17:50:12 -0400
Message-ID: <1268645.1749765012@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMbWs49XTfPD9OCw9xWsRtbiNYgQ-Nau_hHy9UbbGP-w2V=ANQ@mail.gmail.com>
References: <18953-1c9883a9d4afeb30@postgresql.org>
<CAMbWs49XTfPD9OCw9xWsRtbiNYgQ-Nau_hHy9UbbGP-w2V=ANQ@mail.gmail.com>
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 the
lower join node using values passed down from the upper relation along with
values from the join's own outer relation. However, nodeNestloop.c only
supports simple Vars not arbitrary expressions as nestloop parameters.
createplan.c is also a few bricks shy of being able to handle such cases;
it misplaces the PlaceHolderVar parameters in the plan tree, which is why
the visible symptoms of this bug are "plan should not reference subplan's
variable" and "failed to assign all NestLoopParams to plan nodes" planner
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 exception.
Furthermore, adding complexity to nodeNestloop.c would create a run-time
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, guofenglinux@gmail.com, 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: <1268645.1749765012@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox