With Django's prefetch_related('items') on an order list, why does calling order.items.filter(status='shipped') in the loop still query once per order?

seniorshould knowfreq 48%

answer

what exactly was cached
new QuerySet, empty result cache
all() versus chained methods
shape the prefetch, not the loop

basics

~20 s

prefetch_related() caches only the result of order.items.all(). filter(), exclude() or order_by() build a new QuerySet with new SQL, so each call hits the database and the prefetch is wasted. Filter inside a Prefetch object with to_attr instead.

solid answer

~40 s

When the QuerySet is evaluated, prefetch_related('items') stores, per order, a QuerySet whose result cache holds all of that order's items, and the related manager returns it from order.items.all(). Calls that only read those results use the cache: iterating, len(), slicing, even count() and exists(). But filter(), exclude(), order_by() or annotate() return a different QuerySet with different SQL and an empty cache, so order.items.filter(status='shipped') is a fresh query per order, on top of the prefetch query nobody reads. Fix it by moving the filter into the prefetch, Prefetch('items', queryset=OrderItem.objects.filter(status='shipped'), to_attr='shipped_items'), or by filtering order.items.all() in Python. Also remember that add(), create(), remove(), clear() and set() on the manager drop the prefetched cache.

When the QuerySet is evaluated, `prefetch_related('items')` stores, per order, a QuerySet whose result cache holds all of that order's items, and the related manager returns it from `order.items.all()`. Calls that only read those results use the cache: iterating, `len()`, slicing, even `count()` and `exists()`. But `filter()`, `exclude()`, `order_by()` or `annotate()` return a different QuerySet with different SQL and an empty cache, so `order.items.filter(status='shipped')` is a fresh query per order, on top of the prefetch query nobody reads. Fix it by moving the filter into the prefetch, `Prefetch('items', queryset=OrderItem.objects.filter(status='shipped'), to_attr='shipped_items')`, or by filtering `order.items.all()` in Python. Also remember that `add()`, `create()`, `remove()`, `clear()` and `set()` on the manager drop the prefetched cache.

code

python · 16 lines

python

from django.db.models import Prefetch

from .models import Order, OrderItem

# Before: prefetches all items, then Order.shipped_items() calls
# self.items.filter(status='shipped') -> one extra query per order.
orders = Order.objects.select_related('customer').prefetch_related('items')

# After: the filter lives in the prefetch; order.shipped_items is a list.
orders = Order.objects.select_related('customer').prefetch_related(
    Prefetch(
        'items',
        queryset=OrderItem.objects.filter(status='shipped'),
        to_attr='shipped_items',
    )
)

go deeper

for a junior

Remember that a prefetch only covers order.items.all(), and that calling filter() on the relation inside a loop queries again for every row.

for a middle

Explain the per-instance cached QuerySet, which calls read its result cache, which build new SQL, and how manager writes clear the cache.

for a senior

Diagnose the pattern where a model method hides a related filter, notice the wasted prefetch query, and rewrite it with Prefetch and to_attr.

for a principal

Weigh helper methods that silently query per row against explicit loading contracts, and decide who owns declaring what a page must prefetch.

What prefetch_related() actually stores

When a QuerySet built with prefetch_related('items') is evaluated, Django runs the extra items query, groups the rows by order, and for every Order instance stores a QuerySet whose result cache is already filled in a per-instance dictionary (the internal _prefetched_objects_cache). The related manager order.items checks that dictionary in its get_queryset(): if an entry exists, it hands back the cached QuerySet instead of building a new one.

The key point is what that cached QuerySet represents: exactly order.items.all(), every item of the order. Nothing else is cached.

Calls that read the cache, calls that do not

A method that returns a different QuerySet compiles new SQL, and a new QuerySet starts with an empty result cache. Methods that only read the existing results use the cache.

Call on a prefetched `order.items`	Database hit?
`order.items.all()` and iterating it	No
`len(order.items.all())`	No
`order.items.count()`, `order.items.exists()`	No: they use the filled result cache
`order.items.all()[:3]`	No: slicing a filled cache indexes the list
`order.items.filter(...)`, `.exclude(...)`	Yes
`order.items.order_by(...)`, `.annotate(...)`, `.values(...)`	Yes

