Find and Fix N+1 Queries in Django

Problem statement

A list page or API endpoint that is fast in development becomes slow in production. The database shows hundreds of nearly identical queries per request:

SELECT ... FROM "orders_customer" WHERE "orders_customer"."id" = 17;
SELECT ... FROM "orders_customer" WHERE "orders_customer"."id" = 23;
SELECT ... FROM "orders_customer" WHERE "orders_customer"."id" = 41;

This is the N+1 query problem: one query fetches N objects, and then accessing a related object on each one triggers another query, N more in total. Django's ORM makes it easy to write because related attributes load lazily and look like normal attribute access in templates, serializers, and admin list displays.

With ten rows in development, N+1 costs milliseconds. With 500 rows and network latency to a managed database in production, it can take seconds, hold Gunicorn workers, and cause timeouts under load.

Quick answer

1. Detect: count queries per request with django-debug-toolbar in development, assertNumQueries in tests, or database query logs in staging.
2. For forward foreign keys and one-to-one relations used in loops, add select_related().
3. For reverse foreign keys and many-to-many relations, add prefetch_related().
4. Use Prefetch() objects to filter, order, or apply select_related inside prefetched querysets.
5. Replace per-row counts or sums with annotate().
6. Fix the queryset where it is built: the view's get_queryset(), the admin's list_select_related or get_queryset(), or the DRF view's get_queryset().
7. Lock the fix in with assertNumQueries so regressions fail tests.

Step-by-step solution

1. Detect N+1 patterns

In development: django-debug-toolbar's SQL panel shows the number of queries and highlights similar and duplicate queries. Load the page with realistic data volumes.

In tests:

from django.test import TestCase
from django.urls import reverse


class OrderListQueryTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        customer = Customer.objects.create(name="Acme")
        for i in range(20):
            order = Order.objects.create(customer=customer, number=f"A-{i}")
            OrderItem.objects.create(order=order, sku="SKU-1", quantity=1)

    def test_order_list_query_count_is_constant(self):
        with self.assertNumQueries(3):
            self.client.get(reverse("orders:list"))

If the number of queries grows with the number of rows, the test fails. Pick the number from a correct implementation (including session and authentication queries if the view needs a logged-in user).

In staging or production: PostgreSQL's pg_stat_statements extension aggregates statements; an N+1 pattern shows up as a simple lookup by primary key with a very high call count:

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 100)
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

2. Understand select_related

select_related follows forward ForeignKey and OneToOneField relations with SQL joins, fetching related rows in the same query:

# N+1: one query for orders, one per order for its customer
orders = Order.objects.all()
for order in orders:
    print(order.customer.name)

# One query with a JOIN
orders = Order.objects.select_related("customer")
for order in orders:
    print(order.customer.name)

Chain relations with double underscores:

Order.objects.select_related("customer", "customer__account_manager", "shipping_address")

Avoid select_related() with no arguments in production code: it follows all non-null foreign keys and can create large joins you did not intend.

3. Understand prefetch_related

prefetch_related handles relations that return many objects: reverse foreign keys (order.items.all()), many-to-many fields, and generic relations. It runs one additional query per relation and joins the results in Python:

# N+1: one query per order for its items
for order in Order.objects.all():
    for item in order.items.all():
        print(item.sku)

# Two queries total
for order in Order.objects.prefetch_related("items"):
    for item in order.items.all():
        print(item.sku)

The prefetch cache is only used when you call .all() on the relation. Calling .filter(), .order_by(), .exclude(), or .count() on order.items creates a new query and bypasses the prefetch:

for order in Order.objects.prefetch_related("items"):
    active = order.items.filter(active=True)   # new query per order

4. Prefetch objects for filtered or nested data

Use Prefetch to shape the prefetched queryset once:

from django.db.models import Prefetch

orders = Order.objects.select_related("customer").prefetch_related(
    Prefetch(
        "items",
        queryset=OrderItem.objects.filter(active=True).select_related("product").order_by("position"),
        to_attr="active_items",
    )
)

for order in orders:
    for item in order.active_items:
        print(order.customer.name, item.product.name, item.quantity)

• queryset applies the filter, ordering, and a nested select_related for the product.
• to_attr stores the result as a plain list on each order, which makes it explicit in templates and avoids confusion with the unfiltered relation manager.

5. Replace per-row aggregates with annotations

# N+1: a COUNT query per customer
for customer in Customer.objects.all():
    print(customer.name, customer.orders.count())

# One query with GROUP BY
from django.db.models import Count, Sum

customers = Customer.objects.annotate(
    order_count=Count("orders"),
    lifetime_value=Sum("orders__total"),
)
for customer in customers:
    print(customer.name, customer.order_count, customer.lifetime_value)

Be careful when combining several Count or Sum annotations across different multi-valued relations; joins multiply rows and inflate results. Use Count("orders", distinct=True) or subqueries for correctness in those cases.

6. Fix templates by fixing the view

Templates hide queries because attribute access looks harmless:

{% for order in orders %}
  <tr>
    <td>{{ order.customer.name }}</td>
    <td>{{ order.items.count }}</td>
    <td>{{ order.shipping_address.city }}</td>
  </tr>
{% endfor %}

Make the view provide everything the template touches:

from django.db.models import Count
from django.views.generic import ListView


class OrderListView(ListView):
    template_name = "orders/list.html"
    context_object_name = "orders"
    paginate_by = 50

    def get_queryset(self):
        return (
            Order.objects.select_related("customer", "shipping_address")
            .annotate(item_count=Count("items"))
            .order_by("-created_at")
        )

Then use {{ order.item_count }} in the template instead of order.items.count.

