skip to content

PEV2

PEV2 renders PostgreSQL EXPLAIN text or JSON as an interactive tree, so the slow node, the row misestimate or the buffer hog stands out. Interviewers use it to check you can read a plan quickly.

on this pageshow

explore

questions

6

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.

open as a page

In PEV2, what do the buffers and IO views reveal about a PostgreSQL plan node, and why can the IO view be unavailable?

level: middleimportance: should knowfreq 22%

basics

~20 s

PEV2 subtracts children's counts to show each node's own shared, temp and local blocks hit, read, dirtied and written. Its IO view needs per-node I/O times, which PostgreSQL records only with track_io_timing on, and it is off by default.

open as a page

In PEV2, why can the PostgreSQL plan node with the largest actual time in raw EXPLAIN ANALYZE text differ from the node PEV2 marks as slowest?

level: seniorimportance: should knowfreq 27%

basics

~10 s

EXPLAIN ANALYZE prints each node's time per loop, including its children. PEV2 multiplies by loops, divides by the parallel processes sharing the work, subtracts the children, and ranks nodes by that exclusive time instead.

open as a page

How does PEV2 flag a row-estimate miss in a PostgreSQL plan, and how do you decide which flagged node actually matters?

level: seniorimportance: should knowfreq 25%

basics

~20 s

PEV2 divides actual by planned rows (or the reverse for overestimates) and badges nodes off by over 10x, 100x and 1000x. The miss that matters is the lowest one that drove a join or aggregation choice above it.

open as a page

For sharing a slow PostgreSQL reporting-query plan with a colleague in code review, how do you choose between explain.dalibo.com, the offline pev2.html and an embedded PEV2 component?

level: seniorimportance: should knowfreq 14%

basics

~20 s

Use explain.dalibo.com only when the plan and query may leave your control, because sharing stores them on a third-party service. Otherwise open the offline pev2.html, where plans stay in the browser, or embed the pev2 component in an internal tool.

open as a page

What does PEV2 show when you paste a PostgreSQL EXPLAIN ANALYZE plan into it, and why is that faster to read than the raw text?

level: juniorimportance: nice to knowfreq 18%

basics

~20 s

PEV2 draws a PostgreSQL plan as a node tree, computes each node's own time, rows, cost and buffers, and badges the outliers, so the slow node, a large row misestimate or a wasteful filter stands out at a glance.

open as a page