Django select_related() or prefetch_related()? Both methods eliminate N+1 queries in Django, but they produce very different SQL. The first adds a join to the main query. The second runs multiple queries, then connects the objects in Python. The right choice depends less on data volume than on the type of relationship being traversed.

To understand why, let’s start with a deliberately simple model:

from django.conf import settings
from django.db import models


class Post(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name="posts",
    )
    tags = models.ManyToManyField("Tag", related_name="posts")


class Comment(models.Model):
    post = models.ForeignKey(
        Post,
        on_delete=models.CASCADE,
        related_name="comments",
    )
    body = models.TextField()
    is_public = models.BooleanField(default=True)
    author = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)


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

From a Post, author refers to at most one user. By contrast, comments and tags can contain multiple objects. This difference in cardinality almost always determines which method to use.

The N+1 problem in Django

This loop looks harmless:

posts = Post.objects.all()

for post in posts:
    print(post.title, post.author.username)

Django starts by loading the posts:

SELECT id, title, author_id
FROM blog_post;

Then accessing post.author triggers one query for each post:

SELECT id, username
FROM auth_user
WHERE id = 12;

SELECT id, username
FROM auth_user
WHERE id = 37;

-- One new query for each post

With 100 posts, this produces 101 queries: one for the list, then 100 for the authors. Even if several posts share the same author, each Post instance has its own relationship cache. Django therefore does not automatically share these lookups.

This N+1 can remain hidden in a view. It often appears later in a template, a model property, or a DRF serializer.

select_related() loads the relationship in the main query:

posts = Post.objects.select_related("author")

for post in posts:
    print(post.title, post.author.username)

For the non-nullable ForeignKey in our example, Django generates a query similar to this one:

SELECT
    post.id,
    post.title,
    post.author_id,
    author.id,
    author.username
FROM blog_post AS post
INNER JOIN auth_user AS author
    ON post.author_id = author.id;

The columns from both tables arrive in the same SQL result. Django then builds the Post and its author from each row. Accessing post.author no longer triggers any query.

The join type is not always INNER JOIN. If the relationship accepts NULL, Django generally uses an outer join to keep posts without an author:

author = models.ForeignKey(
    settings.AUTH_USER_MODEL,
    null=True,
    on_delete=models.SET_NULL,
)

LEFT OUTER JOIN auth_user AS author
    ON post.author_id = author.id

Queryset filters can also influence which join is selected. It is therefore better to inspect the generated SQL than to memorize a single form.

select_related() only follows relationships that return at most one object from each main row:

  • a ForeignKey in the forward direction;
  • a OneToOneField in both directions;
  • several chained single-valued relationships, such as post.author.profile.
posts = Post.objects.select_related("author__profile")

Django then adds one join for each table it traverses. This remains a single query, but the result contains more columns. Adding every relationship without checking whether it is used needlessly increases network transfer and database work.

select_related() cannot load comments or tags. A join on a collection would repeat the Post columns for every comment or tag, with the risk of rapidly multiplying the number of rows.

For a reverse relationship such as Post.comments, use prefetch_related():

posts = Post.objects.prefetch_related("comments")

for post in posts:
    for comment in post.comments.all():
        print(comment.body)

Django first runs the main query:

SELECT id, title, author_id
FROM blog_post;

Once the identifiers are known, it runs a second query:

SELECT id, post_id, body, is_public, author_id
FROM blog_comment
WHERE post_id IN (1, 2, 3, 4, 5);

The grouping does not use a SQL JOIN. Django builds a lookup table in Python from comment.post_id, then populates the post.comments cache for each post.

The query count remains constant: two queries for 5 posts or for 500. In return, all prefetched posts and comments are kept in memory.

The ManyToMany relationship case

prefetch_related() also works with Post.tags. Django normally does not need a third query for the intermediate table. It joins that table in the tag query:

posts = Post.objects.prefetch_related("tags")

-- Query 1: posts
SELECT id, title, author_id
FROM blog_post;

-- Query 2: tags and their associated post
SELECT
    post_tags.post_id AS _prefetch_related_val_post_id,
    tag.id,
    tag.name
FROM blog_tag AS tag
INNER JOIN blog_post_tags AS post_tags
    ON tag.id = post_tags.tag_id
WHERE post_tags.post_id IN (1, 2, 3, 4, 5);

The additional post_id column lets Django attach each tag to the correct posts.

Ask one question: from each object in the main queryset, can the relationship return multiple objects?

Relationship accessedCardinality from the objectRecommended methodTypical queries
post.author0 or 1select_related("author")1
post.profile as OneToOne0 or 1select_related("profile")1
post.comments0 to Nprefetch_related("comments")2
post.tags0 to Nprefetch_related("tags")2
Filtered collection0 to NPrefetch()2

