DEV Community

Cover image for select_related vs prefetch_related: Practical Django Examples
sizan mahmud0
sizan mahmud0

Posted on

select_related vs prefetch_related: Practical Django Examples

#django #python #orm #postgres

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)
Enter fullscreen mode Exit fullscreen mode

Before: 201 queries 

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

After: 1 query 

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

Many relations: 2 queries 

books = Book.objects.prefetch_related("tags")[:100]
Enter fullscreen mode Exit fullscreen mode

Combined 

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

The common trap 

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

.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",
    )
)
Enter fullscreen mode Exit fullscreen mode

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()]
Enter fullscreen mode Exit fullscreen mode

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?

Top comments (1)

Collapse
 
lycore profile image
Lycore Development •

The book_id detail is easy to miss: deferring it can trigger extra queries while Django attaches prefetched rows. One nuance on the cache trap: book.reviews.count() can use a fully populated prefetch cache, whereas filtering or ordering a new queryset normally issues another query. A query-count test around the exact template or serializer path is a useful way to catch that difference.