Query Performance: select_related & prefetch_related

Reviewed & published by Brayan K

The N+1 query problem is when fetching a list of N objects with one query, then touching a related object on each one in a loop, makes Django run one extra query per row — 1 + N queries instead of one.

Part of the free Django course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll see how N+1 sneaks in, how select_related collapses it into a single JOIN, how prefetch_related turns it into just two queries, and how to trim columns with only()/defer() and actually count the queries you run.

What You'll Learn in This Lesson

• What the N+1 query problem is and how to spot it
• select_related: one JOIN for ForeignKey/OneToOne
• prefetch_related: a second query for to-many relations
• When to choose each one
• Trimming columns with only() and defer()
• Counting queries with connection.queries & assertNumQueries

1 The N+1 Query Problem

Imagine a page that lists blog posts with each author's name. You write the obvious loop: fetch the posts, then read post.author.name for each one. The list itself is a single query, but post.author was never loaded, so Django quietly fires one extra query per post to fetch the author. With 100 posts that is 1 + 100 = 101 queries — the classic N+1.

# blog/views.py
posts = Post.objects.all()        # 1 query: SELECT * FROM post

for post in posts:
    # post.author is a ForeignKey that was NOT loaded above,
    # so each access runs its own query:
    #   SELECT * FROM author WHERE id = <post.author_id>
    print(post.title, "by", post.author.name)

# With N posts this is 1 (the list) + N (one author each) = N + 1 queries.

The query log makes the damage obvious — the same author SELECT repeats over and over:

# What Django actually runs (DEBUG=True, connection.queries):
SELECT id, title, author_id FROM blog_post;             -- 1 list query
SELECT id, name FROM blog_author WHERE id = 1;          -- +1
SELECT id, name FROM blog_author WHERE id = 2;          -- +1
SELECT id, name FROM blog_author WHERE id = 1;          -- +1 (again!)
...                                                      -- one per row

# Let's MAKE the N+1 problem visible by counting "database hits".
# Every time we'd touch the database, we bump query_count by one.

query_count = 0

authors = {1: "Alice", 2: "Bob"}      # the author table
posts = [                              # the post table (author_id only)
    {"title": "Intro to Django", "author_id": 1},
    {"title": "ORM Tips",        "author_id": 2},
    {"title": "Query Counting",  "author_id": 1},
]

# 1 query to load the list of posts
query_count += 1
print("Loaded", len(posts), "posts (1 query so far)")

# Naive loop: each post.author lookup is its own database hit
for post in posts:
    query_count += 1                   # SELECT author WHERE id = author_id
    name = authors[post["author_id"]]
    print(post["title"], "by", name)

print("Total queries:", query_count)
print("That is 1 + N =", 1, "+", len(posts), "=", 1 + len(posts))

# EXPECTED:
# Loaded 3 posts (1 query so far)
# Intro to Django by Alice
# ORM Tips by Bob
# Query Counting by Alice
# Total queries: 4
# That is 1 + N = 1 + 3 = 4

# ✅ Expected output:
# Loaded 3 posts (1 query so far)
# Intro to Django by Alice
# ORM Tips by Bob
# Query Counting by Alice
# Total queries: 4
# That is 1 + N = 1 + 3 = 4

2 select_related & prefetch_related

Django gives you two tools to load related data up front. select_related follows to-one relations (ForeignKey, OneToOneField) with a SQL JOIN, so the author comes back inside the same row as the post — the N+1 becomes 1 query. prefetch_related handles to-many relations (ManyToManyField, reverse ForeignKey) by running a second query for all the related rows and joining them to their parents in Python — N+1 becomes 2 queries.

# select_related: one JOIN, one query (ForeignKey / OneToOne)
posts = Post.objects.select_related("author")
for post in posts:
    print(post.title, post.author.name)   # author already loaded -> 0 extra queries
# SQL: SELECT post.*, author.*
#      FROM blog_post
#      INNER JOIN blog_author ON post.author_id = author.id;

# prefetch_related: a second query, joined in Python (ManyToMany / reverse FK)
posts = Post.objects.prefetch_related("tags")
for post in posts:
    print(post.title, [t.name for t in post.tags.all()])  # 0 extra queries
# SQL query 1: SELECT * FROM blog_post;
# SQL query 2: SELECT * FROM blog_tag
#              JOIN blog_post_tags ON ...
#              WHERE post_id IN (1, 2, 3, ...);   -- all tags at once

