beginnerintermediateadvanced

Django · Section 24

prefetch_related()

Level: advanced
Read: 22 min
Concepts: 2

prefetch_related() fixes N+1 for the relationship types select_related() cannot handle — ManyToManyField and reverse ForeignKey — using a separate-query-plus-Python-join strategy instead of a SQL JOIN, since JOINing a "many" side would multiply the base row. The result is cached, but only for the exact same access pattern: a fresh .filter()/.exclude() on the related manager always issues a new query, silently bypassing the cache. The Prefetch() object replaces the plain string form when a filtered/ordered related queryset or a custom to_attr is needed — essential when the same relation must be prefetched more than once with different filters. Nested prefetch chains through multiple relationship levels with the same __ syntax as select_related().

This section

Django select_related Examples |Django select_related and prefetch_related |Django n+1 Query ProblemCode Band

What is true here

prefetch_related() runs a separate query for the related side (typically a WHERE ... IN (...) query) and joins the results in Python — the correct fix for ManyToManyField/reverse FK, which select_related() cannot JOIN without multiplying rows.
The prefetch cache only serves the exact .all() access pattern — calling .filter()/.exclude() again on the related manager always issues a fresh, un-cached query.
Prefetch("relation", queryset=..., to_attr=...) is required for a filtered/ordered prefetch, and essential whenever the same relation needs prefetching more than once — without to_attr, the later call overwrites the earlier one's cache.
Nested prefetch (relation__nested) chains through multiple levels, one additional query per level — and must traverse through a to_attr name if one was used at that level.
The entire prefetched result set loads into memory at once — a real cost on a relationship with a genuinely large number of matches, mitigated by scoping the prefetch itself.

What you will be able to do

Choose prefetch_related() correctly for M2M/reverse FK relationships select_related() cannot handle
Avoid silently bypassing the prefetch cache with a fresh filter() call
Use Prefetch()/to_attr to prefetch the same relation multiple times with different filters
Chain nested prefetches correctly, including through a to_attr-renamed relation

Separate queries and the prefetch cache

How prefetch_related() actually works, and the specific way its cache can be silently bypassed.

coreadvanced

prefetch_related() fixes N+1 for "many" relationships (ManyToManyField, reverse ForeignKey) that select_related() cannot handle — instead of a JOIN, it runs a SECOND query for ALL the related objects at once, then joins the two result sets together in Python. That result is cached on each object — but calling .filter()/.all() AGAIN on the related manager after prefetching creates a brand-new query, silently bypassing the cache and undoing the optimization.

Think of it as

select_related()'s JOIN trick only works because a forward FK/OneToOne has exactly one related row — a M2M or reverse FK doesn't have that guarantee, so JOINing it in directly would multiply the base row once per match, corrupting the result shape. prefetch_related() sidesteps this by running a SECOND, separate query — 'get every related object for ALL these base objects, in one IN (...) query' — and then does the actual matching in PYTHON, attaching each base object's own slice of the second query's results to it. That Python-side matching is exactly what the cache IS — pizza.toppings.all() after prefetching doesn't re-query, it reads from the already-matched Python list. The moment a DIFFERENT queryset method is called on that manager (.filter(), a fresh .all() reassigned, etc.), that's a genuinely new query, not a read from the cache — the cache only serves the EXACT same access pattern.

python

Model.objects.prefetch_related("m2m_field")
Model.objects.prefetch_related("reverse_fk_set")

What we're doing: Fetch every restaurant along with all its pizzas in exactly 2 queries, and confirm the prefetch cache is actually being used rather than re-queried per restaurant.

shellpython

from django.db import connection, reset_queries
reset_queries()

restaurants = list(Restaurant.objects.prefetch_related("pizzas"))
for r in restaurants:
    list(r.pizzas.all())   # reads the cache — no new query per restaurant

print(len(connection.queries))   # 2 — one for restaurants, one for ALL pizzas

