Horton
Menu

Re: pg_plan_advice

Andrei Lepikhov <lepihov@gmail.com>

View thread
From: Andrei Lepikhov <lepihov@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alexander Lakhin <exclusion@gmail.com>, Lukas Fittl <lukas@fittl.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-04-05T07:57:13Z
Lists: 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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_plan_advice: Fix another unique-semijoin bug.

  2. pg_plan_advice: Export feedback-related definitions.

  3. pg_plan_advice: Fix a bug when a subquery is pruned away entirely.

  4. pg_plan_advice: Add alternatives test to Makefile.

  5. pg_plan_advice: Handle non-repeatable TABLESAMPLE scans.

  6. pg_stash_advice: Allow stashed advice to be persisted to disk.

  7. Add pg_stash_advice contrib module.

  8. pg_plan_advice: Avoid assertion failure with partitionwise aggregate.

  9. pg_plan_advice: Invent DO_NOT_SCAN(relation_identifier).

  10. Add an alternative_plan_name field to PlannerInfo.

  11. pg_plan_advice: Refactor to invent pgpa_planner_info

  12. Respect disabled_nodes in fix_alternative_subplan.

  13. get_memoize_path: Don't exit quickly when PGS_NESTLOOP_PLAIN is unset.

  14. test_plan_advice: Set TAP test priority 50 in meson.build.

  15. pg_plan_advice: Avoid a crash under GEQO.

  16. Test pg_plan_advice using a new test_plan_advice module.

  17. pg_plan_advice: Always install pg_plan_advice.h, and in the right place

  18. pg_plan_advice: Fix failures to accept identifier keywords.

  19. Add pg_plan_advice contrib module.

  20. Allow extensions to mark an individual index as disabled.

  21. Replace get_relation_info_hook with build_simple_rel_hook.

  22. Store information about Append node consolidation in the final plan.

  23. Store information about elided nodes in the final plan.

  24. Store information about range-table flattening in the final plan.

  25. Pass cursorOptions to planner_setup_hook.

  26. Fix PGS_CONSIDER_NONPARTIAL interaction with Materialize nodes.

  27. Fix mistakes in commit 4020b370f214315b8c10430301898ac21658143f

  28. Allow for plugin control over path generation strategies.

  29. Update some comments for fasthash

  30. Allow passing a pointer to GetNamedDSMSegment()'s init callback.

  31. Don't reset the pathlist of partitioned joinrels.

  32. Treat number of disabled nodes in a path as a separate cost metric.