Django ORM Internals: Lazy Evaluation, Query Caching, and the N+1 Problem

Most Django developers use QuerySets daily without thinking much about how they work internally. That’s fine — until you hit a performance wall in production. This article digs into lazy evaluation, the internal result cache, and systematic strategies for killing N+1 queries.

How QuerySets Are Actually Lazy

A QuerySet doesn’t hit the database when you create it. It builds an internal SQL representation and defers execution until you force evaluation.

# No SQL executed here
articles = Article.objects.filter(published=True).order_by("-created_at")

# Still no SQL — chaining is free
articles = articles.select_related("author")

# SQL fires here — iteration forces evaluation
for article in articles:
    print(article.title)

The QuerySet stores its state in self.query — a django.db.models.sql.query.Query object. Chaining creates a clone of that object. This means:

base = Article.objects.filter(published=True)
drafts = base.filter(published=False)  # clone — base is untouched

Evaluation triggers:

Operation	Triggers SQL?
for x in qs	Yes
list(qs)	Yes
qs[0] or qs[0:5]	Yes
bool(qs)	Yes
qs.count()	Yes (SELECT COUNT)
qs.exists()	Yes (SELECT 1)
repr(qs)	Yes (in shell only)
qs.filter(...)	No
qs.order_by(...)	No
qs.select_related(...)	No

The Internal Result Cache

Once a QuerySet evaluates, it caches the results in self._result_cache. Subsequent iterations reuse the cache — no second SQL query.

articles = Article.objects.filter(published=True)

# First iteration → hits DB, populates _result_cache
for a in articles:
    print(a.title)

# Second iteration → reads from cache, no DB round-trip
for a in articles:
    print(a.description)

When the Cache Breaks

Slicing before evaluation bypasses the cache:

articles = Article.objects.all()

# This does NOT populate _result_cache
first = articles[0]

# This re-queries — cache is still empty
second = articles[0]  # second SQL hit!

# Fix: evaluate first
all_articles = list(articles)
first = all_articles[0]
second = all_articles[0]  # no extra query

exists() and count() also bypass the cache — they issue their own optimized queries and don’t fill _result_cache. If you’ve already evaluated a QuerySet, prefer len(qs) over qs.count() and bool(list(qs)) over qs.exists().

Diagnosing N+1 Queries

The N+1 problem is the most common Django performance bug. It occurs when you load N objects and then issue one extra query per object.

# Classic N+1
articles = Article.objects.filter(published=True)
for article in articles:
    # One extra SELECT per article — N queries for N articles
    print(article.author.username)

Using django-debug-toolbar

In development, install django-debug-toolbar and watch the SQL panel. Any page with dozens of nearly-identical queries is an N+1.

Using connection.queries

from django.db import connection, reset_queries
from django.conf import settings

settings.DEBUG = True  # queries only logged in DEBUG mode
reset_queries()

articles = Article.objects.filter(published=True)
for a in articles:
    _ = a.author.username  # triggers N queries

print(len(connection.queries))   # N+1
for q in connection.queries:
    print(q["sql"])

Using assertNumQueries in Tests

from django.test import TestCase

class ArticleQueryTest(TestCase):
    def test_list_view_query_count(self):
        Article.objects.bulk_create([
            Article(title=f"Post {i}", author=self.user, published=True)
            for i in range(10)
        ])
        with self.assertNumQueries(2):  # 1 for articles, 1 for authors
            response = self.client.get("/articles/")
            self.assertEqual(response.status_code, 200)

Fixing N+1: select_related and prefetch_related

select_related — SQL JOIN for ForeignKey / OneToOne

# Before: 1 + N queries
articles = Article.objects.filter(published=True)
for a in articles:
    print(a.author.username)

# After: 1 query with JOIN
articles = Article.objects.select_related("author").filter(published=True)
for a in articles:
    print(a.author.username)  # no extra query

select_related follows ForeignKey and OneToOneField relationships via a SQL JOIN. It’s depth-aware:

# Follow the chain: article → author → profile
Article.objects.select_related("author__profile")

# Select all FK relations (use with care — can be expensive)
Article.objects.select_related()

prefetch_related — Separate Query for ManyToMany / Reverse FK

For ManyToMany and reverse ForeignKey relations, a JOIN multiplies rows. Django uses a separate IN query instead:

# Before: 1 + N queries (one per article for its tags)
for article in Article.objects.all():
    tags = list(article.tags.all())

# After: 2 queries total
articles = Article.objects.prefetch_related("tags")
for article in articles:
    tags = list(article.tags.all())  # reads from prefetch cache

The prefetched data is attached to each instance in _prefetched_objects_cache. Calling .all(), .filter() on the prefetched manager reads from this cache — but only if you don’t add new filters that weren’t in the original prefetch_related.

# This BREAKS the prefetch cache — issues N new queries
for article in Article.objects.prefetch_related("comments"):
    recent = article.comments.filter(created_at__gte=cutoff)  # new filter → new query!

# Fix: use Prefetch object with a custom queryset
from django.db.models import Prefetch

articles = Article.objects.prefetch_related(
    Prefetch(
        "comments",
        queryset=Comment.objects.filter(created_at__gte=cutoff),
        to_attr="recent_comments",
    )
)
for article in articles:
    for comment in article.recent_comments:  # reads from cache
        print(comment.body)

Combining Both

Article.objects.select_related("author__profile").prefetch_related(
    "tags",
    Prefetch("comments", queryset=Comment.objects.select_related("author")),
)

only() and defer() — Column-Level Optimization

If you have wide models (many fields, large TextField columns), you can limit what columns are fetched:

# Fetch only these columns
Article.objects.only("id", "title", "created_at")

# Fetch everything except these
Article.objects.defer("body", "metadata")

Accessing a deferred field on an instance triggers a new SQL query per instance — the same N+1 pattern. Use only()/defer() only on list views where you know you won’t access the skipped fields.

When to Drop to Raw SQL

ORM generates good SQL for most cases. Use raw() or connection.execute() when:

• You need database-specific features (window functions, CTEs, LATERAL joins)
• The generated SQL plan is provably worse than hand-written SQL
• You’re doing bulk analytical queries

# Window function — annotate rank within a partition
from django.db import connection

with connection.cursor() as c:
    c.execute("""
        SELECT id, title, author_id,
               RANK() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rank
        FROM articles_article
        WHERE published = TRUE
    """)
    rows = c.fetchall()

# Or use raw() to get model instances back
articles = Article.objects.raw("""
    SELECT a.*, u.username as author_name
    FROM articles_article a
    JOIN auth_user u ON a.author_id = u.id
    WHERE a.published = TRUE
    ORDER BY a.created_at DESC
    LIMIT 50
""")

Rule of thumb: Profile first with EXPLAIN ANALYZE before assuming the ORM is the problem. Most N+1 issues are solved with select_related and prefetch_related. Raw SQL is a last resort, not a default.

Production Checklist

• Every view that renders a list of related objects uses select_related or prefetch_related
• assertNumQueries tests for all critical list views
• only() applied to wide models on list views
• EXPLAIN ANALYZE run on queries touching tables > 100k rows
• django-debug-toolbar enabled in development, disabled in production