Message-ID: <78dd9572-7569-4025-984d-e07d7f381b6e@gmail.com>
Date: Fri, 3 Apr 2026 09:17:08 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>,
 Robert Haas <robertmhaas@gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
Subject: pg_plan_advice fails when NestLoop outer side is Sort over
 FunctionScan
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/78dd9572-7569-4025-984d-e07d7f381b6e%40gmail.com>
Precedence: bulk

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