There is a conversation that happens in every PostgreSQL shop eventually. A query that has been fine for a year gets slow overnight. Nothing was deployed. The data grew a little, ANALYZE ran, and the planner — entirely reasonably, on the numbers it had — picked a different plan. The old plan was better. You would like it back.

PostgreSQL 19 ships two new modules for exactly this: pg_plan_advice, which can read a plan back out as a string and enforce it later, and pg_stash_advice, which keeps those strings keyed by query id and applies them automatically.

Reading a plan back out

Start with a query that joins three F1 tables. A freshly restored Lab image has never been analyzed, so the setup below includes the ANALYZE from the opening paragraph before running the query — the last thing a comparison needs is statistics that do not reflect the data yet:

analyze f1db.results, f1db.races, f1db.drivers;

  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
order by races desc, drivers.surname
   limit 5;

ANALYZE
  surname   | races 
------------+-------
 Bottas     |    11
 Ericsson   |    11
 Grosjean   |    11
 Hamilton   |    11
 Hülkenberg |    11

Nothing remarkable about the query itself. Now ask the planner not just what it did, but to describe what it did in a form it can 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;

                        QUERY PLAN                        
----------------------------------------------------------
 HashAggregate
   Group Key: drivers.surname
   ->  Hash Join
         Hash Cond: (results.driverid = drivers.driverid)
         ->  Hash Join
               Hash Cond: (results.raceid = races.raceid)
               ->  Seq Scan on results
               ->  Hash
                     ->  Seq Scan on races
                           Filter: (year = 2017)
         ->  Hash
               ->  Seq Scan on drivers
 Generated Plan Advice:
   JOIN_ORDER(results races drivers)
   HASH_JOIN(races drivers)
   SEQ_SCAN(results races drivers)
   NO_GATHER(results races drivers)

Indented EXPLAIN output is a tree written sideways, and it is worth seeing as one before going further — the plan below is what those four lines of advice are describing:

That trailing block under the plan is the whole idea. Four lines, describing four decisions the planner made: which table drives the join and in what order, which join method to use, how to reach each relation, and whether to go parallel.

JOIN_ORDER(results races drivers) says results is the driving table, joined first to races and then to drivers. HASH_JOIN(races drivers) says each of those belongs on the inner side of a hash join. SEQ_SCAN(...) and NO_GATHER(...) say how to read each table and that none of it should run in parallel.

Note what is not here. There is no cost, no row estimate, no timing — advice describes outcomes, not the reasoning that produced them. That is a deliberate design choice, and it is the reason advice survives a change in statistics: it does not mention any.

You do not need PostgreSQL 19 for this part

There is an obvious problem with everything above: pg_plan_advice is a PostgreSQL 19 contrib module, and you are probably not running PostgreSQL 19. Most people will not be for years.

The useful half of that four-line block does not actually depend on the server, though. It is a description of a plan — and the plan text is something every version has been printing all along. So it can be reconstructed. sqlfmt does that, from ordinary EXPLAIN output, on any version:

$ sqlfmt explain advice plans/default.txt

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

That is the same four lines PostgreSQL 19 printed above, from a plan captured on a server that has never heard of pg_plan_advice.

Why this is the right way to compare two plans

Putting two plans side by side works fine at the size of the ones in this article. It stops working at twenty nodes, and twenty-node plans are the ones you actually need to compare. The mechanical answer — run diff over two EXPLAIN outputs — does not help either: every line carries a cost or a timing, so every line differs, and the one change that matters drowns in the noise.

Leaving the numbers out is what makes the comparison tractable. Because no cost or timing appears, two runs of the same plan produce identical output, and any difference is a real difference. Here is the planner’s own plan against one with a different join order forced onto it — the same forced plan the next section walks through in full:

$ sqlfmt explain diff plans/default.txt plans/forced.txt

--- plans/default.txt
+++ plans/forced.txt
@@ plan structure @@
-JOIN_ORDER(results races drivers)
+JOIN_ORDER(drivers results races)
-HASH_JOIN(races drivers)
+HASH_JOIN(results races)
-SEQ_SCAN(results races drivers)
+SEQ_SCAN(drivers results races)
-NO_GATHER(results races drivers)
+NO_GATHER(drivers results races)

The driving table moved and both join methods stayed hash joins — the HASH_JOIN line just names a different pair, because forcing drivers to drive puts a different relation on the inner side. With EXPLAIN (ANALYZE) plans the timings are reported too, as context lines under the structural hunk, so you can see whether the shape change actually bought anything. It exits non-zero when the plans differ, which makes it usable as a check in CI.

