The N+1 query problem: why the page is slow when every query is fast

The list page takes four seconds. You open the statement log, and there is not one slow query in it — a hundred and one lines, every one under a millisecond. The database did about four hundred milliseconds of work inside a four-second request, and no profiler tells you where the rest went.

The video builds that count in front of you: one query for the page, one more per row when the loop touches p.category, then a thousand once the reviews under each product touch their authors. Its fix is one of two things — ask for the related row in the same statement, or fetch related rows in one second statement carrying a list of ids — closing on the rule the subject reduces to: the number of queries a page runs must not grow with the number of rows on it. That’s right, and it’s ninety seconds, which leaves out two things that let a reader ship the fix wrong.

Everything below is checked against PostgreSQL 18.6 and each mapper’s own documentation — SQLAlchemy, Rails, Django, EF Core — as examples of a pattern every mapper with lazy loading shares.

A batched second query is two points in time

The video’s fix for one-to-many — one query for the products, one more for every review whose product_id is in the list — is strictly better for the row count. For one other thing it’s strictly worse.

A single statement sees one snapshot: under Read Committed, a SELECT “sees only data committed before the query began.” The join is one query, so everything it returns is from one instant. Two statements are two instants, and the mappers say so themselves — EF Core: “no such guarantees exist for multiple queries. If the database is updated concurrently… resulting data may not be consistent.” Django’s own example is a pizza deleted between the two queries, which comes back with no toppings.

Applied to the catalogue: the page query runs, product 42 is on it, a review is deleted, the reviews query runs — product 42 shows one review fewer than its review_count column says. Neither a crash nor a bug you’ll reproduce on demand; the kind of thing reported once a month. The mitigation — wrapping both statements in a Repeatable Read transaction so they share one snapshot — has its own cost, which EF Core states plainly: it “may create performance issues of its own,” because the snapshot is held for the whole transaction and anything the transaction writes can now fail and need a retry. So the honest version of the batch fix is a decision: for a list with a few related fields, two statements are fine, because it’s stale by the time a human reads it anyway. For a list next to a total computed from the same rows, or anything that drives a write, it isn’t — join, aggregate in one statement, or accept the transaction’s cost.

A thousand ids in an IN list is a problem of its own

The other half of the batch fix is the list itself. A hundred ids is fine. It’s the page that grew, or the export using the same helper, where it stops being fine. Django’s docs warn that prefetch_related “could [generate] a large ‘IN’ clause… which might have performance problems of its own”; some engines — Oracle, SQLite — cap how many terms an IN clause can hold outright; and a query whose literal list changes with every page is never the same statement twice, so the server reparses it each time.

Two fixes, and they compose: chunk the list (SQLAlchemy does 500 ids by default), and on PostgreSQL, pass it as one array parameter instead of a thousand literals:

SELECT id, product_id, stars FROM reviews
WHERE product_id = ANY($1);   -- $1 = '{41,42,43,...}'

The statement text is identical whether the list holds ten ids or ten thousand, which means it can be prepared once.

A slow query is not a slow page

Everything a developer reaches for — the log, pg_stat_statements, EXPLAIN ANALYZE — answers how long did this statement take. A page is slow because of how many statements it took and what happened between them, and the tools weren’t built to say that. pg_stat_statements folds a hundred identical lookups into one row with a mean_exec_time around a tenth of a millisecond — sort by that column and the N+1 is nowhere. Sort by calls instead, and the query whose call count tracks a page’s row count is the tell — a different column from the one every slow-query dashboard sorts by. The correction worth keeping: the database is fine; the conversation with it is not.

Half the advice on this topic is “add a join,” and it’s right for exactly half the cases. A foreign key pointing at a primary key — category_id to categories.id — is many-to-one: the join returns one row per product, and nothing multiplies. The same shape from the other side — reviews.product_id pointing at products.id — is one-to-many: the product’s columns repeat once per review, and LIMIT 100 on that join can return twelve and a half products with no sign that anything’s missing. EF Core names both failure modes directly: data duplication, and Cartesian explosion, where two collections joined at the same level multiply each other — ten posts and ten contributors on one blog returns a hundred rows for that blog alone. The join can be made safe on a one-to-many by limiting the parent first, in a subquery, then joining — which is exactly what SQLAlchemy’s joined eager loading does for you, and why it then makes you deduplicate the multiplied rows on the way out.

The third fix: one statement that doesn’t multiply

There’s a family of fixes for when the page doesn’t actually want all the children — it wants the count, the latest one, or the top three. Fetching eight hundred review rows to display “4.3 (8)” next to a hundred products moves the waste onto the wire instead of removing it.

When the page needs a number, aggregate — one row per product, no multiplication reaches the client:

SELECT p.id, count(r.id) AS review_count, avg(r.stars) AS avg_stars
FROM (SELECT * FROM products ORDER BY id LIMIT 100) p
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id;

When it needs one row per parent, DISTINCT ON keeps the first row of each group in ORDER BY order — the latest review per product, in one statement. When it needs the top few per parent, a window function like row_number() OVER (PARTITION BY product_id ORDER BY created_at DESC) numbers each product’s reviews and an outer query keeps the first three. Or a LATERAL join, which is the per-row loop expressed in SQL — for each product row, run a correlated subquery limited to three reviews — moving the loop the mapper was running from the client into the one statement where it belongs, with the products limit applied before the join so nothing multiplies and nothing goes stale between two round trips.

Where it hides: serializers, resolvers, and async

The video’s loop is a template touching p.category.name. In production the loop is rarely visible, because it’s not in your code — it’s in the layer that turns objects into JSON. A serializer is a loop over fields, run once per object; a field that reads a relation is a lazy load, and a nested serializer for reviews is a lazy load returning a collection whose members each have a serializer of their own. The view that fetched the products looks innocent and is innocent — the count is decided by a class in a different file, which is also why the query-count test below is about the endpoint, not the query. A GraphQL resolver is the same shape made structural: each field resolver runs once per parent with no view of its siblings, which is what a DataLoader fixes by coalescing every load within one tick of the event loop into a single batched call — one loader per request, though, because a memoization cache shared across users is an authorisation bug wearing a performance feature.

Firing the hundred lookups concurrently instead of serially changes the wall clock and nothing else — not the count, not the server’s parse work, not the number of connections needed at once. A hundred concurrent lookups want a hundred connections from a pool of ten, so ninety queue at checkout, and the “concurrent” N+1 runs at pool width. Async mappers make the point for you: SQLAlchemy’s asyncio extension turns an unloaded relation into an exception rather than a query, precisely because implicit IO isn’t allowed there — the count has to be fixed, not merely overlapped.

The test that stops it coming back

Everything above fixes an N+1 you’ve found. None of it stops the next one, because the next one is a field added to a serializer or a .filter() added inside a template loop — neither touches the line where the fix lives. The p99 will find it eventually; the build should find it first.

Assert the query count of the endpoint, in a test, with a fixture big enough for N to show:

def test_product_list_query_count(self):
    make_products(30, reviews_each=8)   # N large enough that 1+N is obvious
    with self.assertNumQueries(2):
        self.client.get("/products/")

Test the endpoint, not the queryset — the count is decided by the view, the serializer and the template together, and a query-level test passes while the page still runs a hundred and one. Use a fixture with more than one row and more than one child per row — with a single product an N+1 is two queries and the test says nothing. And write the exact count, not a ceiling: “at most 5” is a number that gets raised to 6, then 12, while the exact count turns a regression into a line in a diff a reviewer can see.