Horton
Menu

Re: pg_plan_advice (now with transparent SQL plan performance overrides - pg_stash_advice)

Robert Haas <robertmhaas@gmail.com>

View thread
From: Robert Haas <robertmhaas@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>, Alexandra Wang <alexandra.wang.oss@gmail.com>, Richard Guo <guofenglinux@gmail.com>, Lukas Fittl <lukas@fittl.com>, Tom Lane <tgl@sss.pgh.pa.us>, Jacob Champion <jacob.champion@enterprisedb.com>, Dian Fay <di@nmfay.com>, Matheus Alcantara <matheusssilv97@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-03-04T03:36:13Z
Lists: pgsql-hackers 
On Tue, Mar 3, 2026 at 2:28 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I mostly get why specifying an index that doesn't exist as part of advice, alongside a target relation, produces "matched" along with "inapplicable" and "failed".
>
> INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */
>
> But less understandable is why a failure to match a subplan-qualified target produces "matched" when the subplan doesn't appear.
>
> SEQ_SCAN(t1@minmax_1) /* matched, failed */

Because there's not a way to control aggregation behavior at present,
you can't directly conrol whether t1 or t1@minmax_1 appears in the
winning plan. This case is "matched" because we saw t1@minmax_1 get
planned, but it's "failed" because the non-minmax strategy then won on
cost.

Aside: This is actually an instance of a very common problem into
which I have already invested an enormous amount of time and energy.
Suppose that for some operation X (which might be a scan or a join or
any other kind of thing) you tell the planner that you want it to use
strategy S. If strategy S is not what was going to be chosen anyway,
you've just increased the cost of X. Unsurprisingly, the planner's
response to that is very often to choose not to perform X at all.
Otherwise, it ends up looking like the planner has just made an
end-run around the advice. For example, HASH_JOIN(X) says "put X on
the inner side of a hash join". Obviously, this means that when we see
a join with X as the inner rel, we should disallow all join strategies
other than hash join. Less obviously, it also means that when we see a
join with X as the outer rel, we shouldn't allow *any* join
strategies. If you don't do that, you get a problem very similar to
what you're complaining about here. The difference is that join
control is supported by the patch set, and so I have put in the work
to avoid instances of this problem that occur in that case, and
aggregate control is not, so I haven't.

> Maybe we need to do something like:
>
> relname - matches anywhere in the plan tree
> relname@somewhere - only looks at "somewhere" for matches; absence of somewhere results in "not matched" (the expected feedback for the advice/query combination above).

This would not work in general; "anywhere in the plan tree" is too
broad a scope, and it would be easy to construct an example where it
falsely matches an unrelated part of the query. I think that the
solution here probably looks more like letting the user write
AGGREGATE_PLAIN(something) or AGGREGATE_MINMAX(something), but to make
it really work, we would need to figure out what the "something"
should be, and also remember to account for partitionwise aggregation,
eager aggregation, partial aggregation, hashed aggregation, sorted
aggregation, plain aggregation, and mixed aggregation, some of which
can be combined with some of the others but not all of the others, and
then we would need to add design and implement hooks in core to allow
that advice to be enforced, and then we would need to get
pg_plan_advice to properly accept the syntax and enforce it and also
be able to generate the syntax from a finished plan tree, and then
document all that and add tests. I think that work is worth doing,
but, again, I think it would be a much better idea to spend the next
few weeks trying to figure out whether it makes sense to commit what
I've got, and perform any necessarily stabilization of that
functionality, than to keep saying "hey, maybe you should radically
expand the scope." It's probably a six-month project to get all that
working, and we don't have that time for v19.

> If keeping the status quo the existing behavior should be documented. The existing wording for not matched; "or it may occur if the relevant portion of the query was not planned," seems to be the one that covers this case.

That's definitely relevant here, although these specific examples seem
to have more to do with "failed" than "not matched".

-- 
Robert Haas
EDB: http://www.enterprisedb.com

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.