Skip to content
Published on

pg_plan_advice in Postgres 19: The Compromise From a Project That Has Long Refused Hints

🇰🇷KO🇺🇸EN🇯🇵JA🇨🇳ZH
Split View필사 모드
Share
Authors

Introduction — "We Don't Add Hints"

There's a sentence that has hung over the PostgreSQL community for a long time. The project wiki's Optimizer hints discussion page opens with it — we don't plan to implement hints the way that's commonly implemented in other databases.

Below that are six reasons, summarized here.

  1. Worse maintainability of application code — embed hints in a query and you'll need a large-scale refactor down the line.
  2. Interferes with upgrades — a hint that helps today can hurt performance after an upgrade.
  3. Encourages bad DBA habits — people slap on a hint instead of finding the real cause.
  4. Doesn't scale with data size — a hint that's right when a table is small is wrong once it grows.
  5. Actually hurts performance — the optimizer is usually right.
  6. Gets in the way of planner improvements — people who use hints don't report the underlying problem to the project.

This list isn't just stubbornness. It reads more like a summary of the actual problems hints caused in Oracle and MySQL. But the same page adds — we'd be interested in a proposal that avoids the problems observed in other hint systems.

That proposal landed in PostgreSQL 19 Beta 1, released on June 4, 2026. Two contrib modules built by Robert Haas: pg_plan_advice and pg_stash_advice. The release notes put it as "lets users stabilize and control planner decisions."

This post looks at exactly what that is, and which of the six objections above it erased and which it didn't. For reference, PostgreSQL 19's final release is slated for around September–October 2026; this is still beta.

Why It's Called "Advice," Not a Hint

The official docs, F.30. pg_plan_advice, describe the module this way — it lets you describe, reproduce, and change the planner's major decisions using a dedicated mini-language called "plan advice." The intent is twofold: to pin a plan the user has judged good, and to experiment with a plan the planner believes isn't optimal.

There's a design reason it's called advice rather than a hint. Three things set it apart from existing hints.

First, advice lives outside SQL. Oracle hints and pg_hint_plan put the hint inside the query text itself (as a comment). pg_plan_advice is supplied through a GUC, pg_plan_advice.advice, or stashed in a separate store keyed by query ID via pg_stash_advice. The application's SQL strings are never touched. This design aims squarely at wiki objection #1.

Second, it narrows the search space instead of replacing the planner. This is the most important difference. To carry the documentation's own sentence across — whatever advice you give, for that query only plans the core planner would have considered come out. In other words, advice isn't an order to "use this plan"; it's closer to "pick from these candidates, minus that." A plan the planner believes produces a semantically wrong result never comes out, no matter what the advice says.

Third, you can pin only part of it. You can stuff an entire advice string in and pin the whole plan, but you can also specify just the one piece you need and leave the rest to the planner. Christophe Pettus, in Hints, Part 3: Advice, Not Orders (2026-04-21), notes that this restrained usage is room to ease wiki objections #2 (interferes with upgrades) and #4 (data size). But he immediately adds a caveat — the tool only allows restraint, it doesn't enforce it.

What Advice Actually Looks Like

This is too abstract without a concrete look. Loading the module adds a PLAN_ADVICE option to EXPLAIN, which writes the plan the planner currently picked back out as an advice string.

EXPLAIN (COSTS OFF, PLAN_ADVICE)
        SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;

The output shown in the docs looks like this.

Generated Plan Advice:
   JOIN_ORDER(f d)
   HASH_JOIN(d)
   SEQ_SCAN(f d)
   NO_GATHER(f d)

The reading is intuitive — join f and d in this order, hash-join with d on the inside, sequential-scan both, and don't use parallel Gather. Feed this string straight back in and you reproduce the same plan.

SET pg_plan_advice.advice = 'JOIN_ORDER(f d)';

The advice language's vocabulary is deliberately narrow. The docs define roughly four categories of tags.

  • Scan method: SEQ_SCAN(target), TID_SCAN(target), INDEX_SCAN(target index_name), INDEX_ONLY_SCAN(target index_name), BITMAP_HEAP_SCAN(target), DO_NOT_SCAN(target)
  • Join order: JOIN_ORDER(...) — parentheses can force or loosely constrain join order
  • Join method: HASH_JOIN(...), MERGE_JOIN_PLAIN(...), MERGE_JOIN_MATERIALIZE(...), NESTED_LOOP_PLAIN(...), NESTED_LOOP_MEMOIZE(...), NESTED_LOOP_MATERIALIZE(...)
  • Parallelism: GATHER(...), GATHER_MERGE(...), NO_GATHER(target)

On top of that there's control over partitionwise joins (PARTITIONWISE) and semijoin uniqueness (SEMIJOIN_UNIQUE / SEMIJOIN_NON_UNIQUE). Targets are specified by alias; if the same alias appears more than once, an occurrence number like f#2 is appended, and there's extended syntax that can even point at partitions or subplans.

Daniel Westermann's hands-on writeup (2026-03-13) shows this flow well. Join a 1-million-row t1 with a 2-million-row t2, and the planner spits out advice like this.

