EXPLAIN can price the wire and the planner

The work that happened after the plan finished

EXPLAIN ANALYZE accounts for execution: the nodes, the rows, the loops, the time each part of the tree spent. It has never accounted for what happens next. Once the executor has a row, the server still has to turn every value in it into the representation the client asked for, detoasting anything stored out of line and, for the text format, converting every number and timestamp into characters. That work is real, it is proportional to the width of the result rather than to the difficulty of finding it, and before 17 it was reported nowhere at all.

The gap produced a recognisable and unproductive conversation. A developer reports a slow query, the database side runs EXPLAIN ANALYZE and gets a small number, and the two measurements disagree by an order of magnitude with no shared evidence to reconcile them. The usual explanations offered are network latency and client-side processing, both plausible, neither measured.

PostgreSQL 17 added SERIALIZE, which makes EXPLAIN ANALYZE do the conversion and report what it cost, and MEMORY, which reports what the planner spent on itself while producing the plan. Both are listed under EXPLAIN in the PostgreSQL 17 release notes. They are new options, so nothing that worked before behaves differently; a 16 server simply refuses to recognise them.

A wide result, explained twice

The fixture is a table whose rows are large but whose row count is small, which is the shape where this matters most.

CREATE TABLE report_blob AS
SELECT g AS report_id, repeat(md5(g::text), 400) AS payload
FROM generate_series(1, 20000) AS g;

SELECT 20000

On 16, the option does not exist, and the message is about the option rather than about anything in your query.

EXPLAIN (ANALYZE, SERIALIZE, COSTS OFF) SELECT report_id, payload FROM report_blob;

ERROR:  unrecognized EXPLAIN option "serialize"
LINE 1: EXPLAIN (ANALYZE, SERIALIZE, COSTS OFF) SELECT report_id, pa...
                          ^

On 17, first the familiar answer, the one that ends an investigation prematurely.

EXPLAIN (ANALYZE, COSTS OFF) SELECT report_id, payload FROM report_blob;

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on report_blob (actual time=0.011..9.637 rows=20000 loops=1)
 Planning Time: 0.381 ms
 Execution Time: 10.485 ms
(3 rows)

Then the same statement with the new option, which adds one line and changes the conclusion.

EXPLAIN (ANALYZE, SERIALIZE, BUFFERS, COSTS OFF) SELECT report_id, payload FROM report_blob;

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on report_blob (actual time=0.007..7.038 rows=20000 loops=1)
   Buffers: shared hit=576
 Planning:
   Buffers: shared hit=24
 Planning Time: 0.215 ms
 Serialization: time=67.396 ms  output=250283kB  format=text
 Execution Time: 76.994 ms
(7 rows)

The scan is quick. The serialization line reports both the time spent converting and the volume produced, and the volume is the number worth reading twice: a result measured in hundreds of megabytes leaves the server whatever the plan looks like, and no index will help with that. This is the evidence that turns “the database is slow” into “the query returns too much”, which is a conversation with an obvious fix.

Two details to keep straight. The serialization work is done and then discarded; the rows are not sent anywhere, so the figure excludes the network entirely and is a floor for what the client waits for rather than the whole of it. And adding the option changes the total execution time of the EXPLAIN itself, so the before and after above are not two measurements of one thing.

There is also a binary form, written SERIALIZE BINARY, which measures the cost of the binary wire format instead of the text one. It is worth trying when the client library uses it, because for numeric and timestamp columns the two are not close.

What the planner spent on itself

The second option answers a smaller question that occasionally matters a great deal.

EXPLAIN (MEMORY, COSTS OFF)
SELECT a.report_id, b.payload
FROM report_blob a JOIN report_blob b USING (report_id)
WHERE a.report_id < 100;

                 QUERY PLAN                  
---------------------------------------------
 Merge Join
   Merge Cond: (a.report_id = b.report_id)
   ->  Sort
         Sort Key: a.report_id
         ->  Seq Scan on report_blob a
               Filter: (report_id < 100)
   ->  Materialize
         ->  Sort
               Sort Key: b.report_id
               ->  Seq Scan on report_blob b
 Planning:
   Memory: used=19kB  allocated=32kB
(12 rows)

Planning memory is reported as two numbers, what was used and what was allocated for the purpose, and on an ordinary query both are small enough to be uninteresting. They stop being uninteresting on queries with many joins, on partitioned tables where the planner considers every partition, and on generated SQL with long IN lists. A server whose planning time has crept up without the plan changing is the case to point this at, because planning memory and planning time move together and the memory figure is the one that identifies which part of the statement is responsible when you cut the query down.

MEMORY works without ANALYZE, which is the property that makes it usable on a statement you do not want to execute.

Reading the new lines without drawing the wrong conclusion

• A large serialization time with a small execution time means the result is too wide or too long. Fetch fewer columns, fetch fewer rows, or push the work the client does with them into the query.
• A large serialization time on a narrow result usually means detoasting. Something in the select list is stored out of line and is being reassembled per row, and dropping that column from the query proves it in one step.
• Planning memory that is large relative to planning time points at the number of paths considered rather than at the cost of considering them, which is a partitioning or join-count argument.

None of these is an alert. They belong in the investigation of a specific statement, which is what EXPLAIN has always been for. The recurring signal that sends you here lives in the statements extension, where a statement whose total time far exceeds its execution time is the one to bring to this page.

What it costs to ask

SERIALIZE makes EXPLAIN ANALYZE do work it would otherwise skip, so it makes the statement slower by roughly the amount it reports. On a query returning a great deal of data that is a real cost, paid once, on a statement you chose to run. It does not send the data anywhere, so it is safe against a large result in a way that running the query without a limit is not.

MEMORY costs nothing measurable and needs no ANALYZE. Neither option has a server setting, neither needs a restart, and neither changes the plan that is chosen. Both are available to anyone who can run EXPLAIN on the statement in the first place.