Robert Haas’s pg_plan_advice patch set, proposed for PostgreSQL 19, is where the twenty-year argument from Part 2 has landed — or is trying to. It is not pg_hint_plan brought into core. It is a different thing, with different mechanics, a different scope, and a different answer to the “why is this different from Oracle-style hints” question.

The patch introduces three contrib modules:

  • pg_plan_advice — the core mechanism. Generates plan-advice strings via EXPLAIN, and applies them via a GUC.
  • pg_collect_advice — an example extension showing how advice collection can be extended.
  • pg_stash_advice — an example extension showing how advice application can be extended, matching stored advice strings to queries by query identifier.

That three-way split matters. pg_plan_advice is deliberately minimal — Haas describes the design as “mechanism, not policy” — and the other two modules are demonstrations of what you can build on top of it. The architecture assumes other extensions will do the interesting work.

Generating advice

Load the extension and run EXPLAIN with the new PLAN_ADVICE option:

1LOAD 'pg_plan_advice';


2


3EXPLAIN (COSTS OFF, PLAN_ADVICE)


4  SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;


5


6             QUERY PLAN


7------------------------------------


8 Hash Join


9   Hash Cond: (f.dim_id = d.id)


10   ->  Seq Scan on join_fact f


11   ->  Hash


12         ->  Seq Scan on join_dim d


13 Generated Plan Advice:


14   JOIN_ORDER(f d)


15   HASH_JOIN(d)


16   SEQ_SCAN(f d)


17   NO_GATHER(f d)

The “Generated Plan Advice” section is the new output. It’s a description of the plan shape in a compact, structured form: join order, join method, scan method per table, parallelism decisions. That’s the advice string.

Applying advice

Hand the advice back to the planner through a GUC:

1BEGIN;


2SET LOCAL pg_plan_advice.advice = 'HASH_JOIN(d)';


3


4EXPLAIN (COSTS OFF, PLAN_ADVICE)


5  SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;

The planner produces the same Hash Join plan, and the EXPLAIN output now includes a “Supplied Plan Advice” section alongside the generated one, annotating each supplied directive with /* matched */ or not. You see exactly which parts of your advice the planner honored.

You don’t have to pass back the full advice string. Just the pieces you care about is fine. If you only want to pin the join method and let the planner decide everything else, pass only HASH_JOIN(d).

Forcing a different plan

Vary the advice string to force a different plan shape:

1SET LOCAL pg_plan_advice.advice = 'MERGE_JOIN_PLAIN(d)';


2


3EXPLAIN (COSTS OFF, PLAN_ADVICE)


4  SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;


5


6                           QUERY PLAN


7----------------------------------------------------------------


8 Merge Join


9   Merge Cond: (f.dim_id = d.id)


10   ->  Index Scan using join_fact_dim_id on join_fact f


11   ->  Index Scan using join_dim_pkey on join_dim d


12 Supplied Plan Advice:


13   MERGE_JOIN_PLAIN(d) /* matched */


14 Generated Plan Advice:


15   JOIN_ORDER(f d)


16   MERGE_JOIN_PLAIN(d)


17   INDEX_SCAN(f public.join_fact_dim_id d public.join_dim_pkey)


18   NO_GATHER(f d)

Notice how the generated advice updates to reflect the new plan. Change the join method and the scan methods follow — the planner now picks index scans to get sorted input for the merge. The advice mechanism coerces the choices you specify, and the planner still optimizes everything else around those choices.

The advice vocabulary

From the regression tests and Haas’s blog post, the current directives include:

  • JOIN_ORDER(rel1 rel2 ...) — join order of the named relations
  • HASH_JOIN(rel), MERGE_JOIN_PLAIN(rel) — join method for a given relation
  • SEQ_SCAN(rel), INDEX_SCAN(rel idx ...) — scan method, with optional index specification
  • NO_GATHER(rel rel ...) — disable parallel gather for the named relations

This is narrower than what Oracle or pg_hint_plan support. Notably absent: cardinality overrides, row-count injection, planner cost overrides, set-returning function hints, materialize directives. The surface area is intentionally small. This is version 1.0, and Haas is candid about that — “plenty of limitations” and “a number of things that are still fairly clunky.” Expect the vocabulary to grow.