You can combine and chain them, and even reach across multiple hops with double-underscore syntax:

# Mix both, and follow a relation two levels deep:
posts = (
    Post.objects
        .select_related("author")          # to-one: JOIN
        .prefetch_related("tags", "comments__user")  # to-many: extra queries
)
# Total: 1 (posts + author JOIN) + 1 (tags) + 1 (comments) + 1 (users) = 4 queries,
# no matter how many posts there are.

# Same data, three strategies, counting database hits each time.

authors = {1: "Alice", 2: "Bob"}
posts = [
    {"title": "Intro to Django", "author_id": 1},
    {"title": "ORM Tips",        "author_id": 2},
    {"title": "Query Counting",  "author_id": 1},
]

def naive():
    count = 1                      # load the post list
    for post in posts:
        count += 1                 # one author lookup per post
        _ = authors[post["author_id"]]
    return count

def select_related():
    # A JOIN brings authors back inside the post rows: ONE query total.
    count = 1
    rows = [(p["title"], authors[p["author_id"]]) for p in posts]
    for title, name in rows:
        pass                       # author already present -> no extra hits
    return count

def prefetch_related():
    count = 1                      # query 1: load the posts
    count += 1                     # query 2: load ALL authors in one IN(...) query
    joined = {aid: authors[aid] for aid in authors}
    for post in posts:
        _ = joined[post["author_id"]]   # joined in Python -> no extra hits
    return count

print("naive            :", naive(), "queries")
print("select_related   :", select_related(), "query")
print("prefetch_related :", prefetch_related(), "queries")

# EXPECTED:
# naive            : 4 queries
# select_related   : 1 query
# prefetch_related : 2 queries

# ✅ Expected output:
# naive            : 4 queries
# select_related   : 1 query
# prefetch_related : 2 queries

3 only(), defer() & Counting Queries

Once you have the right number of queries, you can shrink how much each one returns. only() loads just the columns you name (plus the primary key); defer() loads everything except the columns you name. Both are useful when a table has heavy TextField or binary columns you do not need on a list page.

# Load only the columns the list page needs:
Post.objects.only("title", "author_id")
# SQL: SELECT id, title, author_id FROM blog_post;   -- skips the big "body"

# Load everything except a heavy column:
Post.objects.defer("body")
# SQL: SELECT id, title, author_id, created_at FROM blog_post;

# CAREFUL: touching a deferred column triggers a lazy extra query!
for post in Post.objects.only("title"):
    print(post.body)   # body was excluded -> one extra query PER post (N+1 again)

To prove your optimisation worked, count the queries. In development read connection.queries; in tests use assertNumQueries:

# In a shell or view (requires DEBUG=True):
from django.db import connection, reset_queries

reset_queries()
list(Post.objects.select_related("author"))
print(len(connection.queries))     # -> 1

# In a test, assert the exact count:
from django.test import TestCase

class PostQueryTests(TestCase):
    def test_post_list_is_one_query(self):
        with self.assertNumQueries(1):
            list(Post.objects.select_related("author"))

# only()/defer() trim columns, and a deferred column you later touch
# costs an extra query. We simulate both column trimming and that trap,
# counting database hits as we go.

ALL_COLUMNS = ["id", "title", "author_id", "body"]

def only(*columns):
    # only() always keeps the primary key, plus the named columns
    loaded = ["id"] + [c for c in columns if c != "id"]
    return loaded

def defer(*columns):
    return [c for c in ALL_COLUMNS if c not in columns]

print("only('title'):", only("title"))
print("defer('body'):", defer("body"))

# Now the trap: load only 'title', then touch the deferred 'body'.
query_count = 1                        # 1 query to load the trimmed rows
loaded = only("title")
rows = [{"id": 1, "title": "Hello"}, {"id": 2, "title": "World"}]
for row in rows:
    if "body" not in loaded:           # body was deferred...
        query_count += 1               # ...so touching it is an extra query!
    body = "lazy-loaded body"
print("Queries after touching deferred body:", query_count)

# EXPECTED:
# only('title'): ['id', 'title']
# defer('body'): ['id', 'title', 'author_id']
# Queries after touching deferred body: 3

# ✅ Expected output:
# only('title'): ['id', 'title']
# defer('body'): ['id', 'title', 'author_id']
# Queries after touching deferred body: 3

🎯 Your Turn: Collapse the N+1 to One Query

Fill in the blank so the optimised version loads the authors with the posts and ends up at a single query, not 1 + N.

