skip to content

Which EXPLAIN options should you run before pasting a PostgreSQL plan into PEV2, and why is FORMAT JSON a better input than text?

level: middleimportance: must knowfreq 36%

answer

  1. actuals, not estimates
  2. buffers come free from 18
  3. per-worker detail needs VERBOSE
  4. unaligned, tuples-only psql output
  5. named keys beat indentation

basics

~20 s

Run EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON), and add SETTINGS when planner settings differ. PEV2 needs ANALYZE for real times and rows, and JSON gives it every field as a named key instead of text it must reconstruct.

solid answer

~40 s

`ANALYZE` executes the statement and adds actual time, rows and loops. Without it, PEV2 can show only estimates. `BUFFERS` adds shared, local and temp block counts; PostgreSQL 18 includes them automatically with `ANALYZE`, but on 16 and 17 you must ask. `VERBOSE` adds output columns and the per-worker lines PEV2 uses on parallel nodes. `SETTINGS` lists planner settings that differ from the defaults. For I/O times, `track_io_timing` must also be on. Prefer `FORMAT JSON`. Every field arrives as a named key with explicit nesting, while text must be rebuilt from indentation, and copy-paste wrapping can break that. Text also omits zero buffer counters. Capture it cleanly with `psql -XqAt -f explain.sql > plan.json`. Remember that `ANALYZE` really runs the statement, so wrap DML in a rolled-back transaction.

code

sql · 8 lines
sql
-- explain.sql: the reporting query under review
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS, FORMAT JSON)
SELECT c.region, sum(l.amount) AS revenue
FROM invoices i
JOIN invoice_lines l ON l.invoice_id = i.id
JOIN customers c ON c.id = i.customer_id
WHERE i.issued_on >= date '2026-09-01'
GROUP BY c.region;

go deeper

for a junior

Recall the command: EXPLAIN with ANALYZE, BUFFERS, VERBOSE and FORMAT JSON. Without ANALYZE, PEV2 has no real times or rows to show.

for a middle

Explain what each option adds to PEV2's views, why track_io_timing is a separate switch, and why JSON parses more reliably than wrapped or re-indented text.

for a senior

Show safe capture habits: roll back analysed DML, capture against production-like data, attach the query text, and know that 18 adds BUFFERS by default while 16 and 17 do not.

for a principal

Agree on a team convention for capturing plans, with fixed options, a JSON file and the query attached, so every reviewer sees comparable plans and no review stalls on a half-captured one.

