Introduction

You ship a feature. It works perfectly in development. You deploy it and your response times go from 80ms to 4 seconds. You check your logs and there are 312 database queries on a single page load. You didn't write 312 queries. You wrote one loop.

That's the N+1 problem. It's one of the most common performance issues in Django applications, it's invisible until it's a crisis, and once you understand it you'll never look at a queryset the same way again.

What is the N+1 problem?

The name describes exactly what happens. You execute 1 query to fetch a list of objects, then for each of those N objects you execute 1 more query to fetch a related object. That's 1 + N queries total. Load a page with 100 orders and you've just fired 101 queries. Load one with 1000 and you've fired 1001.

The reason it happens so naturally in Django is that the ORM is lazy. Related objects are not fetched until you access them. So when you write a loop that touches a related field, Django happily goes back to the database once per iteration, and nothing in the code looks obviously wrong.

The N+1 problem doesn't announce itself. Your code looks clean, your tests pass, and your local database is fast enough that you never notice. Production is where it ambushes you.

Here's the classic example. You have an Order model with a foreign key to Customer, and you want to render a list of orders with the customer's name.

views.py
def order_list(request):
    orders = Order.objects.all()  # 1 query: SELECT * FROM orders
    return render(request, "orders.html", {"orders": orders})
orders.html
{% for order in orders %}
  <tr>
    <td>{{ order.id }}</td>
    <td>{{ order.customer.name }}</td>  {# 1 query per order #}
    <td>{{ order.total }}</td>
  </tr>
{% endfor %}

That single line order.customer.name triggers a database query every time it runs. With 300 orders on the page, that's 301 queries. The template looks innocent. The view looks innocent. The problem is in what the ORM does behind the scenes when you access a related field that hasn't been fetched yet.

Spotting it in the wild

The easiest way to see N+1 happening is to print the query count before and after a block of code. Django keeps a log of all queries executed during the request in django.db.connection.queries, but only when DEBUG=True.

python
from django.db import connection, reset_queries

reset_queries()

orders = Order.objects.all()
for order in orders:
    _ = order.customer.name  # accessing the related field

print(f"Queries executed: {len(connection.queries)}")
# Queries executed: 301

You can also just look at the queries themselves to see the repetition:

python
for q in connection.queries:
    print(q["sql"])

# SELECT "orders_order"."id", ... FROM "orders_order"
# SELECT "customers_customer"."id", ... WHERE "customers_customer"."id" = 1
# SELECT "customers_customer"."id", ... WHERE "customers_customer"."id" = 2
# SELECT "customers_customer"."id", ... WHERE "customers_customer"."id" = 3
# ... (one per order)

✦ Tip

Django Debug Toolbar makes this effortless

Install django-debug-toolbar in your dev environment. It shows every query on the page, how long each took, and whether any are duplicates. N+1 patterns show up immediately as a long list of near-identical queries.

select_related

select_related is your fix for N+1 on ForeignKey and OneToOneField relationships. It tells Django to fetch the related object in the same query using a SQL JOIN, rather than waiting until you access the field.

The fix for our order list is one word:

views.py
def order_list(request):
    # Before: 1 + N queries
    # orders = Order.objects.all()

    # After: 1 query with a JOIN
    orders = Order.objects.select_related("customer")
    return render(request, "orders.html", {"orders": orders})

Django now executes a single SELECT with an INNER JOIN on the customers table. Every order.customer access in the template reads from the already-fetched data. Zero extra queries.

You can chain multiple relationships and go multiple levels deep:

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

# Fetch multiple FK relationships at once
orders = Order.objects.select_related("customer", "shipping_address", "billing_address")

Note

select_related only works for single-valued relationships

Use it for ForeignKey and OneToOneField. For ManyToManyField and reverse ForeignKey relationships (where one object relates to many), you need prefetch_related instead.

prefetch_related

prefetch_related handles the relationships that select_related can't: ManyToManyFields, reverse ForeignKeys, and anything where one parent has multiple children. It works differently under the hood. Instead of a JOIN, Django executes a second query to fetch all the related objects at once, then links them to the parent objects in Python.

The result is still 2 queries total, regardless of how many parent objects you have. That's the fix for N+1 on these relationship types.

models.py
class Product(models.Model):
    name = models.CharField(max_length=200)
    tags = models.ManyToManyField("Tag")


class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    items = models.ManyToManyField(Product, through="OrderItem")
views.py
# Without prefetch: 1 query for orders + 1 query per order for items
orders = Order.objects.all()

# With prefetch: 2 queries total
orders = Order.objects.prefetch_related("items")

# Combining both for a view that needs customers and their order items
orders = Order.objects.select_related("customer").prefetch_related("items")

When you need more control over the prefetch, for example to filter or order the related objects, use Prefetch objects:

python
from django.db.models import Prefetch

# Only prefetch active items, ordered by name
orders = Order.objects.prefetch_related(
    Prefetch(
        "items",
        queryset=Product.objects.filter(active=True).order_by("name"),
        to_attr="active_items",  # store result in order.active_items
    )
)

# Now in the template or view:
for order in orders:
    for product in order.active_items:  # no extra query
        print(product.name)

⚠ Gotcha

Don't filter after prefetch_related

If you call .filter() on a prefetched queryset after it's been evaluated, Django will hit the database again and your prefetch is wasted. Do all filtering inside the Prefetch object's queryset argument instead.

Annotations over properties

A subtler source of N+1 is Python properties on your models that trigger queries. If you have a property like order.total that sums line items, accessing it in a loop means one aggregation query per object. The fix is to move the calculation into the database using annotate().

models.py
class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)

    @property
    def item_count(self):
        # This hits the database every single time it's called
        return self.items.count()
