N+1 in Django: one page, 500 queries — select_related, prefetch_related and counting queries
The page is instant on your laptop and crawls in production, because the template quietly hits the database on every row. How to find N+1, lock the fix in with a test, and when to reach for select_related versus prefetch_related.
Picture an order list page. Each order shows the customer's name and the products inside it. Locally you have 5 test orders and the page renders instantly. In production the page shows 100 orders with about 3 products each, and it gets noticeably slow. There is no heavy computation, no obvious loop over the database. And yet the page sends not one query but around 500.
This is the N+1 problem. It is one of the most common performance bugs in Django precisely because you can't see it in the code: the queries run silently inside a template or a serializer, when you access what looks like a plain attribute.
Where the problem comes from
The models are simple:
# shop/models.py
from django.db import models
class Customer(models.Model):
name = models.CharField(max_length=200)
class Product(models.Model):
title = models.CharField(max_length=200)
price = models.DecimalField(max_digits=12, decimal_places=2)
class Order(models.Model):
customer = models.ForeignKey(
Customer, on_delete=models.PROTECT, related_name="orders"
)
status = models.CharField(max_length=20, default="new")
created_at = models.DateTimeField(auto_now_add=True)
class OrderItem(models.Model):
order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name="items")
product = models.ForeignKey(Product, on_delete=models.PROTECT)
quantity = models.PositiveIntegerField(default=1)
The view looks completely harmless:
# shop/views.py
from django.shortcuts import render
from .models import Order
def order_list(request):
orders = Order.objects.order_by("-created_at")[:100]
return render(request, "shop/order_list.html", {"orders": orders})
And here is the template:
{% for order in orders %}
<h3>#{{ order.id }} — {{ order.customer.name }}</h3>
<ul>
{% for item in order.items.all %}
<li>{{ item.product.title }} × {{ item.quantity }}</li>
{% endfor %}
</ul>
{% endfor %}
Let's count:
orders— 1 query for the list of orders.order.customer.name— fetching the customer for each order: 100 queries.order.items.all— the lines of each order: another 100.item.product.title— the product for every line: 100 × 3 = 300.
That's 1 + 100 + 100 + 300 = 501. Hence the name: "1 + N" (in our case even "1 + N + N + N×M").
DRF does exactly the same thing, just better hidden:
# shop/serializers.py
from rest_framework import serializers
from .models import Order, OrderItem
class OrderItemSerializer(serializers.ModelSerializer):
product_title = serializers.CharField(source="product.title")
class Meta:
model = OrderItem
fields = ["id", "product_title", "quantity"]
class OrderSerializer(serializers.ModelSerializer):
customer_name = serializers.CharField(source="customer.name")
items = OrderItemSerializer(many=True)
class Meta:
model = Order
fields = ["id", "status", "customer_name", "items"]
source="customer.name" and the nested OrderItemSerializer each mean a separate query per object. Reading the serializer, you would hardly guess it talks to the database at all.
Why the "obvious" code breaks
It comes down to the Django ORM being lazy. Order.objects.order_by(...) doesn't run any SQL yet; it just builds a QuerySet object. The query runs when the template's {% for %} starts iterating over it.
order.customer is a ForeignKey descriptor. When you access it, Django checks the instance's cache; if the customer isn't there, it runs SELECT ... FROM shop_customer WHERE id = %s. The reverse relation order.items.all() returns a fresh QuerySet each time, evaluated with its own WHERE order_id = %s query.
Each of those queries is fast on its own — fetching a single row by index can take a fraction of a millisecond. But every query pays for a network round-trip, SQL parsing, connection handling and turning rows into Python objects. Repeat that 500 times and it becomes most of your response time. Worst of all, the count grows with your data: invisible on a test database, steadily worse in production.
N+1 isn't one slow query, it's many fast ones. That's why you don't find it with
EXPLAIN— you find it by counting queries.
Counting queries: connection.queries, CaptureQueriesContext, assertNumQueries
The simplest tool is django.db.connection.queries, but it's only populated when DEBUG=True. A more convenient one is CaptureQueriesContext, which doesn't depend on DEBUG and works anywhere:
# python manage.py shell
from django.db import connection
from django.template.loader import render_to_string
from django.test.utils import CaptureQueriesContext
from shop.models import Order
orders = Order.objects.order_by("-created_at")[:100]
with CaptureQueriesContext(connection) as ctx:
render_to_string("shop/order_list.html", {"orders": orders})
print(len(ctx.captured_queries))
for q in ctx.captured_queries[:5]:
print(q["sql"])
Note that the template is rendered inside the context manager. Because QuerySets are lazy, wrapping only the orders = ... line would show 0 queries — the real work happens during rendering.
In development, django-debug-toolbar is very handy: it lists the queries for every page and flags repeated SQL.
But the most important tool is a test. Once N+1 is fixed, someone can add {{ order.customer.phone }} to the template and break it again. Lock it in with assertNumQueries:
# shop/tests.py
from django.test import TestCase
from django.urls import reverse
from .models import Customer, Order, OrderItem, Product
class OrderListQueryCountTest(TestCase):
@classmethod
def setUpTestData(cls):
for i in range(10):
customer = Customer.objects.create(name=f"Customer {i}")
order = Order.objects.create(customer=customer)
for j in range(3):
product = Product.objects.create(title=f"P{i}-{j}", price=10)
OrderItem.objects.create(order=order, product=product, quantity=1)
def test_query_count_does_not_grow_with_rows(self):
with self.assertNumQueries(2):
response = self.client.get(reverse("order-list"))
self.assertEqual(response.status_code, 200)
One detail matters here: the test data must contain several orders with several lines each. With a single order, an N+1 page may still look like "2 queries". Also, middleware (sessions, auth) can add its own queries — verify the expected number by hand once.
select_related: a single JOIN
select_related is for "to-one" relations: ForeignKey and OneToOneField (including the reverse side of a one-to-one). Django adds a JOIN to the main query and builds the related object from the same row. After that, order.customer is already cached and no extra query is needed.
If the ForeignKey is null=True, Django uses a LEFT OUTER JOIN instead of an INNER JOIN, so orders without a customer don't disappear from the result.
prefetch_related: a separate IN query
For "to-many" relations (reverse ForeignKey, ManyToMany) a JOIN is a bad fit: each order row would be duplicated once per item. So prefetch_related works differently:
- The main query runs and returns the list of orders.
- Django collects their
ids. - It sends one extra query:
WHERE order_id IN (...). - It groups the results by
order_idin Python and puts them into each order's prefetch cache.
Here's the fixed view:
# shop/views.py
from django.db.models import Prefetch
from django.shortcuts import render
from .models import Order, OrderItem
def order_list(request):
items = OrderItem.objects.select_related("product").order_by("id")
orders = (
Order.objects.select_related("customer")
.prefetch_related(Prefetch("items", queryset=items))
.order_by("-created_at")[:100]
)
return render(request, "shop/order_list.html", {"orders": orders})
The SQL that reaches the database looks roughly like this:
-- 1) select_related("customer"): one JOIN
SELECT "shop_order"."id", "shop_order"."customer_id", "shop_order"."status",
"shop_order"."created_at", "shop_customer"."id", "shop_customer"."name"
FROM "shop_order"
INNER JOIN "shop_customer" ON ("shop_order"."customer_id" = "shop_customer"."id")
ORDER BY "shop_order"."created_at" DESC
LIMIT 100;
-- 2) prefetch_related("items") + select_related("product"): one IN query
SELECT "shop_orderitem"."id", "shop_orderitem"."order_id",
"shop_orderitem"."product_id", "shop_orderitem"."quantity",
"shop_product"."id", "shop_product"."title", "shop_product"."price"
FROM "shop_orderitem"
INNER JOIN "shop_product" ON ("shop_orderitem"."product_id" = "shop_product"."id")
WHERE "shop_orderitem"."order_id" IN (412, 411, 409, 408 /* ... */)
ORDER BY "shop_orderitem"."id" ASC;
Whether there are 100 orders or 10, it's always 2 queries — which is exactly what our test asserts.
select_related |
prefetch_related |
|
|---|---|---|
| Relation type | ForeignKey, OneToOne | reverse FK, ManyToMany, GenericRelation |
| SQL | JOIN in the main query | separate query, IN (...) |
| Query count | 1 | 1 + one per relation |
| Joined where | in the database | in Python |
| Risk | too many JOINs, wide rows | huge IN list, lots of memory |
The Prefetch object: which rows, and where
prefetch_related("items") loads every related row. Often that's not what you want: you need only paid orders, in a particular order, or with a nested select_related. That's what the Prefetch object is for — we already used it above for items + product.
With to_attr the result is stored on a separate attribute as a plain list. Since Django 4.2 you can also slice the queryset inside Prefetch — for example, the last 3 paid orders per customer:
from django.db.models import Prefetch
from shop.models import Customer, Order
last_paid = Prefetch(
"orders",
queryset=Order.objects.filter(status="paid").order_by("-created_at")[:3],
to_attr="last_paid_orders",
)
customers = Customer.objects.prefetch_related(last_paid)
for customer in customers:
for order in customer.last_paid_orders: # plain list, no extra query
print(customer.name, order.id)
to_attr has another benefit: the filtered result doesn't get mixed up with the regular customer.orders.all(), and the name last_paid_orders tells the reader exactly what's in it.
In DRF the fix belongs in the view's get_queryset; the serializer doesn't need to change:
# shop/api.py
from django.db.models import Prefetch
from rest_framework import generics
from .models import Order, OrderItem
from .serializers import OrderSerializer
class OrderListAPIView(generics.ListAPIView):
serializer_class = OrderSerializer
def get_queryset(self):
return (
Order.objects.select_related("customer")
.prefetch_related(
Prefetch("items", queryset=OrderItem.objects.select_related("product"))
)
.order_by("-created_at")
)
Why lazy loading fails in async code
Since Django 4.1, QuerySets have an async API: aget(), afirst(), acount(), async for. So this code looks natural:
# shop/views.py
from django.http import JsonResponse
from .models import Order
async def order_detail(request, pk):
order = await Order.objects.aget(pk=pk)
# SynchronousOnlyOperation: You cannot call this from an async context ...
return JsonResponse({"id": order.id, "customer": order.customer.name})
aget() works, but order.customer.name raises SynchronousOnlyOperation. The reason: attribute access is an ordinary synchronous operation, and you can't put await in front of it. Lazy loading needs to make a synchronous database call right there, and Django's synchronous ORM code deliberately refuses to run inside the event loop so it doesn't block it.
In other words, there is no lazy loading in an async context at all — which is effectively protection against N+1. You have to load everything you need up front, explicitly:
# shop/views.py
from django.db.models import Prefetch
from django.http import JsonResponse
from .models import Order, OrderItem
async def order_detail(request, pk):
order = await (
Order.objects.select_related("customer")
.prefetch_related(
Prefetch("items", queryset=OrderItem.objects.select_related("product"))
)
.aget(pk=pk)
)
items = [
{"product": item.product.title, "quantity": item.quantity}
for item in order.items.all() # already in memory, no DB call
]
return JsonResponse(
{"id": order.id, "customer": order.customer.name, "items": items}
)
select_related and prefetch_related run as usual inside aget(), and afterwards order.customer and order.items.all() are read from memory. If a relation wasn't preloaded, use the async methods such as async for item in order.items.all() or await order.items.acount() — but inside a loop that's N+1 again. Also, since Django 5.0 aiterator() supports prefetch_related too.
One more trap: if an async view calls render(), the template renders in the same context, and {{ order.customer.name }} in the template raises the same error.
Pitfalls that show up in production
Breaking the prefetch cache yourself. Prefetching only covers .all(). Call filter(), exclude() or order_by() on the related manager and Django sends a new query:
orders = Order.objects.prefetch_related("items")
for order in orders:
order.items.all() # from prefetch cache
order.items.count() # from cache: len() of cached results
order.items.filter(quantity__gt=1) # NEW query for every order
order.items.order_by("-quantity") # NEW query for every order
If you need a filter, move it into Prefetch(queryset=...). If you only need a number, don't prefetch at all — compute it in the database:
from django.db.models import Count, Q
orders = Order.objects.annotate(
items_total=Count("items"),
bulk_items=Count("items", filter=Q(items__quantity__gt=1)),
)
only() and defer(). If you do Order.objects.only("id") and the template then uses order.status, Django loads the deferred field with a separate query per object. Same N+1, different costume.
order.customer_id vs order.customer.id. The first is a column on the instance itself, no query. The second (without select_related) loads the customer from the database. If all you need is the ID, use the _id attribute.
Too many JOINs. select_related() can be called with no arguments; it then follows and JOINs every non-null ForeignKey. That means wide rows and data you don't need. Always name the fields explicitly.
A huge IN list. Prefetching across a whole table without pagination puts tens of thousands of IDs into IN (...) and loads all of it into memory. Paginate list pages; in background jobs use iterator(chunk_size=...) — since Django 4.1 it supports prefetch_related as long as chunk_size is given.
Relations in __str__. If Order.__str__ returns self.customer.name, the admin, logs and select widgets all turn into N+1. In the admin use list_select_related or override get_queryset.
Properties. Touching a related object inside a model property is a classic source of N+1 that never shows up in the template. If a property is used in a list, check what query sits behind it.
Checklist
- Every list page and list endpoint gets an
assertNumQueriestest, with several objects in the test data. - ForeignKey/OneToOne →
select_related; reverse FK and ManyToMany →prefetch_related. - Need to filter or order related rows, or add a nested
select_related→Prefetch(queryset=..., to_attr=...). - Need only a count or a sum →
annotate(Count(...)), not a prefetch. - In DRF, optimize in
get_querysetand check everysource="a.b"in your serializers. - In async views, load everything up front;
SynchronousOnlyOperationmeans "there's lazy loading here". - In development, look for repeated SQL with django-debug-toolbar or
CaptureQueriesContext. - Need only the ID →
obj.fk_id.
Takeaway
N+1 comes not from slow queries but from a query count that grows with your data, so you find it by counting and lock it down with assertNumQueries. select_related loads to-one relations with a JOIN, prefetch_related loads to-many relations with a single IN query, and Prefetch lets you control exactly what gets loaded and where. Async code doesn't allow lazy loading at all — and that pushes you into the habit of loading data explicitly, up front.
// comments
Loading comments...