Pointer Walk

The N+1 Query Problem

A loop that loads one author per post runs 101 queries. Fetching the authors together runs 2.

DatabasesBeginner7 min read

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.

python
posts = Post.objects.all()[:100]          # 1 query

for post in posts:
    print(post.title, post.author.name)   # 1 query per post

post.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.

One query per postInteractive
App
queries: 0
Database

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.

Authors fetched togetherInteractive
App
queries: 0
Database

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:

sql
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:

python
posts = Post.objects.select_related("author")[:100]   # one joined query

One line changed. 101 queries became 1 or 2, however many posts there are.

One query per postInteractive
Code
App
queries: 0
Database

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.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    6 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress