Slow Query & N+1 Diagnosis

Logging & Log Analysis's own capstone found an N+1 pattern by comparing query counts before and after a deployment. This chapter goes deeper: how to recognize an N+1 pattern directly, how to tell it apart from a single genuinely slow query (a different problem with a completely different fix), and how to read just enough of an EXPLAIN plan to spot the single most common cause of a slow query without needing a full database-tuning background.

What N+1 Actually Is

Instead of one query that fetches everything needed at once — a JOIN, or a single query with an IN clause — the code runs one query to get a list of N items, then loops through them and runs N additional individual queries, one per item, to fetch each one's related data. N+1 total queries where 1 or 2 would have done the job. A very common ORM pitfall, usually from a relationship being lazily loaded inside a loop without anyone noticing.

Recognizing N+1 vs. a Single Slow Query

The fix for each is completely different, so telling them apart matters — and the distinguishing evidence is straightforward: count the queries a single request triggers, and look at each one's own duration.

One long query and dozens of individually-fast ones can add up to almost the same total time — but the fix couldn't be more different: optimizing or indexing one query, versus restructuring the code so it stops looping and issuing a query per item.

Reading EXPLAIN for a Genuinely Slow Single Query

A full database-tuning background isn't needed to catch the single most common cause of a slow query — a full table scan on a large table:

Seq Scan means the database read through the entire table — 2.5 million rows, to find one matching row — because no index exists on the column being filtered. Contrast with a healthy result on an indexed column:

Same query shape, over 50,000 times faster — the entire difference is having (or not having) the right index. Recognizing Seq Scan on a large table in an EXPLAIN output is, by itself, one of the highest-value diagnostic skills in this chapter.

The N+1 Fix, at a Practical Level

Two standard fix shapes worth being able to describe clearly, even if the actual code change belongs to a developer: batch loading (one additional query using an IN clause to fetch every needed related record at once, instead of one query per item) or a JOIN that retrieves everything in a single query from the start.

Working Example: The Slow Order History Page

A fresh ticket: the order history page takes 8+ seconds to load for users with a long order history, but loads instantly for new users with few orders. That user-count correlation is itself a strong early hint — a single slow query wouldn't care how many orders a particular user has; an N+1 pattern would scale exactly this way. Query logging confirms it directly: one query fetching the order list, followed by one additional query per order to fetch that order's line items — 51 total queries for a user with 50 orders, each individually fast, together adding up to the full 8 seconds plus per-query round-trip overhead. Replacing the per-order loop with a single batched query (an IN clause covering every order ID from the first query) cuts the page load from 8 seconds to under 200ms — the same total data, retrieved in two queries instead of fifty-one.

Hands-On Exercises