Django ORM Query Optimization: Fix N+1, select_related, and prefetch_related

N+1 is the single biggest Django performance killer. A list view that looks fine in development — 20 objects, 20 milliseconds — becomes 200 database queries and 2 seconds of load time in production with real data. The fix is almost always one line of code; finding it requires knowing what to look for.

What the N+1 Problem Actually Is

Consider these models:

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

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

And this view:

def book_list(request):
    books = Book.objects.all()
    for book in books:
        print(book.author.name)  # This triggers a query per book
    return render(request, "books.html", {"books": books})

With 50 books, this runs 51 queries: 1 to fetch all books, then 1 per book to fetch the author. Django does not fetch related objects by default — each attribute access on a related model triggers a new query.

Fixing Foreign Keys: select_related

select_related performs a SQL JOIN and fetches the related object in the same query:

# Before: 51 queries
books = Book.objects.all()

# After: 1 query (JOIN)
books = Book.objects.select_related("author")

The generated SQL shifts from 51 individual lookups to a single JOIN:

-- What the N+1 produces (per book):
SELECT * FROM books;
SELECT * FROM authors WHERE id = 1;
SELECT * FROM authors WHERE id = 2;
-- ... 49 more

-- What select_related produces:
SELECT books.*, authors.*
FROM books
INNER JOIN authors ON books.author_id = authors.id;

Each SELECT * FROM authors WHERE id = N traverses the authors table's primary key B-tree — a fast O(log n) lookup, but it's still a separate database round-trip with connection overhead. The JOIN eliminates all 50 round-trips and lets PostgreSQL use its hash join or nested loop join algorithm to combine the rows in a single pass over the data.

Use select_related for: - ForeignKey relationships - OneToOneField relationships - Any traversal where you need a single related object

It works with chained relationships too:

# Fetch book → author → country in one query
books = Book.objects.select_related("author__country")

Fixing Many-to-Many and Reverse Relations: prefetch_related

select_related uses JOIN, which does not work well for many-to-many relationships (it creates huge cartesian products — if one book has 5 authors and 10 tags, a JOIN returns 50 rows for that book). Use prefetch_related instead:

class Book(models.Model):
    title = models.CharField(max_length=200)
    authors = models.ManyToManyField(Author)
    tags = models.ManyToManyField(Tag)

# Before: 1 + N*2 queries
books = Book.objects.all()

# After: 3 queries total (1 for books, 1 for authors, 1 for tags)
books = Book.objects.prefetch_related("authors", "tags")

prefetch_related runs a separate query per relation and joins the results in Python memory. The SQL looks like:

-- Query 1: fetch all books
SELECT * FROM books;

-- Query 2: fetch all authors for those books (single IN clause)
SELECT books_authors.*, authors.*
FROM books_authors
JOIN authors ON books_authors.author_id = authors.id
WHERE books_authors.book_id IN (1, 2, 3, 4, 5, ...);

The IN clause lets PostgreSQL use the index on author_id in a single scan rather than one lookup per book. The Django ORM then builds a Python dictionary mapping book_id → [author_list] and attaches these to each Book object in memory. The query count is always 2 regardless of how many books you have.

Prefetch with filtering (Prefetch object)

When you need to filter or annotate the prefetched queryset, use the Prefetch class:

from django.db.models import Prefetch

published_books = Book.objects.filter(is_published=True).select_related("author")

authors = Author.objects.prefetch_related(
    Prefetch("book_set", queryset=published_books, to_attr="published_books")
)

for author in authors:
    print(author.published_books)  # No extra queries

only() and defer(): Fetching Fewer Columns

Even with optimized joins, fetching all columns when you only need a few wastes bandwidth and memory. Use only() to specify which fields to load:

# Fetch only the columns you need
books = Book.objects.only("title", "published_at").select_related("author__name")

Or use defer() to exclude specific heavy columns (useful for TextField or large JSON fields):

# Load everything except the large content field
articles = Article.objects.defer("content", "raw_html")

Accessing a deferred field later triggers a separate query — so only defer fields you are certain the view does not need.

Measuring Queries with Django Debug Toolbar

Before optimizing, measure. Install Django Debug Toolbar and watch the query panel:

pip install django-debug-toolbar

# settings.py (development only)
INSTALLED_APPS += ["debug_toolbar"]
MIDDLEWARE = ["debug_toolbar.middleware.DebugToolbarMiddleware"] + MIDDLEWARE
INTERNAL_IPS = ["127.0.0.1"]

The SQL panel shows every query, its execution time, and whether it is a duplicate. "Similar queries" in the toolbar are almost always N+1s.

For production debugging without the toolbar, use Django's query logging:

import logging

LOGGING = {
    "version": 1,
    "handlers": {"console": {"class": "logging.StreamHandler"}},
    "loggers": {
        "django.db.backends": {
            "level": "DEBUG",
            "handlers": ["console"],
        }
    },
}

Or check query counts programmatically in tests:

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

with CaptureQueriesContext(connection) as ctx:
    response = self.client.get("/books/")
    self.assertLessEqual(len(ctx.captured_queries), 5, "Too many queries")

Common Patterns and Their Fixes

The Real-World Checklist

Before any view that fetches multiple objects goes to production:

1. Open Django Debug Toolbar and count the queries
2. Every ForeignKey access in a template or serializer loop → select_related
3. Every M2M or reverse FK access in a loop → prefetch_related
4. Assert a query count ceiling in the test for that view
5. Add select_related/prefetch_related in the queryset definition at the manager level if the same optimization applies everywhere

With 7+ years of Django in production, I have done this optimization pass on every project I have worked on — usually on existing codebases where N+1 was already costing real latency. If you need a Django developer who ships optimized queries from the start, hire me as a Django developer.