One feed page takes a second to render. The data is only 400 posts and 750,000 comments, and the endpoint does something simple: take the 20 latest posts, then show each one's author name and comment count.
No errors. No yellow log lines. Not a single line that looks wrong when you read it. Until someone complains, you measure, and it turns out the database is being hit with 41 queries to serve that one request.
Key takeaways
- N+1 is one query to fetch a list, then N extra queries to complete each row in it.
- "Use eager loading" is not wrong, but it fixes how the data is fetched, not how much data has to cross the wire.
- Query count is the cheap metric to look at, which is why it is the only one anyone looks at. Two query batches can differ in cost by hundreds of times.
- You can work the whole case yourself in the lab:
make up, thenmake check.
N+1, and why it is called that
If you already know this part, skip one paragraph.
It is called N+1 because of the count: 1 query to fetch the main list, then N extra queries to complete each row in that list. For 20 posts, that is 21 queries.
posts = session.execute(select(Post).limit(20)).scalars().all()
for post in posts:
print(post.author.name) # every access fires a new query
The cause is lazy loading, the default behaviour of nearly every ORM: relation data is not fetched until you actually touch it. At a glance that is sensible — save memory, fetch only what you need. The problem is that "what you need" turns out to happen inside the loop, so instead of saving anything you get 20 round trips to the database for something that could have been done in one.
The advice everyone already knows
The fix is eager loading: tell the ORM to fetch the relation up front instead of one at a time.
posts = session.execute(
select(Post).options(joinedload(Post.author)).limit(20)
).scalars().all()
At this point you have done the standard advice correctly. Author names no longer fire an extra query. But that is all that changed:
| version | queries per request | p50 | p95 |
|---|---|---|---|
| as-is | 41 | 950 ms | 1,152 ms |
| author eager loaded | 21 | 915 ms | 981 ms |
You removed twenty queries and p50 dropped by 35 ms. About 4%. Why so small? Because the 20 queries you just removed were lookups by primary key — the cheapest thing a database can do. The round trips were wasteful, but the round trips were never the main cost.
This is where most people stop and conclude the problem must be somewhere else. It is still right in front of them: there is a second N+1, and it accounts for almost everything.
The second N+1
The remaining 20 queries count comments for each post. They look like this:
for post in posts:
post.comment_count = session.execute(
select(func.count())
.select_from(Comment)
.where(Comment.post_id == post.id)
).scalar_one()
Notice this code was written with good intentions. It uses COUNT(*) so it does not have to load
thousands of comment rows just to count them — that really is cheaper than len(post.comments).
One thing was missed: it runs inside a loop, so it is still one query per post.
And once the problem is visible, the fix looks trivial too — fetch everything at once, then count in memory:
posts = session.execute(
select(Post)
.options(joinedload(Post.author), selectinload(Post.comments))
.limit(20)
).scalars().all()
for post in posts:
post.comment_count = len(post.comments)
The query count is now 2. One for the posts with their authors, one for all the comments on the page. Down from 41, and that is a drop worth being pleased about.
Latency: 1,026 ms.
Two queries, one second
That is not a typo. At this point:
| version | queries per request | p50 | p95 |
|---|---|---|---|
| as-is | 41 | 950 ms | 1,152 ms |
| author eager loaded | 21 | 915 ms | 981 ms |
comments eager loaded, counted with len() |
2 | 983 ms | 1,026 ms |
Query count dropped 95%, and latency went up.
This is the part that makes most N+1 stories incomplete. "Use eager loading" is not wrong — but it fixes how the data is fetched, not how much data has to cross the wire. Forty-one cheap queries can be a far smaller problem than two queries that each move tens of thousands of rows from the database into application memory.
And the uncomfortable part: that last snippet feels like correct code. It uses eager loading, exactly what every N+1 article teaches — including this one, three sections up. You can read it three times and you will not find the mistake, because the mistake is not in how you wrote it.
What is still unanswered
If the query count is 2 and the time is still a second, the question has to change. Stop counting queries and ask this instead:
how many rows does the database have to read to serve this one request?
The answer is not forty-something. The answer is hundreds of thousands, and it happens 20 times. Meanwhile on the application side, the number crossing into memory is not 20 either — it is much larger.
Where those numbers come from and why they cost that much cannot be answered here. Not because the answer is long, but because you will not believe it until you see it yourself in a query plan.
The lab: you do the work
git clone https://github.com/izzudd/inva-lab.git
cd inva-lab/0001.n-plus-one
make up # 400 posts, 750,000 comments
make bench # look at the numbers first, before changing anything
make check # target: <= 3 queries, p95 <= 30 ms
What is inside is the "as-is" version of the table above — 41 queries, one second, and code that deliberately has no visible mistake. Those 41 queries are in every response header, so you can guess as much as you like before touching a line.
One thing to know up front: removing the N+1 will not be enough to make make check green. There
is a second layer, and that layer is not in the code.
If you are stuck for more than 45 minutes, HINTS.md has three levels. Use them in order, not the
third one first.
Stuck and want the answer? The answer: Two Fixes in Two Different Places