The slow page with fast queries
A page lists 100 blog posts with the name of each author. It takes two seconds to load. You look at the database and every query finishes in a millisecond. Where did the time go?
Count the queries. There are 101 of them.
How it happens
The code reads naturally. That is what makes this bug so common.
posts = Post.objects.all()[:100] # 1 query
for post in posts:
print(post.title, post.author.name) # 1 query per postpost.author looks like reading a field. It is not. The author lives in another table, and the first time the code touches it, the library quietly runs a query to fetch it. Inside a loop of 100 posts, that is 100 hidden queries.
One query for the list, plus N queries for the N items: N+1.
A page shows 5 posts with the name of each author. Count the trips to the database.
(The animation uses 5 posts so the arrows fit. The pattern is identical for 100.)
Why many small queries are slow
Each query is cheap for the database. The cost is everything around it. Every query is a round trip: the app sends it over the network, waits, and receives the answer. Even on a fast network that is around a millisecond.
Example
The arithmetic
101 round trips at 1 millisecond each is about a tenth of a second spent only on waiting. With 1,000 items it is a full second. If the database is in another data centre and a round trip takes 20 milliseconds, 101 queries take 2 seconds.
The same data in 2 round trips takes 2 to 40 milliseconds.
The fix: ask once
Collect the ids first, then fetch all the related rows in one query.
A page shows 5 posts with the name of each author. Count the trips to the database.
In SQL you can do it with a second query using IN, as in the animation, or with a single joined query:
SELECT posts.title, authors.name
FROM posts
JOIN authors ON authors.id = posts.author_id
LIMIT 100;In practice you tell your library to do it for you. Every one of them has a name for it:
posts = Post.objects.select_related("author")[:100] # one joined queryOne line changed. 101 queries became 1 or 2, however many posts there are.
A page shows 5 posts with the name of each author. Count the trips to the database.
How to catch it
- Log the number of queries per request. A page that runs 300 queries has this problem somewhere.
- Watch for the same query repeated with only the id changing.
- Test with realistic amounts of data. With 3 posts in a test database, 4 queries look fine.
Key takeaways
- N+1 means one query for a list, then one more query for each item in it.
- It hides behind code that looks like reading a field inside a loop.
- Each query is fast. The round trips between them are what add up.
- Fix it by fetching the related rows together: a join, an IN query, or your library's eager loading.
- Count queries per request to find it.