Build1 publisher2 min readPublished
pg_plan_advice reproduces a Postgres plan by forbidding the planner's other options
PostgreSQL 19's new module prints a plan advice string out of EXPLAIN and replays it on later runs. Its own documentation warns that a pinned plan can turn very poor once the data distribution moves.
The Engineer · Build desk
What happened
- PostgreSQL 19's manual documents pg_plan_advice, a module that lets key planner decisions be described, reproduced and altered through a special-purpose plan advice mini-language.
- Once the module is loaded, EXPLAIN accepts a PLAN_ADVICE option that prints an advice string describing the plan the planner chose.
- Replaying a plan means setting pg_plan_advice.advice to a chosen string, and the documentation says that string can be one the system generated or one the user wrote.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint The module is bounded by the planner's own search space, so a team that needs a plan shape the cost model never generates gets nothing from writing a longer advice string.
- cost Adoption costs a server restart via shared_preload_libraries, or a new session via session_preload_libraries, or a LOAD issued by every session that wants advice.
- decision Pinning a plan moves a maintenance job onto the team: someone has to re-examine the query after index changes and data growth, because the planner is no longer free to revisit that choice.
- exposure A plan pinned months earlier is visible to whoever debugs the query next, unless an operator has turned the EXPLAIN annotation off.
Plan advice is written imperatively, specifying what should be done, but at an implementation level the module works by telling the core planner what should not be done, constraining its choices instead of replacing it [14]. A plan shape the planner never generated therefore stays out of reach. The documentation is explicit: no matter what advice you provide, you will only ever get a plan that the core planner would have considered for the query in question [15].
For one two-table join, the documented example emits four tags, `JOIN_ORDER(f d) HASH_JOIN(d) SEQ_SCAN(f d) NO_GATHER(f d)`, covering join order, join method, scan method and the use of parallelism [7][8]. Most queries need fewer. The page recommends taking the system-generated string and picking out only the elements you wish to enforce, and its own worked example keeps `JOIN_ORDER(f d)` alone [10]. Three of the four categories go back to the planner [17].
Each tag you keep is a decision that stops updating. `SEQ_SCAN(f d)` says both relations are accessed by sequential scan [8], so an index added later to either table is not available to those scans while the tag is in force [18]. The documentation puts the failure up front: if the distribution of the underlying data changes, the planner normally has the option to adjust the plan, and where advice prevents that, a very poor plan may be chosen [3].
Supplied advice is printed in EXPLAIN output even when the `PLAN_ADVICE` option was not specified, so anyone reading the plan knows it was influenced [11]. The default there is good engineering. Suppressing it takes `pg_plan_advice.always_explain_supplied_advice = false` [12][16]. Per-item feedback comes with it: `/* matched */` means the named relations were found in the query and the resulting plan conforms to the advice [13]. Set that GUC to false and a plan pinned months earlier is hidden from whoever debugs the query next.
The documented uses are two: stabilization of plan choices the user believes to be good, and experimentation with plans the planner believes to be non-optimal [2]. The page describes the module's behaviour and does not argue the case for hints in Postgres [20]. On when to reach for it, the documentation says: "It is important to use plan advice only when the risks of constraining the planner's choices are outweighed by the benefits." [4]
What to watch
- Whether the advice tag set in the shipped PostgreSQL 19 module matches the four categories in the current documentation.
- Whether the docs add a documented way to attach advice to a query without a session-level SET of pg_plan_advice.advice.
- Whether any guidance appears for detecting advice that has gone stale after a data distribution change.