Filter concepts by levelShowing all levels.

Django · Section 22

N+1 Queries

Level
advanced
Read
20 min
Concepts
2

The N+1 pattern — 1 query for a list of objects, plus N more triggered by a lazy relationship access inside a loop — is called out by the roadmap itself as mandatory knowledge. Covers how to actually detect it by measurement (connection.queries, django-debug-toolbar) rather than assumption, and the four sneaky variants where the exact same bug hides behind something that doesn't look like an explicit loop at all: a nested relationship chain, a DRF serializer field, a template {% for %} tag, and a model's own @property.

What is true here

  1. N+1 needs a loop to manifest — a relationship access (ForeignKey, reverse FK, M2M) triggers its own query by default, and doing that once per item in a loop is the entire bug.
  2. connection.queries (or django-debug-toolbar) is how to actually confirm both the presence of N+1 and that a fix worked — never assume select_related()/prefetch_related() helped without measuring.
  3. A nested relationship chain (entry.blog.owner) needs select_related("blog__owner") — fixing only the first level (select_related("blog")) leaves the second level as its own N+1.
  4. A DRF serializer field, a template {% for %} loop, and a model @property can all hide the exact same N+1 pattern behind something that looks nothing like an explicit Python loop.
  5. The fix always lives at the QUERYSET that ultimately feeds the loop/serializer/template/property — never something fixable at the point the hidden query actually fires.

What you will be able to do

  • Recognize the N+1 pattern on sight, in a Python loop or hidden behind a serializer/template/property
  • Measure query counts with connection.queries to confirm both the bug and the fix
  • Fix a nested relationship chain with double-underscore select_related()
  • Apply the fix at the correct layer — the underlying queryset, not the trigger point

The N+1 pattern, and detecting it

The core 1-plus-N shape, and how to confirm it by measurement rather than assumption.

The N+1 pattern, and how to detect it

coreintermediate

N+1 is 1 query to fetch a list of objects, plus N more queries — one PER OBJECT — triggered by accessing a related field inside a loop. for entry in Entry.objects.all(): print(entry.blog) looks like ordinary code, but every entry.blog access inside the loop is a fresh, separate query, since ForeignKey access is lazy by default. The fix is always the same shape: tell Django up front (select_related()/prefetch_related()) which related data will be needed, so it's fetched in 1-2 queries total instead of N+1.

Think of it as

Every ForeignKey/relationship access on a model instance is, by default, its own lazy lookup — exactly like a fresh QuerySet, it does nothing until accessed, and THEN it runs its own query. That's completely invisible reading the code (entry.blog looks like a plain attribute access, indistinguishable from reading an already-loaded field) which is exactly why N+1 is easy to write and easy to miss in review — the bug isn't in any single line, it's in the multiplication effect of an innocent-looking line running once per loop iteration. Fixing it always means moving the related-data fetch OUTSIDE the loop, to query-build time, via select_related()/prefetch_related().

python
from django.db import connection
# ... run some code ...
print(len(connection.queries))   # how many queries actually ran

What we're doing: Confirm an N+1 suspicion using connection.queries before and after applying select_related(), rather than guessing.

shellpython
from django.db import connection, reset_queries
reset_queries()

for entry in Entry.objects.all()[:10]:
    _ = entry.blog.name
print(len(connection.queries))   # 11 — 1 for entries, 10 for each .blog access

reset_queries()
for entry in Entry.objects.select_related("blog")[:10]:
    _ = entry.blog.name
print(len(connection.queries))   # 1 — everything came back in a single JOINed query
1
reset_queries() clears the tracked query log, so the count that follows reflects only the code under test.
5
11 queries for 10 entries confirms the N+1 pattern by measurement, not by guesswork — 1 (the entries) + 10 (one .blog lookup per entry).
9
select_related("blog") collapses all 11 queries into 1 — the JOIN brings blog data along with each entry in the same round trip.

Why this works: connection.queries turns "I suspect this is slow" into "this measurably runs 11 queries, and here they are" — confirming the fix actually worked (1 query, not 11) is just as important as confirming the bug existed in the first place, since it is easy to add select_related() to the wrong relationship or spell it slightly wrong and see no improvement.

