A Django view that lists blog articles with each author's name and a comment count looks trivial to write, and it stays that way right up until the database holds more than a few dozen rows. The QuerySet sitting in the view is a single line of code, but the actual number of SQL queries fired while the template renders can be ten or a hundred times higher than that line suggests. The trap is especially sneaky in Django because QuerySets are lazy by design: nothing in the view code tells you exactly when each query actually goes out.
The mechanism: lazy evaluation and relation descriptors
A Django QuerySet doesn't touch the database until it's iterated, cast to a list, or evaluated through a stepped slice. Article.objects.all() fires no query; it's the for article in articles in the template that fires one. So far, nothing wrong: a single query is enough to fetch all the articles. The problem shows up when a relationship is accessed inside the loop.
# views.py
def article_list(request):
articles = Article.objects.all()
return render(request, "blog/list.html", {"articles": articles})
<!-- blog/list.html -->
{% for article in articles %}
<h2>{{ article.title }}</h2>
<p>by {{ article.author.full_name }}</p>
<p>{{ article.comments.count }} comments</p>
{% endfor %}
article.author is a ForeignKey relation descriptor. The first time it's accessed on a given instance, Django runs a SELECT * FROM authors WHERE id = %s query and caches the result on that instance. Since each article is a distinct instance, that cache never benefits the next iteration: with 50 articles, you get 1 query for the list, 50 queries for the authors, and 50 more for the comment counts, for a total of 101 queries to render a single page.
Diagnosing before optimizing
django-debug-toolbar remains the reference tool in development: its SQL panel shows the exact query count per page, their execution time, and crucially, duplicate queries, flagged as such.
For a command-line or shell diagnostic, django.db.connection.queries lists every query executed since the last reset, as long as DEBUG = True:
from django.db import connection, reset_queries
reset_queries()
list(Article.objects.all())
print(len(connection.queries)) # 1
for article in Article.objects.all():
article.author.full_name
print(len(connection.queries)) # 1 (list) + N (authors)
In tests, assertNumQueries turns that diagnostic into a permanent regression guard, far more reliable than a manual code review:
from django.test.utils import CaptureQueriesContext
from django.db import connection
def test_article_list_does_not_trigger_n_plus_1(self):
ArticleFactory.create_batch(10)
with self.assertNumQueries(3): # list + authors + comments
response = self.client.get(reverse("blog:article_list"))
self.assertEqual(response.status_code, 200)
A test that fails because the count jumps from 3 to 13 after an innocuous template change is an immediate signal, long before the slowdown gets noticed in production.
The fix: select_related for "one", prefetch_related for "many"
Django offers two distinct methods, and the choice between them depends on the cardinality of the relationship, not on stylistic preference.
select_related applies to single-valued relationships (ForeignKey, OneToOneField) and works via a SQL JOIN: a single query brings back the articles and their authors together.
articles = Article.objects.select_related("author").all()
prefetch_related applies to multi-valued relationships (ManyToManyField, or a reverse ForeignKey such as comments) and works via a separate query followed by a join performed in Python: a SQL JOIN would duplicate one article row per comment, which Django specifically avoids by issuing two separate queries and then grouping the results in memory.
articles = (
Article.objects
.select_related("author")
.prefetch_related("comments")
.all()
)
For a comment count, annotate(Count(...)) avoids even that second query by computing it directly in SQL through aggregation:
from django.db.models import Count
articles = (
Article.objects
.select_related("author")
.annotate(comment_count=Count("comments"))
)
<p>{{ article.comment_count }} comments</p>
That drops the count from 101 queries down to 1 or 2, depending on whether counting is done through aggregation or through prefetch_related.
The tradeoffs between select_related and prefetch_related
These two tools aren't interchangeable, and each carries its own cost.
A select_related chained too deep across several hops (select_related("author__company__country")) produces a single but wide SQL JOIN, potentially transferring a lot of redundant columns per row. Beyond two or three levels, the benefit of a single query can be cancelled out by the volume of data transferred; measuring rather than assuming still matters here.
A prefetch_related on a large relationship (an article with ten thousand comments) loads the entire relation into memory even if the template only renders the first five. To restrict that load, the Prefetch object lets you customize the prefetched queryset:
from django.db.models import Prefetch
articles = Article.objects.prefetch_related(
Prefetch(
"comments",
queryset=Comment.objects.filter(is_approved=True).order_by("-created_at")[:5],
to_attr="recent_comments",
)
)
to_attr materializes the result into a new attribute instead of replacing the default manager, which avoids a situation where some other code accessing article.comments.all() later fires an extra, non-prefetched query by mistake.
Restricting columns with only(), or excluding unneeded ones with defer(), reduces the volume transferred on models with heavy columns, such as a long TextField description that a list view never renders.
When select_related and prefetch_related aren't enough
Generic relations (GenericForeignKey) from django.contrib.contenttypes can't be optimized with select_related, because Django doesn't know the target model at the time it builds the JOIN. The django-generic-relations library, or a manual prefetch grouped by content_type, is still needed to avoid an N+1 on this kind of relationship.
Nested aggregations (say, counting only approved comments) need a Count with a conditional filter rather than a plain annotate, to avoid Django generating a JOIN that skews the total count:
from django.db.models import Count, Q
articles = Article.objects.annotate(
approved_comment_count=Count("comments", filter=Q(comments__is_approved=True))
)
For dashboards aggregating large volumes, even a well-optimized ORM stays bounded by the number of on-demand queries. A periodically refreshed PostgreSQL materialized view, or a denormalized column updated by a Django signal on write, shifts the computation cost from read time to write time, which is often the right tradeoff for read-heavy traffic.
Automating detection beyond hand-written tests
assertNumQueries protects the views covered by an explicit test, but a Django application always has uncovered paths, particularly in the admin or in views added quickly under deadline pressure. The nplusone library detects N+1 patterns at runtime by watching for access to relations that weren't prefetched, and it can be wired in as middleware during development:
# settings.py
MIDDLEWARE = [
# ...
"nplusone.ext.django.NPlusOneMiddleware",
]
NPLUSONE_LOGGER = logging.getLogger("nplusone")
NPLUSONE_LOG_LEVEL = logging.WARNING
NPLUSONE_RAISE = DEBUG # raise locally, only log elsewhere
With NPLUSONE_RAISE enabled locally, a relation accessed without having been prefetched immediately raises an exception with the exact call stack, instead of a silent slowdown that's only detectable with django-debug-toolbar open at the right moment. It's a safety net that complements assertNumQueries: the latter verifies a contract known in advance for a given view, while nplusone catches a generic pattern on code paths no test has covered yet.
One Django-specific trap is worth flagging here: the relation cache set by select_related or prefetch_related lives on the Python instance, not in a cache shared across HTTP requests. Every incoming request starts with fresh instances and no prior loading at all, unlike an application-level cache (Redis, Memcached) that would persist between two different requests. Conflating the two leads to the mistaken assumption that a relation "already seen" by a previous request doesn't need reloading on the next one.
Takeaway
The choice between select_related and prefetch_related isn't a matter of preference, it's a matter of relationship cardinality: a JOIN for "one", a separate query plus a Python-side join for "many". assertNumQueries in tests turns that choice into an automatically verifiable contract, which keeps a template change six months from now from silently reintroducing the same N+1 that got fixed today.