views.py
from django.db.models import Count

# Instead of relying on the property, annotate at query time
orders = (
    Order.objects
    .select_related("customer")
    .annotate(item_count=Count("items"))
)

# Now order.item_count is just an integer attribute
# computed in a single query. No per-object database calls.

The same pattern applies to sums, averages, and any other aggregation you'd normally compute in Python. Push the work into the database where it belongs and your loop becomes pure Python iteration over already-computed values.

Detecting N+1 in your project

Catching N+1 early is much better than fixing it after users complain. Here are the tools worth having in place.

The nplusone library

The nplusone package hooks into Django's ORM and raises an error (or a warning) the moment it detects a lazy-loading pattern that would cause N+1. It's perfect for catching issues in your test suite before they reach production.

settings.py
# Add to INSTALLED_APPS in development
INSTALLED_APPS = [
    ...
    "nplusone.ext.django",
]

NPLUSONE_RAISE = True  # raise an error instead of just logging
tests/test_views.py
from django.test import TestCase


class TestOrderListView(TestCase):
    def test_no_n_plus_one(self):
        # nplusone will raise NPlusOneError if the view triggers
        # lazy loading on related fields
        response = self.client.get("/orders/")
        self.assertEqual(response.status_code, 200)

assertNumQueries in tests

Django's built-in assertNumQueries context manager lets you assert that a block of code executes exactly the number of queries you expect. It's a simple and effective way to prevent regressions.

python
class TestOrderList(TestCase):
    def test_query_count(self):
        # Create some test data
        customers = [Customer.objects.create(name=f"Customer {i}") for i in range(10)]
        for customer in customers:
            Order.objects.create(customer=customer, total=100)

        with self.assertNumQueries(2):
            # 1 query for orders, 1 for customers via select_related
            response = self.client.get("/orders/")
            self.assertEqual(response.status_code, 200)

✦ Tip

Monitor query counts in production too

Tests only catch what you write tests for. In production, slow pages with high query counts are worth alerting on. Tools like Watchdock can track API response times and flag endpoints that degrade over time as your data grows.

Summary

N+1 is one of those bugs that feels like a performance problem but is really a correctness problem. Your code is doing the wrong thing. Here's how to make sure it does the right thing.

  • Use select_related for ForeignKey and OneToOneField to fetch related objects in a single JOIN.
  • Use prefetch_related for ManyToManyField and reverse ForeignKey to fetch related sets in a second batch query.
  • Use Prefetch objects when you need to filter or order the prefetched queryset.
  • Use annotate() to move aggregations into the database instead of computing them per-object in Python.
  • Install Django Debug Toolbar in development so you can see exactly what queries each view is firing.
  • Use assertNumQueries in your test suite to lock down query counts and prevent regressions.
  • Use the nplusone library to catch lazy-loading patterns automatically during testing.