EXPLAIN & EXPLAIN ANALYZE

The Query Planner picks a strategy silently — EXPLAIN is how you see which one it actually chose, and whether its estimates matched reality.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN alone shows the planned execution without running the query:

EXPLAIN SELECT * FROM accounts WHERE balance > 1000;

-- Seq Scan on accounts  (cost=0.00..18.50 rows=333 width=12)
--   Filter: (balance > 1000)

EXPLAIN ANALYZE actually executes the query and adds real timing and row counts alongside the estimates:

EXPLAIN ANALYZE SELECT * FROM accounts WHERE balance > 1000;

-- Seq Scan on accounts  (cost=0.00..18.50 rows=333 width=12)
--                       (actual time=0.015..0.089 rows=310 loops=1)
--   Filter: (balance > 1000)
--   Rows Removed by Filter: 690
-- Planning Time: 0.112 ms
-- Execution Time: 0.121 ms

Add BUFFERS to see actual page I/O — how much came from shared buffers (cache) versus disk, which is often more useful than timing alone for diagnosing a slow query:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM accounts WHERE balance > 1000;
-- ...
--   Buffers: shared hit=1 read=17

hit is a page already in shared memory; read came from disk (or the OS page cache). A query that's all hit on a warm cache but still slow points away from I/O and toward CPU-bound work — sorting, hashing, or function evaluation.

Reading the plan tree

Plans nest: the innermost, most-indented lines execute first, feeding rows up to the operations above them. For a join:

EXPLAIN ANALYZE
SELECT a.id, o.total
FROM accounts a
JOIN orders o ON o.account_id = a.id
WHERE a.balance > 1000;

-- Hash Join  (cost=25.00..65.30 rows=310 width=16)
--            (actual time=0.45..2.10 rows=298 loops=1)
--   Hash Cond: (o.account_id = a.id)
--   ->  Seq Scan on orders o  (cost=0.00..30.00 rows=2000 width=12)
--                             (actual time=0.01..0.55 rows=2000 loops=1)
--   ->  Hash  (cost=18.50..18.50 rows=310 width=8)
--             (actual time=0.35..0.35 rows=310 loops=1)
--         ->  Seq Scan on accounts a  (cost=0.00..18.50 rows=310 width=8)
--                                      (actual time=0.01..0.20 rows=310 loops=1)
--                Filter: (balance > 1000)

Read this bottom-up: scan accounts filtering balance > 1000, build a hash table from the result, scan orders in full, then probe the hash table for each orders row to find matches.

The one signal that matters most

Every line has both an estimated row count (rows=310 in the cost line) and an actual row count (rows=298 in the actual-time line). When these diverge wildly — the planner expected 10 rows and got 100,000 — every decision built on top of that estimate (which join strategy, which scan type) is likely wrong too, even though each individual choice looked reasonable given the (bad) numbers it had.

With planning and diagnosis covered, the next group of lessons moves back to SQL itself — starting with Joins, CTEs & Recursion.