Read a plan in depth with EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)

Command

-- ANALYZE really executes the statement: run DML inside a transaction and roll it back
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, VERBOSE)
UPDATE customer SET balance = balance + 1 WHERE city = 'Ankara';
ROLLBACK;

-- For SELECT
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT name FROM customer ORDER BY name DESC LIMIT 10 OFFSET 1000;

What it does

Returns the plan with real execution statistics: BUFFERS shows shared hit/read/dirtied and temp blocks per node, SETTINGS shows non-default parameters that affect planning, WAL shows the WAL records/bytes generated.

When to use it

To analyze the gap between estimated and actual rows, sorts/hashes spilling to disk (Sort Method: external merge, temp read/written) and the WAL cost of DML.

Caution

ANALYZE really executes the statement; always run DML inside BEGIN/ROLLBACK (triggers and sequence increments still happen). Reading tips: a 10x or larger gap between estimated 'rows' and 'actual rows' points to a statistics problem; for nodes inside loops, time and rows are per loop, multiply by loops for the total; high 'Heap Fetches' on an Index Only Scan means the visibility map is stale, and 'Workers Planned' > 'Workers Launched' means the worker pool is exhausted. Use TIMING OFF where timing overhead is high; COSTS OFF also hides the JIT section.