N+1 query pattern (missing child-table index)

N+1 query pattern (missing child-table index)
Type: incident simulation · Topic: Query Performance · Level: L2 · Duration: 10–15 min
Launch: ride postgres start stage-01/08-n-plus-one-query-pattern

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the application loads a page of orders and then issues one
`SELECT * FROM order_items WHERE order_id = $1` per order — a classic N+1
pattern. Each query is individually fast-looking, but there are thousands of
them, and order_items had no index on order_id, so every call was a Seq Scan.
The latency hid behind call *count*, not single-query time.

How it was found: pg_stat_statements showed a child query with an enormous
`calls` value; EXPLAIN on the child query showed a Seq Scan on order_items.

The fix (database side): CREATE INDEX ON order_items (order_id);
Each lookup becomes a cheap Index Scan, which removes most of the damage.

The real fix (application side): stop issuing N+1 queries — batch them
(`WHERE order_id = ANY($1)`) or join order_items in the original query. The
index is mitigation; eliminating the N+1 is the cure.

Lesson: when total latency is high but every individual query is fast, sort
pg_stat_statements by calls. Index the child foreign key, and push the team to
batch/join the access pattern.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Don't sort pg_stat_statements only by total time — sort by `calls`. The pain here is one tiny query executed a huge number of times (an N+1 pattern from the app).
2. That per-item query is `SELECT ... FROM order_items WHERE order_id = $1`. EXPLAIN it: order_items has no index on order_id, so every one of the thousands of calls is a Seq Scan.
3. Index the child foreign key: CREATE INDEX ON order_items (order_id). Each lookup becomes an Index Scan. (The real app-side fix is to batch/join instead of N+1 — see the postmortem.)