pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_plan_advice fails when NestLoop outer side is Sort over FunctionScan
3+ messages / 2 participants
[nested] [flat]
* pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan
@ 2026-04-03 07:17 Andrei Lepikhov <lepihov@gmail.com>
2026-04-04 18:52 ` Re: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan Lukas Fittl <lukas@fittl.com>
0 siblings, 1 reply; 3+ messages in thread
From: Andrei Lepikhov @ 2026-04-03 07:17 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Robert Haas <robertmhaas@gmail.com>
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] over fresh PostgreSQL master.
[1] https://github.com/danolivo/pgdev/tree/bounded-left-join-outer
--
regards, Andrei Lepikhov,
pgEdge
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan
2026-04-03 07:17 pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan Andrei Lepikhov <lepihov@gmail.com>
@ 2026-04-04 18:52 ` Lukas Fittl <lukas@fittl.com>
2026-04-04 20:56 ` Re: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Lukas Fittl @ 2026-04-04 18:52 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Robert Haas <robertmhaas@gmail.com>
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].
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:
[application/x-patch] v1-0001-pg_plan_advice-Skip-Sort-nodes-under-all-join-typ.patch (2.6K, ../../CAP53Pkw2Lw0bavkCR-ygmaGACBjwmY3Voq0Yhwo9rVOBWxQuyw@mail.gmail.com/2-v1-0001-pg_plan_advice-Skip-Sort-nodes-under-all-join-typ.patch)
download | inline diff:
From 34438e9a6a4ebd0eae7612b5bde4679f7d0eae0a Mon Sep 17 00:00:00 2001
From: Lukas Fittl <lukas@fittl.com>
Date: Sat, 4 Apr 2026 11:41:03 -0700
Subject: [PATCH v1] pg_plan_advice: Skip Sort nodes under all join types
during analysis
With current core planning logic, only Merge Joins may get a Sort node
between them and the actual activity producing tuples underneath. However,
extensions may add Sort nodes under Nested Loops or Hash Joins as well.
Generalize the handling in pgpa_decompose_join that skips such nodes,
and ignore intermediate Sort nodes for any join type. This avoids a
"ERROR: plan node has no RTIs: 380" that was reported to occur otherwise.
Reported-by: Andrei Lepikhov <lepihov@gmail.com>
Author: Lukas Fittl <lukas@fittl.com>
Reviewed-by:
Discussion: https://www.postgresql.org/message-id/flat/78dd9572-7569-4025-984d-e07d7f381b6e%40gmail.com
---
contrib/pg_plan_advice/pgpa_join.c | 22 ++++++++++++----------
1 file changed, 12 insertions(+), 10 deletions(-)
diff --git a/contrib/pg_plan_advice/pgpa_join.c b/contrib/pg_plan_advice/pgpa_join.c
index 4610d02356f..57f4ff6795d 100644
--- a/contrib/pg_plan_advice/pgpa_join.c
+++ b/contrib/pg_plan_advice/pgpa_join.c
@@ -373,16 +373,6 @@ pgpa_decompose_join(pgpa_plan_walker_context *walker, Plan *plan,
else
strategy = JSTRAT_MERGE_JOIN_PLAIN;
- /*
- * For a MergeJoin, either the outer or the inner subplan, or
- * both, may have needed to be sorted; we must disregard any Sort
- * or IncrementalSort node to find the real inner or outer
- * subplan.
- */
- if (elidedouter == NULL && is_sorting_plan(outerplan))
- elidedouter = pgpa_descend_node(pstmt, &outerplan);
- if (elidedinner == NULL && is_sorting_plan(innerplan))
- elidedinner = pgpa_descend_node(pstmt, &innerplan);
break;
case T_NestLoop:
@@ -422,6 +412,18 @@ pgpa_decompose_join(pgpa_plan_walker_context *walker, Plan *plan,
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(plan));
}
+ /*
+ * For any join type, the planner may have inserted a Sort or
+ * IncrementalSort node on either side; we must disregard it to find the
+ * real inner or outer subplan. In practice this only happens with Merge
+ * Join in core today, but extensions can cause this for other join types
+ * to happen as well.
+ */
+ if (elidedouter == NULL && is_sorting_plan(outerplan))
+ elidedouter = pgpa_descend_node(pstmt, &outerplan);
+ if (elidedinner == NULL && is_sorting_plan(innerplan))
+ elidedinner = pgpa_descend_node(pstmt, &innerplan);
+
/*
* The planner may have decided to implement a semijoin by first making
* the nullable side of the plan unique, and then performing a normal join
--
2.47.1
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan
2026-04-03 07:17 pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan Andrei Lepikhov <lepihov@gmail.com>
2026-04-04 18:52 ` Re: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan Lukas Fittl <lukas@fittl.com>
@ 2026-04-04 20:56 ` Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andrei Lepikhov @ 2026-04-04 20:56 UTC (permalink / raw)
To: Lukas Fittl <lukas@fittl.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Robert Haas <robertmhaas@gmail.com>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-04 20:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-03 07:17 pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan Andrei Lepikhov <lepihov@gmail.com>
2026-04-04 18:52 ` Lukas Fittl <lukas@fittl.com>
2026-04-04 20:56 ` Andrei Lepikhov <lepihov@gmail.com>
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