Horton
Menu

Re: pg_plan_advice

Michael Paquier <michael@paquier.xyz>

View thread
From: Michael Paquier <michael@paquier.xyz>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>, 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-06-10T00:21:25Z
Lists: pgsql-hackers 
On Mon, Apr 06, 2026 at 10:01:52AM -0400, Robert Haas wrote:
> On Mon, Apr 6, 2026 at 9:22 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
>> It would be better to introduce such a code at the beginning of the
>> development cycle, not right before the code freeze. At least we would
>> discuss its design without rushing.
>
> Yes, the timing is not ideal. However, I posted the patch on October
> 30th and committed the main patch on March 12th. I think that's a
> reasonable length of time to wait for people to provide feedback.

(Speaking with the pg_hint_plan kind-of-maintainer hat on.)
The timing is fine IMO.  In terms of integration with new APIs of
upstream, there is really nothing one can do until we are at least in
feature freeze.  Trying to work around APIs that have been committed
in the tree, which may be tuned after the initial commit, is just a
loss of time.  Things may get adjusted during beta, but the waves are
much weaker to deal with.

> During that time, the only person who provided information on how this
> will interact with out-of-core extensions was Lukas Fittl, who came to
> the conclusion that the pgs_mask infrastructure will be reusable by
> pg_hint_plan and will result in that module being simpler and
> involving less code duplication. Other extension authors could have
> provided feedback during that time as well, but none did, even after I
> posted to my blog to try to raise the visibility of this project. As
> far as I can tell, most extension developers don't pay much attention
> to core development until after we ship a beta. Had I waited until
> July to commit, I think there's a chance that it would have simply
> resulted in me getting whatever feedback I'm going to get next summer
> rather than this summer. At least this way, the issues will hopefully
> be fresh in my mind when the feedback arrives.

I have an answer to this one, in the shape of the following commits in
pg_hint_plan:
https://github.com/ossc-db/pg_hint_plan/commit/e42246a82589001de2f08255d3b4d984fb134d38
https://github.com/ossc-db/pg_hint_plan/commit/75b3d0142d2a8ea0e3d656e1c95ea3fdd6e8f082
https://github.com/ossc-db/pg_hint_plan/commit/5d386d3ecb832d3ea205d1e42e305cafefbefc76

The first commit is the most relevant one, and on a number basis I
finish with that, where I have been able to basically remove *all* the
historical hacks of the model in terms of plugs it added in the
planner:
 17 files changed, 1486 insertions(+), 3820 deletions(-)

At the end, I am particularly happy with the way things are regarding
the new join_path_setup and joinrel_setup hooks, that have removed
most of the bloat.

One thing that has caused me quite a bit of headache was parallel
hints.  At the end, I have followed Lukas suggestion to remove the old
path regeneration logic that was based on an enforcement of the GUCs
and switched to the PGS logic.  This is coming with some breakages in
the module, but these are actually super minor compared to the
accumulation of weird historical behavior that we had in it:
- When specifying only a JOIN hint (without leading), we now let the
planner decide the inner/outer order depending on the cost it sees,
not the order of the clauses.  That can always be enforced with a
Leading hint, which is the same thing as the JOIN_ORDER hint in
pg_plan_advice.
- Some slight changes in the way parallel hints are propagated to
child relations, due to build_simple_rel_hook().  We cannot really
avoid that, both behaviors are debatable, edge enough that I don't
worry much in terms of plan instabilities after a major release.  We
have some degree of that for each major release, users care *a lot*
about plan stability across minor releases, work around these after
major upgrades.

I strongly suspect that all these things are just going to be noise.
The regression test suite has basically no changes.

There are still gaps between pg_plan_advice and pg_hint_plan, and the
maintenance of the latter is now muuuuuch easier (still need to
maintain some versions for the stable branches).  The end game for me
would be to close the gap and merge both things together, then drop
pg_hint_plan.  I'll try to find some victi^D^D^D^D^D resources to do
some of the leg work to do the gap here (not planning to do that
myself), for some patches to-be-proposed in v20.  We have row hints,
parallel worker hints (aka RelOptInfo.rel_parallel_workers), memoize
hints, SET hints that could be added to contrib/pg_plan_advice.  There
are also hints that negate scan behaviors.  The negation hints are
not that popular, I think, but that may worth considering.

As of today on HEAD, 60%-ish of the remaining code relates to the
custom hint string parsing and feeding into the various hint
structures.  40%-ish of the code comes from the hooks and the internal
routines used by the hooks, for something like 5k lines of code.  This
is a difference between night and day.  (There may be more
simplifications doable in the code, planning an extra round of checks
during beta.)

In short, thanks for the work you have done in the v19 release cycle
in this area, Robert and others.
--
Michael

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.