select_related and prefetch_related — solving N+1

Why a loop over a queryset can silently issue hundreds of queries, and the two different tools that fix it for two different kinds of relationship.

Intermediate

3 min read

What N+1 actually looks like

books = Book.objects.all()
for book in books:
    print(book.author.name)

This looks like one query. It's actually 1 + N: one query for books, then one additional query for book.author on every single iteration, because accessing a foreign key by default is lazy — Django doesn't fetch related objects until you actually touch the attribute. With 200 books, that's 201 queries to render one page. This is the single most common real-world Django performance bug, and it's invisible in casual testing because it works fine with 5 rows and quietly falls over with 5,000.

select_related — for "one" relationships (ForeignKey, OneToOne)

books = Book.objects.select_related("author")
for book in books:
    print(book.author.name)  # no extra query — already fetched

select_related works by doing a SQL JOIN — it pulls the related row in the same query, as extra columns on the same result set. This only works for relationships where there's exactly one related object to fetch per row (ForeignKey, OneToOneField), because a JOIN can only attach one related row per base row without duplicating the base row.

You can chase relationships more than one level deep: Book.objects.select_related("author__publisher") joins both author and author.publisher in a single query.

prefetch_related — for "many" relationships (reverse FK, ManyToMany)

authors = Author.objects.prefetch_related("books")
for author in authors:
    print([b.title for b in author.books.all()])  # no extra query per author

A JOIN can't work here — an author can have many books, so joining would duplicate the author row once per book, which isn't what you want. Instead, prefetch_related issues a second query, fetches every related Book for every Author in the original queryset in one go, then stitches them together in Python. Still just 2 queries total, regardless of whether there are 5 authors or 5,000 — a constant number of queries instead of one per row.

The decision, in one sentence

If the relationship is "this object has (at most) one of those" — ForeignKey or OneToOne — reach for select_related. If it's "this object has many of those" — reverse ForeignKey or ManyToMany — reach for prefetch_related. Using the wrong one either does nothing (prefetch_related on a ForeignKey still works but is a wasted extra query where select_related would've joined it for free) or is impossible (select_related can't be used on a many-relationship at all).

How to actually catch this before it ships

Don't rely on remembering to add these — verify it:

from django.test.utils import CaptureQueriesContext
from django.db import connection
 
with CaptureQueriesContext(connection) as ctx:
    list(Book.objects.select_related("author"))
assert len(ctx.captured_queries) == 1

In development, django-debug-toolbar shows the exact query count and highlights duplicates for every page load — the fastest way to see an N+1 problem instead of reasoning about it from the code.