1: reset_queries() clears the tracked log so the count below reflects only this block.
5: r.pizzas.all() reads from the prefetch cache — despite running inside a loop over every restaurant, it adds ZERO new queries, unlike the same call without prefetch_related() applied first.

Why this works: The query count (2, not 1-per-restaurant) is the actual proof the optimization worked — the SECOND query fetched every pizza for every restaurant in this queryset at once (a single WHERE restaurant_id IN (...) query), and the loop afterward only ever reads the already-fetched, already-matched Python data.

Calling .filter() on a prefetched relation, silently bypassing the cache

Wrong

python

pizzas = Pizza.objects.prefetch_related("toppings")
spicy = [list(pizza.toppings.filter(spicy=True)) for pizza in pizzas]
# EVERY pizza.toppings.filter() call is a FRESH query — the prefetch was wasted

Better

python

from django.db.models import Prefetch
spicy_toppings = Topping.objects.filter(spicy=True)
pizzas = Pizza.objects.prefetch_related(Prefetch("toppings", queryset=spicy_toppings))
spicy = [list(pizza.toppings.all()) for pizza in pizzas]   # cache — no new queries

What you see: A query count that "should" be 2 (base objects + one prefetch query) turns back into N+1, and it is easy to miss why, since prefetch_related() was applied and looks correct at a glance.

Why: pizza.toppings.filter(spicy=True) is a genuinely NEW QuerySet — Django has no way to know this particular filter matches what was already prefetched, so it always issues a fresh query rather than trying to filter the in-memory cache. The fix is to move the filtering INTO the prefetch itself, via a Prefetch() object with a custom queryset, so the already-filtered result set is what gets cached.

Two queries, joined in Python — not a JOIN

Query 1: restaurants

Query 2: WHERE restaurant_id IN (...)

all matching pizzas, at once

Python matches each restaurant to its pizzas

restaurant.pizzas.all()

reads the cache — no new query

select_related() vs prefetch_related()

select_related() vs prefetch_related()
Aspect	select_related()	prefetch_related()
Strategy	one query, SQL JOIN	two (or more) separate queries, joined in Python
Relationship types	forward FK, forward/reverse OneToOne	ManyToManyField, reverse FK, reverse OneToOne, GenericForeignKey
Cache broken by	nothing extra — it's a real column on the row	calling .filter()/.exclude()/etc. again on the related manager

Together

python

pizzas = Pizza.objects.prefetch_related("toppings")   # 2 queries total
for pizza in pizzas:
    pizza.toppings.all()          # cache — no new query
    pizza.toppings.filter(spicy=True)   # NEW query — bypasses the cache entirely

Remember: prefetch_related() runs a separate query for the related side and joins the results in Python — the fix for ManyToManyField/reverse FK, which select_related() cannot handle. The cached result only serves the EXACT same access pattern (.all()) — a fresh .filter()/.exclude() on the related manager always issues a new query, bypassing the cache entirely.

The Prefetch() object

Filtered prefetches, prefetching the same relation more than once, and nested prefetch chains.

coreadvanced

Prefetch("relation", queryset=..., to_attr=...) replaces the plain string form when the prefetch itself needs customizing — a filtered/ordered queryset for the related side, and/or a custom attribute name (to_attr) so the prefetched result doesn't collide with the relation's normal manager. Nested prefetch chains through multiple levels with __, exactly like select_related() — "pizzas__toppings" prefetches toppings for every pizza of every restaurant, in one additional query per level.

Think of it as

The plain string form ("toppings") is really shorthand for Prefetch("toppings", queryset=Topping.objects.all()) — the object form exists for exactly the cases the shorthand can't express: a FILTERED or ordered related queryset, or storing the result somewhere OTHER than the relation's own manager (to_attr). to_attr matters specifically when the SAME relation needs to be prefetched twice with different filters (all pizzas AND vegetarian pizzas) — without it, the second Prefetch() call on the same relation would just overwrite the first's cached result.