# 🎯 YOUR TURN: replace ___ with 1 so select_related stays at one query.
# select_related uses a JOIN, so the authors come back WITH the posts.

posts = [
    {"title": "A", "author_id": 1},
    {"title": "B", "author_id": 2},
    {"title": "C", "author_id": 1},
]

# A JOIN loads posts AND authors together in a single round trip.
query_count = ___      # <-- how many queries does select_related need?

for post in posts:
    pass               # author already loaded -> no extra queries here

print("select_related queries:", query_count)

# EXPECTED OUTPUT:
# select_related queries: 1

Common Errors (And How to Fix Them)

❌ Using select_related on a ManyToMany or reverse FK

Django raises FieldError: Invalid field name(s) given in select_related — those relations return many rows, so a single JOIN can't represent them.

✅ Fix: use prefetch_related("tags") for to-many relations and reserve select_related for ForeignKey/OneToOne.

❌ The N+1 is still there after adding select_related

You touched a relation you didn't name, e.g. post.author was loaded but you used post.category.name.

✅ Fix: name every relation you access — select_related("author", "category") — or count queries to find what you missed.

❌ A new N+1 appears after using only()

You excluded a column with only() or defer(), then accessed it in a loop, so Django re-fetches it per row.

✅ Fix: include every column you actually use in only(...), or drop only() for that view.

📋 Quick Reference

🏆 Mini Challenge: Predict the Query Count

Given a queryset's optimisations, compute how many queries it runs. Each select_related field folds into the base query (a JOIN), while each prefetch_related field adds one separate query.

# 🏆 Mini Challenge: total queries =
#   1 (base query, including every select_related JOIN)
#   + 1 per prefetch_related field.

def count_queries(select_related, prefetch_related):
    base = 1                       # the main query + all FK/OneToOne JOINs
    extra = len(prefetch_related)  # one extra query per to-many prefetch
    return base + extra

# Post.objects.select_related("author").prefetch_related("tags", "comments")
print("Queries:", count_queries(["author"], ["tags", "comments"]))

# A plain queryset with no optimisations:
print("Queries:", count_queries([], []))

# Two JOINs but no prefetch -> still one query:
print("Queries:", count_queries(["author", "category"], []))

# EXPECTED:
# Queries: 3
# Queries: 1
# Queries: 1

❓ Frequently Asked Questions

Lesson complete — you can find and fix N+1 queries!

You can now recognise the 1 + N pattern, reach for select_related on to-one relations and prefetch_related on to-many relations, trim columns with only() and defer(), and prove the win by counting queries with connection.queries and assertNumQueries.

Practice quiz

Continue this course

• Previous: Aggregation & Annotation
• Next: Custom Managers & QuerySets — Encapsulate reusable query logic in managers and chainable QuerySets
• Quick reference: Django cheat sheet

Frequently asked questions

What is the N+1 query problem in Django?

The N+1 query problem happens when you fetch a list of N objects with one query, then access a related object on each one inside a loop. Because the related object was not loaded up front, Django runs one extra query per row to fetch it — that is the N. So you end up running 1 query for the list plus N more for the relations, N+1 in total. It is the single most common cause of slow Django pages, and it gets worse linearly as your data grows.

When do I use select_related vs prefetch_related?

Use select_related for ForeignKey and OneToOneField relations, where each object points at exactly one related row. It adds a SQL JOIN so everything comes back in a single query. Use prefetch_related for ManyToManyField and reverse ForeignKey relations, where each object can have many related rows. It runs a second query for all the related rows and then joins them to the parents in Python. As a rule of thumb: select_related for to-one, prefetch_related for to-many.

What do only() and defer() do?

Both limit which columns are loaded from the database. only() loads just the columns you name (plus the primary key), and defer() loads everything except the columns you name. They reduce how much data crosses the wire, which helps when a table has large text or binary columns you do not need for a particular page. Be careful: touching a column you excluded triggers an extra query to fetch it lazily, which can reintroduce an N+1 pattern.

How do I count the queries Django actually runs?

In development with DEBUG=True, Django records every SQL statement in django.db.connection.queries, so you can check len(connection.queries) before and after a block of code. In tests, the cleaner tool is assertNumQueries(n), which fails if the wrapped code does not run exactly n queries. Third-party tools like django-debug-toolbar surface the same counts visually. Counting queries is how you prove a select_related or prefetch_related actually fixed an N+1.