Use EXPLAIN ANALYZE Without Surprises

Photo: Unsplash.

Plain EXPLAIN shows PostgreSQL’s estimates without running the query. Adding ANALYZE executes it and reports actual time and row counts. That difference is the first safety rule.

Begin with the estimated plan:

EXPLAIN
SELECT *
FROM posts
WHERE published_at >= now() - interval '30 days'
ORDER BY published_at DESC;

For a read-only query in a controlled environment, add actual execution data and buffer activity:

BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM posts
WHERE published_at >= now() - interval '30 days'
ORDER BY published_at DESC;

ROLLBACK;

statement_timeout limits a plan that is much more expensive than expected. BUFFERS shows whether work came from shared-buffer hits, reads, writes, or temporary blocks.

Compare estimated rows with actual rows at each important node. A large mismatch can point to stale statistics, correlated columns, or a predicate the planner cannot estimate well. Also multiply per-loop actual time and rows conceptually by loops when reading a node executed repeatedly.

Be careful with write statements. EXPLAIN ANALYZE UPDATE ... really performs the update. A transaction followed by ROLLBACK can undo database changes, but it does not make every external side effect harmless. Triggers, locks, load, sequences, and calls outside the database deserve separate consideration. Prefer a production-like copy when analyzing risky statements.

Finally, do not optimize only for the lowest time in one run. Warm caches, data size, parameter values, and concurrent load change plans. Save the query, PostgreSQL version, plan, parameters, and relevant table sizes so the result can be understood later.

Reference

• PostgreSQL: Using EXPLAIN