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:
What’s the worst N+1 you’ve found in a real project?