Query Plansoptimizationn+1select *offsetfan-out

Query Optimization: Finding the Actual Bottleneck

Slow queries have a short list of causes — missing index, wrong index, wrapped column, N+1, fan-out, deep OFFSET, SELECT *, bad statistics — and the plan tells you which one before you change anything.

▶ Interactive Interview question See how this works internally →

Progress

The usual suspects

Missing index: Seq Scan with a selective filter. Add the index. Wrong composite order: an index exists but the leading column is not constrained. Reorder or add. Wrapped column: lower(email), date(created_at) — expression index or rewrite as a range. N+1: one query per row in application code; the database sees a thousand fast queries and the user sees one slow page. Preload or join. Fan-out: an aggregate over a join that multiplied rows; wrong, not just slow. Deep OFFSET: linear in the page number; use keyset pagination. SELECT \*: wide rows, no index-only scan, more network. Stale statistics: estimates wrong by orders of magnitude; ANALYZE. Sort spilling: Sort Method: external merge — raise work_mem for the session or add an index that provides the order.

The N+1 problem

Fetch twenty users, then for each user fetch their order count: twenty-one queries. Each is sub-millisecond on the server and each pays a network round trip, so the page costs 21 × latency. At a hundred rows and two milliseconds of latency that is 200 ms of pure waiting, invisible in any per-query monitoring because no single query is slow. ORMs produce this by default when you access a relationship inside a loop.

The fix is one of two shapes: a single query with a join and GROUP BY, or two queries — the parents, then all their children with WHERE parent_id IN (…) — stitched in memory. Every ORM has a preload/eager-load mechanism for the second shape; use it. The tell in logs is the same statement repeated with different parameters in quick succession.

N+1 and its two fixes

1# N+1: one query per user
2users = db.query("SELECT id, name FROM users LIMIT 20")
3for u in users:
4    u.orders = db.query("SELECT count(*) FROM orders WHERE user_id = %s", u.id)
5 
6# Fix 1: one query
7rows = db.query("""
8  SELECT u.id, u.name, count(o.id) AS orders
9  FROM users u LEFT JOIN orders o ON o.user_id = u.id
10  GROUP BY u.id, u.name LIMIT 20""")
11 
12# Fix 2: two queries, stitched
13users = db.query("SELECT id, name FROM users LIMIT 20")
14counts = dict(db.query("SELECT user_id, count(*) FROM orders WHERE user_id = ANY(%s) GROUP BY user_id",
15                       [u.id for u in users]))
16for u in users:
17    u.orders = counts.get(u.id, 0)

Query → plan → bottleneck → fix → plan

The loop that never fails: get the real query with real parameters; EXPLAIN (ANALYZE, BUFFERS); identify the single node responsible for most of the time or rows; apply the one change that addresses that node; run the plan again and confirm the node changed. Then, if it is still slow, repeat for the next node. Never change two things at once and never skip the second EXPLAIN.

Resist the reflex to add an index first. Half of slow queries are not missing an index — they are asking for too much (SELECT *, OFFSET 50000, a join that was not needed) or asking badly (N+1, wrapped columns). Those are fixed in the query, and an index would have hidden the problem for another year.

Key points

Eight causes cover most slow queries; the plan tells you which.
N+1 is invisible per query and dominant per page. Preload or join.
Change one thing, re-run EXPLAIN, confirm the node changed.
Not every slow query wants an index; many want a smaller question.

N+1 queries

Render 20 users with their order count. Same page, three implementations — count the statements, then multiply by network latency.

Statements sent

Rows returned

+ round-trip cost @ 0.4 ms

8.4 ms

Statements

SELECT id, name FROM users ORDER BY id LIMIT 20;
SELECT count(*) FROM orders WHERE user_id = 1;   -- 149
SELECT count(*) FROM orders WHERE user_id = 2;   -- 60
SELECT count(*) FROM orders WHERE user_id = 3;   -- 44
SELECT count(*) FROM orders WHERE user_id = 4;   -- 38
-- … 16 more, one per user

One query for the list plus one per row: 1 + 20 = 21 statements. Each is fast on its own — that is what makes this hard to spot. The cost is the round trip, so it scales with page size and with distance to the database. At 100 rows and 2 ms latency this page takes 200 ms of pure waiting.

executed here in 36.5 ms