Fix N+1 Queries in Django with select_related and prefetch_related

Your endpoint returns 20 books in 40 ms on your laptop. In production, with real data, the same page takes two seconds. The database isn't slow: Django is sending it hundreds of tiny queries. This is the N+1 query problem, and it is the most common performance bug in Django and Django REST Framework apps.

This guide shows how to spot it, how select_related() and prefetch_related() fix it, and how to make sure it never comes back.

What an N+1 query looks like#

Take three models. A book has one author and many tags:

from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=200)


class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True)


class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    tags = models.ManyToManyField(Tag, related_name="books", blank=True)
    published = models.DateField()

Now list the books with their authors:

for book in Book.objects.all():
    print(book.title, book.author.name)

Django runs one query to fetch the books. Then, the first time the loop reads book.author on each row, it runs another query to fetch that author. With 100 books, that is 101 queries: 1 for the list, plus N for the related rows. Hence the name.

Each query is fast on its own, so the problem hides in development, where tables are small and the database runs on the same machine. In production, every round trip adds network latency, and the number of queries grows with your data.

How to spot N+1 queries#

• django-debug-toolbar lists every query a page ran and groups the duplicates. Many near-identical queries that differ only in an id are the signature of an N+1.
• Log the SQL in development by setting the django.db.backends logger to DEBUG. This works for API views too, where the toolbar has no page to render on.
• Count queries in code with CaptureQueriesContext, in a shell or a test:

from django.db import connection
from django.test.utils import CaptureQueriesContext

with CaptureQueriesContext(connection) as ctx:
    rows = [(b.title, b.author.name) for b in Book.objects.all()]

print(len(ctx.captured_queries))  # 1 + one per book

select_related: follow foreign keys with a JOIN#

select_related() tells Django to fetch related objects in the same query, with a SQL JOIN. It works for relations that point to a single object: ForeignKey and OneToOneField, and the reverse side of a one-to-one.

books = Book.objects.select_related("author")

for book in books:
    print(book.title, book.author.name)  # no extra queries

That is one query, whatever the number of books. Follow longer chains with double underscores, such as select_related("author__publisher"), and pass several names at once when you need them.

prefetch_related: many-to-many and reverse relations#

A JOIN can't fetch a many-to-many relation or a reverse foreign key without multiplying rows, so select_related() doesn't accept them. prefetch_related() runs one extra query per relation instead, and joins the results in Python:

books = Book.objects.select_related("author").prefetch_related("tags")

for book in books:
    tag_names = [tag.name for tag in book.tags.all()]  # served from the prefetch cache

That is two queries in total: one for the books joined with their authors, and one for the tags of all those books, using WHERE ... IN (...). It works in the other direction too: Author.objects.prefetch_related("books"). Nested paths work as well: prefetch_related("books__tags") fetches every author's books and their tags in three queries.

Prefetch objects: filter, order and rename#

A Prefetch object lets you shape the prefetched queryset. With to_attr, the results land in a plain list attribute, which makes it obvious in the code that the data was prefetched:

from django.db.models import Prefetch

recent = Book.objects.filter(published__year__gte=2024).order_by("-published")

authors = Author.objects.prefetch_related(
    Prefetch("books", queryset=recent, to_attr="recent_books")
)

for author in authors:
    print(author.name, [book.title for book in author.recent_books])

Count related rows with annotate(), not in a loop#

Counting is another common source of N+1 queries. Calling author.books.count() for every author sends one COUNT query per row. Let the database count once:

from django.db.models import Count

authors = Author.objects.annotate(book_count=Count("books"))

for author in authors:
    print(author.name, author.book_count)

When one queryset annotates across two multi-valued relations, add distinct=True to each Count. Otherwise the joins multiply the rows and inflate both numbers.

Fixing N+1 queries in Django REST Framework#

Serializers are where N+1 queries usually hide. A nested serializer or a source="author.name" field reads a relation for every object in the list, and the view's queryset decides whether each read costs a query:

from rest_framework import serializers, viewsets


class BookSerializer(serializers.ModelSerializer):
    author_name = serializers.CharField(source="author.name", read_only=True)
    tags = serializers.SlugRelatedField(slug_field="name", many=True, read_only=True)

    class Meta:
        model = Book
        fields = ["id", "title", "published", "author_name", "tags"]


class BookViewSet(viewsets.ReadOnlyModelViewSet):
    serializer_class = BookSerializer

    def get_queryset(self):
        return Book.objects.select_related("author").prefetch_related("tags")

Keep the optimization in get_queryset(), next to the serializer it serves, so that the two change together. Watch out for SerializerMethodField: any query inside its method runs once per object. Move that work into an annotation or a prefetch, and read the result in the method. The serializers guide has more on keeping list responses fast.

Lock the fix in with a test#

Query counts creep back as code changes. A test that pins the number fails the moment someone adds a field that reads a new relation. Here the viewset is registered with router.register("books", BookViewSet, basename="book"):

from django.test import TestCase
from django.urls import reverse


class BookListQueryTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        author = Author.objects.create(name="Ada")
        tag = Tag.objects.create(name="python")
        for i in range(10):
            book = Book.objects.create(title=f"Book {i}", author=author, published="2025-01-01")
            book.tags.add(tag)

    def test_list_runs_a_constant_number_of_queries(self):
        with self.assertNumQueries(2):
            response = self.client.get(reverse("book-list"))
        self.assertEqual(response.status_code, 200)

The two queries are the books with their authors, and the tags prefetch. If the API paginates, expect one more for the COUNT. With pytest, the django_assert_num_queries fixture does the same job; see Testing DRF APIs with pytest.

Quick reference#

Frequently asked questions#

More in this series#

This is part 4 of 5 in Django REST Framework in practice:

1. DRF serializers: validation, nested writes and speed
2. Authentication, permissions and JWT in DRF
3. Pagination, filtering and search in DRF
4. Fixing N+1 queries with select_related and prefetch_related (this post)
5. Testing DRF APIs with pytest