So order.items.filter(status='shipped') is a brand-new query for that one order. In a loop over 50 orders that is 50 queries.

Why the page can get worse

The usual path to this bug:

Someone adds prefetch_related('items') to fix per-order item queries on the order list.
Later, a model method such as Order.shipped_items(), returning self.items.filter(status='shipped'), is called from the template.
The page now runs the orders query, the prefetch query nobody reads, and one filter query per order.

The result is one more query than having no prefetch at all, plus the memory of every prefetched item. Django's documentation warns about exactly this: the prefetch cannot help a different query, and it costs a query you do not use.

Fixes

Move the filter into the prefetch. Prefetch('items', queryset=OrderItem.objects.filter(status='shipped'), to_attr='shipped_items') runs one filtered query for all orders and stores each order's matches as a plain list on order.shipped_items.
Filter in Python over the cache when the page also needs all items anyway: [i for i in order.items.all() if i.status == 'shipped'] reads memory only.
Keep both views of the relation by prefetching twice: a plain 'items' for the full list and a Prefetch(..., to_attr='shipped_items') for the subset.
Rewrite helpers that hide queries. A model method that calls filter() on a related manager is a per-row query waiting to be looped; make it read the prefetched attribute when present, or document that callers must prefetch.

If the page only needs a number per order, such as a count of shipped items, a database-side annotation is a better tool than loading rows; that is a separate technique from related-object loading.

Writes clear the cache

The cache is dropped on purpose when the relation changes through its manager. Calling add(), create(), remove(), clear() or set() on order.items removes the prefetched entry, so the next order.items.all() queries again and sees the change. (On a reverse ForeignKey manager, remove() and clear() exist only when the key is nullable.) refresh_from_db() without a field list also empties the instance's prefetched cache.

What Django does not do is update the cache when rows change by other routes, such as an OrderItem saved directly or a bulk update(). A prefetched collection is a snapshot taken when the QuerySet was evaluated.

Spotting it in review

The bypass is easy to miss because the offending line rarely sits next to the prefetch_related() call. Look for:

related-manager calls inside loops that chain filter(), exclude(), order_by() or get(); get() applies its arguments as a filter first, so it queries too;
model methods and properties such as shipped_items that wrap those calls;
custom template tags or filters that receive an order and query its relation;
a query count that still grows with the number of rows even though a prefetch is present.

How to explain it in an interview

The prefetch caches the result of .all() and nothing else.
Chained QuerySet methods create new SQL and bypass it.
Prefetch with to_attr is the way to prefetch a filtered subset.

A strong answer names the silent part too: the unused prefetch query still runs, so the fix is to change what is prefetched, not to delete prefetch_related() blindly or to add more of it.

