What is the N+1 query problem?

Loading a list with one query and then one extra query per item to fetch a related object, so N items cost N+1 queries.

When do you use select_related versus prefetch_related?

select_related joins single-valued relations (ForeignKey, OneToOne) in the same query; prefetch_related runs a separate query for multi-valued relations (many-to-many, reverse foreign keys).

How can tests catch N+1 regressions?

With assertNumQueries (or CaptureQueriesContext) around a view or serializer call, failing when the number of queries grows.

Relationships and the N+1 Query Problem — Django from Basics to Production

What you'll build

Authors, books and tags, a listing that prints each book with its author and tags, and a query counter showing the same listing taking 1 + N + N queries naively and 2 with select_related and prefetch_related.

The idea

ForeignKey creates a many-to-one relation; ManyToManyField a many-to-many one through a join table.
Accessing book.author loads the author on demand: in a loop over 50 books, that's 50 extra queries (N+1).
select_related("author") joins the related table in the same query (for ForeignKey and OneToOne).
prefetch_related("tags") runs one extra query for all related rows and joins them in Python (for many-to-many and reverse relations).

The code

Counting queries before and after

import sys, types
import django
from django.conf import settings

sys.modules["shop"] = types.ModuleType("shop")       # lets this single file act as the "shop" app
sys.modules["shop"].__file__ = __file__
settings.configure(
    SECRET_KEY="dev-only", DEBUG=False, ALLOWED_HOSTS=["testserver"], ROOT_URLCONF=__name__,
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
    INSTALLED_APPS=["django.contrib.contenttypes", "django.contrib.auth", "shop"],
)
django.setup()

from django.db import connection, models
from django.test.utils import CaptureQueriesContext

class Author(models.Model):
    name = models.CharField(max_length=100)
    class Meta: app_label = "shop"

class Tag(models.Model):
    name = models.CharField(max_length=30)
    class Meta: app_label = "shop"

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    tags = models.ManyToManyField(Tag)
    class Meta: app_label = "shop"

with connection.schema_editor() as editor:
    for model in (Author, Tag, Book):
        editor.create_model(model)

classic, scifi = Tag.objects.create(name="classic"), Tag.objects.create(name="sci-fi")
for i in range(5):
    author = Author.objects.create(name=f"Author {i}")
    Book.objects.create(title=f"Book {i}", author=author).tags.add(classic if i % 2 else scifi)

def listing(qs):
    return [f"{b.title} by {b.author.name} [{', '.join(t.name for t in b.tags.all())}]" for b in qs]

with CaptureQueriesContext(connection) as naive:
    rows = listing(Book.objects.all())
with CaptureQueriesContext(connection) as tuned:
    rows2 = listing(Book.objects.select_related("author").prefetch_related("tags"))
print(rows[0])
print("same output:", rows == rows2)
print("queries naive:", len(naive), "| with select_related + prefetch_related:", len(tuned))

import sys, types
import django
from django.conf import settings

sys.modules["shop"] = types.ModuleType("shop")       # lets this single file act as the "shop" app
sys.modules["shop"].__file__ = __file__
settings.configure(
    SECRET_KEY="dev-only", DEBUG=False, ALLOWED_HOSTS=["testserver"], ROOT_URLCONF=__name__,
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
    INSTALLED_APPS=["django.contrib.contenttypes", "django.contrib.auth", "shop"],
)
django.setup()

from django.db import connection, models
from django.test.utils import CaptureQueriesContext

class Author(models.Model):
    name = models.CharField(max_length=100)
    class Meta: app_label = "shop"

class Tag(models.Model):
    name = models.CharField(max_length=30)
    class Meta: app_label = "shop"

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    tags = models.ManyToManyField(Tag)
    class Meta: app_label = "shop"

with connection.schema_editor() as editor:
    for model in (Author, Tag, Book):
        editor.create_model(model)

classic, scifi = Tag.objects.create(name="classic"), Tag.objects.create(name="sci-fi")
for i in range(5):
    author = Author.objects.create(name=f"Author {i}")
    Book.objects.create(title=f"Book {i}", author=author).tags.add(classic if i % 2 else scifi)

def listing(qs):
    return [f"{b.title} by {b.author.name} [{', '.join(t.name for t in b.tags.all())}]" for b in qs]

with CaptureQueriesContext(connection) as naive:
    rows = listing(Book.objects.all())
with CaptureQueriesContext(connection) as tuned:
    rows2 = listing(Book.objects.select_related("author").prefetch_related("tags"))
print(rows[0])
print("same output:", rows == rows2)
print("queries naive:", len(naive), "| with select_related + prefetch_related:", len(tuned))

Output

Book 0 by Author 0 [sci-fi]
same output: True
queries naive: 11 | with select_related + prefetch_related: 2

Run this on your computer — see “Get Started” for setup.

Common mistakes

Templates and serializers that access related objects in loops: the N+1 is invisible in the code.
Using select_related for many-to-many (it doesn't work there; use prefetch_related).
Fetching whole related objects when only one field is needed; consider values() or only().

Key points

Accessing relations in loops causes N+1 queries.
select_related joins foreign keys; prefetch_related batches many-to-many and reverse relations.
Measure query counts in tests (assertNumQueries) to catch regressions.

Further reading: select_related · prefetch_related · Database access optimization

What you'll build

The idea

The code

Common mistakes

Key points

Practice questions