count() on a prefetched relation is free. filter() costs a query per row.

Twelve queries where you expected two, in a loop that looks like somebody
already optimised it.

Second of two posts from the same afternoon. The first, Django's .exclude() does not drop your NULL rows, is about a check I measured and did not build. This is the one that survived.

---

Here is a page that is slower than the version with no optimisation in it at
all:

orders = Order.objects.prefetch_related("lines")
for order in orders:
    for line in order.lines.filter(active=True):
        ...

With ten orders that is twelve queries: one for the orders, one for the
prefetch, and one per order because .filter() cannot be answered from the
cache the prefetch just filled.

Delete the prefetch_related and it is eleven. The prefetch is pure loss — an
extra query, plus the memory to hold every line on the page, and not one row
of it is read.

What makes it survive code review is that it reads like care. Somebody thought
about performance here. There is an eager load right there in the queryset.

Which accessors read the cache

prefetch_related fills a cache on each parent object, and the related
manager hands it back — but only to the accessors that can use it. Ask it
anything the cache cannot answer and it goes back to the database.

I did not want to reason about which was which, so I counted. Ten parents,
three children each, CaptureQueriesContext around every variant, Django 6.1:

written on a prefetched related manager	queries
order.lines.all()	2	cache
order.lines.all()[0]	2	cache
order.lines.all()[:2]	2	cache
order.lines.count()	2	cache
order.lines.exists()	2	cache
order.lines.filter(...)	12	one per parent
order.lines.exclude(...)	12	one per parent
order.lines.order_by(...)	12	one per parent
order.lines.first()	12	one per parent
order.lines.last()	12	one per parent
order.lines.only(...)	12	one per parent
order.lines.defer(...)	12	one per parent
order.lines.values(...)	12	one per parent
order.lines.values_list(...)	12	one per parent
order.lines.distinct()	12	one per parent
order.lines.select_related(...)	12	one per parent

Two things in that table surprised me.

.count() and .exists() are free. I had assumed they were the classic
mistake — that you were supposed to write len(order.lines.all()) to use the
cache. You are not. The related manager answers both from the prefetched
result. Rewriting them changes nothing at all.

.first() is not free. It looks like a cheap peek at data you already
have in memory, and it is a fresh query with a LIMIT 1 on it. So is
.order_by(), which sorts a list that is already sitting in RAM by asking the
database to sort it again, once per parent.

The dividing line is not how expensive the operation sounds. It is whether the
call can be answered from a list of already-fetched objects without changing
the query — and .filter(), .order_by() and .first() all change the
query.

I later re-ran the same fixture against Django 4.2, the oldest version still
supported, and got an identical table. So this is not a recent behaviour to
wait for or a legacy one to migrate off.

(A note on that: I originally wrote that count and exists had been
cache-served "since Django 4.1". That was a recollection, not a measurement —
the 4.1 release notes say nothing about it. I have measured 4.2 and 6.1 and
they agree; which version it landed in, I do not know, and I have stopped
claiming to.)

Fixing it

If the condition can move into the prefetch, move it:

from django.db.models import Prefetch

orders = Order.objects.prefetch_related(
    Prefetch("lines",
             queryset=OrderLine.objects.filter(active=True),
             to_attr="active_lines")
)
for order in orders:
    for line in order.active_lines:      # a plain list, no query
        ...

Two queries, total, for any number of orders.

to_attr matters more than it looks. It puts the rows on a new attribute and
leaves order.lines doing exactly what it did before — so a later
order.lines.filter(...) is an ordinary query with no prefetch behind it,
rather than a prefetch being thrown away. Without to_attr the filtered
prefetch overwrites the default cache for that relation, which is fine until
some other code on the page wanted all the lines and now silently gets the
active ones.

For .order_by() and .first() there is usually no need for a Prefetch at
all. The rows are already in memory; sort them there:

newest = max(order.lines.all(), key=lambda line: line.created_at)

And if the accessor really does need something the cache cannot give — a
different filter each time round the loop, say — then the honest fix is to
delete the prefetch. It is not helping. Paying for it and re-querying is
the worst of both.

How often does this actually happen