prefetch_related() can technically load a ForeignKey, but it then uses two queries where select_related() can use only one. This can still make sense if the related table contains many columns or if the join produces an expensive query plan, but that decision should come from measurements, not an abstract rule.

If you only need the relationship identifier, neither method is useful:

for post in Post.objects.all():
    print(post.author_id)  # Already present on the Post row

A page often displays each post’s author and comments. The two methods are complementary in this case:

posts = (
    Post.objects
    .select_related("author")
    .prefetch_related("comments")
)

Django runs only two queries:

-- Query 1: posts and authors with a join
SELECT post.*, author.*
FROM blog_post AS post
INNER JOIN auth_user AS author
    ON post.author_id = author.id;

-- Query 2: comments for all posts
SELECT *
FROM blog_comment
WHERE post_id IN (1, 2, 3, 4, 5);

Without optimization, this page would have produced 1 + N + N queries. With 100 posts, the count drops from 201 queries to 2.

Avoiding an N+1 in prefetched objects

Prefetching comments does not automatically prefetch their authors:

posts = Post.objects.prefetch_related("comments")

for post in posts:
    for comment in post.comments.all():
        print(comment.author.username)  # N+1 on authors

A nested traversal works:

posts = Post.objects.prefetch_related("comments__author")

It runs three queries: posts, comments, then authors. Because Comment.author is a single-valued relationship, we can do better by joining the authors in the comment query:

from django.db.models import Prefetch

posts = Post.objects.prefetch_related(
    Prefetch(
        "comments",
        queryset=Comment.objects.select_related("author"),
    )
)

This version returns to two queries: one for the posts, then one for the comments joined to their authors. To learn more about custom querysets, filters, and to_attr, read the article on Prefetch(), defer(), and only().

The prefetched cache corresponds exactly to the relationship queryset. A call to .all() reuses it:

for post in posts:
    list(post.comments.all())  # No new query

But a new operation on the manager builds another queryset:

for post in posts:
    post.comments.filter(is_public=True)   # New query
    post.comments.order_by("-id")          # New query

Inside a loop, this recreates the N+1 that you thought you had removed. Apply the filter during the prefetch instead:

public_comments = Comment.objects.filter(is_public=True)

posts = Post.objects.prefetch_related(
    Prefetch(
        "comments",
        queryset=public_comments,
        to_attr="public_comments",
    )
)

for post in posts:
    for comment in post.public_comments:
        print(comment.body)

Here, to_attr stores an explicit Python list. post.comments.all() therefore continues to mean all comments, while post.public_comments refers only to the prefetched subset.

Viewing the actual SQL run by Django

str(queryset.query) displays the SQL for the main query:

posts = Post.objects.select_related("author")
print(posts.query)

This is enough to inspect the JOIN produced by select_related(). It is not enough for prefetch_related(), because the second query depends on the identifiers returned by the first and is only built when the queryset is evaluated.

To capture every query, use Django Debug Toolbar during development or CaptureQueriesContext in a test:

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

posts = Post.objects.prefetch_related("comments")

with CaptureQueriesContext(connection) as captured:
    loaded_posts = list(posts)
    for post in loaded_posts:
        list(post.comments.all())

for query in captured:
    print(query["sql"])

A regression test can then protect the query count:

class PostQueryTests(TestCase):
    def test_posts_and_comments_use_two_queries(self):
        with self.assertNumQueries(2):
            posts = list(Post.objects.prefetch_related("comments"))
            comments = [list(post.comments.all()) for post in posts]

The value of comments appears unused, but the assignment deliberately forces each relationship to be evaluated inside the measured block.

Be careful with very large querysets

prefetch_related() generally builds an IN clause containing the identifiers from the main queryset. With a few hundred rows, this behavior is normal. With tens of thousands, the query becomes more expensive to parse, and all the loaded objects consume memory.

Pagination is often the first solution. For batch processing, iterator() can prefetch each chunk separately in recent Django versions:

posts = Post.objects.prefetch_related("comments")

for post in posts.iterator(chunk_size=500):
    process(post)

The trade-off changes: the main query is still traversed in chunks, and Django runs one prefetch query per chunk. This limits memory usage and the size of IN clauses, but increases the total number of queries. Measure with a representative data volume.

Key takeaways

select_related() performs a SQL join and is suitable for single-valued relationships. prefetch_related() runs separate queries, then associates collections in Python. They are not mutually exclusive: a realistic queryset often combines both.

The most reliable rule remains simple: start with cardinality, inspect the queries that are actually run, then lock in the result with assertNumQueries. A useful ORM optimization is measured not by the number of chained methods, but by the SQL and the volume of objects it produces.