Sourav Kohli
DjangoPythonPerformanceDatabase

From 8 Seconds to 80ms: Diagnosing and Eliminating N+1 Queries in Django

A practical field guide to tracking down the query patterns that quietly kill Django performance — and the exact ORM tools to fix them.

Every Django project eventually hits the same wall. The list view that loaded instantly in development starts timing out in production. You add caching, tweak the template, maybe bump up the server — and it helps a little, but not enough. Then someone installs Django Debug Toolbar and sees it: 847 queries to render a single page.

This is the N+1 problem. It’s not exotic, but it’s surprisingly easy to introduce and genuinely painful to diagnose without the right tools. Here’s how I find and fix it in production Django codebases.

What N+1 Actually Looks Like

Take a typical pattern: you’re rendering a list of orders with their associated customer names.

# views.py
def order_list(request):
    orders = Order.objects.filter(status='pending')
    return render(request, 'orders/list.html', {'orders': orders})
<!-- list.html -->
{% for order in orders %}
  <tr>
    <td>{{ order.id }}</td>
    <td>{{ order.customer.name }}</td>  {# ← this line is the problem #}
    <td>{{ order.total }}</td>
  </tr>
{% endfor %}

Each order.customer.name triggers a separate SQL query to look up that customer. If the list has 200 orders, you’ve just fired 201 queries: 1 to get the orders, 200 to get each customer. And if the template also accesses order.customer.email, you don’t fire 400 queries — Django caches the related object on the instance after the first access. But if you have 10 different related models accessed in the template, you can easily end up with thousands of queries.

Step 1: Confirm the Problem

Before optimising anything, measure. Install Django Debug Toolbar for development:

# settings.py (dev only)
INSTALLED_APPS += ['debug_toolbar']
MIDDLEWARE += ['debug_toolbar.middleware.DebugToolbarMiddleware']
INTERNAL_IPS = ['127.0.0.1']

The SQL panel will show you every query, its execution time, and — crucially — a duplicate detection feature that highlights when you’re running the same query pattern repeatedly. Look for high query counts and duplicate patterns.

For production diagnosis or CI-level visibility, you can log queries directly:

import django.db
import logging

logger = logging.getLogger('django.db.backends')
# In settings: LOGGING = {..., 'django.db.backends': {'level': 'DEBUG', ...}}

Or count queries in tests:

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

with CaptureQueriesContext(connection) as ctx:
    response = client.get('/orders/')
assert len(ctx) < 5, f"Too many queries: {len(ctx)}"

This last pattern is worth adding to your test suite for critical views. It’ll catch regressions before they reach production.

select_related follows foreign key relationships and fetches them in a single SQL JOIN instead of separate queries. Use it for ForeignKey and OneToOne relationships.

# Before: 1 + N queries
orders = Order.objects.filter(status='pending')

# After: 1 query with JOIN
orders = Order.objects.select_related('customer').filter(status='pending')

The generated SQL becomes:

SELECT order.*, customer.*
FROM order
INNER JOIN customer ON order.customer_id = customer.id
WHERE order.status = 'pending'

One round trip. The customer attribute on each Order instance is pre-populated from that single query.

select_related handles nested relationships too:

# Get the order, its customer, and the customer's account manager
orders = Order.objects.select_related('customer__account_manager').filter(...)

For ManyToMany relationships and reverse ForeignKey lookups, select_related doesn’t work — the join would produce duplicate rows in a way that’s expensive to deduplicate. prefetch_related does two queries total (one for the base objects, one for the related objects) and handles the matching in Python:

# Order has ManyToMany 'tags'
orders = Order.objects.prefetch_related('tags').filter(status='pending')

# Also works for reverse FK: get each customer's orders
customers = Customer.objects.prefetch_related('order_set').all()

You can combine both:

orders = (
    Order.objects
    .select_related('customer')        # JOIN — one query covers this
    .prefetch_related('tags', 'items') # Two extra queries, then Python merge
    .filter(status='pending')
)

Total queries: 3, regardless of how many orders are in the list.

Step 4: Annotate Instead of Querying in Loops

A related pattern: computing aggregates per object in Python instead of in the database.

# Terrible: N+1 and loads unnecessary data
for customer in Customer.objects.all():
    print(customer.order_set.count())

# Good: one query, database does the counting
from django.db.models import Count

customers = Customer.objects.annotate(order_count=Count('order')).all()
for customer in customers:
    print(customer.order_count)  # already computed

annotate with Count, Sum, Avg, Min, Max covers most aggregate use cases. For more complex per-object computations, Subquery and OuterRef let you express correlated subqueries in the ORM.

A Note on only() and defer()

If you’re accessing many rows but only need a few columns, only() fetches only the specified fields, and defer() defers the rest until accessed:

# Fetch only what the template needs
orders = Order.objects.only('id', 'status', 'total').filter(...)

Use this cautiously — accessing a deferred field triggers an additional query per object, so it can make N+1 worse if you’re not careful about what the template actually uses.

The Payoff

In practice, converting a view from naive ORM calls to properly prefetched queries is often the difference between a page that loads in 8 seconds and one that loads in 80ms. The database is doing the same logical work in both cases; the difference is entirely in how many round trips you’re making and how well you’re using the database’s ability to join and aggregate.

Profile first. Fix with select_related, prefetch_related, and annotate. Add query count assertions to your tests so you know immediately when a future change introduces a regression. That’s the whole loop.

← All Posts