Django Query Optimization: Fix the N+1 Problem with Debug Toolbar and EXPLAIN ANALYZE (9/30)

Sizan Mahmud

4 min read

·

1 day ago

--

1

Listen

Share

Day 9 of a 30-Day Django Series — From Beginner Fundamentals to Production-Grade Architecture

Press enter or click to view image in full size

Yesterday we learned the ORM tools for efficient queries. Today we use them on the problem that quietly slows down more Django apps than any other: N+1 queries. More importantly, we’ll learn to measure performance instead of guessing, because an optimization you can’t prove is just a hopeful rewrite.

What the N+1 Problem Actually Is

You fetch a list of 100 orders (1 query), then print each order’s customer name. Because related objects load lazily, each access runs another query (100 more). That’s 1 + N queries for what should be one or two.

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

On your laptop with 10 rows, it’s invisible. In production with 10,000 rows and a database across the network, each query adds latency, and a page that “worked fine in development” times out.

N+1 is sneaky because the slow line doesn’t look slow. It hides in templates, serializers, __str__ methods, and admin list pages. We flagged these places all week; now we'll catch them.

Step 1: Make Queries Visible

You can’t fix what you can’t see. Three tools, from quick to thorough:

Django Debug Toolbar. Install it in development and every page shows how many queries ran, how long each took, and which line of code triggered it. Duplicate queries are highlighted, which is exactly the N+1 signature. This should be in every Django dev environment.

connection.queries and assertNumQueries. For scripts and tests, you can inspect queries directly. In a test, assertNumQueries fails if a view runs more queries than you expect:

with self.assertNumQueries(2):
    self.client.get("/api/orders/")

This is powerful: it turns “performance” into a regression test, so nobody accidentally reintroduces N+1 later. We’ll build on this when we cover testing on Day 13.

Logging SQL. Setting the django.db.backends logger to DEBUG prints every query, useful when you can't run the toolbar, such as on API-only projects.

Step 2: Apply the Fix

Once you’ve found the repeated query, the fix is usually the tools from Day 8:

• Foreign key or one-to-one accessed in a loop? Add select_related("customer").
• Reverse relation or many-to-many? Add prefetch_related("orders").
• Counting related rows per object? Use annotate(Count(...)) instead of calling .count() in a loop.

For DRF, apply the fix in the viewset’s queryset, since the serializer is where the extra queries fire:

class OrderViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Order.objects.select_related("customer").prefetch_related("items")
    serializer_class = OrderSerializer

Then re-run the toolbar or assertNumQueries and confirm the count dropped. Always verify; a wrong prefetch can add queries instead of removing them.

Step 3: Reduce What You Fetch

Fewer queries isn’t the only goal. Fetching less data per query matters too:

• .only() / .defer() load just the columns you need, helpful when models carry large text or JSON fields.
• .values() / .values_list() return plain dictionaries or tuples, skipping the cost of building model instances when you only need raw data.
• .exists() instead of len(qs) or if qs: checks for rows without loading them.
• .iterator() streams large result sets instead of loading everything into memory, a technique that becomes essential when we reach very large tables.
• Pagination on every list endpoint, as set up on Day 7.

Step 4: When the Query Itself Is Slow

Sometimes the query count is fine, but one query takes seconds. Now you need to see what PostgreSQL is doing. Print the SQL from your queryset with str(queryset.query), or use .explain(), then run it under EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

Three things to look for in the output:

• Seq Scan on a large table means PostgreSQL read every row. For a filter on a big table, that’s usually a missing index.
• Actual time vs. estimated rows. Large mismatches suggest stale statistics, which ANALYZE can refresh.
• Sorts and joins spilling to disk, which point back to the work_mem setting we discussed on Day 4.

EXPLAIN ANALYZE actually executes the query, so be careful running it on statements that modify data. Reading plans takes practice, but even spotting a sequential scan on a million-row table is a major win. We'll go deep on the fix, indexing, tomorrow.

A Practical Workflow

1. Measure first. Find the slow endpoint using the toolbar, logs, or monitoring.
2. Count queries. Fix N+1 with select_related and prefetch_related.
3. Trim the data. Use only, values, exists, and pagination.
4. Inspect the plan. Use EXPLAIN ANALYZE for individual slow queries.
5. Lock it in. Add a assertNumQueries test so the fix stays fixed.

Resist optimizing by instinct. The query you suspect is rarely the slowest one, and measuring takes minutes.

What’s Next

On Day 10, we go deep on database indexing: B-tree, GIN, partial, and composite indexes, and the surprising cases where an index makes things worse.

This is Day 9 of a 30-day series covering Django from beginner fundamentals to advanced production architecture — ORM optimization, handling 300-million-row tables, Kafka and RabbitMQ integration, Redis caching, Docker, CI/CD, load balancing, and cloud deployment. Follow along for the rest of the series.