The N+1 query problem in Django: how 3 lines send 101 queries

#webdev #django #python #database

Here's a loop that looks completely harmless:

orders = Order.objects.all()
for order in orders:
    print(order.customer.name)

Three lines. Show each order with its customer's name. With 100 orders, this sends 101 queries to your database.

What actually happens

Order.objects.all() builds a query but sends nothing yet. QuerySets are lazy.
The loop starts, so Django fetches the orders. That's 1 query.
order.customer is a foreign key that isn't loaded yet. Every time the loop touches it, Django asks the database again. That's 1 query per order.

100 orders = 1 + 100 = 101 queries. That's the N+1 problem: one query for the list, plus one for every row.

At around 2 ms per query, that's 200 ms added to a single page, and it grows with your data. It looks fine on your laptop with 5 rows and melts in production.

The fix is one method call

orders = Order.objects.select_related("customer")
for order in orders:
    print(order.customer.name)

select_related joins the customer into the same query. The loop then reads it from memory. Same loop, same output: 101 queries become 1.

select_related or prefetch_related?

The relation you follow	Use	Queries
One object: ForeignKey, OneToOne (`order.customer`)	`select_related()`	1 (JOIN)
Many objects: reverse FK, ManyToMany (`order.items.all()`)	`prefetch_related()`	2 (second query with `IN`)

Catch it before production

django-debug-toolbar shows how many queries each page ran.
assertNumQueries in a test fails the day someone reintroduces the loop:

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

Django 6.1: fetch_mode(models.FETCH_RAISE) turns a hidden lazy query into an error, which is great in tests.

Quick check

A view loops over 50 blog posts and prints post.author.name. How many queries without any fix?

51. One for the posts, plus one per post for its author. select_related("author") brings it down to 1.

Remember

Looping over rows and touching a related field? Check the query count.
select_related for one related object, prefetch_related for many.
Measure with the debug toolbar; lock it in with assertNumQueries.

This article and the video are a free sample from Django Mastery, a video course with 306 short animated lessons that takes you from your first model to production deployment (Django 6.1 and 5.2 LTS).

👉 Full course: https://djangomastery.gumroad.com/l/django-mastery
📺 More free lessons: https://www.youtube.com/@Django-Mastery

The course and this article were produced with AI assistance, and the video uses AI narration.

DEV Community