Django select_related vs prefetch_related (With Examples)

#django #python #tutorial #webdev

Both exist to fix the same thing — the N+1 query problem — but they work on different kinds of relationships. The whole decision comes down to one question: is the relationship single-valued (a ForeignKey) or multi-valued (a ManyToMany)?

The problem they solve: N+1 queries

Say each Book has a ForeignKey to Author. This innocent loop quietly runs one query for the books, then one more per book to fetch its author:

books = Book.objects.all()
for book in books:
    print(book.author.name)   # a new DB query every iteration

100 books → 101 queries. That's the N+1 problem, and it's the single most common Django performance bug. select_related and prefetch_related both collapse those extra queries — they just do it differently.

select_related — for ForeignKey and OneToOne

select_related follows a single-valued relationship (ForeignKey, OneToOne) by doing a SQL JOIN and pulling the related object in the same query:

books = Book.objects.select_related('author')
for book in books:
    print(book.author.name)   # no extra queries — author came via the JOIN

101 queries become 1. Because it's a database join, it only works when each row has exactly one related row — that's why it's for ForeignKey and OneToOne, the "forward" single object.

prefetch_related — for ManyToMany and reverse ForeignKey

A JOIN can't efficiently fetch a multi-valued relationship (many rows per object), so prefetch_related takes a different approach: it runs a second query for all the related objects, then stitches them together in Python.

authors = Author.objects.prefetch_related('books')
for author in authors:
    for book in author.books.all():   # no extra query per author
        print(book.title)

That's 2 queries total (one for authors, one for all their books) instead of one-per-author. Use it for ManyToMany fields and for reverse ForeignKey access (the "many" side).

The rule

This is the whole thing to remember:

select_related   -> single-valued (ForeignKey, OneToOne)   -> SQL JOIN, 1 query
prefetch_related -> multi-valued  (ManyToMany, reverse FK) -> separate query, joined in Python

Put another way: if you'd follow the relationship to one object (book.author), use select_related. If you'd follow it to a set of objects (author.books.all()), use prefetch_related.

Combining them

Real queries often need both, and they chain cleanly:

# each book's author (FK) AND each book's tags (M2M), minimal queries
books = Book.objects.select_related('author').prefetch_related('tags')

You can also span relationships with __ — select_related('author__publisher') joins two levels of ForeignKey in one query, and prefetch_related('author__books') prefetches across them.

Common gotchas

Wrong tool, no error: using prefetch_related on a ForeignKey works but runs an extra query you didn't need — select_related is strictly better there. Using select_related on a ManyToMany raises an error, because you can't JOIN a multi-valued relation into one row.
Filtering after prefetch re-queries: calling .filter() on a prefetched set hits the DB again. To filter the prefetched data, use a Prefetch object with a queryset instead.
Only prefetch what you use: prefetching a relation you never touch just wastes a query and memory.

The N+1 problem and these two methods are a staple of Django interviews — see the full Django interview questions and the SQL questions on joins and query performance.

Prepping for a Django interview? Peakblick runs mock interviews on a timer and scores every answer 1–10 with feedback — so you find the weak spots before the real thing. Free to try, no card.

DEV Community