Django’s ORM can feel like magic, but sometimes that magic goes wrong and you end up with a dragon: the N+1 query problem. This happens when your code makes one query to fetch a list of items, and then for each item in that list, it makes another query to fetch related data, leading to a cascade of database hits. It’s like ordering one pizza, and then for every slice, ordering a separate side dish.
Let’s see it in action. Imagine you have Author and Book models, where an Author can have many Books.
# models.py
from django.db import models
class Author(models.Model):
name = models.CharField(max_length=100)
def __str__(self):
return self.name
class Book(models.Model):
title = models.CharField(max_length=200)
author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
def __str__(self):
return self.title
Now, a naive view that lists authors and their books:
# views.py
from django.shortcuts import render
from .models import Author
def author_book_list(request):
authors = Author.objects.all()
return render(request, 'authors/author_book_list.html', {'authors': authors})
And the template that displays them:
<!-- authors/author_book_list.html -->
{% for author in authors %}
<h2>{{ author.name }}</h2>
<ul>
{% for book in author.books.all %} {# <--- THIS IS THE CULPRIT #}
<li>{{ book.title }}</li>
{% endfor %}
</ul>
{% endfor %}
If you have 10 authors and each has 5 books, this view will execute 1 (for Author.objects.all()) + 10 (for each author.books.all()) = 11 queries. If you had 100 authors, it’s 101 queries. This is the N+1 problem.
The core problem is that when Author.objects.all() fetches authors, it doesn’t fetch their related books. It’s lazy. The author.books.all() call inside the loop triggers a separate query for each author.
To fix this, we need to tell Django to "look ahead" and fetch the related books at the same time it fetches the authors. This is called "eager loading" or "joining."
The primary tool for this in Django is select_related for foreign key (one-to-one, many-to-one) relationships and prefetch_related for many-to-many and reverse foreign key relationships.
For our Author and Book example, since books is a reverse foreign key relationship (an Author has many Books), we use prefetch_related.
Here’s the optimized view:
# views.py (optimized)
from django.shortcuts import render
from .models import Author
def author_book_list_optimized(request):
# Fetch authors AND prefetch their related books in one go
authors = Author.objects.prefetch_related('books').all()
return render(request, 'authors/author_book_list.html', {'authors': authors})
Now, when Author.objects.prefetch_related('books').all() is executed, Django performs two queries:
SELECT * FROM authors;SELECT * FROM books WHERE author_id IN (1, 2, 3, ...);(where 1, 2, 3 are the IDs of the authors fetched in the first query).
It then efficiently "attaches" the books to their respective authors in Python memory. The template remains the same, but the number of database queries drops dramatically from N+1 to just 2.
select_related works similarly but uses SQL JOINs. It’s more efficient when you have foreign keys or one-to-one relationships from the model you’re querying to another model.
# Example using select_related (if we were querying books and wanted authors)
def book_list_with_authors(request):
books = Book.objects.select_related('author').all() # Fetches author data in the same query
return render(request, 'books/book_list.html', {'books': books})
This Book.objects.select_related('author').all() query becomes a single SQL query with a JOIN:
SELECT "books"."id", "books"."title", "books"."author_id", "authors"."id", "authors"."name" FROM "books" INNER JOIN "authors" ON ("books"."author_id" = "authors"."id");
You can chain select_related and prefetch_related for deeper relationships. For example, if Author had a Publisher and you wanted to show books, their authors, and the authors’ publishers:
# Assuming Author has ForeignKey to Publisher
# Publisher model defined elsewhere
def complex_list(request):
authors = Author.objects.select_related('publisher').prefetch_related('books').all()
# This will perform:
# 1. Query for Authors with JOIN to Publishers
# 2. Query for Books where author_id is in (list of author IDs)
return render(request, 'complex_list.html', {'authors': authors})
The key is understanding when to use select_related (for FK/1-1, uses JOIN, fewer queries but can make one query huge and slow) versus prefetch_related (for M2M/reverse FK, uses separate queries, better for many-to-many or when joined table would be massive).
Beyond N+1, slow scans happen when your queries aren’t using indexes effectively. This often means querying on fields that don’t have database indexes, or using complex lookups that can’t leverage them.
For instance, if you frequently filter Book objects by title:
# Potentially slow if title is not indexed and many books exist
books = Book.objects.filter(title__icontains='the')
To optimize, add indexes to your models:
# models.py (with indexes)
from django.db import models
class Book(models.Model):
title = models.CharField(max_length=200, db_index=True) # <--- Add index here
author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
class Meta:
indexes = [
models.Index(fields=['title']), # Explicitly define index
]
After adding db_index=True or a Meta.indexes entry, you need to create and apply a database migration:
python manage.py makemigrations
python manage.py migrate
The db_index=True argument tells Django to create a B-tree index on that column. This dramatically speeds up queries that filter or order by that column because the database can quickly locate rows without scanning the entire table.
A common pitfall is using .count() inside a loop. If you need a count of related items, do it before you start iterating or after fetching with prefetch_related. For example, author.books.count() inside a loop is another N+1. If you’ve prefetched, len(author.books) is efficient.
The next hurdle is understanding how to optimize complex queries involving multiple joins, annotations, and aggregations, and how django-debug-toolbar can help you spot these issues.