Horton
Menu

Re: pg_plan_advice

Lukas Fittl <lukas@fittl.com>

View thread
From: Lukas Fittl <lukas@fittl.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-04-01T06:33:50Z
Lists: pgsql-hackers

Attachments

On Tue, Mar 31, 2026 at 7:25 PM Lukas Fittl <lukas@fittl.com> wrote:
>
> On Mon, Mar 30, 2026 at 7:53 AM Robert Haas <robertmhaas@gmail.com> wrote:
> >
> >
> > The module doesn't have a built-in way to do that right now. Are you
> > thinking we would document that pg_get_dsm_registry_allocations() can
> > be used?
>
> Yeah, for example. Alternatively we could provide a function/view that
> lists all advice across all stashes, so you can more easily see the
> result size of that and estimate what the in-memory use is. But
> pointing to pg_get_dsm_registry_allocations seems easier.

Actually, that won't work in practice with the code as of v23 -
pg_get_dsm_registry_allocations() always returns the fixed 64 byte
allocation from GetNamedDSMSegment, but is oblivious to the individual
DSA allocations (even after adding hundreds of entries):

SELECT * FROM pg_get_dsm_registry_allocations();

      name       |  type   | size
-----------------+---------+------
 pg_stash_advice | segment |   64
(1 row)

Is there a reason you didn't use GetNamedDSA / GetNamedDSHash for the
other allocations? (which we have as of fe07100e82b09)

With the adjustments in the attached patch, this gets reported as expected:

SELECT * FROM pg_get_dsm_registry_allocations();

         name          |  type   |   size
-----------------------+---------+-----------
 pg_stash_advice       | segment |        24
 pg_stash_advice_stash | hash    |   1048576
 pg_stash_advice_dsa   | area    | 803209216
 pg_stash_advice_entry | hash    |   1048576
(4 rows)

>
> > > In practice for a good amount of our user base these days the question
> > > will be "Does my cloud provider give me access to create stash
> > > entries", so its maybe worth thinking about if we could also allow
> > > pg_maintain to manage entries by default?
> >
> > Wouldn't it make more sense for the cloud provider to grant execute
> > permissions on these functions to pg_maintain if they are so inclined?
> > This is a brand-new facility, so I think we had better be conservative
> > in terms of default permissions.
>
> I guess. I'm always worried that providers get that wrong and forget
> to give end users the permissions - but I suppose end users can
> complain to their providers if that's the case.
>
> I've done another look over pg_set_stashed_advice and I think its in
> good shape. The only trailing thought I have is that we could consider
> running a fuzzer against the pg_set_advice function in particular,
> just to see if anything pops up (beyond having the ability to make a
> very large memory allocation through a large advice string, which is
> maybe a problem?).

Obviously I meant "I've done another look over pg_stash_advice and I
think its in good shape".

I've done some basic fuzzing with the pg_set_stashed_advice function,
including concurrently setting advice, and that didn't yield any
surprises.

Thanks,
Lukas

-- 
Lukas Fittl

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.