I turned it into a static check, and the first version was wrong in an
instructive way.

The obvious implementation is to find the relations a file prefetches, then
find accessors on those names in the same file. Across those same nine
projects that found 79 sites.

I read five of them by hand. One was a real defect. Four were coincidence —
the same relation name on completely unrelated objects, because lines and
items and findings are names that appear in a large codebase more than
once. In one Saleor file, a name bound inside one function matched an accessor
a hundred lines away in a different function.

So I narrowed it: the prefetch and the accessor have to be provably the same
object — a name bound in the same scope, or the loop variable iterating one.
That finds 7 across the same nine projects, and I read and confirmed all
seven:

• Wagtail — wagtail/admin/views/pages/edit.py:207, values_list() on a prefetched comment_replies inside a comprehension over the prefetched queryset.
• Saleor — three in graphql/meta/permissions.py, .filter() on a user_addresses prefetched two lines earlier.
• Saleor — product_variant_delete.py:112. This is my favourite, because the comment directly above it says "Get cached variant with related fields" and the next line is variant.channel_listings.all().values_list(...), which throws the cache away.
• pretix — base/services/invoices.py:277, p.answers.filter(...) inside a loop over positions prefetched with answers.
• DefectDojo — dojo/jira/helper.py:868, finding_group.findings.filter(...) seven lines after Finding_Group.objects.prefetch_related("findings").

Seven in nine mature projects is not an epidemic. It is also not nothing, and
every one of them costs a query per row on a page somebody loads.

The narrowing is the part I would keep. 79 findings that are 80% wrong is
worse than 7 that are right, because the first number gets the tool switched
off and takes the seven real ones with it.

What the cache does not survive

Worth knowing regardless of tooling:

• Anything that changes the query: .filter(), .exclude(), .order_by(), .only(), .values_list(), and friends.
• .first() and .last(), which feel like indexing and are not.
• A second .prefetch_related() on the related manager.
• Templates are fine: {% for line in order.lines.all %} reads the cache, and a template cannot call .filter() with arguments anyway.

The check

This is one of the checks in
django-chainsaw-mcp, a
static analyser for the Django questions that stop a deploy. It runs as a CLI
and as an MCP server:

django-chainsaw prefetch

It reports .filter() and the rest, stays silent on .count() and
.exists() because the fix would change nothing, and only speaks where it can
prove the prefetch and the accessor are the same object.
docs/prefetch.md
has the full table and the blind spots.

The tool is written largely with Claude; docs/authorship.md sets out which
parts, and the measuring and the narrowing from 79 to 7 are the parts I did.

Comments

[Baptiste Le Bouquin]

The .filter() on a prefetched relation silently falling back to per-row queries is one of my least favorite Django traps, because the code looks optimised and the query count only reveals it under a profiler.

We hit the same wall in a sessions-store loop on our agent fleet, and the fix we settled on was pushing the predicate into the prefetch itself — Prefetch('lines', queryset=Line.objects.filter(active=True)) — so the cache actually holds what the loop asks for. One extra query total instead of N. Did you measure that variant, or was the point specifically about the naive mix?

[Mohammad Alsakka]

same fix, yes. The only difference is that I put to_attr on it
I had only measured the to_attr version so I ran yours as well same fixture as in the post, 10 parents with 3 children each:

Prefetch(queryset=filter) + .all() 2 queries
Prefetch(queryset=filter) + .filter(active=True) 12 queries
Prefetch(queryset=filter, to_attr) + obj.pub 2 queries

The middle row is the one that caught me out. Pushing the predicate into the Prefetch does nothing on its own if the loop still calls .filter(). Still one query per parent, because .filter() never looks at the cache regardless of what is in it. The accessor has to become .all() as well. In your case it obviously did, otherwise the query count would not have moved.

The tradeoff without to_attr is that the default cache for that relation now holds only the filtered rows. I checked, obj.entries.all() gives back 2 of 3. That is fine inside the loop you wrote it for. It is less fine when something else on the same page wanted all the lines and quietly gets the active ones. So I default to to_attr, it leaves lines doing what it did before.

which way did you end up going, accessor to .all() or to_attr?