Killing N+1 Queries with select_related and prefetch_related

An endpoint looks fine in development with ten rows of seed data. In production with ten thousand rows it takes two seconds. Most of the time the cause is the same: an N+1 query, where the object-relational mapper (ORM) quietly runs one extra query per row.

First principles

The time an endpoint spends in the database is roughly the number of queries times the cost of one round trip, plus the work each query does. On a laptop a round trip is nearly free. In production it is a network hop, and it is paid once per query.

An ORM hides those round trips. Reading order.vendor looks like reading an attribute, but if the vendor was not loaded, it is a query. So the query count is set by which relations the code touches inside a loop, and nothing on that line shows it.

That gives one rule: for a list endpoint, the number of queries should be a constant, not a function of the number of rows. The tools below load related data up front so that the rule holds.

What an N+1 looks like

This loop runs one query for the orders, then one more every time the loop touches a related vendor.

orders = Order.objects.all()
for order in orders:
    print(order.vendor.name)  # a fresh query on every iteration

One thousand orders becomes one thousand and one queries. The code reads innocently, which is exactly why this is so easy to ship.

select_related for forward relations

Use select_related for ForeignKey and OneToOne fields. It pulls the related rows into the same query with a Structured Query Language (SQL) join.

orders = Order.objects.select_related("vendor")
for order in orders:
    print(order.vendor.name)  # no extra queries

prefetch_related for reverse and many-to-many

A join cannot fan out across a one-to-many or many-to-many relation without duplicating rows, so prefetch_related runs a second query and stitches the results together in Python.

vendors = Vendor.objects.prefetch_related("orders")
for vendor in vendors:
    print(vendor.name, vendor.orders.count())  # two queries total

Catch it before production

The reliable way to keep N+1 out is to assert the query count in a test. If a future change adds a query, the test fails before the slow endpoint ever ships.

def test_order_list_query_count(client, django_assert_num_queries):
    OrderFactory.create_batch(20)
    with django_assert_num_queries(3):
        client.get("/api/v1/orders/")

Prefetch with a filtered queryset

When you only need some of the related rows, a Prefetch object lets you filter the second query instead of loading everything.

from django.db.models import Prefetch

vendors = Vendor.objects.prefetch_related(
    Prefetch(
        "orders",
        queryset=Order.objects.filter(status="dispatched"),
        to_attr="dispatched_orders",
    )
)

Trim the row with only and defer

Once the query count is right, only() fetches just the columns you use and defer() skips heavy ones you do not. On wide tables that is a real cut to the bytes moved per request.

The habit that matters

Measure, do not guess. Turn on a query inspector while you build the endpoint, watch the count, and treat any number that grows with the data as a bug to fix now rather than a mystery to debug later.

← All writing