## What prefetch_related() actually stores When a QuerySet built with `prefetch_related('items')` is evaluated, Django runs the extra items query, groups the rows by order, and for every `Order` instance stores a **QuerySet whose result cache is already filled** in a per-instance dictionary (the internal `_prefetched_objects_cache`). The related manager `order.items` checks that dictionary in its `get_queryset()`: if an entry exists, it hands back the cached QuerySet instead of building a new one. The key point is what that cached QuerySet represents: exactly `order.items.all()`, every item of the order. Nothing else is cached. ## Calls that read the cache, calls that do not A method that returns **a different QuerySet** compiles new SQL, and a new QuerySet starts with an empty result cache. Methods that only read the existing results use the cache. | Call on a prefetched `order.items` | Database hit? | |---|---| | `order.items.all()` and iterating it | No | | `len(order.items.all())` | No | | `order.items.count()`, `order.items.exists()` | No: they use the filled result cache | | `order.items.all()[:3]` | No: slicing a filled cache indexes the list | | `order.items.filter(...)`, `.exclude(...)` | Yes | | `order.items.order_by(...)`, `.annotate(...)`, `.values(...)` | Yes | So `order.items.filter(status='shipped')` is a brand-new query for that one order. In a loop over 50 orders that is 50 queries. ## Why the page can get worse The usual path to this bug: 1. Someone adds `prefetch_related('items')` to fix per-order item queries on the order list. 2. Later, a model method such as `Order.shipped_items()`, returning `self.items.filter(status='shipped')`, is called from the template. 3. The page now runs the orders query, **the prefetch query nobody reads**, and one filter query per order. The result is one more query than having no prefetch at all, plus the memory of every prefetched item. Django's documentation warns about exactly this: the prefetch cannot help a different query, and it costs a query you do not use. ## Fixes - **Move the filter into the prefetch.** `Prefetch('items', queryset=OrderItem.objects.filter(status='shipped'), to_attr='shipped_items')` runs one filtered query for all orders and stores each order's matches as a plain list on `order.shipped_items`. - **Filter in Python over the cache** when the page also needs all items anyway: `[i for i in order.items.all() if i.status == 'shipped']` reads memory only. - **Keep both views of the relation** by prefetching twice: a plain `'items'` for the full list and a `Prefetch(..., to_attr='shipped_items')` for the subset. - **Rewrite helpers that hide queries.** A model method that calls `filter()` on a related manager is a per-row query waiting to be looped; make it read the prefetched attribute when present, or document that callers must prefetch. If the page only needs a **number** per order, such as a count of shipped items, a database-side annotation is a better tool than loading rows; that is a separate technique from related-object loading. ## Writes clear the cache The cache is dropped on purpose when the relation changes through its manager. Calling `add()`, `create()`, `remove()`, `clear()` or `set()` on `order.items` removes the prefetched entry, so the next `order.items.all()` queries again and sees the change. (On a reverse `ForeignKey` manager, `remove()` and `clear()` exist only when the key is nullable.) `refresh_from_db()` without a field list also empties the instance's prefetched cache. What Django does **not** do is update the cache when rows change by other routes, such as an `OrderItem` saved directly or a bulk `update()`. A prefetched collection is a snapshot taken when the QuerySet was evaluated. ## Spotting it in review The bypass is easy to miss because the offending line rarely sits next to the `prefetch_related()` call. Look for: - related-manager calls inside loops that chain `filter()`, `exclude()`, `order_by()` or `get()`; `get()` applies its arguments as a filter first, so it queries too; - model methods and properties such as `shipped_items` that wrap those calls; - custom template tags or filters that receive an order and query its relation; - a query count that still grows with the number of rows even though a prefetch is present. ## How to explain it in an interview - The prefetch caches the result of `.all()` and nothing else. - Chained QuerySet methods create new SQL and bypass it. - `Prefetch` with `to_attr` is the way to prefetch a filtered subset. A strong answer names the silent part too: the unused prefetch query still runs, so the fix is to change what is prefetched, not to delete `prefetch_related()` blindly or to add more of it.

Does order.items.count() use the prefetched items, or run SELECT COUNT(*)?
It uses the cache. The manager returns the prefetched QuerySet, and `QuerySet.count()` returns the length of an already-filled result cache instead of querying; `exists()` behaves the same way. Only chained methods that change the SQL go back to the database.
What happens to the prefetched items after order.items.add(new_item)?
The manager removes that relation's entry from the prefetched cache, so the next `order.items.all()` runs a fresh query and includes the new item. Direct saves of `OrderItem` rows or bulk `update()` calls do not touch the cache, so those changes stay invisible until the orders are reloaded.

saying these in an interview costs you the question

Once items are prefetched, any query on order.items is answered from memory
Django applies filter() to the prefetched list in Python when the relation is cached
order.items.count() always runs SELECT COUNT(*) even after prefetching
The prefetched cache stays valid after order.items.add() or clear()
The fix is to remove prefetch_related() because it caused the extra queries

With Django's prefetch_related('items') on an order list, why does calling order.items.filter(status='shipped') in the loop still query once per order?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What prefetch_related() actually stores

Calls that read the cache, calls that do not

Why the page can get worse

Fixes

Writes clear the cache

Spotting it in review

How to explain it in an interview

▾common follow-ups2 questions

▾red flags5 flags

What prefetch_related() actually stores

Calls that read the cache, calls that do not

Why the page can get worse

Fixes

Writes clear the cache

Spotting it in review

How to explain it in an interview

answer

go deeper