Comparing across versions

The comparison worth making most is the one you make before an upgrade. You have the plan your PostgreSQL 16 server produces for a query you care about, and you have what a PostgreSQL 19 server says about the same query, printed by pg_plan_advice itself. Did the planner change its mind?

One plan from each side, and the two write the same decisions down slightly differently — 19 orders a set of relations by its own internal numbering and schema-qualifies index names, neither of which is in plain EXPLAIN text. -canonical puts both into one normal form:

$ diff <(sqlfmt explain advice -canonical pg16-plan.txt) \
       <(sqlfmt explain canonical pg19-plan.txt)

It sorts what is a set, leaves JOIN_ORDER alone because there the order is the meaning, keeps index pairs together as pairs, and drops schema qualifiers. explain canonical is the counterpart for the other side: it reads an advice block a server already printed, finding it inside a whole EXPLAIN (PLAN_ADVICE) capture.

Making the planner take it

Feed a string back through pg_plan_advice.advice and the planner is obliged to follow it. Here is the same query, told to drive from drivers instead:

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

 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;

SET
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: drivers.surname
   ->  Sort
         Sort Key: drivers.surname
         ->  Hash Join
               Hash Cond: (results.raceid = races.raceid)
               ->  Hash Join
                     Hash Cond: (drivers.driverid = results.driverid)
                     ->  Seq Scan on drivers
                     ->  Hash
                           ->  Partial HashAggregate
                                 Group Key: results.raceid, results.driverid
                                 ->  Seq Scan on results
               ->  Hash
                     ->  Seq Scan on races
                           Filter: (year = 2017)
 Supplied Plan Advice:
   JOIN_ORDER(drivers results races) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(drivers results races)
   HASH_JOIN(results races)
   SEQ_SCAN(drivers results races)
   NO_GATHER(drivers results races)

Two things to notice. The plan really did change — drivers is now the driving table — and the shape of the change is a PostgreSQL 19 feature in its own right: Partial HashAggregate moved below the join, on results alone, with Finalize GroupAggregate combining the partial groups once every relation is joined.

When advice does not win

Advice constrains the planner’s choice among plans it would consider. It does not resurrect plans that have been taken off the table. Turn off hash joins and ask for one anyway:

reset pg_plan_advice.advice;

set enable_hashjoin = off;

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

 explain (costs off)
  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;

RESET
SET
SET
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: drivers.surname
   ->  Sort
         Sort Key: drivers.surname
         ->  Nested Loop
               ->  Nested Loop
                     Disabled: true
                     ->  Partial HashAggregate
                           Group Key: results.raceid, results.driverid
                           ->  Seq Scan on results
                     ->  Index Scan using idx_49556_primary on races
                           Index Cond: (raceid = results.raceid)
                           Filter: (year = 2017)
               ->  Index Scan using idx_49514_primary on drivers
                     Index Cond: (driverid = results.driverid)
 Supplied Plan Advice:
   JOIN_ORDER(results races drivers) /* matched */
   HASH_JOIN(races) /* matched, failed */

JOIN_ORDER matched. HASH_JOIN(races) reports matched, failed — the advice was understood, it applied to the right part of the query, and the planner still could not honour it.

Stashing advice by query id

Setting pg_plan_advice.advice by hand works for experimenting, but you cannot ask an application to do it. pg_stash_advice closes that gap: it maps query ids to advice strings in shared memory, and applies them to any query whose id matches.

The part the documentation says twice

Both modules’ documentation carries the same warning, and it is worth repeating rather than paraphrasing: the planner’s ability to change its mind as the data changes is a feature. Advice takes that away. If the distribution shifts under a pinned plan, you get the old plan applied to new data, which is exactly the failure the planner exists to prevent. The README is blunter still — bad advice producing a bad plan is “user error, not a defect in this module”.

What this replaces

You have met the alternatives if you have run PostgreSQL at scale: pg_hint_plan’s out-of-tree hints in query comments, the blunt per-session enable_* family, or rewriting the query until the planner agrees with you — which is not available when the query comes out of an ORM you do not control.

What is new is the round trip: a plan can be read out, and the same string put back. You are not writing hints from first principles and hoping they describe the plan you remember; you are keeping a plan you measured. That is a smaller feature than a hint language, and a much more useful one.