EXPLAIN Options and Output Formats #

EXPLAIN is not one tool but a dozen: each option adds a specific kind of evidence to the plan tree, and choosing the wrong set is how teams end up arguing about a plan that is missing the one field that would have settled the question.

Plain EXPLAIN shows only what the planner intended. ANALYZE adds what happened; BUFFERS adds where the I/O went; SETTINGS adds the configuration that shaped it; and the newer options — WAL, MEMORY, SERIALIZE, GENERIC_PLAN — close the gaps between plan output and what a client actually experiences. This page maps each option to the question it answers, explains what each costs, and covers when the machine-readable formats are the better choice. It is part of reading and interpreting query plans, and every other page in that section assumes you can capture the right plan.

When to Use Each Option #

The options fall into three groups by what they require.

Planning only — the statement is not executed:

Execution — the statement runs:

Presentation:

-- A complete capture for a read query
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE, SERIALIZE)
SELECT o.id, o.total, c.email
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= now() - interval '7 days';
EXPLAIN options by what they require Three columns. Planning only, without execution: COSTS, VERBOSE, SETTINGS, GENERIC_PLAN, MEMORY and SUMMARY. Execution, which runs the statement: ANALYZE, BUFFERS, WAL, TIMING and SERIALIZE. Presentation: FORMAT TEXT, JSON, YAML and XML. An arrow notes that anything in the execution column performs side effects of the statement. planning only COSTSVERBOSESETTINGS GENERIC_PLANMEMORYSUMMARY executes the statement ANALYZEBUFFERSWAL TIMINGSERIALIZE side effects happen presentation FORMAT TEXTFORMAT JSON FORMAT YAMLFORMAT XML same data, different shape combine freely, except GENERIC_PLAN with ANALYZE

Annotated Output: Every Option on One Node #

The fields each option adds appear in predictable places. Here is one scan with the full set:

Seq Scan on public.orders o  (cost=0.00..24812.00 rows=58210 width=24)
                             (actual time=0.018..96.402 rows=61034 loops=1)
  Output: o.id, o.total, o.customer_id                 -- VERBOSE: columns this node emits
  Filter: (o.placed_at >= (now() - '7 days'::interval))
  Rows Removed by Filter: 938966                       -- ANALYZE
  Buffers: shared hit=4102 read=8710                   -- BUFFERS: 4102 cached, 8710 read from disk
  I/O Timings: shared read=61.883                      -- BUFFERS + track_io_timing: ms spent reading
  WAL: records=0 bytes=0                               -- WAL (only shown when requested)
Settings: random_page_cost = '1.1', work_mem = '64MB'  -- SETTINGS: non-default planner GUCs
Planning:
  Buffers: shared hit=212                              -- catalog lookups during planning
  Memory: used=1872kB  allocated=2048kB                -- MEMORY (PostgreSQL 17+)
Planning Time: 0.412 ms                                -- SUMMARY
Serialization: time=38.214 ms  output=5812kB  format=text   -- SERIALIZE (PostgreSQL 17+)
Execution Time: 142.771 ms

Breakdown:

Option Internals and Overhead #

ANALYZE instrumentation. Each node is wrapped in counters that record rows and loops, and, with TIMING on, two clock reads per row passing through the node. On systems where the clock source is slow — some virtual machines — that overhead can double execution time for row-heavy plans, and it inflates nodes that process many cheap rows far more than nodes that process few expensive ones. pg_test_timing measures the clock cost; TIMING OFF removes it while keeping actual rows and loops.

EXPLAIN (ANALYZE, TIMING OFF, BUFFERS)
SELECT count(*) FROM events WHERE kind = 'view';
-- Aggregate  (actual rows=1 loops=1)
--   ->  Seq Scan on events  (actual rows=41822031 loops=1)
-- Execution Time: 4102.6 ms   (vs 7881.2 ms with TIMING ON on this VM)

BUFFERS accounting. Buffer counts are cumulative: a parent node’s counts include its children’s. To get a node’s own I/O, subtract the sum of its children. The same inclusive rule applies to actual time, which is why computing exclusive time is the first thing plan tools do with JSON output.

