Lesson 15 · Query performance

Reading EXPLAIN / EXPLAIN ANALYZE

The single most useful PostgreSQL skill — see the plan the planner chose.

Your win: read a query plan, tell an estimate from an actual, and spot the node that's costing you — the skill every "diagnose a slow query" interview needs.

Two commands

EXPLAIN vs EXPLAIN ANALYZE EXPLAIN shows the chosen plan + cost estimates, without running the query. EXPLAIN ANALYZE actually runs it and adds actual time, rows, and loops.¹ ⚠️ Because ANALYZE executes, on an INSERT/UPDATE/DELETE wrap it in a transaction you ROLLBACK.

A plan is a tree — read it inside-out

EXPLAIN ANALYZE
SELECT subject FROM emails WHERE resource_path = $1 AND status = $2;

 Index Scan using idx__emails__resource_path on emails
   (cost=0.42..8.10 rows=3 width=32)
   (actual time=0.03..0.05 rows=2 loops=1)
   Filter: (status = $2)

Nodes at the bottom (leaves) are scans that return rows; parents are joins/sorts/aggregates that consume them. Read bottom-up, inside-out. Each node shows cost=start..total and estimated rows; with ANALYZE also actual time=start..total, actual rows, and loops.

What to look for

Signal	Means
estimated `rows` ≫/≪ actual `rows`	stale/missing stats (Lesson 14) → likely a bad plan
`Seq Scan` on a big table + selective `Filter`	a missing/unused index (Part 3)
high `loops` on a `Nested Loop`	inner side rescanned too often (maybe should be a Hash Join)
most of total time in one node	that's your bottleneck — start there

Add BUFFERS (EXPLAIN (ANALYZE, BUFFERS)) to see actual I/O — the truest measure of "expensive".

Anchor — the workflow here Our app code doesn't call EXPLAIN. Instead: OpenTelemetry spans (interceptors.StartSpan on every repo method) tell you which query is slow; then the /query-db skill (read-only, RLS set for you) lets you run EXPLAIN ANALYZE on the real SQL against a Cloud SQL DB. Paste the output into explain.depesz.com to highlight the slowest nodes.

PostgreSQL — Using EXPLAIN + the depesz visualizer

The authoritative walkthrough of plan output; then paste a real plan into depesz for a ranked breakdown, or pgMustard for suggestions.

→ postgresql.org/docs — Using EXPLAIN
→ explain.depesz.com · pgMustard EXPLAIN glossary

Check yourself (from memory)

Q1. EXPLAIN ANALYZE differs from EXPLAIN by…

ANALYZE executes it → real time/rows/loops. Wrap writes in a rolled-back transaction.

Q2. A Postgres plan tree is read…

Leaves (scans) produce rows that flow up into joins/sorts/aggregates. Read inside-out.

Q3. A big estimated-vs-actual row gap suggests…

The planner mis-estimated selectivity — run ANALYZE; the chosen plan is probably wrong because of it.

What do EXPLAIN / EXPLAIN ANALYZE show, how do you read the tree, and the #1 red flag?

recall, then click to reveal

EXPLAIN shows the chosen plan + cost ESTIMATES without running; EXPLAIN ANALYZE RUNS it and adds ACTUAL time/rows/loops per node (on writes, wrap in a rolled-back tx). The plan is a tree — read bottom-up/inside-out: leaves are scans feeding parent join/sort/aggregate nodes. #1 red flag: a large gap between ESTIMATED and ACTUAL rows (stale/missing stats → bad plan); also a Seq Scan on a big table with a selective filter, and high loops on a Nested Loop. Paste into explain.depesz.com to rank the slow nodes.

Want me to walk a real plan with you node-by-node? Run one via /query-db and paste it — ask me.

1. PostgreSQL — Using EXPLAIN.