Fix Django N+1 Queries with select_related and prefetch_related

The problem: one query becomes one hundred

Imagine a Django view that lists recent blog posts. In the template, you display each post’s author:

{{ post.title }} by {{ post.author.name }}

If the view fetches 50 posts with Post.objects.all(), Django runs one query to get the posts. Then, every time the template accesses post.author, Django runs another query to fetch that author’s row. Fifty posts become fifty-one database round trips. This is the N+1 query problem, and it is one of the most common performance issues in Django applications.

select_related: fetch single-valued relations in one query

select_related follows foreign key and one-to-one relationships using a SQL JOIN. The related rows come back in the same result set as the parent, so Django does not need to hit the database again when you access them.

Applied to the post list:

posts = Post.objects.select_related('author').all()

Now Django generates one query with a JOIN to the author table. Accessing post.author.name in the template triggers no additional query.

Use select_related for:

• ForeignKey relationships
• OneToOneField relationships

Chain multiple relationships with double underscores when needed:

Post.objects.select_related('author__profile').all()

prefetch_related: fetch multi-valued relations separately

prefetch_related works differently. It issues a separate query for each relationship, then joins the results in Python. This supports many-to-many fields and reverse foreign key relationships, which select_related cannot handle with a single JOIN.

If each post has multiple tags:

posts = Post.objects.prefetch_related('tags').all()

Django runs one query for posts, then one query for all tags belonging to those posts. In the template, post.tags.all() hits the in-memory cache instead of the database.

Use prefetch_related for:

• ManyToManyField relationships
• Reverse ForeignKey relationships, such as author.post_set.all
• Reverse OneToOne relationships

Quick comparison

Method	Relationship type	Database strategy
select_related	ForeignKey, OneToOneField	SQL JOIN, single query
prefetch_related	ManyToManyField, reverse relations	Separate queries, joined in Python

Worked example: posts, authors, categories, and tags

Suppose your models look like this:

class Author(models.Model):
    name = models.CharField(max_length=100)

class Post(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    category = models.ForeignKey('Category', on_delete=models.CASCADE)
    tags = models.ManyToManyField('Tag')

A view that displays title, author name, category name, and tag names should combine both methods:

posts = Post.objects.select_related('author', 'category').prefetch_related('tags')

This produces roughly three queries regardless of how many posts you fetch: one for posts with author and category joined, and one for tags. Without it, the same page could issue one query per post per relationship.

Diagnose before you optimize

Run the following in a Django shell (python manage.py shell) as a user with access to the project code. It requires DEBUG = True in settings; otherwise connection.queries is empty.

from django.db import connection

# Before optimization
posts = Post.objects.all()
list(posts)
print(len(connection.queries))

Expected check: the number should grow with the number of posts if an N+1 problem exists. After adding select_related or prefetch_related, the count should stay flat. You can also inspect the generated SQL:

print(str(posts.query))

Look for JOIN clauses with select_related and separate IN queries with prefetch_related. Risk: leaving DEBUG = True in production exposes sensitive query and stack-trace information, so keep this diagnostic local.

Trade-offs and limitations

select_related can inflate result sets when you join many single-valued relationships, because the database returns a row for every combination. Avoid chaining too many foreign keys unless you actually need every column.

prefetch_related loads related objects into memory. If you only need a few fields from a large related table, you may waste memory. Combine with Prefetch and only to limit columns:

from django.db.models import Prefetch

posts = Post.objects.prefetch_related(
    Prefetch('tags', queryset=Tag.objects.only('name'))
)

Be careful with only() and defer(): if you omit a field required for a join or filter, Django will fetch it anyway or raise an error.

Check the result

After applying the change, reload the page and compare query counts. A successful optimization shows a flat query count as you increase the number of parent rows, rather than a count that grows with the result set. Confirm under representative data volumes, because a fast local database with ten rows can hide problems that appear with ten thousand.

Query optimization is not about using every ORM method at once. Start with the relationship type, measure the query count, and add select_related or prefetch_related only where the template actually touches related data.