Query Optimization0%
Django Template Filters & Tags

Query Optimization

Beginner12 min read•Updated: Jul 11, 2026
Study MaterialsCheatsheet

Query Optimization

The Object-Relational Mapper (ORM) abstracts database interactions, but naive ORM queries frequently introduce severe performance bottlenecks. The most notorious enterprise performance flaw is the N+1 Queries Problem, where fetching a list of 100 records issues 101 separate SQL queries to the database. Django provides select_related and prefetch_related to eliminate the N+1 problem completely.

1. The Catastrophic N+1 Queries Problem

Consider displaying 100 articles alongside their author's name:

Python
# ❌ The N+1 Anti-Pattern:
articles = Article.objects.all()[:100] # 1 SQL Query: SELECT * FROM articles LIMIT 100
 
for article in articles:
# Triggers an additional SQL query ON EVERY ITERATION:
# SELECT * FROM auth_user WHERE id = article.author_id
print(article.author.username)
 
# Total Database Queries: 1 + 100 = 101 queries!

If 100 concurrent users visit this view, the database receives 10,100 queries per second, crushing database CPU and ballooning response times to seconds.

2. select_related: SQL INNER JOIN (Single-Valued Relationships)

select_related performs an SQL JOIN in a single database query, pre-populating related foreign keys and one-to-one models:

Python
# ✅ Solved with select_related (One-to-Many & One-to-One):
articles = Article.objects.select_related("author", "category")[:100]
 
# Single SQL Query Executed:
# SELECT enterprise_articles.*, auth_user.*, enterprise_category.*
# FROM enterprise_articles
# INNER JOIN auth_user ON enterprise_articles.author_id = auth_user.id
# LEFT OUTER JOIN enterprise_category ON enterprise_articles.category_id = enterprise_category.id
# LIMIT 100
 
for article in articles:
# Zero additional queries! Author and Category are already in Python memory!
print(article.author.username, article.category.name)
 
# Total Database Queries: EXACTLY 1!

Rule of Thumb: Use select_related for ForeignKey and OneToOneField.

3. prefetch_related: Multi-Valued Relationships (Many-to-Many)

SQL JOINs on Many-to-Many or reverse ForeignKey relationships result in Cartesian product data duplication. prefetch_related solves this by executing two separate queries and stitching records together in Python:

Python
# ✅ Solved with prefetch_related (Many-to-Many & Reverse FK):
articles = Article.objects.prefetch_related("tags")[:100]
 
# Query 1: SELECT * FROM enterprise_articles LIMIT 100
# Query 2: SELECT * FROM enterprise_tags WHERE article_id IN (1, 2, 3, ... 100)
# Django joins the instances in memory!
 
for article in articles:
for tag in article.tags.all(): # Zero additional queries!
print(tag.title)
 
# Total Database Queries: EXACTLY 2!

4. Combining Both in Production

In complex dashboards, combine both techniques seamlessly:

Python
articles = (
Article.objects
.filter(status="published")
.select_related("author", "category") # SQL JOIN for single relations
.prefetch_related("tags", "comments") # Batch queries for multi-relations
.only("id", "title", "slug", "created_at", "author__username") # Restrict columns
)

only() and defer()

  • only(*fields): Limits the SELECT clause to retrieve only specified columns, reducing database bandwidth for tables with massive TextField columns.
  • defer(*fields): Fetches all columns except the specified heavy columns.
Interactive Quiz

Practice Quiz

Question 1
What is the "N+1 Queries Problem" in database ORMs?
Question 2
When should you use select_related vs prefetch_related?
Question 3
How many total database queries are executed when querying 500 articles with Article.objects.select_related('author')?
Question 4
What does the .only("title", "created_at") method accomplish on a QuerySet?
Question 5
What happens if code accesses a deferred field that was excluded via .defer('content')?
Next Lesson

Django Template Filters & Tags

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Signals & Model ManagersDjango Template Filters & Tags

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext