Обсуждение: 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] 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].

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

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