PostgreSQL 19 plan advice - pg_plan_advice and pg_stash_advice

A plan that flips from index scan to sequential scan after an ANALYZE is not the planner being wrong - it is the planner being responsive, and occasionally wrong in the process. PostgreSQL has never shipped an in-core way to say “keep this part of the plan”. The enable_* GUCs are a sledgehammer and pg_hint_plan is an extension that not every environment allows. PostgreSQL 19 adds two modules that fill the gap: pg_plan_advice, which expresses planner decisions as a text mini-language, and pg_stash_advice, which stores those strings and applies them automatically to matching queries.

This is not pg_hint_plan. Hints replace the planner’s judgement with yours; plan advice constrains the planner - you will only ever get a plan the core planner would have considered.

Before and after

A small example, adapted from the module documentation. Without advice the planner picks a nested loop; pinning the join order and method produces a hash join, and EXPLAIN reports the advice as matched:

-- before advice:                     -- after SET pg_plan_advice.advice
 Nested Loop                          --   = 'JOIN_ORDER(f d) HASH_JOIN(d)':
   ->  Seq Scan on join_dim d         Hash Join
         Filter: (val2 = 1)             Hash Cond: (f.dim_id = d.id)
   ->  Index Scan on join_fact f        ->  Seq Scan on join_fact f
         Index Cond: (dim_id = d.id)    ->  Hash
         Filter: (val1 = 1)                 ->  Seq Scan on join_dim d
                                             Filter: (val2 = 1)
                                       Supplied Plan Advice:
                                         JOIN_ORDER(f d) /* matched */
                                         HASH_JOIN(d) /* matched */

Whether the second plan is actually better is for EXPLAIN ANALYZE on real data - advice fixes which plan runs, not whether that plan is good.

pg_plan_advice: capturing and editing a plan

Load the module (shared_preload_libraries, session_preload_libraries, or LOAD) and EXPLAIN gains a PLAN_ADVICE option that prints the advice string describing the chosen plan:

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
--  Hash Join
--    Hash Cond: (f.dim_id = d.id)
--    ->  Seq Scan on join_fact f
--    ->  Hash
--          ->  Seq Scan on join_dim d
--  Generated Plan Advice:
--    JOIN_ORDER(f d)
--    HASH_JOIN(d)
--    SEQ_SCAN(f d)
--    NO_GATHER(f d)

Each line is an advice tag over advice targets (usually relation aliases). JOIN_ORDER(f d) makes f the driving table joined first to d; HASH_JOIN(d) puts d on the inner side of a hash join; NO_GATHER keeps the target out of parallel plans. Parenthesised sublists in JOIN_ORDER force non-outer-deep joins; curly-brace sublists force a join to happen together without fixing its internal order.

Targets are usually just the relation alias, but three cases need an extended spelling: subqueries (the target carries the subquery path), partitioned tables (the target names the partition), and the same alias appearing twice in one subquery (a self-join). In all three, let EXPLAIN (PLAN_ADVICE) generate the string and copy the target spelling it produces rather than writing it by hand.

To pin a plan you take the generated string, keep only the parts you want to enforce, and supply it back:

SET pg_plan_advice.advice = 'JOIN_ORDER(f d)';
EXPLAIN (COSTS OFF)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
--  Supplied Plan Advice:
--    JOIN_ORDER(f d) /* matched */

EXPLAIN annotates each supplied tag with advice feedback: matched, partially matched, not matched, inapplicable, conflicting, or failed. The subtlety worth knowing: advice works by disabling the plan alternatives that violate it. Ask for something impossible - a join order referencing a table that is not there - and you do not get an error; you get a plan with Disabled: true nodes that can be worse than the unconstrained plan. JOIN_ORDER(x f d) against a query that only touches f and d is the documentation’s example, and it produces a disabled nested loop instead of the hash join. Validate advice against EXPLAIN feedback before relying on it.

Parameters: pg_plan_advice.advice (the string), pg_plan_advice.always_explain_supplied_advice (default true), pg_plan_advice.always_store_advice_details (default false; needed for EXPLAIN EXECUTE on prepared queries), pg_plan_advice.feedback_warnings (default false), and pg_plan_advice.trace_mask for debugging.

Limitations stated by the documentation: no control over aggregation (sort versus hash, eager or partitionwise), none over set operations (UNION, INTERSECT), and under GEQO advice applies only probabilistically because the constrained plan may never enter the random candidate pool.

pg_stash_advice: advice that applies itself

pg_plan_advice applies to the session that sets the GUC. pg_stash_advice maps query IDs to advice strings in dynamic shared memory, so every session planning a query whose ID is in the active stash gets the advice without setting anything:

CREATE EXTENSION pg_stash_advice;

SELECT pg_create_advice_stash('prod_pins');

-- find the query id: EXPLAIN (VERBOSE) shows it, as does pg_stat_statements
SELECT pg_set_stashed_advice('prod_pins', -8123456789012345678, 'JOIN_ORDER(f d)');

-- sessions opt in with:
SET pg_stash_advice.stash_name = 'prod_pins';

Query IDs come from EXPLAIN (VERBOSE) or pg_stat_statements, and require compute_query_id - set to auto, loading pg_stash_advice turns it on. Stashes persist to pg_stash_advice.tsv in the data directory via a background worker (pg_stash_advice.persist, default on, checked every persist_interval seconds, default 30). pg_get_advice_stashes() and pg_get_advice_stash_contents(name) are the introspection functions - passing NULL for the name returns every stash’s entries.

Housekeeping: pg_drop_advice_stash(name) removes a stash and its entries, and NULL as the advice string to pg_set_stashed_advice removes one entry. If the module was not in shared_preload_libraries at startup, pg_start_stash_advice_worker() starts the persistence worker manually - started that way it writes changes to disk but does not load anything, so a restart with the worker configured is still needed before persisted advice applies.

Two cautions from the documentation deserve emphasis:

• Applying advice costs planning time even when it changes nothing, so stash advice only for queries with a real risk of planner error, and trim strings to the decisions that matter.
• There is no real security model yet: only superusers (or grantees) can write stashes, but any user can SET pg_stash_advice.stash_name and thereby read a stash’s contents - treat advice strings as visible to non-privileged users.

What changed, and in which release

Where it breaks

• Data drifts under a pinned plan - the documentation’s own warning: the planner adapts to changing distributions; advice that prevents it “may choose a very poor plan”. Pin the minimum necessary.
• Impossible advice degrades silently - Disabled: true nodes rather than errors; check feedback for failed or conflicting.
• GEQO queries - once geqo_threshold is exceeded, even valid advice may not be satisfiable; the docs suggest disabling GEQO if you need to constrain join order substantially.
• Query ID changes - stashes key on query IDs; a rewrite of the query text produces a new ID and the advice stops applying.
• Beta caveat - the tag set and GUC names are from the beta documentation.

What this is not for

• Fixing systematically wrong estimates - advice constrains plan shape; if the row-count estimate is wrong, fix statistics (default_statistics_target, extended stats) first. Advice pinned over bad statistics pins the wrong plan permanently.
• Aggregates and set operations - not controllable at all in this version.
• Routine tuning - this is a scalpel for the handful of queries where the planner is provably wrong, not a replacement for ANALYZE and sane cost settings.