## Why the input decides what PEV2 can show PEV2 never talks to PostgreSQL. It can only show what the pasted `EXPLAIN` output contains, so choosing the options means choosing what the visualiser will be able to tell you. The demo page that ships with PEV2 recommends `EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)`. `COSTS` is on by default, so in practice you add the rest. | Option | What PostgreSQL adds | What PEV2 gains | |---|---|---| | `ANALYZE` | runs the statement; actual time, rows and loops per node | exclusive time, % of execution, the estimate factor, row-weighted edges | | `BUFFERS` | shared, local and temp blocks hit, read, dirtied, written | the buffers metric and the per-node block columns | | `VERBOSE` | output column lists, schema-qualified names, per-worker detail | accurate per-worker figures on parallel nodes | | `SETTINGS` | planner settings that differ from the built-in default | a settings panel in the header | | `FORMAT JSON` | every field as a named key, nesting as arrays | exact parsing, no reconstruction from indentation | Two more inputs sit outside `EXPLAIN`'s option list. First, **`track_io_timing`** must be on for `BUFFERS` to include I/O read and write times. Without it, PEV2's IO metric stays disabled. The setting is off by default, and only a superuser or a role granted `SET` on it can turn it on in a session. Second, the **query text** is not part of `EXPLAIN` output. Paste it separately so the Query tab has something to show. ## The command to run Put the statement in a file and let psql write bare JSON: ```sql -- explain.sql EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS, FORMAT JSON) SELECT c.region, sum(l.amount) AS revenue FROM invoices i JOIN invoice_lines l ON l.invoice_id = i.id JOIN customers c ON c.id = i.customer_id WHERE i.issued_on >= date '2026-09-01' GROUP BY c.region; ``` ```bash psql -XqAt -d reports -f explain.sql > plan.json ``` `-X` skips `~/.psqlrc` and `-q` suppresses informational messages. `-A` switches off aligned output, and `-t` drops the column header and the row-count footer, so the file holds nothing but JSON. Drop `plan.json` onto PEV2 or paste its contents. ## Text or JSON PEV2 accepts both. It first tries to parse the input as JSON. If that fails, it looks for a JSON block inside surrounding noise, and only then falls back to its **text parser**. The text parser is careful but heuristic: - it rebuilds the tree from indentation and the `->` arrows, and reads costs, actual times, rows and loops with regular expressions; - it strips psql's table borders, the `QUERY PLAN` header, the `(N rows)` footer and the `+` continuation marks; - it tries to rejoin lines that a terminal or a chat tool wrapped, by counting parentheses and comparing indentation. That usually works, but copy-paste damage is exactly where a node can end up under the wrong parent. JSON removes the guesswork, because each node's children sit in an explicit `Plans` array. JSON is also **explicit about zeros**. The manual says non-text formats carry the same information as text. But text prints only the non-zero buffer counters, so a missing counter means zero, whereas JSON names every counter. ## Release differences worth knowing - **PostgreSQL 18** includes `BUFFERS` automatically whenever `ANALYZE` is used. On 16 and 17 you must request it. - **18** prints actual rows with two decimals (`rows=0.50`), so a per-loop average below one row is no longer rounded away. - **17** added the `SERIALIZE` and `MEMORY` options, and local-block I/O timings in `BUFFERS`. PEV2 shows the serialization and planning blocks when they are present. ## Pitfalls 1. `ANALYZE` **executes** the statement. For an `INSERT`, `UPDATE` or `DELETE`, wrap it in `BEGIN` and `ROLLBACK`. 2. `TIMING FALSE` keeps row counts but removes per-node times, which leaves PEV2's time views empty. 3. Clock reads cost time on some platforms. `pg_test_timing` measures that before you turn `track_io_timing` on in production. 4. Capture the plan where the data looks like production. A plan from an almost-empty development database is a different plan. 5. Paste the matching query text. A plan without its SQL is much harder for a colleague to review.

  • PEV2 drew a strangely nested tree from a text plan you pasted. What probably happened?
    The copy-paste changed the indentation or wrapped long lines, and PEV2's text parser depends on indentation and the `->` arrows to decide each node's parent. It does try to rejoin wrapped lines, but it can guess wrong. Re-capture with `FORMAT JSON` into a file using `psql -XqAt`. JSON nests children in explicit `Plans` arrays, so nothing has to be guessed.
  • Why add SETTINGS when capturing a plan for review in PEV2?
    `SETTINGS` lists every planner-affecting setting whose value differs from the built-in default, such as a raised `work_mem` or a disabled `enable_*` switch in the session. PEV2 shows them in its header, so a reviewer can see at once why the plan differs from what production would choose.
  • How do you capture a PEV2-ready plan for an UPDATE without keeping its changes?
    Run `BEGIN`, then the `EXPLAIN (ANALYZE, ...)` of the UPDATE, then `ROLLBACK`. The statement really executes: it takes row locks until the rollback, fires triggers, and advances sequences, which a rollback does not undo. Do it where blocking other sessions is acceptable.

  • A plain EXPLAIN is enough for PEV2 to show where the time went
  • On PostgreSQL 16, EXPLAIN ANALYZE already includes buffer counts
  • VERBOSE is what adds I/O read and write times to the BUFFERS output
  • Text and JSON plans print exactly the same set of buffer fields
  • EXPLAIN ANALYZE of a DELETE only plans it, so no rows are removed

Assumes PostgreSQL 18, where EXPLAIN ANALYZE includes BUFFERS automatically; on 16 and 17 BUFFERS must be requested explicitly.