Re: pg_plan_advice - Mailing list pgsql-hackers

On 5/4/26 00:52, Robert Haas wrote:
> On Sat, Apr 4, 2026 at 5:02 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
>> That’s exactly what concerns me. I see it as a potential design flaw if
>> the extension has to make assumptions about possible plan configurations.
>> I’m not sure how it works in detail, of course. However, when I designed
>> Postgres replanning in the past, and made similar core changes to what
>> you’ve done for pg_plan_advice, this kind of problem couldn’t have
>> happened. So, I think it’s worth questioning the current approach and
>> looking for other options.
> 
> I mean, any plan stability feature is intrinsically tied to a
> particular planner. Nobody thinks you can use Aurora Postgres's Query
> Plan Management feature with MySQL or DB2 or Oracle. Those products

I don’t expect any Postgres extension to work in DB2.

These optimisations are simple. Here, I provided the optimiser with one 
extra path that it skipped itself just to reduce computational overhead 
- nice in the general case, but not ok in analytics. This extension of 
planning scope allowed the optimiser to build JOIN over the Sort 
operator, which didn’t change the main logic at all. I followed the 
usual cost-based model and used add_path.

Another optimisation improves Memoize so it can run on top of SubPlan 
when the cost model predicts many repeated parameter values. One more 
extension uses MergeJoin estimation on the required values of its inputs 
to determine how many tuples are needed from each input, which adds 
kinda 'soft' LIMIT emerged from the plan structure ... The Append node 
serves as the backbone of any partitioning or sharding setup, but 
contributors often overlook it, and we use multiple extra optimisations 
here too.

There’s a lot to say about branched out-of-core optimisations 
infrastructure, but it’s clear that supporting analytical workloads 
means adding extra features. Developers usually stick to standard 
Postgres practices, cost model and routines providing the planner with 
alternatives without forcing any 'magical' paths. So, they expect 
built-in extensions not to interfere with their code by design.

Looking back at the pg_plan_advice development cycle, I don’t see many 
discussions about the design. It seems unusual given how complex the 
planner's structure is. It makes sense to follow the typical way and let 
it serve out of the contrib for some time and see if it works well.

Introducing such a module into the core would effectively cancel 
alternative solutions, as seen with PGSS. Therefore, it is important to 
ensure the code is well-designed before proceeding. Do you agree?

-- 
regards, Andrei Lepikhov,
pgEdge