select_related vs prefetch_related: Practical Examples in Django

If you have ever opened Django Debug Toolbar and seen a page run 400 queries, you have met the N+1 problem. The two tools Django gives you to fix it are select_related() and prefetch_related(). They solve the same problem, but they work in completely different ways, and choosing the wrong one can leave your page just as slow, or even slower.

This guide explains how each one works, shows practical examples with real query counts, covers the Prefetch object, and ends with a simple decision guide you can apply to any queryset.

The Problem Both Tools Solve

Imagine these models for a bookstore:

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

class Profile(models.Model):
    author = models.OneToOneField(Author, on_delete=models.CASCADE)
    bio = models.TextField()

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

class Tag(models.Model):
    name = models.CharField(max_length=50)

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    tags = models.ManyToManyField(Tag, related_name="books")

class Review(models.Model):
    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name="reviews")
    rating = models.PositiveSmallIntegerField()
    approved = models.BooleanField(default=False)

Now a typical list page:

books = Book.objects.all()[:100]
for book in books:
    print(book.title, book.author.name, book.publisher.name)

Django runs one query for the books, and then one query for the author and another for the publisher for every book. That is 1 + 100 + 100 = 201 queries for a single page. Django loads relations lazily by default, which is convenient until you loop over a list. This is the N+1 problem.

How select_related Works

select_related() uses a SQL JOIN. Django fetches the main object and its related object in a single query, then builds both model instances from the same row.

books = Book.objects.select_related("author", "publisher")[:100]
for book in books:
    print(book.title, book.author.name, book.publisher.name)

The query count drops from 201 to 1. The SQL looks roughly like this:

SELECT book.*, author.*, publisher.*
FROM book
INNER JOIN author ON book.author_id = author.id
INNER JOIN publisher ON book.publisher_id = publisher.id
LIMIT 100;

Use select_related for single-valued relations:

• ForeignKey (forward direction)
• OneToOneField (forward and reverse)

You can follow relations across several levels using double underscores:

books = Book.objects.select_related("author__profile", "publisher")

Here Django joins Book, Author, Profile, and Publisher in one query. If a foreign key is nullable, Django uses a LEFT OUTER JOIN so books without that relation are not dropped.

Why it cannot handle "many" relations: joining a one-to-many or many-to-many relation would repeat the parent row once per child, which inflates the result set. That is exactly the job prefetch_related() was built for.

How prefetch_related Works

prefetch_related() runs a separate query for each relation and then joins the results together in Python.

books = Book.objects.prefetch_related("tags")[:100]
for book in books:
    print(book.title, [tag.name for tag in book.tags.all()])

Instead of 101 queries, Django runs 2:

-- Query 1: the books
SELECT * FROM book LIMIT 100;

-- Query 2: all tags for those books in one go
SELECT tag.*, book_tags.book_id
FROM tag
INNER JOIN book_tags ON tag.id = book_tags.tag_id
WHERE book_tags.book_id IN (1, 2, 3, ..., 100);

Django then matches tags to books in memory, so book.tags.all() inside the loop does not touch the database.

Use prefetch_related for multi-valued relations:

• ManyToManyField
• Reverse ForeignKey (for example author.books)
• GenericForeignKey and generic relations

authors = Author.objects.prefetch_related("books")
for author in authors:
    print(author.name, author.books.count())  # careful, see the pitfall below

select_related vs prefetch_related: Side-by-Side

	select_related	prefetch_related
Mechanism	SQL JOIN, one query	Separate queries, joined in Python
Relation types	ForeignKey, OneToOne	ManyToMany, reverse ForeignKey, generic relations
Query count	1	1 + one per prefetched relation
Memory pattern	Wider rows	More queries, extra objects held in memory
Filtering related rows	Not directly	Yes, with Prefetch objects

A useful way to remember it: select_related is for "one", prefetch_related is for "many".

Combining Both in One Queryset

Real pages usually need both. Here is a book list that shows the author, their profile, the publisher, and all tags:

books = (
    Book.objects
    .select_related("author__profile", "publisher")
    .prefetch_related("tags")
    .order_by("-id")[:100]
)

That is 2 queries total (one JOIN query plus one for tags), no matter how many books are on the page. Before optimization, the same page could have run hundreds.

Filtering Prefetched Data with the Prefetch Object

Here is a subtle trap. You might try to show only approved reviews like this:

books = Book.objects.prefetch_related("reviews")
for book in books:
    approved = book.reviews.filter(approved=True)  # runs a NEW query per book

Calling .filter() on a prefetched relation ignores the prefetch cache and hits the database again, which brings the N+1 problem back. The fix is the Prefetch object:

from django.db.models import Prefetch

books = Book.objects.prefetch_related(
    Prefetch(
        "reviews",
        queryset=Review.objects.filter(approved=True).only("id", "book_id", "rating"),
        to_attr="approved_reviews",
    )
)

for book in books:
    print(book.title, len(book.approved_reviews))

Two things happen here:

