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.

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:

QueryHash AggregateHash JoinHash JoinSeq ScanresultsHashbuildSeq ScanracesHashbuildSeq ScandriversThe plan as a tree, rows flowing upward: three sequential scans at the bottom, two of them feeding hash builds, two hash joins above those, and a Hash Aggregate under the query. Nothing in the tree carries a number, because this plan was taken with COSTS OFF.

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.

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.

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.

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.

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.

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.

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