DEV Community: Mohammad Alsakka

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

Mohammad Alsakka — Thu, 10 Sep 2026 08:31:48 +0000

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.

Django's .exclude() does not drop your NULL rows

Mohammad Alsakka — Thu, 10 Sep 2026 08:29:28 +0000

I spent an afternoon measuring a bug that does not exist. Here is the
measurement, and the rule I should have applied an hour earlier.

I was adding a check to a static analyser for Django, and I had what felt like
a good candidate.

Take a nullable column:

class Task(models.Model):
    status = models.CharField(max_length=20, null=True)

and the query everybody writes:

Task.objects.exclude(status="done")

Read that out loud and it means everything that is not done. Now think about
what the database does with a row whose status is NULL. In SQL,
NULL = 'done' is not false. It is NULL. And NOT NULL is still NULL,
which is not true, and a WHERE clause keeps a row only when its condition is
true.

So the row disappears. Not excluded because it is done — excluded because the
database does not know whether it is done, and silently declines to guess.

This is real. It is the classic three-valued-logic trap, it is in every
database textbook, and it bites people in raw SQL constantly. My check would
find every .exclude(field=value) where field is nullable and say: these
rows are vanishing and nobody told you.

The prevalence looked excellent

The bar in that repository is that you measure before you build, so I did. I
cloned nine large open-source Django projects — Wagtail, Saleor,
django-oscar, Misago, Weblate, NetBox, pretix, DefectDojo, Read the Docs —
and counted .exclude() calls with a plain keyword argument, skipping
anything already using __isnull because that author was clearly thinking
about NULLs already.

742 call sites. 588 of them outside test code.

That is a lot. For comparison, a different candidate I had measured that same
morning — a Meta.ordering that spans a relation, which forces a JOIN on
every query including .count() and .exists() — appeared 6 times in 562
orderings. I dropped that one for being too rare to be worth a check.

588 is not too rare. 588 is a check that fires on nearly every project it
touches.

Then I checked the prior art, and it disagreed with me

Before writing anything I went looking for whoever had already solved this.
What I found instead was a page asserting, in passing, that Django's ORM "is
generally smart enough to include rows where the status is NULL."

That is the opposite of my premise.

One of us was wrong, and I could not tell which from reading. A blog post is
not evidence and neither is my recollection of a textbook. The answer was in
the SQL Django actually generates, which is twenty lines away from anyone who
wants it.

Twenty lines

import django
from django.conf import settings

settings.configure(
    INSTALLED_APPS=["django.contrib.contenttypes"],
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3",
                           "NAME": ":memory:"}},
)
django.setup()

from django.db import connection, models


class Thing(models.Model):
    status = models.CharField(max_length=20, null=True)
    note = models.CharField(max_length=20)

    class Meta:
        app_label = "contenttypes"


with connection.schema_editor() as editor:
    editor.create_model(Thing)

Thing.objects.create(status="done", note="a")
Thing.objects.create(status="open", note="b")
Thing.objects.create(status=None, note="c")     # the row in question

qs = Thing.objects.exclude(status="done")
print(qs.query)
print(sorted(qs.values_list("note", flat=True)))

Output:

SELECT ... FROM "contenttypes_thing"
WHERE NOT ("contenttypes_thing"."status" = done
           AND "contenttypes_thing"."status" IS NOT NULL)

['b', 'c']

c is there. The NULL row survives.

Look at the WHERE clause and you can see exactly why. Django did not write
NOT (status = 'done'). It wrote NOT (status = 'done' AND status IS NOT NULL). For the NULL row the inner expression is NULL AND FALSE, which is
FALSE, and NOT FALSE is TRUE. The row is kept.

Django put the guard in. My premise was about SQL, and it was correct about
SQL — but I was not writing SQL, and the ORM had already thought about this.

It is not one lucky path

Three shapes, same result:

	keeps the NULL row
`exclude(status="done")`	yes
`filter(~Q(status="done"))`	yes
`exclude(status__in=["done"])`	yes

And the guard is not applied blindly. Same model, one nullable field and one
that is not:

exclude(nullable="done")
  NOT ("thing"."nullable" = done AND "thing"."nullable" IS NOT NULL)

exclude(required="done")
  NOT ("thing"."required" = done)

Django adds the IS NOT NULL only where a NULL is possible. It is not
defensive boilerplate; the ORM knows which columns need it.

While I was there: the multi-valued case

There is a neighbouring warning that gets repeated a lot — that filter() and
exclude() stop being mirror images once you cross a multi-valued relation.
I had half-expected to find a second check hiding there, so I measured that
too:

Parent.objects.exclude(children__tag="red")

with four parents: one with a red child, one with red and blue, one with only
blue, one with no children at all.

result:            ['no_children', 'only_blue']
naive expectation: ['no_children', 'only_blue']

Identical. The generated SQL is a NOT EXISTS subquery, which expresses
parents with no red child precisely, including the childless one.

There is a genuine subtlety in this area about chaining and about what
filter() does across multiple calls, but the scary version of the warning —
that exclude() across a relation quietly returns nonsense — is not what
modern Django does.

What I did with the 588

Nothing. There is no check.

Had I built it on the strength of the prevalence number, it would have opened
588 findings, every one of them wrong, in nine of the most carefully
maintained Django codebases in existence. And the worst part is that it would
have looked authoritative. Each finding would have quoted a real line, named a
real nullable field, and explained a real property of SQL. A reviewer without
a SQLite shell open would have believed every word.

Static analysis does not fail by crashing. It fails by being confidently
wrong, and a plausible sentence that sends someone in the wrong direction
costs more than no sentence at all.

The rule

I had two numbers that afternoon: 6 out of 562, and 588.

I rejected the first for being too rare, which was the easy call. I nearly
accepted the second because it was large — and prevalence measures how often
your pattern appears, not whether it is a defect. They are separate
questions and only one of them was answered.

Prevalence is not permission. Measure that the thing is real before you
measure how often it happens.

Twenty lines and about four minutes. That is the entire cost of finding out.

I write and maintain
django-chainsaw-mcp, a
static analyser for the Django questions that stop a deploy. The check that
did get built out of that afternoon is in the next post. The tool is written
largely with Claude — docs/authorship.md sets out which parts, and the
deciding, rejecting and measuring are the parts I do.