1. The prefetch query itself applies the filter, so only approved reviews are loaded.
2. to_attr="approved_reviews" stores the results as a plain Python list on each book. This avoids clashing with the normal book.reviews manager and makes it clear which data you are using.

Using .only() inside the Prefetch queryset keeps the second query lean. Just remember to include the foreign key field (book_id), because Django needs it to match rows back to their parents.

Prefetching Through Multiple Levels

You can chain relations across levels, and you can mix both tools inside a Prefetch:

authors = Author.objects.prefetch_related(
    Prefetch(
        "books",
        queryset=Book.objects.select_related("publisher").prefetch_related("tags"),
    )
)

This loads authors, then their books joined with each book's publisher, then the tags for those books. It is a small number of queries, regardless of how many authors there are.

Common Pitfalls

1. Using select_related on a many relation. Django raises an error for many-to-many and reverse foreign keys because a JOIN cannot represent them cleanly. Use prefetch_related() instead.

2. Calling .filter(), .order_by(), or .count() on a prefetched relation. These create a new queryset and bypass the cache. Use Prefetch with a custom queryset, or work with the prefetched list in Python, such as len(book.tags.all()).

3. Over-fetching columns. select_related("author") joins every column of the author. If you only need the name, combine it with only("title", "author__name"), or use values() if you do not need model instances.

4. Prefetching things you never use. Every prefetch costs a query and memory. Add only the relations the template or serializer actually reads.

5. Huge IN lists. prefetch_related() builds a query with WHERE parent_id IN (...). If you prefetch for tens of thousands of parents at once, that list gets large. Process in chunks instead of one giant queryset.

6. Deep JOIN chains. Long select_related() chains can produce wide, slow queries. Measure before assuming a bigger JOIN is better.

Verify with Query Counts

Do not trust the optimization until you have measured it. A simple test catches regressions:

from django.test import TestCase

class BookListTests(TestCase):
    def test_book_list_query_count(self):
        # create test data here
        with self.assertNumQueries(2):
            books = list(
                Book.objects
                .select_related("author", "publisher")
                .prefetch_related("tags")[:50]
            )
            for b in books:
                _ = b.author.name, b.publisher.name, [t.name for t in b.tags.all()]

assertNumQueries fails the test if someone later adds a lazy relation access in a loop. In development, Django Debug Toolbar shows the query count and the duplicate queries on each page, and queryset.explain(analyze=True) shows how PostgreSQL executes each query.

When Tables Get Big

The patterns above behave very differently on a table with millions of rows. A JOIN across large tables needs proper indexes on the foreign key columns, and prefetch_related() over a huge parent queryset can load a lot into memory. For batch work, combine iterator(chunk_size=...) with prefetching so Django processes a bounded number of parents at a time instead of holding everything at once.

If you want to go deeper on indexing, pagination, and memory control at scale, read my full guide on Django ORM optimization for millions of records: https://blog.reiteachaigean.com/blog/django-orm-optimize-millions-of-records

And when the job is not a web request but a large background task, such as recalculating or backfilling data, the same ideas apply inside each chunk. Fetch each ID range with select_related() and prefetch_related() so no task falls back into N+1 queries, as shown in this guide on processing 5 million records with Django and Celery: https://blog.reiteachaigean.com/blog/process-5-million-records-django-celery

Which One Should You Use? A Quick Decision Guide

Ask these questions in order:

1. Is the relation a ForeignKey or OneToOne that you follow forward? Use select_related().
2. Is it a ManyToMany, a reverse ForeignKey, or a generic relation? Use prefetch_related().
3. Do you need only some of the related rows, or a custom ordering? Use Prefetch() with a queryset, and to_attr if helpful.
4. Do you need both single and multi-valued relations? Combine them in one queryset.
5. Is the dataset large? Check indexes, limit columns with only(), and process in chunks.

Conclusion

select_related() and prefetch_related() are not competitors. They are two tools for two shapes of relationship. Use select_related() when the related object is a single row and a JOIN makes sense. Use prefetch_related() when the relation holds many rows and a second query is cleaner. Reach for Prefetch when you need to filter, order, or limit the related data, and always confirm the result with a query count.

Get this habit right and most N+1 problems disappear before they ever reach production.

Frequently Asked Questions

What is the main difference between select_related and prefetch_related? select_related() uses a SQL JOIN to fetch related objects in a single query and works for ForeignKey and OneToOne relations. prefetch_related() runs a separate query per relation and joins the results in Python, which suits ManyToMany and reverse ForeignKey relations.

Can I use select_related and prefetch_related together? Yes. Chain them on the same queryset. Use select_related() for the single-valued relations and prefetch_related() for the multi-valued ones.

Why is my prefetch_related still running extra queries? The most common cause is calling .filter(), .order_by(), or .count() on the prefetched relation, which bypasses the prefetch cache. Use a Prefetch object with a custom queryset instead.

Is select_related always faster than prefetch_related? Not necessarily. They solve different problems. A JOIN across large tables without proper indexes can be slower than two simple queries, so measure with EXPLAIN ANALYZE and query