Re: pg_plan_advice fails when NestLoop outer side is Sort over FunctionScan

Hi Andrei,

On Fri, Apr 3, 2026 at 12:17 AM Andrei Lepikhov <lepihov(at)gmail(dot)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