EXPLAIN describes a PostgreSQL execution plan; EXPLAIN ANALYZE also executes the statement and measures its work. The useful comparison is between estimated rows and observed rows at the same node. Neither number is a verdict by itself: an estimate is a model, while a measurement describes one execution under particular data, cache, concurrency, and instrumentation conditions.

Read comparable quantities

Planner costs are relative units, not milliseconds. The two cost values describe startup and completion, and the preferred plan can change when LIMIT makes early rows especially valuable. Actual timings use milliseconds. Comparing a cost of 100 with an elapsed time of 100 is therefore meaningless even when the printed values happen to match.

For a node executed repeatedly, actual rows and node timings are averages per loop. Multiplying by loops helps expose repeated work, but summing every node’s time double-counts work included in parent nodes. Parallel execution makes naive totals still less useful. Read the tree, its loop counts, filters, and row flow before assigning blame to the visually largest number.

BUFFERS separates shared, local, and temporary block activity. Shared hits mean PostgreSQL found blocks in its shared buffer cache. A shared read does not prove a physical disk access: the operating system may satisfy it from another cache. Temporary tables use local buffers, while sort or hash spills can use temporary files. Parent counters include children, so adding all rows of counters inflates the apparent I/O.

Investigate the first meaningful discrepancy

A selective predicate estimated at ten rows but returning ten thousand deserves investigation before rewriting a join above it. Stale statistics, skew, correlated columns, parameter values, and unsupported selectivity models can all contribute. ANALYZE refreshes statistics, but it cannot guarantee exact estimates or repair every plan. Inspect representative data and consider targeted statistics changes only after identifying the missing information.

An accurate row estimate does not prove the query is efficient. Missing access paths, expensive expressions, spills, lock waits, and workload contention can remain. Conversely, a sequential scan can be appropriate for a small table or a query returning much of it. Measure the application’s actual query shape and parameters instead of treating every index-free plan as a defect.

When it breaks

The dangerous mistake is treating ANALYZE as a dry run. An UPDATE changes rows, triggers execute, locks are acquired, and a SELECT can call a function with side effects. Wrapping the statement in a transaction and rolling back restores transactional table changes, but sequence advances and external effects are not generally reversible. Use a disposable dataset when the statement’s effects are uncertain.

Timing also misleads when the first execution warms caches or instrumentation dominates a cheap operation. TIMING OFF retains actual row counts and total execution time while avoiding per-node clock reads. EXPLAIN discards ordinary result delivery, so its duration does not include sending the application’s full response over the network. Repeated representative measurements and application latency answer different parts of the performance question.

Check it quickly

Use psql against a disposable PostgreSQL 18 database. Save this block as explain.sql and run psql -X -v ON_ERROR_STOP=1 -f explain.sql. Connection options must name that database. Everything created here is temporary, and the final rollback removes it.

\set ON_ERROR_STOP on
BEGIN;
SET LOCAL statement_timeout = '5s';
CREATE TEMP TABLE orders (id integer, status text);
INSERT INTO orders SELECT g, CASE WHEN g <= 100 THEN 'paid' ELSE 'new' END
FROM generate_series(1, 10000) AS g;
ANALYZE orders;
EXPLAIN (COSTS ON) SELECT * FROM orders WHERE status = 'paid';
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM orders WHERE status = 'paid';
CREATE TEMP SEQUENCE measured_calls;
SAVEPOINT before_measurement;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
UPDATE orders SET id = id + 10000 WHERE id <= 3;
EXPLAIN (ANALYZE, TIMING OFF) SELECT nextval('measured_calls');
ROLLBACK TO SAVEPOINT before_measurement;
SELECT count(*) AS updated_rows_after_rollback FROM orders WHERE id > 10000;
SELECT nextval('measured_calls') AS sequence_after_rollback;
DO $$ BEGIN
  IF (SELECT count(*) FROM orders WHERE status = 'paid') <> 100 THEN
    RAISE EXCEPTION 'unexpected selected row count';
  END IF;
  IF EXISTS (SELECT FROM orders WHERE id > 10000) THEN
    RAISE EXCEPTION 'UPDATE was not rolled back';
  END IF;
  IF (SELECT last_value FROM measured_calls) <> 2 THEN
    RAISE EXCEPTION 'sequence advance unexpectedly rolled back';
  END IF;
END $$;
ROLLBACK;

The fixture contains exactly 100 paid orders. The measured SELECT reports 100 actual rows and 9,900 removed by its filter. The UPDATE’s scan reports three rows even though its top node returns zero without RETURNING; zero there does not mean nothing changed. After the savepoint rollback, the changed-row count is zero, but the next sequence value is two because the measured SELECT already consumed one.

On PostgreSQL 18.6 these assertions pass. Buffer counts, timings, formatting, and chosen plans can vary; the deterministic results are the row counts, rollback behavior, and sequence advance. The five-second statement timeout bounds individual statements in this exercise. It does not make arbitrary production writes safe to inspect.

Sources