select_related, prefetch_related and the N+1 problem

The N+1 query problem is the single most common performance bug in Django applications, and it is caused by the very feature that makes the ORM pleasant — that following a relationship (appointment.doctor) is just attribute access, with the database query hidden. This lesson shows you the bug with real query counts, then the two tools that fix it: select_related and prefetch_related. Learn to see this one and you will prevent most of the slow pages you would otherwise ship.

The bug, with real numbers

Suppose you list every appointment with its doctor's name — an utterly ordinary page:

for a in Appointment.objects.all():
    print(a.doctor.name)

This looks like one loop. It is not one query. Django runs:

1. One query to fetch all the appointments (SELECT * FROM appointment).
2. Then, for each appointment, another query to fetch its doctor (SELECT * FROM doctor WHERE id=…) — because a.doctor is a lazy link that hits the database the first time you touch it.

So for N appointments you run 1 + N queries. Measured on Nidaan's data — 5 appointments — this is verified at 6 queries (1 for the appointments, 5 for the doctors). With 5 rows you will not notice. With 5,000 appointments on a real page, that is 5,001 database round-trips, and the page crawls. That is the N+1 problem: one query for the list, plus N queries for the related object of each item.

The insidious part is that the code looks innocent — there is no visible query in the loop, because the ORM hides it behind attribute access. You cannot spot N+1 by reading for the word "query"; you spot it by knowing that crossing a relationship inside a loop is where it hides.

Fix one-to-one and forward ForeignKey with select_related

When the related object is on the other end of a ForeignKey or OneToOneField (a "to-one" relationship — each appointment has one doctor), the fix is select_related:

for a in Appointment.objects.select_related("doctor"):
    print(a.doctor.name)

select_related("doctor") tells Django to fetch the appointments and their doctors in a single query using a SQL JOIN. Measured: this drops the count from 6 to 1 query, verified. One join, all the data, no per-row round-trips. Because it uses a join, select_related is for to-one relationships — the ones where each row has exactly one related object to join to. You can follow several and go deeper: select_related("doctor", "patient") joins both; select_related("appointment__patient") follows a chain.

Fix many-related with prefetch_related

A JOIN cannot efficiently pull a collection — if each doctor has many appointments, joining would duplicate the doctor row per appointment. For to-many relationships (reverse ForeignKey, ManyToManyField), use prefetch_related:

for d in Doctor.objects.prefetch_related("appointments"):
    print(d.name, list(d.appointments.all()))

prefetch_related("appointments") runs two queries — one for the doctors, one for all their appointments — and then matches them up in Python. Measured: 2 queries, verified, regardless of how many doctors there are. Without it, accessing d.appointments.all() inside the loop would be N+1 all over again. The rule of thumb: select_related for to-one (it joins), prefetch_related for to-many (it does a second query and stitches). Reach for the one that matches the relationship's direction.

How to actually catch N+1

You will not always reason it out in advance, so know how to measure:

• Count queries in a test. Django's assertNumQueries(1) fails the test if a block runs more than the expected number — the best defence, because it catches a regression the moment someone reintroduces N+1.
• Log queries in the shell. With DEBUG=True, django.db.connection.queries lists every query run; len(connection.queries) is the count. This is exactly how the numbers in this lesson were measured.
• Use Django Debug Toolbar in development — it shows the query count and the duplicate queries on every page, and makes N+1 impossible to miss. A near-essential tool for a real project.

The habit that matters: when you write a loop that touches a related object, check the query count. It takes seconds and it is the difference between a page that scales and one that dies under real data.

The trap of over-fetching, too

The opposite mistake exists: select_related on relationships you never use pulls columns you do not need, and prefetching a huge collection you only count wastes memory. select_related/prefetch_related are for data you will access. And if you only need one field from the related object, values() or only()/annotate() (the ORM-in-depth module) can be leaner still. The goal is not "always eager-load" — it is fetch what the page uses, in as few queries as sensible. N+1 is the common bug; blindly eager-loading everything is the less common but real over-correction.

Check your work

What N+1 is. One query for a list plus one query per item for its related object — because crossing a lazy relationship inside a loop triggers a query each time. Verified: 5 appointments → 6 queries.

Why it is hard to spot. The per-row query is hidden behind attribute access (a.doctor); you find it by knowing that crossing a relationship in a loop is where it lives, not by reading for "query".

select_related. For to-one (ForeignKey, OneToOneField) — fetches related rows via a SQL JOIN in one query. Verified: 6 → 1.

prefetch_related. For to-many (reverse ForeignKey, ManyToManyField) — a second query plus Python matching. Verified: 2 queries regardless of count.

How to catch it. assertNumQueries in tests, connection.queries in the shell (with DEBUG=True), and the Django Debug Toolbar in development.

The over-fetch trap. Eager-loading data you never use wastes work; fetch what the page uses in as few queries as sensible — not "always eager-load everything".

Practice

1. Create several doctors and many appointments. With DEBUG=True, loop appointments accessing a.doctor.name and print len(connection.queries) — reproduce the N+1 count (1 + N).
2. Add .select_related("doctor") and confirm the count drops to 1. Read the SQL in connection.queries[0] and find the JOIN.
3. Loop doctors accessing d.appointments.all(); confirm N+1, then add .prefetch_related("appointments") and confirm it becomes 2 queries.
4. Write a test using self.assertNumQueries(1) around a select_related loop; then remove the select_related and watch the test fail — a regression guard.
5. Install Django Debug Toolbar in the dev project, load a page with an N+1, and see it flag the duplicate queries.
6. select_related a relationship you then never access; reason about why that is wasted work, and remove it.

Official documentation

• Django — select_related — The to-one, join-based optimiser.
• Django — prefetch_related — The to-many, second-query optimiser.
• Django — Database access optimization — The broader guide, including assertNumQueries.
• Django Debug Toolbar — See query counts on every page in development.

Next: the Django admin as an operations tool.