Avoiding N+1 in Django: When to Use select_related vs prefetch_related

The N+1 problem in a list view

When a view loads a queryset and then accesses a related object for each instance, the ORM can issue one query for the main table and an additional query per row. The result is a request that looks fast during development but scales poorly.

select_related – a single JOIN for forward FK/OneToOne

For a ForeignKey or OneToOneField that points from the main model to a single related row, select_related() tells Django to perform a SQL JOIN. The database returns each row with the related columns already populated, so the Python code can read the related data without a second hit.

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

This method only works for single‑valued relations. If you try to select_related on a reverse foreign key or a ManyToManyField, Django raises a ValueError because a single row cannot contain multiple related rows.

prefetch_related – a second query for multi‑value relations

Reverse foreign keys and many‑to‑many relationships can return many rows per main record. prefetch_related() runs a second query that fetches all related rows, then stitches them together in Python and caches the result on each instance.

posts = Post.objects.prefetch_related('comments')

To pull only a subset of the related rows you can use a Prefetch object with a custom queryset:

from django.db.models import Prefetch
posts = Post.objects.prefetch_related(
    Prefetch('comments', queryset=Comment.objects.filter(approved=True).order_by('-created'))
)

Measuring the impact

In a shell or in a test you can count the actual database queries that occur:

from django.test.utils import CaptureQueriesContext
from django.db import connection
from myapp.models import Post

with CaptureQueriesContext(connection) as ctx:
    posts = list(Post.objects.all())
    for post in posts:
        _ = post.author.name
print(len(ctx.captured_queries))

After adding select_related('author') the number of queries should drop to a constant value, regardless of how many posts you iterate over. For reverse or many‑to‑many relations repeat the pattern with prefetch_related and confirm the same reduction.

During local development you can also install django‑debug‑toolbar and inspect the SQL panel to see whether a per‑row query is still being issued.

Trade‑offs and practical cautions

• Memory vs. query count: prefetch_related loads all related rows into memory at once; a very large set can increase memory usage.
• Join size: select_related widens each result row with columns from the joined table. If the joined table is wide, the payload can grow, and a single large join may be slower than two indexed queries. Measure total execution time, not just query count.
• Interaction with only()/defer(): When you restrict the columns of the main query, select_related still pulls the full set of columns for the joined table unless you explicitly limit them. Conversely, prefetch_related can be combined with only() on the prefetched queryset to reduce the amount of data fetched.
• Slicing and nested prefetches: prefetch_related works on sliced querysets, but the prefetched data is still fetched for all objects that match the slice. Nested prefetch_related calls can be used to prefetch related objects of the prefetched objects, but the total number of queries can grow quickly if not managed.
• Unnecessary prefetches: Adding a prefetch_related for a relation that is never accessed in the view adds overhead. Only prefetch the relations you actually read.

Checklist for a production view

1. Identify which relations are accessed in the template or loop.
2. Determine the relation type: forward FK/OneToOne → select_related; reverse FK or M2M → prefetch_related.
3. Apply the narrowest prefetch that removes per‑row queries.
4. Measure query count and total response time with CaptureQueriesContext or assertNumQueries in a test.
5. Verify that memory usage remains acceptable for the expected dataset size.
6. Add a unit test that asserts the number of queries so future refactors do not re‑introduce N+1.