PostgreSQL 18
EXPLAIN ANALYZE says more than it did
For anyone who parses plan text, diffs plans between runs, or stores them for later.
Reference page, revised in place. Last updated .
Plans are data, and this one changed shape
A query plan is read by people, and it is also parsed by machines rather more often than the people doing the reading realise. Plan-capture tools store them. Regression harnesses diff yesterday’s against today’s. Log analysers pull the row estimate and the actual row count out of auto-explain output and compute the ratio between them, which is the single most useful derived number a plan produces.
Every one of those depends on the output format, and the format is not a contract. PostgreSQL 18 changes it in three ways, one of which is announced, one of which is not, and one of which is the kind of change that survives a test suite and fails in production six weeks later.
The fixture is a small table and a query that touches a hundred rows of it, chosen because the plan is short enough to read in full.
CREATE TABLE price_points (point_id integer PRIMARY KEY, label text);
INSERT INTO price_points SELECT g, md5(g::text) FROM generate_series(1, 5000) AS g;
ANALYZE price_points;CREATE TABLE
INSERT 0 5000
ANALYZEThe same plan on both versions
EXPLAIN ANALYZE SELECT count(*) FROM price_points WHERE point_id < 100;On 17 this is six lines and every one of them has been in the same place for years.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10.26..10.27 rows=1 width=8) (actual time=0.435..0.436 rows=1 loops=1)
-> Index Only Scan using price_points_pkey on price_points (cost=0.28..10.02 rows=99 width=0) (actual time=0.019..0.027 rows=99 loops=1)
Index Cond: (point_id < 100)
Heap Fetches: 99
Planning Time: 1.397 ms
Execution Time: 0.492 ms
(6 rows)On 18 it is eleven.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10.26..10.27 rows=1 width=8) (actual time=0.045..0.048 rows=1.00 loops=1)
Buffers: shared hit=3
-> Index Only Scan using price_points_pkey on price_points (cost=0.28..10.02 rows=99 width=0) (actual time=0.033..0.040 rows=99.00 loops=1)
Index Cond: (point_id < 100)
Heap Fetches: 99
Index Searches: 1
Buffers: shared hit=3
Planning:
Buffers: shared hit=65
Planning Time: 0.286 ms
Execution Time: 0.094 ms
(11 rows)Three things are different, and they are worth separating because they break different things.
The buffer counts are the announced change. Asking for them used to require an option; now an analyzed plan carries them by default, for each node and for the planner. This is unambiguously an improvement for anyone reading a plan, because the question of whether a node was slow due to I/O or due to work is the first question you ask and it previously needed a second run with an extra option. For anything parsing plan text it means extra lines in places where there were none, and a parser that treats an unexpected line as the end of a node will lose the nodes below it.
The search count on the index scan is the unannounced one. It reports how many times the index was descended, which distinguishes a scan that walked the index once from one that restarted it repeatedly, and that distinction previously had to be inferred from the loop count and the shape of the plan. It is a genuinely useful number and it appears in the release notes nowhere.
The third is the one to take seriously.
Row counts are decimals now
Look at the actual row counts on 18. They are not ninety-nine and one, they are ninety-nine point zero zero and one point zero zero.
The reason is sound. A node inside a loop reports the average rows per loop, and that average has always been a fraction that the server rounded to an integer before printing. Rounding it away meant a node that produced one row on nine loops out of ten printed the same as one that produced a row every time, and the estimate-versus-actual ratio that everybody computes was quietly wrong on exactly the nested loops where it matters most. Printing two decimal places fixes a real and long-standing misreading.
It also changes the type of a field that thousands of scripts extract with a regular expression. The common pattern matches a run of digits after the word rows, and against ninety-nine point zero zero it matches ninety-nine, silently discarding the part after the decimal point. For a whole number that is harmless. For a node reporting nought point four three rows per loop it extracts nought, and a ratio computed against an estimate of one becomes a division that either reports infinite over-estimation or divides by zero, depending on which way round the script does it.
The structured format shows the same change without the parsing ambiguity, which is the argument for using it.
EXPLAIN (ANALYZE, FORMAT JSON, TIMING OFF, SUMMARY OFF, COSTS OFF, BUFFERS OFF)
SELECT count(*) FROM price_points WHERE point_id < 100; QUERY PLAN
-----------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Plain", +
"Partial Mode": "Simple", +
"Parallel Aware": false, +
"Async Capable": false, +
"Actual Rows": 1, +
"Actual Loops": 1, +
"Plans": [ +
{ +
"Node Type": "Index Only Scan", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Scan Direction": "Forward", +
"Index Name": "price_points_pkey", +
"Relation Name": "price_points", +
"Alias": "price_points", +
"Actual Rows": 99, +
"Actual Loops": 1, +
"Index Cond": "(point_id < 100)", +
"Rows Removed by Index Recheck": 0,+
"Heap Fetches": 99 +
} +
] +
}, +
"Triggers": [ +
] +
} +
]
(1 row)And the same plan on 18:
QUERY PLAN
-----------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Plain", +
"Partial Mode": "Simple", +
"Parallel Aware": false, +
"Async Capable": false, +
"Actual Rows": 1.00, +
"Actual Loops": 1, +
"Disabled": false, +
"Plans": [ +
{ +
"Node Type": "Index Only Scan", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Scan Direction": "Forward", +
"Index Name": "price_points_pkey", +
"Relation Name": "price_points", +
"Alias": "price_points", +
"Actual Rows": 99.00, +
"Actual Loops": 1, +
"Disabled": false, +
"Index Cond": "(point_id < 100)", +
"Rows Removed by Index Recheck": 0,+
"Heap Fetches": 99, +
"Index Searches": 1 +
} +
] +
}, +
"Triggers": [ +
] +
} +
]
(1 row)The row counts are numbers with a fractional part rather than integers, so a consumer that reads them into a floating-point field is already correct and one that reads them into an integer field will fail or truncate depending on how strict its parser is. There is a new boolean on every node saying whether that node was disabled, which is a small and welcome clarification of something that used to be expressed by adding a large constant to the cost. And the search count appears here too.
That JSON was produced with the buffer output explicitly suppressed, which is worth saying because it is not how the format looks by default on 18. With buffers included, each node in the structured output gains sixteen fields, and the plan doubles in size. On a cluster where plans are captured and stored, that is a storage consideration rather than a curiosity.
Turning the new output off
The buffer output can be suppressed, which the previous block already relied on and which is worth showing in the text format because it is what a plan-capture tool will want.
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM price_points WHERE point_id < 100; QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10.26..10.27 rows=1 width=8) (actual rows=1.00 loops=1)
-> Index Only Scan using price_points_pkey on price_points (cost=0.28..10.02 rows=99 width=0) (actual rows=99.00 loops=1)
Index Cond: (point_id < 100)
Heap Fetches: 99
Index Searches: 1
(5 rows)That gets the node structure back to something close to the old shape. The search count is still there and the decimals are still there, because neither has a switch. There is no option that makes an 18 plan look like a 17 plan, and building one is not the right goal anyway: the decimals are more accurate than what they replaced, and a tool that needs them rounded should round them itself and say so.
The planner has its own buffer line now
One part of the new output is easy to skim past because it sits at the bottom and looks like a footnote. The plan on 18 carries a section describing buffers used during planning, separately from the buffers each node used during execution.
That is a genuinely new signal rather than a reorganisation. Planning reads the catalog: table definitions, index definitions, column statistics, and on a partitioned table all of that for every partition. Most of the time it hits shared buffers and costs nothing worth reporting, which is why the number in the plan above is small and boring.
It stops being boring in two situations, and both are ones people debug badly. The first is a query against a table with many partitions, where planning touches a great deal of catalog and the planning time can exceed the execution time by a wide margin. The second is the first execution after a restart or after a large eviction, where the catalog is not resident and planning does real reads. In both cases the old output gave you a planning time with no indication of where it went, and the natural but wrong conclusion is that the planner is doing too much thinking. The buffer line distinguishes thinking from reading.
There is a subtlety about when the section appears. Planning buffers are reported for the plan that was actually planned, so a statement served from a cached plan does not pay for planning and does not report it. In a session that runs the same prepared statement repeatedly, the section is present on the first executions and absent later, which is a difference a plan-diffing tool will flag as a change unless it is taught otherwise.
Auto-explain output is subject to all of this too. The same defaults apply, so logs from an upgraded cluster carry the buffer lines whether or not the auto-explain configuration asked for them, and a log volume estimate made before the upgrade will be low.
What to check before the upgrade
- Test every plan parser against real 18 output rather than against a description of it. This is a change where reading the release note carefully still leaves you with a broken regular expression, because the thing that broke is not in the release note.
- Grep for patterns that extract a row count as digits. The fix is to accept an optional decimal part, and it is a one-character change in most expressions.
- Decide what plan capture should store. Buffers by default is a good default for humans and roughly doubles the size of a stored structured plan, so a tool keeping months of them has a decision to make and the default has already made it.
- Treat stored plans from before and after the upgrade as different formats when comparing them. A diff across the boundary is all noise, and a regression harness that flags it will flag every query at once.
What it costs
Collecting buffer counts during an analyzed run is bookkeeping the executor was doing anyway, so the new default costs essentially nothing at execution time. The cost is in the output: more lines to ship, more text to store, more fields to parse.
Timing is the expensive part of an analyzed plan and it is unchanged here. It can still be switched off per statement, as the examples above do, and doing so is the right move whenever the question is about row counts and buffer usage rather than about where the time went.