PostgreSQL 19 will let you pin the execution plan that used to work

There's a scene every DBA has lived through: a query that ran fine for a year suddenly gets slow overnight. Nothing was deployed, the data volume grew a bit, ANALYZE ran, and the planner, quite rightly given the new numbers, chose a different plan. The old plan was better, and you wanted it back. It's exactly for this problem that PostgreSQL 19 brings two new modules, broken down by Dimitri Fontaine, a major contributor to the project, in an article published on Planet PostgreSQL based on tests against version 19 Beta 3.

The core idea: pg_plan_advice reads an execution plan back as a string and forces the planner to follow it afterward; pg_stash_advice stores those strings indexed by query id and applies them automatically. Both are contrib modules, loaded via session_preload_libraries and shared_preload_libraries respectively (the latter needs to be loaded this way to survive a restart).

Reading the plan back as text

The idea starts with an augmented EXPLAIN. Fontaine uses a query that joins three Formula 1 tables and asks the planner not just what it did, but to describe what it did in a format it can itself read back:

explain (costs off, plan_advice)
select drivers.surname, count(*) as races
  from f1db.results
  join f1db.races   using (raceid)
  join f1db.drivers using (driverid)
 where races.year = 2017
 group by drivers.surname;

Below the traditional plan a new block appears, Generated Plan Advice, with four lines describing four decisions:

JOIN_ORDER(results races drivers)
HASH_JOIN(races drivers)
SEQ_SCAN(results races drivers)
NO_GATHER(results races drivers)

Each line names a choice: which table drives the join and in what order, which join method to use, how to reach each relation, and whether or not to parallelize. The design detail that makes it all work is what is not there: there's no cost, no row estimate, no timing. As Fontaine observes, "advice describes outcomes, not the reasoning that produced them." It's precisely because it mentions no statistics at all that the advice survives a change in statistics.

Reapplying: the round trip is the feature

When you feed a string back via pg_plan_advice.advice, the planner is forced to follow it. Forcing the query to drive from drivers:

set pg_plan_advice.advice = 'JOIN_ORDER(drivers results races)';

The plan actually changes, and the output now carries two blocks: Supplied Plan Advice, echoing what you asked for with a / matched / annotation, and Generated Plan Advice, describing the plan that actually came out. This cycle (reading the advice from a plan you liked, storing it, and reapplying it later, confirming via the matched annotations that each piece took) is the heart of the feature.

An interesting side benefit appears in this example: by forcing drivers as the driving table, the planner was able to push a Partial HashAggregate down below the join (enable_eager_aggregate, on by default in 19), reducing rows before the join. Forcing results to drive left no room for this; forcing drivers did.

When the advice doesn't win

This is, in this writer's reading, the most important part of the interface, and the easiest to overlook. Advice restricts the planner's choice among the plans it would consider. It doesn't resurrect plans that were taken off the table. Disabling hash join and asking for one anyway:

Supplied Plan Advice:
  JOIN_ORDER(results races drivers) /* matched */
  HASH_JOIN(races) /* matched, failed */

The matched, failed says the advice was understood, applied to the right part of the query, and still the planner couldn't honor it. Advice that silently does nothing would be worse than none: you'd carry a string in the config for two years believing it was pinning a plan. Here you can check. For anyone doing production tuning, this failure telemetry is worth more than the imposition itself.

Applying by query id, without touching the application

Setting pg_plan_advice.advice by hand is good for experimenting, but you don't ask the application to do that. That's where pg_stash_advice comes in: it maps query ids to advice strings in shared memory and applies them to any query whose id matches.

The id comes from pg_stat_statements, which is where you were already looking when you noticed the slow query. The flow is to create the stash, register the advice for the query id, and turn on the stash name in the session:

select pg_create_advice_stash('production');
select pg_set_stashed_advice(
  'production', -5243066567089054587,
  'JOIN_ORDER(drivers results races)'
);
-- then, in the application:
set pg_stash_advice.stash_name = 'production';

From there the application changes nothing: no advice string in the query, no LOAD, no rewrite. The plan changes because the stash matched the query id. Fontaine points out that pg_stat_statements and pg_plan_advice compute query ids the same way, so one can name what the other saw.

The warning the documentation repeats on purpose

Here lies the caveat that separates disciplined use from a self-inflicted wound. The planner's ability to change its mind as data changes is a feature, and advice takes that away. If the data distribution shifts under a pinned plan, you get the old plan applied to new data, exactly the failure the planner exists to avoid. The README is even more direct: bad advice producing a bad plan is "user error, not a module defect."

The discipline that makes advice useful is trimming: the generated string describes every decision, and you almost never want to pin them all. If what flipped was the join order, keep only the JOIN_ORDER(...) line and drop the rest, leaving the planner free on everything else. Applying advice also costs planning time even when the plan doesn't change, which reinforces using it per query, not across the whole cluster.

What this replaces, and what you can already use today

Anyone running PostgreSQL at scale knows the alternatives: pg_hint_plan hints in query comments, the per-session enable_* family (too blunt), or rewriting the query until the planner agrees, which isn't an option when the query comes out of an ORM you don't control. What's new is the round trip: a plan can be read out and the same string reimposed. You don't write hints from scratch hoping they describe the plan you remember; you keep a plan you actually measured.

There's also a practical note for anyone who, like most people, won't be running 19 anytime soon. The reading half doesn't depend on the 19 server: every version already prints the text of a plan. The sqlfmt tool rebuilds the same four-line block from an ordinary EXPLAIN on any version, with sqlfmt explain advice plan.txt. And it goes further when comparing plans: since the format carries no cost or timing, two runs of the same plan produce identical output, and any difference is a real difference, something a raw diff of two EXPLAIN outputs (where every line carries a cost and so every line differs) can't deliver. It's the kind of comparison worth doing before an upgrade, to answer whether the planner changed its mind between 16 and 19. It's worth remembering, though, that sqlfmt's output is a comparison key, not a round-trippable string: don't feed it back into pg_plan_advice expecting it to apply.