EXPLAIN ANALYZE in PostgreSQL, and when not to run it

Plain EXPLAIN shows what PostgreSQL expects to do. EXPLAIN ANALYZE shows what actually happened, because it runs the statement. That makes it the best way to find where the time really goes, and the riskiest one to run carelessly.

-- A read-only query, on a copy or at a quiet time
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

-- A statement that writes: roll it back
BEGIN;
EXPLAIN (ANALYZE) UPDATE orders SET status = 'archived' WHERE created_at < '2024-01-01';
ROLLBACK;

The rollback undoes the row changes, but the statement still did the work and held its locks while it ran. Triggers fired, sequence values were used up, and anything a function did outside the database is not undone. Prefer a copy of the database for anything heavy.

Index Scan using idx_orders_customer_created on orders
  (cost=0.43..812.20 rows=520 width=16) (actual time=0.031..4.870 rows=498 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.210 ms
Execution Time: 5.012 ms

The sample numbers are made up for this guide.

• actual time is in milliseconds: the time to the first row, then to the last. Compare it between nodes to find the slow one.
• rows appears twice. The estimate (inside cost) and the real count (after actual). A large gap, for example expecting 10 and getting 100,000, usually means the table statistics are stale. Run ANALYZE on the table and plan again.
• loops is how many times the node ran. Multiply actual time by loops to get its real cost; a small node run 50,000 times is not small.
• BUFFERS adds how many 8 KB blocks were found in memory and how many were read from disk.