select_related vs prefetch_related: Practical Django Examples

select_related() and prefetch_related() both fix the N+1 problem, but they work differently. Here's the practical difference.

The rule

• select_related → SQL JOIN, one query. For ForeignKey and OneToOne.
• prefetch_related → separate queries, joined in Python. For ManyToMany and reverse ForeignKey.

Example models

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)

Before: 201 queries

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

After: 1 query

books = Book.objects.select_related("author", "publisher")[:100]

Many relations: 2 queries

books = Book.objects.prefetch_related("tags")[:100]

Combined

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

The common trap

for book in Book.objects.prefetch_related("reviews"):
    book.reviews.filter(approved=True) # new query per book

.filter(), .order_by(), and .count() on a prefetched relation bypass the cache. Use Prefetch instead:

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",
    )
)

Include the foreign key (book_id) when you use .only() in a Prefetch, since Django needs it to match rows to parents.

Lock it in with a test

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()]

Full guide

The complete post covers multi-level prefetching, six common pitfalls, behavior on large tables, a decision guide, and an FAQ: 👉 https://blog.reiteachaigean.com/blog/select-related-vs-prefetch-related-django

If you work with big tables, these two follow-ups go deeper:

• Django ORM optimization for millions of records
• Processing 5 million records with Django and Celery

What's the worst N+1 you've found in a real project?