Message-ID: <7093fa9e-6451-42f7-8956-4f9310545a89@gmail.com>
Date: Sat, 4 Apr 2026 22:56:42 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_plan_advice fails when NestLoop outer side is Sort over
 FunctionScan
To: Lukas Fittl <lukas@fittl.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>,
 Robert Haas <robertmhaas@gmail.com>
References: <78dd9572-7569-4025-984d-e07d7f381b6e@gmail.com>
 <CAP53Pkw2Lw0bavkCR-ygmaGACBjwmY3Voq0Yhwo9rVOBWxQuyw@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CAP53Pkw2Lw0bavkCR-ygmaGACBjwmY3Voq0Yhwo9rVOBWxQuyw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/7093fa9e-6451-42f7-8956-4f9310545a89%40gmail.com>
Precedence: bulk

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