Build1 publisher3 min readPublished
A .filter() inside the loop turns prefetch_related into a query per order
Ten orders with a filtered accessor measured twelve queries with the eager load against eleven without it, and the same fixture behaves identically on Django 4.2 and 6.1, so there is no version boundary to wait out.
The Engineer · Build desk
What happened
- A loop that prefetches lines and then calls order.lines.filter(active=True) measured twelve queries for ten orders: one for the orders, one for the prefetch, and one per order the cache could not answer.
- Deleting the prefetch from that same loop leaves eleven queries, so the eager load buys an extra round trip and the memory to hold rows the loop never reads.
- Rewriting the eager load as a Prefetch with a filtered queryset and to_attr brings the page down to two queries in total, whatever the number of orders.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint The value of an eager load is set by the accessor in the loop body, not by the queryset, so a condition added to that body later can flip the prefetch to a net loss without anyone touching the line that created it.
- decision Where the loop needs a different filter each iteration, the choice is to remove the prefetch rather than keep it, because paying for a cache and re-querying anyway costs more than never optimising.
- exposure A filtered prefetch written without to_attr replaces the default relation cache, leaving unrelated code on the same page that asked for every line holding only the active ones.
- cost Lint that matches relation names against accessors moves the cost onto a reviewer: four of the five flagged sites the author checked were name collisions, and the rest of the 79 still need a human.
Call `order.lines.filter(active=True)` on a prefetched parent and the related manager finds a call that changes the SQL, so it builds a new queryset and goes to the database [5]. The rows the prefetch already loaded sit in memory, unread [3]. The dev.to author's test is not how expensive the operation sounds but whether it can be answered from a list of already-fetched objects without changing the query, and `filter()`, `order_by()` and `first()` all change it [6]. That is why `count()` and `exists()` come back from the cache for nothing, while `first()` issues a fresh query with a `LIMIT 1` on it [7][8]. `order_by()` is the one worth staring at, since it asks the database to sort rows that are already in RAM, once per parent [9].
The measured fixture is ten parents with three children each, wrapped in `CaptureQueriesContext`, on Django 6.1 [4]. Generalise the count from the mechanism and the loop costs N+2 queries with the prefetch against N+1 without, so the penalty is exactly one query whatever N is [1]. What scales is the hydration: that fixture parks 30 child rows in caches the loop never touches [2]. The query numbers transfer to your code on one condition, which is that you call the same accessor; they do not depend on row width or backend. The memory number is the whole prefetched set, parents times children, so it only bites on wide pages.
Moving the condition into the prefetch gets the page to two queries for any number of orders [10]. The part of that recipe doing real work is `to_attr`. It writes rows to a new attribute and leaves `order.lines` behaving as it did, so a later `order.lines.filter(...)` is an ordinary query rather than a prefetch being thrown away [11]. Where the accessor is `order_by()` or `first()`, no `Prefetch` is needed at all; the rows are in memory, and `max(order.lines.all(), key=...)` reads them there [19].
Same fixture on Django 4.2, the oldest supported version, produced an identical table [12]. The author had earlier written that `count` and `exists` were cache-served since Django 4.1, went to the 4.1 release notes, found nothing, and withdrew the version claim rather than keep it [13]. Two measured versions and a retracted third is the shape of a result I will act on.
The static check is where this stops being cheap. The naive rule (relations a file prefetches, matched against accessors on those names in the same file) flagged 79 sites across nine projects [14]. Five were read by hand: one real defect, four coincidences on relation names that recur in any large codebase [15]. In one Saleor file, a name bound inside one function matched an accessor a hundred lines away in a different function [16]. That is about six percent of the flagged set inspected [3], and the published post breaks off as the author begins describing how he narrowed the rule, so the narrowed version's numbers are not in evidence [17].
The reusable artefact is therefore the method rather than the table. `CaptureQueriesContext` around one loop answers the question for the accessors you actually call, in about five lines, and it answers it for your Django version instead of his.
What to watch
- The narrowed static check: the post breaks off before its results, so whether scoping cuts the four-in-five coincidence rate is unknown.
- Whether the cache-serving of count() and exists() ever appears in Django's documentation rather than only in someone's query counter.
- Any release-note change to related-manager cache behaviour, which would break the 4.2 and 6.1 agreement the table rests on.