pg_stash_advice: advice without touching the query

Setting a GUC for every query is fine for interactive debugging and useless for production. That’s what pg_stash_advice is for:

1ALTER SYSTEM SET shared_preload_libraries = 'pg_stash_advice';


2ALTER SYSTEM SET pg_stash_advice.stash_name = 'my_stash';


3


4SELECT pg_create_advice_stash('my_stash');


5SELECT pg_set_stashed_advice('my_stash', :qid, 'MERGE_JOIN_PLAIN(d)');

After a reload, every time a query with that query identifier is planned in a session where pg_stash_advice.stash_name matches, the stashed advice is applied automatically. You didn’t touch the SQL. You didn’t touch the application. You pinned the plan from a database role with the right privilege.

This is the SQL Server plan-guide pattern and the Db2 optimization-profile pattern, landing in PostgreSQL nearly twenty years after it arrived in either of them.

The stash is keyed by query identifier — the same identifier you see in pg_stat_statements. So the workflow writes itself: find a slow query via pg_stat_statements, grab the advice via EXPLAIN (PLAN_ADVICE) for the plan you want, stash it against the query id, walk away.

If both pg_plan_advice.advice and a stashed advice entry apply to a query, the session-level setting wins. Stashed advice is the baseline; the session setting is the override.

Mechanism, not policy

Haas is explicit about the architecture: pg_plan_advice is the pluggable core, and pg_stash_advice and pg_collect_advice are example extensions demonstrating what the pluggability lets you build. pg_stash_advice keys by query identifier and stores in dynamic shared memory because that’s a reasonable default, not because it’s the one true design. A different extension could key by a query hash you computed, by a regex over the normalized query text, by user or role, by the phase of the moon. A different extension could store advice in a catalog table, in etcd, in a sidecar service.

This is why pg_plan_advice is not pg_hint_plan brought into core. pg_hint_plan is a complete, opinionated solution. pg_plan_advice is a toolkit.

The practical consequence is that once this lands, pg_hint_plan itself can shed a significant amount of code. It currently duplicates large portions of the planner to achieve what it does; the new infrastructure lets much of that duplication go away. pg_hint_plan becomes a consumer of pg_plan_advice’s hooks rather than a parallel implementation of the planner. That is a genuine engineering win regardless of what you think of hints as a concept.

How this answers (or doesn’t) the six objections

Running down the list from Part 2:

Maintainability. pg_stash_advice keys advice to query identifiers, not embedded in SQL. You change advice in the database, not the application. Answered.

Upgrade interference. Partially answered. If you only stash the specific directives you care about — MERGE_JOIN_PLAIN(d), nothing else — the planner continues to optimize everything else adaptively. A full advice string, pinned verbatim, reintroduces the problem. The tool permits restraint; it doesn’t enforce it.

Enabling bad DBA habits. Not answered, and not meant to be. A DBA who reaches for pg_plan_advice instead of fixing a statistics problem will get the same bad outcome they would have gotten with pg_hint_plan. This is a cultural problem, not a technical one.

Does not scale with data size. Partially answered, for the same reason as upgrades. Targeted advice survives data growth better than full plan pinning. Full plan pinning has the same problem it always did.

Usually unnecessary. Irrelevant. This objection was never about what the mechanism should look like, only about whether the mechanism should exist. pg_plan_advice concedes that it sometimes is necessary.

Interference with planner development. The weakest of the original objections, and weaker still now. pg_stat_statements plus pg_plan_advice makes it trivial to characterize and report planner regressions. Stashed advice is evidence, not cover.

Status

The patch is not committed. Haas notes that “time is rapidly running out to get things into v19,” and the -hackers discussion has been lively — both supportive and skeptical. Andrey, in the blog comments, made the case that pg_hint_plan already solved this problem and that the core planner shouldn’t be complicated to serve a use case an extension already handles. That is not a fringe objection. It is a reasonable version of the 2011 position, restated for 2026.

Whether pg_plan_advice lands in 19, 20, or never, the argument has moved. The core project is no longer saying “we do not want this.” It is negotiating over the shape.

That is the real news.