TopolearnThe N+1 query problem is the most common Django performance bug, and it hides in innocent-looking templates.
Accessing a relationship on each object of a list triggers one query per object. Listing 50 posts with {{ post.author.name }} runs 1 query for the posts plus 50 for the authors: the N+1 problem.
Two tools fix it:
- `select_related("author")` follows ForeignKey and OneToOne relations with a SQL JOIN, so posts and authors arrive in one query. Chain through relations:
select_related("author__user"). - `prefetch_related("tags", "comments")` handles many-to-many and reverse foreign keys: it runs one extra query per relation (
WHERE post_id IN (...)) and joins the results in Python.
Prefetch("comments", queryset=Comment.objects.filter(approved=True), to_attr="approved_comments") customizes what is prefetched and stores it as a list attribute.
A prefetched relation is used only if you access it with .all(). Calling .filter() on it afterwards runs a new query, so put the filter in a Prefetch instead.
Related tools:
only("title", "slug")anddefer("body")skip loading heavy columns.iterator()streams huge result sets instead of caching them allbulk_createandbulk_updatewrite many rows in a few queriescount()andexists()beatlen(qs)andif qs:when you don't need the objects
Detect problems with django-debug-toolbar in development and self.assertNumQueries(n) in tests, which fails when a change sneaks in extra queries. Put select_related/prefetch_related in the view's queryset (or a manager method), not scattered in templates.
Example
from django.db.models import Prefetch
from blog.models import Comment, Post
# N+1: 1 query for posts + 1 per post for its author
for post in Post.objects.all():
print(post.author.name)
# 3 queries total: posts JOIN authors, then tags, then approved comments
posts = (Post.objects
.select_related("author")
.prefetch_related("tags",
Prefetch("comments",
queryset=Comment.objects.filter(approved=True),
to_attr="approved_comments")))
for post in posts:
print(post.author.name, [t.name for t in post.tags.all()], len(post.approved_comments))
# In tests:
# with self.assertNumQueries(3):
# self.client.get(reverse("blog:list"))Remember
- select_related = JOIN for ForeignKey/OneToOne
- prefetch_related = one extra query per many-valued relation
- Prefetch(queryset=..., to_attr=...) customizes prefetching
- assertNumQueries guards against regressions
Watch out for
- Calling .filter() on a prefetched relation, which bypasses the prefetch cache and queries again.