public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: pgsql-bugs <[email protected]>
To: Robert Haas <[email protected]>
Subject: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan
Date: Fri, 3 Apr 2026 09:17:08 +0200
Message-ID: <[email protected]> (raw)

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

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox