Detecting N+1 Queries in the Django ORM #

The N+1 query is the most common way a productive ORM quietly generates a slow database workload. A page renders correctly in development, passes its functional tests, and then melts a production database under load — not because any single query is slow, but because one innocuous line in a template triggers hundreds of them. This page shows how to detect the pattern precisely in Django, read the evidence in the PostgreSQL plan, and collapse the storm into a single JOIN. It sits under N+1 Query Detection and the broader ORM Translation Pitfalls pillar, which covers how ORM abstractions map onto real execution plans.

The Symptom #

Consider an order dashboard that lists every order with its customer name:

# views.py — the innocent-looking culprit
orders = Order.objects.all()          # 1 query: SELECT * FROM orders

# template
{% for order in orders %}
  {{ order.id }} — {{ order.customer.name }}   {# lazy load per row #}
{% endfor %}

Order.objects.all() runs one query. But order.customer is a lazy ForeignKey descriptor: the first time each order touches .customer, Django emits a fresh query. With 46 orders on the page, that is 1 parent query plus 46 child queries — 47 total. This is the “N+1”: one query to fetch the list, plus N to fetch each row’s relation.

On the database side, the child queries are byte-for-byte identical except for the bound parameter:

-- Fired once per order row, N times per request
SELECT "customers"."id", "customers"."name", "customers"."region_id"
FROM "customers"
WHERE "customers"."id" = $1;   -- $1 = 8123, then 8124, then 8125 ...

Each lookup is a fast primary-key hit. Individually they cost almost nothing. In aggregate they dominate the request: network round-trips, planner overhead, and lock acquisition multiply by N.

Four Ways to Detect It #

1. Count queries in a shell with len(connection.queries). With DEBUG = True, Django records every SQL statement:

from django.db import connection, reset_queries
reset_queries()
list(render_dashboard())          # exercise the code path
print(len(connection.queries))    # 47  ← the smoking gun

If this number scales with the number of rows, you have an N+1.

2. The django-debug-toolbar SQL panel. In a browser, the toolbar’s SQL panel shows the query count, flags “similar” and “duplicate” queries, and lets you expand the 46 identical customers lookups. Duplicate-query highlighting is the fastest visual confirmation.

3. assertNumQueries in tests. This is how you keep the fix from regressing:

def test_dashboard_query_budget(self):
    OrderFactory.create_batch(50)
    with self.assertNumQueries(2):        # fails today with 51
        self.client.get("/dashboard/")

The test asserts a constant query budget. If someone later removes the eager-load, CI fails instead of production.

4. pg_stat_statements in production, where DEBUG is off. PostgreSQL normalizes literals, so all N per-row lookups collapse into a single normalized entry whose calls counter reveals the volume:

-- Database-side fingerprint of an N+1 loop
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
WHERE query LIKE 'SELECT %customers%WHERE %id% = $1'
ORDER BY calls DESC
LIMIT 5;
--  query                               | calls  | mean_exec_time | rows
--  SELECT ... FROM customers WHERE ... | 892140 |         0.04   |    1  ← huge calls, tiny time, 1 row each

A very high calls count paired with a tiny mean_exec_time and rows = 1 is the unmistakable signature: the database is being asked the same trivial question hundreds of thousands of times.

N+1 lazy loading versus one eager JOIN Left: one parent query for orders followed by N separate per-row customer lookups. Right: a single query whose plan is a Hash Join between orders and customers, returning all rows at once. N+1 lazy loading (47 queries) SELECT * FROM orders customers id=$1 customers id=$2 customers id=$3 ... ×N per row Eager select_related (1 query) orders JOIN customers Hash Join (one plan) Seq Scan orders Hash: customers all rows returned once

Reading the Plan: Before #

Django’s lazy per-row query is a bare primary-key scan. Run it with EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, region_id FROM customers WHERE id = 8123;
Index Scan using customers_pkey on customers
   (cost=0.29..8.30 rows=1 width=27) (actual time=0.018..0.019 rows=1 loops=1)
   Index Cond: (id = 8123)          -- one row, one round-trip
   Buffers: shared hit=3
Planning Time: 0.06 ms
Execution Time: 0.03 ms

The cost=0.29..8.30 is a unitless planner estimate; the actual time of 0.03 ms is genuinely fast. The problem is not this plan — it is that Django runs it 46 times, each with its own planning overhead and network round-trip. Multiply 0.03 ms of execution by 46 and add per-statement overhead, and a “fast” query becomes a page-blocking loop.

The relation direction dictates the tool:

Here customer is a forward FK, so select_related is correct:

orders = Order.objects.select_related("customer").all()   # one JOIN, no per-row lookups

The SQL Django now emits joins the two tables:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_amount, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
Hash Join  (cost=18.50..96.20 rows=46 width=35)
           (actual time=0.21..0.63 rows=46 loops=1)
   Hash Cond: (o.customer_id = c.id)   -- single join replaces 46 lookups
   Buffers: shared hit=14
   ->  Seq Scan on orders o (cost=0.00..12.46 rows=46 width=12)
                            (actual time=0.01..0.05 rows=46 loops=1)
   ->  Hash (cost=13.00..13.00 rows=440 width=27)
            (actual time=0.16..0.16 rows=440 loops=1)
         ->  Seq Scan on customers c (actual time=0.01..0.08 rows=440 loops=1)
Planning Time: 0.14 ms
Execution Time: 0.71 ms

One hash join now supplies every customer in a single pass. The 46 separate index scans are gone. Whether the planner picks a hash join, nested loop, or merge join depends on cardinality and available indexes — a tradeoff explored in Eager Loading vs Lazy Loading: Plan Impact — but any of them collapses N round-trips into one.

For a reverse relation (say, each order’s line_items), a JOIN would multiply order rows by their item counts, so prefetch_related batches instead:

-- prefetch_related("line_items") issues exactly one batched follow-up
SELECT id, order_id, sku, qty FROM line_items
WHERE order_id IN (8123, 8124, 8125, ...);   -- one query for all parents

Step-by-Step Resolution Workflow #

Step 1 — Count the queries #

Reproduce the request and measure. In a shell use reset_queries() then len(connection.queries); in a browser read the django-debug-toolbar SQL panel. Record the count and the row count of the list.

Step 2 — Confirm the pattern #

Verify you see one parent query followed by many identical child queries that differ only in the bound id. If the child count equals the number of rows, it is an N+1, not a one-off.

Step 3 — Identify the relation direction #

Look at the model. A forward ForeignKey/OneToOne (the FK column lives on the object you start from) takes select_related. A reverse FK or ManyToMany takes prefetch_related. Getting this wrong is the number-one cause of a “fix” that does nothing.

Step 4 — Apply the eager load #

Add select_related("customer") for forward relations or prefetch_related("line_items") for reverse/many-to-many. Chain multiple relations in one call where needed.

Step 5 — Verify the plan #

Run EXPLAIN (ANALYZE, BUFFERS) on the rewritten SQL. Confirm a single JOIN plan (hash/nested-loop/merge) for select_related, or exactly one additional WHERE id IN (...) query for prefetch_related. The per-row Index Scan ... id = $1 repetitions must be gone.

Step 6 — Lock it in #

Add assertNumQueries(2) around the view so a regression fails CI, and watch pg_stat_statements.calls in production to confirm the counter no longer climbs with row count.

Before/After Plan Comparison #

-- BEFORE: 1 list query + 46 per-row primary-key lookups = 47 queries
Seq Scan on orders                                   (rows=46)
Index Scan using customers_pkey  Index Cond: (id=$1) (rows=1, loops=1)  ×46

-- AFTER: 1 joined query = 2 queries total (list + any prefetch batch)
Hash Join  Hash Cond: (o.customer_id = c.id)         (rows=46, loops=1)
  -> Seq Scan on orders
  -> Hash -> Seq Scan on customers

The request drops from 47 queries to 2: one JOIN for the forward relation, plus at most one batched IN (...) query per prefetched reverse relation.

Common Pitfalls #

prefetch_related re-triggers N+1 when you filter per object. Calling order.line_items.filter(status="OPEN") inside the loop ignores the prefetched cache and issues a fresh query per order. Use Prefetch("line_items", queryset=LineItem.objects.filter(status="OPEN")) so the filter is applied once, in the batched query, and cached.

.count() inside the loop. order.line_items.count() runs a SELECT COUNT(*) per order even after prefetch_related, because .count() hits the database rather than the prefetched Python list. Use len(order.line_items.all()) against the prefetched cache instead.

Nested DRF serializers. A CustomerSerializer embedded in an OrderSerializer accesses order.customer for every serialized row. If the queryset feeding the view lacks select_related("customer"), serialization silently reintroduces the N+1 that the view thought it had fixed.

only() / defer() dropping a needed FK. Trimming columns with .only("id", "total_amount") is a good instinct, but if you later touch order.customer_id’s relation, Django issues a deferred field re-fetch per row — a second, subtler N+1. Include the FK column in only() whenever you traverse the relation.

Frequently Asked Questions #

Does select_related or prefetch_related fix an N+1 query? Use select_related for forward ForeignKey and OneToOne relations: it adds a SQL JOIN and returns everything in one query, visible as a Hash Join or Nested Loop in the plan. Use prefetch_related for reverse ForeignKey and ManyToMany relations: it issues a second query with WHERE id IN (...) and stitches the rows together in Python. Picking the wrong one either fails to eliminate the N+1 or produces a needless join.

How many queries should a list view run? A list view should run a constant number of queries regardless of row count — typically one query per relation you traverse, not one per row. If the query count grows when the result set grows, you have an N+1 pattern. assertNumQueries with a fixed expected count locks that guarantee in place so a regression fails CI.

Can pg_stat_statements detect an N+1 that DEBUG hides? Yes. pg_stat_statements normalizes literals, so the N repeated SELECT ... FROM customers WHERE id = $1 lookups collapse into a single row whose calls counter climbs by N per request. A high calls value with a tiny mean_exec_time is the database-side fingerprint of an N+1 loop and works in production where Django DEBUG is off.

Related