How to Find and Fix N+1 Queries in Django

An endpoint that was comfortably under 100 ms on my laptop was taking 4.2 seconds in production. The database CPU was fine. The application server was not saturated. The slow query log had nothing in it — because there was no slow query. There were 1,847 fast ones.

That is the signature of an N+1 problem, and it is the single most common performance bug I find in Django codebases. The ORM makes it almost invisible: the code that causes it looks completely ordinary.

What an N+1 query actually is#

You run one query to fetch a list of objects. Then, for each object in that list, an attribute access triggers another query. One query, plus N more.

Here is the code that produced my 1,847 queries:

class ArticleSerializer(serializers.ModelSerializer):
    author_name = serializers.CharField(source="author.name")
 
    class Meta:
        model = Article
        fields = ["id", "title", "author_name"]

class ArticleListView(generics.ListAPIView):
    queryset = Article.objects.all()
    serializer_class = ArticleSerializer

Nothing about that looks expensive. But Article.objects.all() selects only from the article table. When the serializer reaches author.name on the first article, Django has not loaded that author, so it issues a query. Then it does the same for the second article, and the third.

sequenceDiagram
    participant V as View
    participant DB as PostgreSQL
    V->>DB: SELECT * FROM article
    DB-->>V: 500 rows
    loop once per article
        V->>DB: SELECT * FROM author WHERE id = ?
        DB-->>V: 1 row
    end

Each of those author lookups takes maybe 0.4 ms at the database. The problem is not the query — it is the 500 network round trips, each of which costs far more in latency than the query costs in execution. This is why the problem hides in development: with a local database and 20 seed rows, 21 queries over a Unix socket is genuinely fast.

Finding them#

django-debug-toolbar, for interactive work#

For anything you can hit in a browser, django-debug-toolbar is the fastest way to see the problem. Its SQL panel shows the query count, the total time, and — critically — groups duplicate queries together.

INSTALLED_APPS += ["debug_toolbar"]
MIDDLEWARE.insert(0, "debug_toolbar.middleware.DebugToolbarMiddleware")
INTERNAL_IPS = ["127.0.0.1"]

A page showing 487 queries in 2841.02 ms with a "similar queries" count next to it is an N+1 problem. You do not need to read them.

Logging every query, for API endpoints#

The toolbar does not help with a JSON API you are hitting from a test client. Turn on query logging instead:

LOGGING = {
    "version": 1,
    "handlers": {"console": {"class": "logging.StreamHandler"}},
    "loggers": {
        "django.db.backends": {
            "handlers": ["console"],
            "level": "DEBUG",
        },
    },
}

assertNumQueries, to stop it coming back#

Finding the problem once is worth much less than preventing its return. Django ships with an assertion for exactly this:

def test_article_list_query_count(client, article_factory):
    article_factory.create_batch(10)
 
    # Regardless of how many articles exist, this endpoint must issue a
    # fixed number of queries.
    with self.assertNumQueries(2):
        response = client.get("/api/articles/")
 
    assert response.status_code == 200

The important part is create_batch(10). A test with one article will pass whether or not you have an N+1 problem. Seed enough rows that the count would visibly change, then assert a constant.

nplusone, for a codebase-wide sweep#

If you have inherited a large project, nplusone hooks into the ORM and warns whenever a lazy load happens on an object that came from a queryset:

INSTALLED_APPS += ["nplusone.ext.django"]
MIDDLEWARE.insert(0, "nplusone.ext.django.NPlusOneMiddleware")
NPLUSONE_RAISE = True  # fail loudly in tests

Fixing them#

There are two tools, and choosing between them is entirely determined by the kind of relation.

select_related for forward relations#

select_related performs a SQL JOIN and populates the related object in the same query. It works for ForeignKey and OneToOneField — relations where each row has exactly one related row.

class ArticleListView(generics.ListAPIView):
    queryset = Article.objects.select_related("author")
    serializer_class = ArticleSerializer

That is the entire fix for my endpoint. 1,847 queries became 2.

It follows relations as deep as you need, using the same double-underscore syntax as filter:

Article.objects.select_related("author__organization")

prefetch_related for reverse and many-to-many relations#

You cannot JOIN a one-to-many relation without multiplying rows: an article with 30 comments would come back as 30 rows, each repeating the whole article. So prefetch_related does something different — it runs a second query for all the related objects at once, and joins them in Python.

articles = Article.objects.prefetch_related("tags")

That is 2 queries total, no matter how many articles: one for the articles, one SELECT ... WHERE article_id IN (...) for all their tags.

flowchart TB
    subgraph before["Before — 1 + N queries"]
        A1[SELECT articles] --> B1[SELECT tags WHERE article_id = 1]
        A1 --> B2[SELECT tags WHERE article_id = 2]
        A1 --> B3[SELECT tags WHERE article_id = ...]
    end
    subgraph after["After — 2 queries"]
        A2[SELECT articles] --> B4["SELECT tags WHERE article_id IN (1,2,...)"]
    end

Both at once#

Real endpoints usually need both, and they compose:

Article.objects.select_related("author").prefetch_related("tags", "comments__author")

Note comments__author: prefetch_related can traverse into a forward relation on the prefetched objects, which saves you an N+1 inside the prefetch.

The traps#

.count() and len() are not the same#

If you have already prefetched a relation, .count() throws the prefetched data away and issues a fresh SELECT COUNT(*) — reintroducing the N+1 you just fixed.

# Bad: one COUNT query per article, even after prefetch_related("tags").
{"tag_count": article.tags.count()}
 
# Good: counts the objects already in memory.
{"tag_count": len(article.tags.all())}

Better still, when you only want the number and not the objects, do not prefetch at all — annotate:

from django.db.models import Count
 
Article.objects.annotate(tag_count=Count("tags"))

Filtering a prefetched relation re-queries it#

This looks harmless and is not:

# Bad: `.filter()` on the related manager ignores the prefetch cache.
article.comments.filter(approved=True)

Use Prefetch to push the filter into the prefetch query itself:

from django.db.models import Prefetch
 
queryset = Article.objects.prefetch_related(
    Prefetch(
        "comments",
        queryset=Comment.objects.filter(approved=True).select_related("author"),
        to_attr="approved_comments",
    )
)

Then read article.approved_comments — a plain Python list, already loaded, with each comment's author already joined.

select_related on a nullable FK silently changes the join#

select_related on a nullable ForeignKey produces a LEFT OUTER JOIN. That is correct, but if the related table is large and the join is on an unindexed column, you can trade 500 fast queries for one slow one. Check with EXPLAIN:

print(Article.objects.select_related("author").explain(analyze=True))

What actually changed#

For the endpoint I started with — 500 articles, each serialising an author and a list of tags:

The database was never the problem. 1,844 of those queries each took under half a millisecond to execute. What cost four seconds was asking for them one at a time.

A checklist that catches this early#

1. Any serializer field with source="something.other" needs a select_related.
2. Any template loop that accesses object.related_set.all needs a prefetch_related.
3. Every list endpoint gets an assertNumQueries test with at least ten seeded rows.
4. When you add a field that traverses a relation, update the queryset in the same commit.

The first three take an afternoon to apply to an existing codebase. The fourth is the one that keeps it fixed.