Parallel plans. With VERBOSE, each parallel-aware node lists per-worker lines (Worker 0: actual time=… rows=…). Without it, the node shows totals divided by loops, which is easy to misread — covered in parallel query execution.

GENERIC_PLAN. The planner treats each $n as an unknown of its inferred type and plans exactly as it would for a cached generic plan, so Index Cond: (tenant_id = $1) and averaged row estimates appear. It is the fastest way to see what the custom-to-generic switch will produce without preparing anything; the details are in EXPLAIN GENERIC_PLAN for parameterized SQL.

Format cost. Formats differ only in serialization of the plan itself. JSON is larger than text by several times and includes every field with explicit keys, including zero-valued counters that text omits.

What each timing line covers A horizontal timeline with four consecutive segments: planning, execution, serialization and network transfer. Planning Time covers the first segment. Execution Time covers the second. The Serialization line from the SERIALIZE option covers the third. Nothing in EXPLAIN covers the fourth, network transfer to the client. plan execute serialize send to client Planning Time Execution Time Serialization: not measured the application's latency is the whole line; plain EXPLAIN ANALYZE reports only the second box SERIALIZE (PostgreSQL 17+) closes the third gap; the fourth needs client-side timing

Memory, I/O and Safety Considerations #

Side effects. EXPLAIN ANALYZE executes the statement. For INSERT, UPDATE, DELETE and MERGE, rows change, triggers fire, and row locks are taken. Wrap the capture in a transaction and roll it back. Even then, sequences advance, and anything a trigger did outside the database is not undone.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders SET status = 'archived' WHERE placed_at < '2025-01-01';
ROLLBACK;

WAL for writes. The WAL: line shows records, fpi (full-page images) and bytes. A large fpi count right after a checkpoint is why the same update can generate several times more WAL at one moment than another; that write amplification is also what fills replication links and slows index maintenance.

Memory of ANALYZE itself. Instrumentation is per node and small, but a plan with many thousands of nodes — heavily partitioned tables — consumes noticeable memory both for planning (visible with MEMORY) and for per-node counters.

Production capture. For statements you cannot run by hand, the equivalent options are available through auto_explain (log_analyze, log_buffers, log_wal, log_settings, log_timing, log_format = json). The setup is in capturing ORM plans with auto_explain.

-- Session-level capture of one application code path
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;
SET auto_explain.log_settings = on;
SET auto_explain.log_format = 'json';

Step-by-Step Capture Workflow #

  1. Decide whether execution is safe. Read-only query: proceed. Write: use a transaction with ROLLBACK, on a replica-like environment if locks matter. Cannot execute at all: start with EXPLAIN (VERBOSE, SETTINGS) or GENERIC_PLAN.

  2. Turn on I/O timing for the session if you have permission:

    SET track_io_timing = on;

  3. Capture the core set:

    EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE) <query>;

  4. Add the situational options. WAL for writes; SERIALIZE when the result is large or includes wide text, JSON or bytea columns; MEMORY when Planning Time is significant.

  5. Check for instrumentation distortion. If a row-heavy node dominates, re-run with TIMING OFF and compare Execution Time. A large drop means per-node times were inflated by clock reads.

  6. Export for tools and history. Capture FORMAT JSON alongside text for anything you will compare later, feed to a visualiser, or store with an incident. The settings, WAL and serialize options guide covers reading those three in depth.

  7. Record the server version. Field availability and some output names differ across major versions; SELECT version(); belongs next to every saved plan.

Comparing plans across major versions #

Upgrades change plan output in ways that look like regressions but are not. PostgreSQL 18 includes buffer counts automatically with ANALYZE, adds Index Searches to index scan nodes, and reports fractional row counts for nodes executed in many loops, where earlier versions rounded actual rows to an integer per loop. PostgreSQL 17 added SERIALIZE and MEMORY. PostgreSQL 16 introduced GENERIC_PLAN and new join node names such as Hash Right Anti Join. A diff between a PostgreSQL 15 plan and a PostgreSQL 18 plan of the same query therefore shows many textual changes even when the plan shape is identical.

Compare structure rather than text: node types, relation and index names, join conditions and the order of children. JSON output makes that straightforward, and it tolerates new keys gracefully. When a real plan change appears after an upgrade, capture SETTINGS on both versions as well — defaults for some planner parameters have changed across releases, and a changed default shows up in Settings: on one side only if someone had overridden it on the other.

Choosing options for a shared incident record #

For a plan that will be discussed by several people or attached to a ticket, the most useful single capture is EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE, FORMAT JSON) stored alongside the same capture in text format, the server version, the time of capture, and whether it was taken on a primary or a replica. The text version is what people read; the JSON version is what tools and later comparisons use; the context lines prevent the most common misreading, which is comparing a warm-cache replica plan with a cold-cache primary plan and attributing the difference to the query.

Question first, options second Five questions each map to an option. Is the estimate wrong maps to ANALYZE. Where is the I/O maps to BUFFERS. Why does my plan differ from yours maps to SETTINGS. Why is the write heavy maps to WAL. Why is the client slower than the server maps to SERIALIZE. are the row estimates wrong? which node reads from disk? why does my plan differ from staging? why does this UPDATE flood replication? why is the client slower than the server says? ANALYZE BUFFERS SETTINGS WAL SERIALIZE

Common Pitfalls #

Running EXPLAIN ANALYZE on a write outside a transaction. The rows change for real. Diagnostic signal: a table that is suddenly missing data after an investigation. Fix: make BEGIN; EXPLAIN ANALYZE …; ROLLBACK; a reflex for any statement that is not a plain SELECT, and check for data-modifying CTEs inside what looks like a read.

Comparing plans captured with different options. A plan with BUFFERS from one server and without it from another invites guesses about I/O. Diagnostic signal: a debate about cache effects in which only one side has buffer counts. Fix: agree on one option set per investigation, including SETTINGS.

Treating per-node actual time as exclusive. Times include children. Diagnostic signal: a top-level node that “takes” nearly the whole execution time. Fix: subtract children’s actual total time × loops, or let a JSON-based tool compute exclusive time.

Trusting timings on a slow clock source. Instrumentation overhead inflates row-heavy nodes. Diagnostic signal: Execution Time far above the unobserved query’s runtime. Fix: compare with TIMING OFF and check pg_test_timing.

Assuming Execution Time is client latency. Serialization and network are outside it. Diagnostic signal: the server says 140 ms and the application logs 900 ms. Fix: add SERIALIZE, then measure network and driver time separately.

Reading GENERIC_PLAN output as the plan the application gets. Whether the application uses the generic plan depends on the plan cache mode and the five-execution rule. Diagnostic signal: a generic plan that differs from what auto_explain logs. Fix: treat GENERIC_PLAN as the candidate, and confirm with pg_prepared_statements or auto_explain.

Frequently Asked Questions #

Which EXPLAIN options should I use by default? #

For a query that is safe to execute, use EXPLAIN (ANALYZE, BUFFERS, SETTINGS). ANALYZE gives actual rows and time, BUFFERS gives I/O per node, and SETTINGS records any planner-relevant parameter that differs from the default, which makes the plan reproducible by someone else. Add VERBOSE when you need output columns or schema-qualified names.

Does EXPLAIN ANALYZE execute data-modifying statements? #

Yes. EXPLAIN ANALYZE on INSERT, UPDATE, DELETE or MERGE performs the change, fires triggers and takes locks. Wrap it in BEGIN and ROLLBACK when you only want the plan and its timings, remembering that sequences still advance and locks are held until the rollback.

Why does EXPLAIN ANALYZE report a different time than my application sees? #

Execution Time excludes sending rows to the client and network time, and without SERIALIZE it excludes converting rows to their output format. Instrumentation overhead can also inflate it. SERIALIZE measures output conversion, and TIMING OFF removes most per-node clock overhead while keeping row counts.

When should I use FORMAT JSON instead of text? #

Use JSON whenever a program will read the plan: plan visualisers, regression tests that compare node types, auto_explain logs you want to query, or scripts that compute exclusive time per node. Text is better for people, because it is compact and indented.

Up: Reading & Interpreting Query Plans