JOIN_ORDER(t2 t1) NESTED_LOOP_MEMOIZE(t1) SEQ_SCAN(t2) INDEX_SCAN(t1 public.t1_pkey) NO_GATHER(t1 t2)

Feed it straight back in and everything is marked matched and the plan stays the same. Flip just the join order instead, giving JOIN_ORDER(t1 t2), and the plan changes to a Merge Join with a Sort and Materialize. It's the shortest experiment for confirming that advice actually takes effect.

Feedback That Tells You Whether the Advice Landed

This part is personally my favorite thing about the design. The chronic ailment of hint systems is "I used a hint and it feels like it got ignored, but there's no way to check." pg_plan_advice attaches a verdict as a comment on every line of advice.

INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */
HASH_JOIN(f) /* matched */
HASH_JOIN(g) /* not matched */
JOIN_ORDER(f g) /* partially matched */

The verdict vocabulary is six terms per the docs — matched (the target was seen during planning and was applicable), partially matched (only part was seen, or it wasn't seen together), not matched (the target wasn't seen at all), inapplicable (the tag couldn't apply, e.g. an index that doesn't exist), conflicting (two pieces of advice contradict each other), failed (a matched piece of advice wasn't followed by the final plan).

Turning on pg_plan_advice.feedback_warnings raises a warning whenever advice wasn't enforced. It defaults to off. Why this matters — advice rots over time (schemas change, indexes disappear, data distributions shift), and rotten advice being silently ignored versus loudly warning about it are operationally two completely different stories.

pg_stash_advice — Pinning a Plan Without Touching the Application

pg_plan_advice supplies advice as a session GUC. That's fine for experimenting, but you can't SET it per session in production just to pin one problem query. That's what F.33. pg_stash_advice is for.

The mechanism is simple. A mapping from query ID to advice string (the docs call it an advice stash) lives in dynamic shared memory; if the ID of the query being planned is in the stash, the corresponding advice is applied automatically. Shaun Thomas, in a pgEdge post (2026-06-05), shows usage like this.

SELECT pg_create_advice_stash('production_tuning');
SELECT pg_set_stashed_advice('production_tuning', 5424487836266966148,
    'INDEX_SCAN(f idx_fact_dim_id) NESTED_LOOP_PLAIN(f)');
SET pg_stash_advice.stash_name = 'production_tuning';

pg_stash_advice.stash_name can also be set via ALTER DATABASE ... SET or ALTER ROLE ... SET, which means you can effectively pin a specific query's plan without changing a single line of application code. It's the same class of operational pattern as SQL Server's plan guides or DB2's plan pinning.

There are a few prerequisites. It has to go in shared_preload_libraries and requires a restart, and compute_query_id has to be on (it turns on automatically when the module loads if set to auto). By default the stash is persisted to pg_stash_advice.tsv in the data directory every 30 seconds, and that interval is tunable via pg_stash_advice.persist_interval.

Scoring the Wiki's Six Objections

Now for the fun part. Let's go through how much of the project's own stated objections this design actually erased.

#1. Application maintainability — erased. Advice lives outside SQL, so query text stays uncontaminated. This is a clear improvement.

#5. The optimizer is usually right — structurally erased. Advice only picks within the set of plans the core planner would have considered. A plan the planner has judged to produce a wrong result can't be pulled out through advice either. So the worst case is "a slow plan," not "a wrong plan." This is the fundamental difference from Oracle-style hinting.

#6. Gets in the way of planner improvements — partially erased. Thanks to the feedback comments and EXPLAIN (PLAN_ADVICE), what the planner chose versus what you forced stays around in a machine-readable form. It's a good format to attach to a bug report. That said, it still doesn't stop someone from attaching advice and quietly moving on.

#2. Interferes with upgrades / #4. Data size — only partly erased. Keep advice to the bare minimum and the planner keeps adapting for the rest. But pin the entire plan wholesale and both problems come right back. This is exactly Pettus's point — fully pinning a plan brings back both upgrade interference and scaling problems, because the tool only allows restraint, it doesn't enforce it. The docs say the same thing — the planner changing its plan when data distribution shifts is generally a feature, not a bug.

#3. Bad DBA habits — not erased at all. Pettus's sentence is the most honest one — a DBA who reaches for pg_plan_advice instead of fixing the underlying statistics problem gets the same bad outcome. And he classifies this as "not a technical problem but a cultural one." It's the kind of objection a tool can't solve.

To sum up: 2 of the 6 are clearly erased, 1 mostly, 2 only when the user exercises restraint, and 1 not at all. Not a bad score, but not a clean sweep either.

The Honest Limits

The docs themselves admit to quite a few things. Don't skip this part.

A lot of territory is out of your control. You can't decide whether aggregation uses sort or hash. You can't touch strategies like eager aggregation or partitionwise aggregation either. The behavior of set operations like UNION and INTERSECT isn't under control either. Per the commit message depesz summarized, the feature targets outcomes like join method or scan type — it doesn't touch cost estimation or selectivity itself.

A cost sticks even when the plan doesn't change. The pg_stash_advice docs are explicit about this — applying plan advice has a noticeable performance cost even when it doesn't change the plan. Use it only when needed, they say flatly. The pgEdge post explains the same thing — a small penalty is unavoidable because advice interrupts the planner's loop partway through. Neither source gives a concrete figure, so I won't invent one here.

The security model is still immature. The docs use the word "unsophisticated." Only superusers or authorized roles can create or modify a stash, but any user can set pg_stash_advice.stash_name on their own session, which means stash contents can be exposed to unprivileged users. Advice strings contain index names and table aliases, so this is a schema-information leak path.

It eats memory. The stash lives in dynamic shared memory. The docs' advice is to factor that in when deciding how much advice to load.

And this is 1.0. Haas himself wrote, on his own blog (before it was even committed) — this is very clearly version-1.0 technology. There are a lot of limitations, and quite a few things are still rough around the edges. The commit landed on March 12, 2026 and made it into beta, but there's no reason to think that self-assessment was wrong.

Finally, one line from depesz sums up the tool's character best — powerful, and a foot-gun at the same time. Meaning: advice left in production can do harm once requirements change.

Design Backstory — Mechanism, Not Policy

One more thing worth knowing. Haas deliberately kept this work minimal. In his own words — I've deliberately tried to separate mechanism from policy. pg_plan_advice is core technology that provides only the simplest way to generate advice (namely, EXPLAIN) and the simplest way to supply it (namely, the pg_plan_advice.advice setting), but it's pluggable.

Why this separation matters is that the infrastructure underneath it can outlive pg_plan_advice itself. Per pganalyze's writeup, Haas added a bitmask called pgs_mask to RelOptInfo, letting extensions impose constraints — like "don't use a sequential scan on this table" — at the planner's earliest stage. The existing pg_hint_plan had nothing like this, so it had to copy over 2,500 lines of core source and manipulate global settings; pganalyze's observation is that, on top of this infrastructure, most of that copy could be deleted. In other words, even if you don't like pg_plan_advice itself, this opens a path for third-party hint extensions to be reimplemented in a far less risky way.

Of the three modules Haas originally proposed, pg_collect_advice isn't in the 19 docs' contrib listing. What actually shipped is two: pg_plan_advice (F.30) and pg_stash_advice (F.33).

So, When Should You Use It, and When Shouldn't You

Starting with the docs' own warning — because the planner usually makes good decisions, overriding its judgment can easily backfire. It matters to use plan advice only when the risk of constraining the planner's choice is smaller than the payoff.

Things to try before you touch this.

  • Check that ANALYZE is actually running and that statistics targets are sufficient
  • For correlated columns, try extended statistics (CREATE STATISTICS) first
  • Check that cost parameters like random_page_cost and effective_cache_size match your actual hardware
  • Ask whether you're trying to paper over a missing index by forcing a join method

Only once you've tried all four and the planner still repeatedly gets it wrong on a specific query, and that wrongness actually hurts the business — that's where pg_plan_advice belongs. In Pettus's words, it isn't a substitute for statistics tuning; it's a last resort for planner misjudgments you can't otherwise avoid.

When not to use it

  • 19 isn't GA yet. It's currently beta, with the final release slated for around September–October 2026. This isn't something to plan a production rollout around right now.
  • When the problem is statistics, not the planner. Most cases fall here.
  • When you find yourself wanting to pin dozens of queries instead of a handful. At that point the real problem is your schema, statistics, or hardware, not the tool.
  • Tables whose data distribution keeps shifting. Advice that's right today is wrong next month — wiki objection #4 isn't erased by this design either.
  • Teams with no culture of recording who set which piece of advice, when, and why. Orphaned advice becomes a landmine nobody can touch a few months later.

If you do use it, use it like this

Keep advice to the bare minimum. That's exactly what the docs themselves recommend — trim the advice string down to control only what's necessary, and apply it only where the risk of planner misjudgment is high. The moment you paste in an entire plan wholesale, wiki objections #2 and #4 come right back. And turn on feedback_warnings so rotten advice doesn't die silently.

Closing

Summarizing pg_plan_advice as "Postgres finally accepted hints" gets two things wrong. First, this is a constraint, not a command — since it only picks among plans the core planner would have considered anyway, the worst case even when misused is a slow plan, not a wrong one. Second, this is a mechanism, not a policy — Haas laid down only the minimal foundation and left the rest to extensions.

This design answered a bit over half of the objection list the project has stood behind for a long time. SQL contamination and "the optimizer is usually right" are structurally solved; upgrade and scaling problems are only eased when the user exercises restraint; and bad DBA habits aren't a problem a tool can solve. Both are true: that half is a real improvement, and the other half is still on you.

Once 19 goes GA, use it — just enough, no more — on that one query that still won't behave even after you've fixed the statistics and cost parameters. That's the usage the tool itself demands.

References

Discuss on Twitter • View on GitHub