select_related & prefetch_related (Killing N+1)

Diagnose and eliminate the N+1 query problem — the single most common Django performance bug.

This is the lesson that separates a working Django app from a fast one. The N+1 problem is when you run one query for a list, then — usually inside a template loop — one more query per row to fetch a related object. A page listing 100 posts with their authors quietly fires 101 queries.

Two tools, two relationship shapes

Method	Use for	How
`select_related`	ForeignKey & OneToOne (to-one)	A single SQL JOIN
`prefetch_related`	ManyToMany & reverse FK (to-many)	A second query, joined in Python

# N+1: 1 query for posts + 1 per post for author = slow
for post in Post.objects.all():
    print(post.author.name)   # hits the DB every iteration

# Fixed: 1 JOINed query total
for post in Post.objects.select_related('author'):
    print(post.author.name)   # author already loaded

You can span relationships with double underscores (select_related('author__profile')) and combine both methods in one chain. For fine control over the prefetched queryset — filtering or ordering the related rows — wrap it in a Prefetch object.

How do you even find N+1 bugs? Install django-debug-toolbar in development, or wrap a view in assertNumQueries() in a test. If the query count grows with the number of rows on the page, you have an N+1.

Example

from django.db.models import Prefetch, Count


# A blog index that would naively fire hundreds of queries,
# collapsed down to a small, fixed number.
def blog_index(request):
    approved = Comment.objects.filter(is_approved=True).select_related('user')

    posts = (
        Post.objects
        .select_related('author', 'author__profile', 'category')  # to-one: JOINs
        .prefetch_related(
            'tags',                                                # M2M
            Prefetch(
                'comments',                                        # reverse FK
                queryset=approved.order_by('-created'),
                to_attr='approved_comments',   # -> post.approved_comments (a list)
            ),
        )
        .annotate(total_comments=Count('comments'))
        .filter(status='published')
        .order_by('-published_at')
    )

    # In the template none of these touch the DB again:
    #   {{ post.author.profile.avatar }}
    #   {% for tag in post.tags.all %} ...
    #   {% for c in post.approved_comments %} {{ c.user.username }}
    return render(request, 'blog/index.html', {'posts': posts})


# Prove it in a test: the query count must NOT scale with row count.
from django.test import TestCase

class BlogIndexQueryBudget(TestCase):
    def test_constant_query_count(self):
        PostFactory.create_batch(50)
        with self.assertNumQueries(5):   # fixed budget, not 50+
            self.client.get('/blog/')

When to use it

A developer adds select_related('author') to a post list query to avoid N+1 queries when displaying author names.
A developer uses prefetch_related('tags') to load many-to-many tags for all posts in a single extra query.
A developer uses Prefetch with a queryset to filter related comments to only approved ones while prefetching.

More examples

select_related for ForeignKey

Uses a SQL JOIN to fetch posts and their authors in a single query, eliminating N+1 author lookups.

# Without: 1 query for posts + N queries for authors
# With select_related: 1 SQL JOIN query
posts = Post.objects.select_related("author").all()
for post in posts:
    print(post.author.username)  # no extra query

prefetch_related for ManyToMany

Fetches all tags for all posts in a single extra query and caches them, avoiding N×M tag lookups.

# 2 queries: one for posts, one for all related tags
posts = Post.objects.prefetch_related("tags").all()
for post in posts:
    print([t.name for t in post.tags.all()])  # no extra queries

Prefetch with custom queryset

Prefetches only approved comments and stores them as post.approved_comments to avoid filtering in templates.

from django.db.models import Prefetch

approved_comments = Comment.objects.filter(approved=True)
posts = Post.objects.prefetch_related(
    Prefetch("comments", queryset=approved_comments, to_attr="approved_comments")
)

Discussion

Be the first to comment on this lesson.