python

Prefetch("relation", queryset=custom_qs, to_attr="custom_name")

What we're doing: Prefetch a restaurant's full menu AND, separately, just its vegetarian pizzas — both from the same relation, without one overwriting the other.

restaurants/views.pypython

from django.db.models import Prefetch

restaurants = Restaurant.objects.prefetch_related(
    Prefetch("pizzas", to_attr="menu"),
    Prefetch("pizzas", queryset=Pizza.objects.filter(vegetarian=True), to_attr="vegetarian_menu"),
)
# restaurant.menu and restaurant.vegetarian_menu both available, both prefetched

3: to_attr="menu" stores this prefetch's result as restaurant.menu (a plain list) — NOT restaurant.pizzas, avoiding a collision with the second Prefetch() below.
4: Without to_attr here (or a different one), this SECOND Prefetch() on the same "pizzas" relation would simply overwrite whatever the first one cached.

Why this works: Two separate needs — the full menu, and a vegetarian-only view — both stem from the same underlying relationship (pizzas), and to_attr is specifically what makes it possible to prefetch that ONE relation twice, with two different filters, without either overwriting the other.

Prefetching the same relation twice with different filters but no to_attr

Wrong

python

Restaurant.objects.prefetch_related(
    "pizzas",
    Prefetch("pizzas", queryset=Pizza.objects.filter(vegetarian=True)),
)
# the SECOND one silently overwrites the first — restaurant.pizzas.all() only shows vegetarian pizzas now

Better

python

Restaurant.objects.prefetch_related(
    "pizzas",
    Prefetch("pizzas", queryset=Pizza.objects.filter(vegetarian=True), to_attr="vegetarian_menu"),
)

What you see: restaurant.pizzas.all() shows only vegetarian pizzas — the FULL menu prefetch was silently replaced by the second, more specific Prefetch() call, with no error or warning raised anywhere.

Why: Both Prefetch() calls target the SAME relation name ("pizzas") and neither uses to_attr, so they write to the same cache slot — the later one in the call simply wins, discarding the earlier result entirely. to_attr is the only way to keep two differently-scoped prefetches of the same relation both available at once.

Prefetching the same relation twice, without collision

Restaurant.objects.prefetch_related( Prefetch("pizzas", to_attr="menu"), Prefetch("pizzas", queryset=veg_pizzas, to_attr="vegetarian_menu"), )

Prefetch("pizzas", to_attr="menu")

first Prefetch() — restaurant.menu — every pizza

queryset=veg_pizzas, to_attr="vegetarian_menu"

second Prefetch() — restaurant.vegetarian_menu — filtered, its own attribute

When Prefetch() (the object) is needed over the plain string form

When Prefetch() (the object) is needed over the plain string form
Need	Form
Prefetch everything, unfiltered	"relation" — the plain string is enough
Prefetch only a FILTERED/ordered subset	Prefetch("relation", queryset=custom_qs)
Prefetch the SAME relation twice, differently	Prefetch("relation", queryset=..., to_attr="name") — once per variant
Prefetch through a filtered relation, one level further	Prefetch("relation", queryset=custom_qs, to_attr="x"), "x__nested"

Together

python

from django.db.models import Prefetch

veg_pizzas = Pizza.objects.filter(vegetarian=True)
Restaurant.objects.prefetch_related(
    Prefetch("pizzas", to_attr="menu"),
    Prefetch("pizzas", queryset=veg_pizzas, to_attr="vegetarian_menu"),
)
# restaurant.menu — every pizza
# restaurant.vegetarian_menu — only vegetarian ones

Remember: Prefetch("relation", queryset=..., to_attr=...) is needed for a filtered/ordered prefetch, or whenever the same relation is prefetched more than once — to_attr keeps each variant in its own attribute instead of the later one silently overwriting the earlier. Nested prefetch chains with __, and must traverse through a to_attr name if one was used at that level.