Adding select_related() to a query, but never actually confirming the query count dropped

Wrong

python
entries = Entry.objects.select_related("blog")
# "should be fixed now" — never actually verified

Better

python
reset_queries()
entries = list(Entry.objects.select_related("blog"))
for entry in entries:
    _ = entry.blog.name
assert len(connection.queries) == 1, f"expected 1 query, got {len(connection.queries)}"

What you see: select_related() is added, the code "looks" fixed, but a typo in the relationship name, or a relationship select_related() genuinely cannot handle (a reverse FK or M2M — needing prefetch_related() instead), leaves the exact same N+1 pattern running, undetected.

Why: select_related() silently accepts a wrong-but-plausible-looking field name (or one that just doesn't reduce anything, if applied to the wrong relationship type) without raising an error the way a genuine typo in a filter() lookup would — a real, measured query count before and after is the only reliable confirmation the fix actually worked, not just that the code compiles and runs.

1 + N queries vs. 1 query, JOINed

for entry in Entry.objects.all()

  • +1 query for the list
  • +N more — one .blog access per entry
  • +Invisible in the code — looks like a plain attribute

.select_related("blog")

  • −Exactly 1 query total
  • −blog data JOINed in up front
  • −Applied before the loop, at query-build time
  • for entry in Entry.objects.all()
    • 1 query for the list
    • N more — one .blog access per entry
    • Invisible in the code — looks like a plain attribute
  • .select_related("blog")
    • Exactly 1 query total
    • blog data JOINed in up front
    • Applied before the loop, at query-build time

Spotting and fixing N+1

Spotting and fixing N+1
StepTool
See the raw SQL Django is runningfrom django.db import connection; connection.queries
See query count/duplicates in the browserdjango-debug-toolbar (a separate package)
Understand a single slow queryQuerySet.explain()
Fix a forward FK/OneToOne N+1select_related()
Fix a reverse FK/M2M N+1prefetch_related()

Together

python
# N+1: 1 query for entries, then 1 MORE per entry for its blog
for entry in Entry.objects.all():
    print(entry.blog.name)

# fixed: exactly 1 query total, blog data JOINed in
for entry in Entry.objects.select_related("blog"):
    print(entry.blog.name)

Remember: N+1 = 1 query for a list, plus 1 more PER ITEM from a related-field access inside a loop — invisible in the code, since the extra access looks like a normal attribute read. Confirm both the bug and the fix by measuring with connection.queries (or django-debug-toolbar), never by assumption — the fix is always select_related()/prefetch_related(), applied before the loop.

See also: hidden n plus 1 sources · select related · many to many and reverse fk

Advertisement

Where it hides

Four places the same bug shows up without looking like an explicit loop at all.

The sneaky sources: nested, serializer, template, and property N+1

coreadvanced

The basic N+1 pattern is easy to spot once you know to look for a relationship access inside a Python for loop — these four variants are the same bug hiding in places that don't look like a loop at all. Nested N+1 stacks a SECOND N+1 inside the first (each entry's blog, then each blog's owner). Serializer-driven N+1 hides the loop inside a DRF serializer's own internal iteration over a queryset. Template-driven N+1 hides it inside a {% for %} tag. Property-driven hidden queries hide it inside an innocent-looking @property.

Think of it as

All four of these are the exact same root cause as the basic N+1 pattern — a relationship access that triggers a query, executed once per item in some collection — the only thing that changes is WHERE the hidden loop lives. A DRF serializer iterates its queryset internally when it produces JSON; a template's {% for %} tag is a real Python loop under the hood; an @property that reads self.some_relation is a query trigger disguised as a plain attribute access, exactly as invisible from a CALLER's perspective as it is from inside an explicit loop. Recognizing these means learning to ask "does the queryset feeding this serializer/template/property already have the right select_related/prefetch_related?" rather than only checking for an explicit for loop in the view.

python
Entry.objects.select_related("blog__owner")            # nested, one JOIN
class EntryViewSet(ModelViewSet):
    queryset = Entry.objects.select_related("blog")     # fixes serializer-driven N+1 too

What we're doing: Fix a serializer-driven N+1 by moving the optimization to the ViewSet's queryset — where the serializer can never override it — rather than trying to fix it inside the serializer itself.

blog/views.pypython
class EntrySerializer(serializers.ModelSerializer):
    blog_name = serializers.CharField(source="blog.name")   # triggers a query per Entry, unless prefetched
    class Meta:
        model = Entry
        fields = ["id", "headline", "blog_name"]

class EntryViewSet(viewsets.ModelViewSet):
    queryset = Entry.objects.select_related("blog")   # the actual fix — applied here, not in the serializer
    serializer_class = EntrySerializer
2
source="blog.name" reads entry.blog.name for every Entry the serializer processes — invisible from the serializer's own code, this is exactly the same query-per-item pattern as a Python for loop.
3
select_related("blog") on the VIEW'S queryset is what actually fixes it — the serializer itself never needs to know or care that the optimization happened; it just benefits from blog already being loaded.

Why this works: The serializer field (blog_name) has no way to know or control what query produced the Entry instances it's serializing — the fix has to live upstream, at the queryset that feeds the serializer, which is exactly the ViewSet's queryset attribute, not something addable inside EntrySerializer itself.

Fixing the outer relationship in a nested access but leaving the inner one unfixed

Wrong

python
entries = Entry.objects.select_related("blog")   # fixes entry.blog
for entry in entries:
    print(entry.blog.owner.email)   # entry.blog.owner is STILL a fresh query, every time

Better

python
entries = Entry.objects.select_related("blog__owner")   # fixes BOTH levels, one JOIN
for entry in entries:
    print(entry.blog.owner.email)   # no extra queries

What you see: Query count drops from what it was, confirming SOME improvement, but a residual N+1 remains — connection.queries still shows N extra queries, just for the second relationship instead of the first.

Why: select_related("blog") only tells Django to JOIN in the blog table — it says nothing about blog's OWN relationships. select_related() supports double-underscore traversal (blog__owner) specifically to extend the JOIN one level further, fixing nested relationship chains in exactly one query instead of needing a second round of optimization after noticing the first fix was incomplete.

Four places the same loop hides

Nested

entry.blog.owner — a second access chained off the first

Serializer-driven

DRF's own internal loop over the queryset

Template-driven

{% for %} is a real loop under the hood

Property-driven

@property reading a relationship, disguised as a plain attribute

  1. Nested — entry.blog.owner — a second access chained off the first
  2. Serializer-driven — DRF's own internal loop over the queryset
  3. Template-driven — {% for %} is a real loop under the hood
  4. Property-driven — @property reading a relationship, disguised as a plain attribute

The four hidden N+1 sources

The four hidden N+1 sources
SourceWhere the hidden loop actually isFix
Nested N+1a SECOND relationship access, chained off the firstselect_related("a__b") — double-underscore traversal
Serializer-drivenDRF's own internal loop over the querysetselect_related()/prefetch_related() on the VIEW's queryset
Template-driven{% for %} in the templateselect_related()/prefetch_related() in the view before rendering
Property-drivenan @property reading a relationship internallyselect_related()/prefetch_related() on the queryset feeding the property

Together

python
# nested: fixes only the FIRST level
Entry.objects.select_related("blog")            # entry.blog.owner is STILL N+1

# fixes BOTH levels in one JOIN
Entry.objects.select_related("blog__owner")

Remember: These four are the same N+1 bug, hidden behind something that doesn't look like a loop: a chained relationship access (nested), a DRF serializer field (serializer-driven), a {% for %} tag (template-driven), or an @property (property-driven). The fix always lives at the QUERYSET that ultimately feeds them — select_related("a__b") for nested chains, and select_related()/prefetch_related() on the view's queryset for the other three, never something fixable at the point the hidden query fires.

See also: the n plus 1 pattern · select related · instance methods and domain logic

Advertisement