A slow shop admin: N+1 queries and a missing index

The warehouse works from the order list in the admin: 50 per page, filtered by status, newest first. Each order shows the customer's name, the shipping method and the number of items. After a few years the orders table holds hundreds of thousands of rows.

The template loops over the orders and reads order.customer.name, order.shipping_method.name and order.items.count for each.

The list slowed gradually, so for a long time nobody noticed. In the pre-Christmas season the page started ending in a 502: Gunicorn kills a worker that has not answered within its default 30 seconds, and Nginx then returns 502. The warehouse could only process orders through an export.

Database load rose at the same time and the shop itself slowed down, because customers and the admin share the same database.

The first cause is the N+1 pattern. The order query loads only the orders table. Each order.customer and order.shipping_method in the template then sends its own query, and order.items.count another. With 50 orders that is 1 + 50 × 3 = 151 queries per page load. Each is fast, but 151 round trips to the database add up.

The second cause is a missing index. Filtering by status and sorting by creation date had no suitable index, so for every view PostgreSQL read the whole table and sorted the result just to return the first 50 rows.

On a developer machine with a few dozen test orders, neither was visible.

Locally, on a copy of production data with personal data replaced by made-up values: django-debug-toolbar showed 151 queries, most of them repeats with a different ID.

In production, the pg_stat_statements extension, which collects statistics on queries. It has to be in shared_preload_libraries, which needs a PostgreSQL restart, so it was switched on overnight. The order list query topped the list by total time.

EXPLAIN (ANALYZE, BUFFERS) on that query showed a sequential scan of the whole table (Seq Scan) and a sort of every row with the chosen status.

Related data is loaded at once. select_related joins the customer and shipping method into the same query, and prefetch_related loads the items for all 50 orders in one further query.

orders = (
    Order.objects
    .filter(status=status)
    .select_related("customer", "shipping_method")  # joined into the same query
    .prefetch_related("items")                       # one extra query for the whole page
    .order_by("-created_at")
)
page = Paginator(orders, 50).get_page(request.GET.get("page"))

# template: {{ order.customer.name }}, {{ order.items.all|length }}
# both now read what is already loaded instead of asking the database again

An index on status and creation date in descending order. The table is large and the shop is live, so the index is built CONCURRENTLY, which does not block writes. That cannot run inside a transaction, hence atomic = False on the migration.

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models


class Migration(migrations.Migration):
    # CREATE INDEX CONCURRENTLY cannot run inside a transaction
    atomic = False

    dependencies = [("shop", "0042_order_note")]

    operations = [
        AddIndexConcurrently(
            "order",
            models.Index(fields=["status", "-created_at"], name="order_status_created_idx"),
        ),
    ]

A test that guards against the number of queries growing with the number of orders. It does not pin an exact number, which may legitimately change, but checks that 5 and 50 orders cost the same.

from django.db import connection
from django.test import TestCase
from django.test.utils import CaptureQueriesContext
from django.urls import reverse


def count_queries(client, url):
    with CaptureQueriesContext(connection) as ctx:
        client.get(url)
    return len(ctx.captured_queries)


class OrderListTests(TestCase):
    def test_query_count_does_not_grow_with_orders(self):
        self.client.force_login(make_staff())
        url = reverse("admin_orders")

        make_orders(5)
        few = count_queries(self.client, url)
        make_orders(45)
        many = count_queries(self.client, url)

        self.assertEqual(few, many)

The number of queries is now constant regardless of how many orders are on the page. The data the template reads is already in memory, so it does not ask again.

The index is ordered exactly the way we ask: status first, then date from newest. PostgreSQL finds the first 50 rows of that status directly in the index and stops reading. It no longer scans the whole table or sorts anything.

The test fails on the first new field in the template that would bring N+1 back, before the warehouse finds out.

What it does not address: pagination needs the total number of orders in that status, and that COUNT(*) still grows with their number, though the index makes it cheaper. Other admin pages have not been reviewed.