pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan
Hi,
While testing the optimiser extension that extends planner's scope by
pre-sorted outer paths I found that current master hit a crash in the
test_plan_advice TAP test
(src/test/modules/test_plan_advice/t/001_replan_regress.pl):
ERROR: plan node has no RTIs: 380
The error originates in pgpa_scan.c:
if (within_join_problem)
elog(ERROR, "plan node has no RTIs: %d", (int) nodeTag(plan));
It is triggered by the pg_lsn regression test query:
SELECT DISTINCT (i || '/' || j)::pg_lsn f
FROM generate_series(1, 10) i,
generate_series(1, 10) j,
generate_series(1, 5) k
WHERE i <= 10 AND j > 0 AND j <= 10
ORDER BY f;
I have the following query plan:
Unique
-> Nested Loop
-> Sort
-> Nested Loop
-> Function Scan on generate_series j
Filter: ((j > 0) AND (j <= 10))
-> Function Scan on generate_series i
Filter: (i <= 10)
-> Function Scan on generate_series k
The assumption baked into pg_plan_advice is that when walking a join
subtree (within_join_problem = true), every leaf node will be a
base-relation scan with RTIs. Before now, that assumption was always
valid: no PostgreSQL core code placed a Sort node between a NestLoop and
a FunctionScan. But extensions might want to employ more sorted paths to
find better plan - it might happen in complex analytics tasks. My case
is presorted outer side of a LEFT JOIN in case of ORDER-BY .. LIMIT
present on the outer table only.
I'm not aware about how this module is designed, but I think it should
not unconditionally error. A Sort injected between a join and a
non-relation scan leaf is a legitimate plan node that the walker should
handle gracefully in case it is loaded with other extensions.
Just for the reproduction, see the branch [1]https://github.com/danolivo/pgdev/tree/bounded-left-join-outer over fresh PostgreSQL master.
[1]: https://github.com/danolivo/pgdev/tree/bounded-left-join-outer
--
regards, Andrei Lepikhov,
pgEdge
Hi Andrei,
On Fri, Apr 3, 2026 at 12:17 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
Hi,
While testing the optimiser extension that extends planner's scope by
pre-sorted outer paths I found that current master hit a crash in the
test_plan_advice TAP test
(src/test/modules/test_plan_advice/t/001_replan_regress.pl):ERROR: plan node has no RTIs: 380
Thanks for the bug report and reproducer!
it seems to me this is caused by the join analysis tree walker in
pgpa_join.c / pgpa_decompose_join being a bit too specific to what the
core planner will produce, i.e. it only assumes Merge Joins will have
Sort nodes directly underneath them.
I personally don't see harm in broadening the logic here to support
Nested Loop Joins and Hash Joins as well.
See attached a patch that addresses this, and passes cleanly for me on
a modified version of your branch [0]https://github.com/lfittl/postgres/tree/bounded-left-join-outer-with-fix.
Btw, is your extension available somewhere? That could help verify
that the extension also works as expected with the fix.
Thanks,
Lukas
[0]: https://github.com/lfittl/postgres/tree/bounded-left-join-outer-with-fix
--
Lukas Fittl
Attachments:
v1-0001-pg_plan_advice-Skip-Sort-nodes-under-all-join-typ.patchapplication/x-patch; name=v1-0001-pg_plan_advice-Skip-Sort-nodes-under-all-join-typ.patchDownload+12-11
On 4/4/26 20:52, Lukas Fittl wrote:
Hi Andrei,
it seems to me this is caused by the join analysis tree walker in
pgpa_join.c / pgpa_decompose_join being a bit too specific to what the
core planner will produce, i.e. it only assumes Merge Joins will have
Sort nodes directly underneath them.
My initial concern was about the design of the pg_plan_advice extension
in general. It seems flawed if it must follow the core plan-building
logic. Explore pg_hint_plan - their general concept is just to increase
the probability of a template subtree to maximum, not to assume that
some constructions are possible or not.
Btw, is your extension available somewhere? That could help verify
that the extension also works as expected with the fix.
This is the stage of core patch development. If the community rejects
the feature, I will convert it into an extension module (on request). -
It is too expensive to support each optimisation when you do it 'just
for fun'.
--
regards, Andrei Lepikhov,
pgEdge