7. Django admin list pages

Admin changelists call __str__ and list_display callables for each row:

from django.contrib import admin
from django.db.models import Count


@admin.register(Order)
class OrderAdmin(admin.ModelAdmin):
    list_display = ["number", "customer", "item_count", "created_at"]
    list_select_related = ["customer"]

    def get_queryset(self, request):
        return super().get_queryset(request).annotate(_item_count=Count("items"))

    @admin.display(ordering="_item_count")
    def item_count(self, obj):
        return obj._item_count

Also check __str__ methods of related models: if Customer.__str__ accesses self.account.name, every customer displayed triggers another query unless that relation is also selected.

8. Django REST Framework serializers

Nested serializers and source="relation.field" lookups cause N+1 queries per object in list endpoints. Fix the queryset in the view:

from rest_framework import viewsets


class OrderViewSet(viewsets.ReadOnlyModelViewSet):
    serializer_class = OrderSerializer

    def get_queryset(self):
        return (
            Order.objects.select_related("customer")
            .prefetch_related("items__product")
            .order_by("-created_at")
        )

SerializerMethodField methods that run queries per object are a common hidden source; move that work into annotations or prefetches.

9. Verify the improvement

Compare before and after in staging with production-like data:

from django.db import connection
from django.test.utils import CaptureQueriesContext

with CaptureQueriesContext(connection) as ctx:
    for order in OrderListView().get_queryset()[:50]:
        order.customer.name, order.shipping_address.city, order.item_count
print(len(ctx.captured_queries))

Verification check:

• query count for the endpoint is constant regardless of the number of rows
• assertNumQueries tests pass and are part of CI
• p95 latency for the endpoint drops in APM or access logs
• pg_stat_statements no longer shows the per-row lookup at high call counts

Explanation

Django's lazy relationship loading is convenient for single objects and expensive for collections. select_related turns many lookups into a join, prefetch_related turns them into a single batch query per relation, and annotations push aggregation into SQL. Because the problem lives in how querysets are built rather than in how they are used, the durable fix is to shape querysets where views, admins, and serializers create them, and to guard query counts with tests.

Edge cases / notes

Very large prefetches

Prefetching thousands of related rows for thousands of parents builds large IN lists and uses memory. Paginate the parent list, or limit what you prefetch.

Deferred fields

only() and defer() reduce column loading, but accessing a deferred field later triggers a query per object, creating a new N+1 pattern.

Caching is not the fix

Caching an N+1 page reduces how often the cost is paid, not the cost itself. Cache misses under load still hit the database with hundreds of queries.

Catching N+1 queries automatically in development

Tests catch regressions in covered views; tooling can flag new N+1 patterns while you develop.

Fail loudly on lazy loading in development

Django 4.2 and later do not include a strict mode for lazy loading, but you can approximate one with a query counter middleware in development that warns when a request exceeds a threshold:

# core/middleware_dev.py
import logging

from django.db import connection
from django.test.utils import CaptureQueriesContext

logger = logging.getLogger("dev.queries")


class QueryCountWarningMiddleware:
    threshold = 30

    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        with CaptureQueriesContext(connection) as ctx:
            response = self.get_response(request)
        count = len(ctx.captured_queries)
        if count > self.threshold:
            logger.warning("%s %s ran %d queries", request.method, request.path, count)
        return response

Enable it only in development settings. CaptureQueriesContext forces query recording for the duration of the request, so it adds overhead that does not belong in production.

Spot repeated queries in captured output

When a count is high, group captured SQL by its shape to find the repeated statement:

import re
from collections import Counter

from django.db import connection
from django.test.utils import CaptureQueriesContext


def summarize_queries(func):
    with CaptureQueriesContext(connection) as ctx:
        func()
    shapes = Counter(re.sub(r"\b\d+\b", "?", q["sql"]) for q in ctx.captured_queries)
    for sql, count in shapes.most_common(5):
        print(count, sql[:150])

Replacing numbers with ? collapses WHERE id = 17 and WHERE id = 23 into one shape, so the N+1 statement appears at the top with a count equal to the number of rows.

Production signals

In production, look for endpoints whose database time grows with result size. APM tools and OpenTelemetry traces show many short, similar database spans under one request, which is the visual signature of N+1 queries. pg_stat_statements shows primary key lookups with call counts far higher than request counts.

Common mistakes

• Using prefetch_related and then calling .filter() on the relation in a loop.
• Calling select_related() without arguments.
• Fixing a template's queries in one view while another view renders the same template with a different queryset.
• Relying on development data sizes to judge performance.

Next steps

• Decide what to cache after fixing queries with Django Caching Strategy for Production.
• Find other hot spots with Profile Slow Django Endpoints in Production with py-spy.
• If endpoints are already timing out, see Fix Django 504 Gateway Timeout Behind Nginx.
• Apply the same fixes to APIs with Deploy a Django REST Framework API to Production.

FAQ

What is the N+1 query problem in Django?

One query loads a list of objects, and accessing a related object for each item triggers an additional query, so the total number of queries grows with the number of rows.

When should I use select_related versus prefetch_related?

Use select_related for forward ForeignKey and OneToOneField relations, which can be joined. Use prefetch_related for reverse foreign keys and many-to-many relations, which return multiple objects.

Why does prefetch_related not reduce my queries?

The code probably calls .filter(), .order_by(), or .count() on the related manager, which creates new queries. Use Prefetch with a queryset and to_attr, or annotations.

How do I prevent N+1 queries from coming back?

Add tests with assertNumQueries that load list views and API endpoints with several related objects, so any change that adds per-row queries fails CI.