EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows the planner’s chosen plan without running the query. EXPLAIN ANALYZE runs the query and adds actual timings and row counts.

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Warning: EXPLAIN ANALYZE actually executes — including INSERT, UPDATE, DELETE. Wrap in a transaction and roll back if you don’t want the side effect:

BEGIN;
EXPLAIN ANALYZE DELETE FROM ...;
ROLLBACK;

Reading a plan — top-down

Sort  (cost=0.42..1.50 rows=10 width=80) (actual time=0.123..0.135 rows=10 loops=1)
  Sort Key: created_at DESC
  Sort Method: top-N heapsort  Memory: 26kB
  ->  Index Scan using idx_user_id on posts  (cost=0.29..1.36 rows=10 width=80) (actual time=0.018..0.105 rows=10 loops=1)
        Index Cond: (user_id = 42)

• cost=A..B — A is startup cost (time to first row), B is total. Unitless; relative to a sequential page read.
• rows — planner’s estimate. Compared against actual rows; large mismatches mean stale stats.
• actual time=A..B — A is time to first row, B is per-loop total time. With loops=N, true total = B × N.
• loops — how many times the node executed. A nested loop’s inner side runs once per outer row.

The biggest red flag: estimated rows differs from actual rows by orders of magnitude. Run ANALYZE <table>; to refresh statistics.

Scan types

Bitmap scans are faster than index scans when you’d otherwise touch many random heap pages — the bitmap converts random I/O into mostly sequential.

Join types

Postgres picks nested loop when the outer side has very few rows (~thousands or less) AND the inner side has an index. Otherwise hash join. Merge join is rare in OLTP — common when the sort is “free” (e.g., already sorted by index).

BUFFERS — the I/O picture

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Index Scan using idx_email on users
  Buffers: shared hit=3 read=1

• shared hit — pages found in shared memory (cache). Fast.
• shared read — pages read from disk. Slow.
• shared dirtied / written — pages modified.

A query with thousands of reads is hitting cold disk. Repeating it should switch them to hit.

Common pathologies

• Mismatch in estimated vs actual rows: stats stale → ANALYZE table; (or autovacuum is too lazy → tune autovacuum_analyze_scale_factor).
• Seq Scan when an index exists: planner thinks the scan is cheaper. Either the index isn’t selective enough, or random_page_cost is set too high (default 4 — set to 1.1 for SSDs).
• Sort using disk (Sort Method: external merge): work_mem is too small for this query.
• Nested Loop with huge outer side: bad estimate. Often fixed by ANALYZE or by adding a multi-column index.
• Lossy bitmap heap scan (Heap Blocks: lossy=N): work_mem ran out, bitmap stored at page granularity → re-checks each row.

SQLAlchemy: getting the plan for a query

from sqlalchemy import text

stmt = session.query(User).filter(User.email == "a@b.com")
compiled = stmt.statement.compile(compile_kwargs={"literal_binds": True})
plan = session.execute(text(f"EXPLAIN ANALYZE {compiled}")).fetchall()
for row in plan:
    print(row[0])

Or use pg_stat_statements to see plans for real production queries (after the fact, with timing histograms).

Interview angle

• Q: “What’s EXPLAIN ANALYZE and what does it tell you?”
• Q: “Sequential scan vs index scan — when does the planner pick which?” — selectivity. >~10% of table → seq scan often wins.
• Follow-up: “Estimated rows say 10, actual says 10 million. What do you do?” — ANALYZE; if still wrong, multi-column stats or extended statistics.
• Follow-up: “How do you read Buffers: shared hit=N read=M?” — N pages from cache, M from disk; high read = cold cache or working set > shared_buffers.

See 02_indexes.md, 11_index_types.md.