Finding and fixing N+1 queries

I spent most of a week chasing a page that took 1.8 seconds to render a list of 200 orders. Every query was fast. There were 201 of them.

That is the N+1 pattern. You run one query to get N parent rows, then loop over them and run one more query per row to fetch related data. The total is N+1 round trips, and the time goes to the round trips, not to the work the database does.

What it looks like

orders = query("SELECT id, total FROM orders WHERE customer_id = $1", cid)
for order in orders:
    order.items = query("SELECT sku, qty FROM order_items WHERE order_id = $1", order.id)

Each inner query is trivial and indexed, which is why it hides. A code review sees one function and one loop, with nothing that looks like a join.

Spotting it

The query log is the first place to look. Log statements on staging with a request id in the log line prefix, then look at one request. If the same statement shows up dozens of times with only the parameter changing, you have found it.

The second place is pg_stat_statements. It normalizes literals to $1 placeholders, so an N+1 collapses into one row with a huge call count. The extension has to be listed in shared_preload_libraries and created in the database first.

SELECT left(query, 80) AS query,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 0) AS total_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

The signature is a high calls count with a low mean_ms. Each call looks innocent, and the total time column tells you whether it matters. I reset the counters with pg_stat_statements_reset() before each load test so the numbers belong to one scenario.

Fix 1: a JOIN

When you control the query, a join is the cleanest fix.

SELECT o.id, o.total, i.sku, i.qty
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.customer_id = $1
ORDER BY o.id;

Parent data repeats on every child row, so the application folds rows back into a tree. The ordering makes that easy. If you want one row per order, json_agg can aggregate in SQL, but keep an eye on the size of the result.

Fix 2: batch with ANY

When the child logic is more involved, I keep two queries and make the second one a batch.

SELECT order_id, sku, qty
FROM order_items
WHERE order_id = ANY($1::bigint[]);

The application collects the ids, sends them as one array parameter, and groups the results in memory. That is two round trips no matter how big N is. A B-tree index on order_id is still used for = ANY(array), so this does not become a sequential scan. Watch the array size, though. A few thousand ids is fine. Several hundred thousand needs chunking.

Fix 3: eager loading

Most ORMs offer a preload or include option that does the batching for you:

orders = Order.where(customer_id: cid).preload(:items)

Under the hood this is usually the batch approach. The trap is enabling lazy loading everywhere, then having a template or serializer touch a relation the query never preloaded. Check the query count after a change, not only the output.

When N+1 is fine

Not every loop is a bug. If N is bounded at about three and each lookup is a primary key hit, the extra round trips cost a millisecond or two. Rewriting that into a join makes the code harder to read for no measurable gain. Measure first.

Connection pooling changes the math

A pool does not reduce the number of queries. Each query still checks out a connection, sends a statement, waits, and returns the connection. The round trip is what you pay per query, and it grows with distance. On a local socket it is tens of microseconds. Between availability zones it is around half a millisecond. Across regions it can be 50 ms or more.

So 200 queries at 0.5 ms each is 100 ms of waiting. Under concurrency it is worse. With a pool of 10 connections and requests that each hold one for 100 ms, the whole service tops out near 100 requests per second before the database is slow at all. I have seen a pool exhaustion alert that turned out to be an N+1 in disguise. Shrinking the time each connection is held often beats any index you could add.