Plan Advice in PostgreSQL 19

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.

The same plan expressed twice: as a plan tree on the left, and as four lines of advice on the right. Join order, join method, scan method and parallelism each get one line, colour-matched to the part of the tree they describe.

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.

One thing this is not: PostgreSQL 19 computes advice inside the planner, which knows the whole query, while sqlfmt reconstructs it from a rendering of the result. It is a comparison key, not a round-trippable advice string — do not feed its output to pg_plan_advice and expect it to apply.

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.

What is left after that is only real difference. Which is the point — you are asking whether the upgrade changed your plans, and you want the answer to be a short list or an empty one, not a page of notation.

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(results races drivers)

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. That is enable_eager_aggregate (on by default), pushing as much of the GROUP BY down as it safely can so the join has fewer rows to process. Forcing results to drive left no room for that; forcing drivers to drive did.

The output also now carries two blocks: Supplied Plan Advice, echoing what you asked for with a /* matched */ annotation, and Generated Plan Advice describing the plan you actually got.

That round trip is the feature. You can take the generated advice from a plan you liked, hand it back later, and confirm from the matched annotations that every piece of it landed.

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.

This annotation is the most useful thing in the whole interface, and it is easy to walk past. Advice that silently does nothing would be worse than no advice at all; you would carry a string in your configuration for two years believing it was holding a plan in place. Here you can check.

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 id could come from EXPLAIN (VERBOSE), but more usefully it comes from pg_stat_statements, which is where you were already looking when you noticed the query had got slow — and not typed in from memory. Run the query as the application actually sends it, no EXPLAIN wrapper, and look it up:

create extension if not exists pg_stat_statements;

  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;

select queryid
  from pg_stat_statements
 where query like 'select drivers.surname%'
   and query not like '%order by%';

create extension if not exists pg_stash_advice;

select pg_create_advice_stash('production');

select pg_set_stashed_advice(
         'production', -5243066567089054587,
         'JOIN_ORDER(drivers results races)'
       );

select * from pg_get_advice_stash_contents('production');

CREATE EXTENSION
  surname   | races 
------------+-------
 Alonso     |    10
 Bottas     |    11
 Button     |     1
 di Resta   |     1
 Ericsson   |    11
 Giovinazzi |     2
 Grosjean   |    11
 Hamilton   |    11
 Hülkenberg |    11
 Kvyat      |    11
 Magnussen  |    11
 Massa      |    10
 Ocon       |    11
 Palmer     |    11
 Pérez      |    11
 Räikkönen  |    11
 Ricciardo  |    11
 Sainz      |    11
 Stroll     |    11
 Vandoorne  |    11
 Verstappen |    11
 Vettel     |    11
 Wehrlein   |     9

       queryid        
----------------------
 -5243066567089054587

CREATE EXTENSION
 pg_create_advice_stash 
------------------------
 

 pg_set_stashed_advice 
-----------------------
 

 stash_name |       query_id       |           advice_string           
------------+----------------------+-----------------------------------
 production | -5243066567089054587 | JOIN_ORDER(drivers results races)

-5243066567089054587 is not a number this article picked; it is what pg_stat_statements reports for that exact query text, and it is the same number the plan advice above was generated for — pg_stat_statements and pg_plan_advice compute query ids the same way, so one can name what the other saw. From then on, the application changes nothing:

set pg_stash_advice.stash_name = 'production';

 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;

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 */

The only thing set is pg_stash_advice.stash_name — no pg_plan_advice.advice string, no rewritten query, no LOAD. That last one is not an accident: this session never asked for the module, and it was there anyway, because shared_preload_libraries put it there before the server even accepted its first connection. The plan changed because the stash matched the query id, not because anything about this query mentioned advice at all.

The plan advice workflow: find the query in pg_stat_statements, read its plan back with EXPLAIN (PLAN_ADVICE), keep only the lines that matter, and stash it by query id. The dashed return path is the step people forget.

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”.

So the discipline that makes advice useful is trimming: the generated string describes every decision, and you almost never want to pin every decision. If the join order flipped, keep JOIN_ORDER(...) and delete the rest, so the planner keeps its freedom everywhere else. Applying advice also costs planning time even when the plan does not change, which argues for reaching for it per-query rather than cluster-wide.

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.

And the reading-out half you can have today, on whatever PostgreSQL you already run: paste any EXPLAIN output into a file and run sqlfmt explain advice over it —

$ sqlfmt explain advice plan.txt