Django ORM Query Optimization: select_related, prefetch_related and the N+1 Problem

A practical guide to finding and fixing the query patterns that make Django views slow — N+1 lookups, over-fetched columns, and aggregation done in Python instead of the database.

The Django ORM is deliberately convenient: post.author.name reads like attribute access on a plain object. That convenience hides the fact that each traversal of an unfetched relation issues its own SELECT. A template loop over fifty posts touching post.author runs fifty-one queries rather than one.

Because each query is individually fast, this rarely shows up in development against a local database with twenty rows. It shows up in production, as a view that degrades linearly with the size of the result set.

Why Lazy Relations Multiply Queries

Consider a blog with posts, authors and comments. The following models are the basis for every example in this guide.

              
            
Python
The example model definitions.
from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=120)
    email = models.EmailField(unique=True)


class Tag(models.Model):
    slug = models.SlugField(unique=True)


class Post(models.Model):
    title = models.CharField(max_length=200)
    body = models.TextField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="posts")
    tags = models.ManyToManyField(Tag, related_name="posts")
    published_at = models.DateTimeField(null=True, blank=True)


class Comment(models.Model):
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name="comments")
    body = models.TextField()
    approved = models.BooleanField(default=False)

The naive view below issues one query for the posts, then one additional query per post to load its author.

              
            
Python
An N+1 pattern: 1 query for posts, plus 1 per post for the author.
# 1 query for the posts...
posts = Post.objects.filter(published_at__isnull=False)

for post in posts:
    # ...then one extra SELECT per post, every iteration
    print(post.title, post.author.name)

# 50 posts => 51 queries

Note: Install django-debug-toolbar in development, or wrap a view in django.test.utils.CaptureQueriesContext, to see the real query count. Optimising without measuring first usually means optimising the wrong query.

Resolving ForeignKey and OneToOne in a Single JOIN

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

              
            
Python
One query instead of fifty-one.
# A single SELECT with an INNER JOIN on author
posts = Post.objects.select_related("author").filter(
    published_at__isnull=False
)

for post in posts:
    print(post.title, post.author.name)   # no extra query

# Traverses multiple levels with the double-underscore syntax
Comment.objects.select_related("post__author")

# Several relations at once
Comment.objects.select_related("post", "post__author")

Each argument adds a JOIN, so the row width grows with every relation. Selecting a dozen relations on a wide table can make a single query slower than two well-chosen ones — the technique is not free, it trades round trips for row size.

Separate Queries Joined in Python

A JOIN cannot efficiently express "all posts and all of their tags" without duplicating post rows once per tag. prefetch_related therefore issues a second query and matches the results up in Python, giving a constant two queries regardless of result size.

              
            
Python
Two queries total: one for posts, one for all their tags.
posts = Post.objects.prefetch_related("tags", "comments")

for post in posts:
    # Both relations are already in memory
    print(post.title, [t.slug for t in post.tags.all()])
    print(len(post.comments.all()))

# Combine both tools: JOIN the author, prefetch the collections
posts = (
    Post.objects
    .select_related("author")
    .prefetch_related("tags", "comments")
)

Filtering a Prefetch with Prefetch()

Calling a queryset method on an already-prefetched relation discards the prefetch and runs a fresh query. To filter or order within a prefetch, use the Prefetch object instead.

              
            
Python
Prefetch with a filtered queryset and a custom attribute name.
from django.db.models import Prefetch

# WRONG: .filter() on the relation re-queries per post, undoing the prefetch
posts = Post.objects.prefetch_related("comments")
for post in posts:
    approved = post.comments.filter(approved=True)   # N+1 again

# RIGHT: push the filter into the prefetch query
posts = Post.objects.prefetch_related(
    Prefetch(
        "comments",
        queryset=Comment.objects.filter(approved=True).order_by("-id"),
        to_attr="approved_comments",
    )
)

for post in posts:
    # A plain list, populated by the prefetch query
    print(len(post.approved_comments))

Note: When to_attr is set, the result is a plain Python list rather than a manager. That is usually what you want in a template, because a list cannot accidentally trigger another query.

Relation type	Use	Queries
`ForeignKey` (forward)	`select_related`	1 (JOIN)
`OneToOneField`	`select_related`	1 (JOIN)
Reverse `ForeignKey`	`prefetch_related`	2
`ManyToManyField`	`prefetch_related`	2

only, defer, values and count

Query count is one dimension; payload size is the other. A model with a large TextField pulls that column on every row even when the view only renders titles.

              
            
Python
Trimming columns and avoiding full object construction.
# only() — fetch just these columns
Post.objects.only("id", "title", "published_at")

# defer() — fetch everything except these
Post.objects.defer("body")

# values() / values_list() — dicts and tuples, no model instances
Post.objects.values("id", "title")
Post.objects.values_list("title", flat=True)

# count() runs SELECT COUNT(*) instead of loading every row
Post.objects.filter(published_at__isnull=False).count()

# exists() stops at the first matching row
if Post.objects.filter(author_id=7).exists():
    ...

# Iterate huge result sets without loading them all into memory
for post in Post.objects.iterator(chunk_size=500):
    process(post)

Note: Accessing a field excluded by only or defer triggers a new query for that column, per object. Deferring a field you then use in a loop converts one query into N.

annotate and aggregate Instead of Python Loops

Counting or summing in Python requires loading every row. Pushing the computation into SQL with annotate keeps the data in the database and returns only the result.

              
            
Python
Database-side counting, conditional aggregation and bulk updates.
from django.db.models import Count, Q, Avg, F

# Slow: loads all comments to count them
for post in Post.objects.all():
    print(post.comments.count())      # one query per post

# Fast: one query, count computed by the database
posts = Post.objects.annotate(num_comments=Count("comments"))
for post in posts:
    print(post.num_comments)

# Conditional aggregation with a filter argument
posts = Post.objects.annotate(
    approved_count=Count("comments", filter=Q(comments__approved=True))
)

# aggregate() returns a single summary dict
Post.objects.aggregate(avg_comments=Avg("comments__id"))

# F() expressions update in the database, avoiding a read-modify-write race
Post.objects.filter(pk=1).update(view_count=F("view_count") + 1)

# bulk_create / bulk_update replace per-object saves
Post.objects.bulk_update(posts, ["title"], batch_size=500)

The F() expression deserves particular attention: it performs the increment atomically inside the database. The equivalent Python code reads the value, adds one, and writes it back, losing concurrent increments between the read and the write.

• Measure query counts with django-debug-toolbar before changing anything.
• select_related for ForeignKey and OneToOne; prefetch_related for reverse and many-to-many.
• Use Prefetch(queryset=...) rather than filtering an already-prefetched relation.
• Replace per-object .count() loops with annotate(Count(...)).
• Use F() for increments so concurrent updates are not lost.

Summary

Nearly all Django performance problems reduce to three causes: issuing one query per object, fetching columns the view never renders, and computing in Python what the database could compute in SQL.

select_related and prefetch_related address the first, only, defer and values the second, and annotate, aggregate and F() expressions the third.

Add a query-count assertion to the tests covering your heaviest views. It turns a performance regression into a failing test instead of a production incident discovered weeks later.