N+1 vs JOIN

401 fast queries still make a slow page

4 min read

An N+1 query fetches a list in one query and then runs a separate query for each row's related data; a single JOIN fetches the list and all its related rows in one round trip. That is the difference between N+1 queries and a single JOIN — and it is the whole reason a page can fire four hundred queries that are each individually fast and still take seconds to load. The cost was never the queries. It was the round trips.

The N+1 query

The application asks for a list: SELECT * FROM posts LIMIT 100. That is the 1 — one query, one round trip, a hundred rows back. Then the code does the obvious thing and loops over them: for post in posts: print(post.author.name).

Here is the part nobody wrote on purpose. Most ORMs lazy-load relationships, so post.author was not fetched with the list. The first time the loop touches it, the ORM quietly issues a query on the spot — SELECT * FROM authors WHERE id = ? — and does it again for every post in the list. That is the N. One page now costs N + 1 round trips.

The defining detail: not one of those queries is slow. Each is a primary-key lookup that returns in a few hundred microseconds. The page is slow anyway, because the cost is round-trip latency multiplied by N, not query time multiplied by N. This is why the bug is invisible where it is written — a local database over a socket with five rows of seed data answers instantly — and shows up in production, where there is a network hop and four hundred rows. A query log full of sub-millisecond queries tells a beginner nothing is wrong. Everything is.

The single JOIN

Now tell the ORM up front that you need the relation. It issues one query that joins parents and children together — or one extra batched WHERE id IN (...) query — and hands back everything at once. One round trip instead of a hundred and one.

This is not a free win, and pretending it is would be dishonest. A JOIN returns a wider, larger result set: the parent columns are duplicated across every child row. The batched IN form — SQLAlchemy's selectinload, and its siblings — avoids that duplication by using a second query instead of a join, which is why the honest recap is "one round trip," not "one JOIN." Eager loading has two legitimate shapes, and either way the database does more work per query. You get the round trips back; you pay in bytes.

Eager vs lazy loading

The switch between the two behaviours is a single ORM setting, and this is the eager vs lazy loading distinction. Lazy is the default in most ORMs — ActiveRecord, SQLAlchemy, Hibernate, Doctrine — because in isolation it is the safe general choice: do not fetch what the caller might never touch. Eager loading tells the ORM to fetch the relation alongside the parents.

The trap is that lazy loading looks free until you touch the relation inside a loop, at which point "fetch it only if you need it" becomes "fetch it N times." That gap between the default and the loop is the entire ORM N+1 problem: the developer wrote a for-loop, and the ORM wrote four hundred queries.

How to fix N+1 queries

Everything about how to fix N+1 queries is one instruction to the ORM: name the relation you are about to use, so it is fetched up front. The keyword differs by ecosystem; the effect is identical.

• Rails / ActiveRecord — .includes(:author)
• SQLAlchemy — selectinload() or joinedload()
• JPA / Hibernate — JOIN FETCH
• EF Core — .Include()

The catch is not to overcorrect. Eager-load a relation you never actually read and you have traded N round trips for one enormous result set full of data you throw away — the opposite bug. The rule is narrow: eager-load the relations a page touches, lazy-load the ones it does not. Find the offenders by their signature in the query log — one query, then a burst of near-identical queries that differ only by an id.

N+1 vs JOIN in a system design interview

An interviewer rarely says "N+1." They describe a symptom — "the list endpoint got slow as the table grew, but every query in the log is fast" — and watch whether you reach for the right cause. The crisp answer names the mechanism before the fix: the ORM is lazy-loading a relation inside a loop, so one page costs one query per row. Say that and you have named the bug.

Then they push on the fix, and the trap is answering "add a JOIN" and stopping. The stronger answer is "eager-load the relation — a JOIN or a batched IN — so the page is one round trip," followed immediately by the cost: a larger, duplicated result set, and the risk of eager-loading relations you never read. Bonus points for placing it at the right layer, because the same bug lives in GraphQL resolvers, where the fix is request batching with a DataLoader rather than a SQL JOIN. Naming both sides — the round trips you save and the bytes you pay — is what separates a memorised answer from an understood one.

Sources

• AppMap — Eager Loading and the N+1 Query Problem
• TheCodingMachine — Solving the N+1 problem in ORMs