Reading a Postgres Query Plan with EXPLAIN ANALYZE

The slowest part of most "slow API" incidents is one query, and the fastest way to find out why it's slow is to stop guessing and ask the database. EXPLAIN ANALYZE runs the query and reports the exact plan it chose, with real timings. Learning to read it is maybe the highest-leverage database skill there is.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows the plan the planner would use, with estimates. EXPLAIN ANALYZE actually runs it and reports what happened — real row counts, real time per step. I almost always want the second one.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;

Read it inside-out

A plan is a tree. It executes from the innermost/most-indented node outward — the deepest nodes run first and feed their parents. Each node reports its operation, an estimated row count, and (with ANALYZE) the actual rows and time.

Sort  (actual time=12.3..12.4 rows=8 ...)
  Sort Key: created_at DESC
  ->  Index Scan using idx_orders_user on orders  (actual time=0.02..0.05 rows=8 ...)
        Index Cond: (user_id = 42)

Read that bottom-up: an index scan fetched 8 rows for user_id = 42 fast, then a sort ordered them. That's a healthy plan.

The scan types, and what they signal

The single most important word in any node is the scan type:

• Index Scan — used an index to jump to matching rows. Usually what you want for selective filters.
• Seq Scan (sequential scan) — read the entire table. Fine on small tables and when you're selecting most rows. A red flag on a large table with a selective WHERE.
• Bitmap Heap Scan — a middle ground for medium selectivity, combining an index with batched row fetches.
• Index Only Scan — answered entirely from the index without touching the table. The fastest case.

A Seq Scan on a million-row table where you filtered to 10 rows means an index is missing or unusable — that's your fix.

The three red flags I look for

1. Seq Scan on a big table with a selective filter. The planner is reading everything because it has no better option. Add or fix the index the filter needs.

2. Estimated rows wildly different from actual rows. If the plan estimated 5 rows and 50,000 came back, the planner is working from stale statistics and will keep making bad choices. Refresh them:

ANALYZE orders;   -- recompute statistics the planner relies on

3. A Nested Loop over many rows — the N+1 in disguise. A nested loop is great when the outer side has few rows. When it runs the inner lookup tens of thousands of times, it's quadratic. This is the query-plan signature of the classic N+1 problem, where an ORM fires one query per parent row.

Fixing the N+1

The N+1 is the performance bug I catch most in application code. The ORM makes it invisible: loop over 100 orders, touch order.user, and quietly fire 100 extra queries.

1 query:   SELECT * FROM orders LIMIT 100;
100 queries: SELECT * FROM users WHERE id = ?;   -- once per order

The fix is to fetch related data in one query — a join, or the ORM's eager-loading (JOIN, include, selectinload, depending on your stack). One query with a join replaces 101 round trips. EXPLAIN won't show you the N+1 directly — your query log will, by showing the same query repeated — but the nested-loop plan is the hint that you're doing per-row lookups.

The workflow

When an endpoint is slow, the loop is fast and mechanical: find the query (from logs or an APM), run EXPLAIN ANALYZE on it, read the tree inside-out, and look for a big Seq Scan, a bad estimate, or a hot nested loop. Then act — add the missing index, run ANALYZE to refresh stats, or collapse an N+1 into a join. Re-run EXPLAIN to confirm the plan actually changed. No guessing, no "try adding an index and see" — the database already knows the answer and will hand it to you.