Avoiding N+1 Queries

One query per row is a trap

Accessing a related object in a loop (p.category.name) runs one extra query per row: the N+1 problem, often invisible in development and slow in production. select_related joins single-valued relations (ForeignKey, OneToOne) into the same query; prefetch_related fetches multi-valued relations in one extra query. Measure with assertNumQueries in tests, Django Debug Toolbar, or query logging.

Counting queries with and without select_related, run

I ran this script with python manage.py shell --no-imports in the demo project (Django 6.1.1, SQLite). Listing four products with their category names takes 5 queries without select_related (1 for products plus 4 for categories) and 1 query with it.

from django.db import connection
from django.test.utils import CaptureQueriesContext
from catalog.models import Product

with CaptureQueriesContext(connection) as naive:
    names = [f"{p.name}/{p.category.name}" for p in Product.objects.all()]   # one query per category access
with CaptureQueriesContext(connection) as joined:
    names2 = [f"{p.name}/{p.category.name}" for p in Product.objects.select_related("category")]
print(names)
print("without select_related:", len(naive.captured_queries), "queries")
print("with select_related   :", len(joined.captured_queries), "query")

Output:

['Lamp/Home', 'Mug/Home', 'Notebook/Office', 'Pen/Office']
without select_related: 5 queries
with select_related   : 1 query

Lock query counts in tests

assertNumQueries in a view test fails the build if someone later introduces an N+1 query.

Quick check: Which method fixes N+1 queries for a ForeignKey?

• order_by
• prefetch_related only for strings
• select_related
• distinct