Django query performance
last modified November 13, 2026 Python 3.15
In this article we make Django ORM code fast. We measure queries with
CaptureQueriesContext and connection.queries, see the N+1
problem, fix it with select_related() and prefetch_related(),
load only the columns we need with only() and defer(), choose
the right way of counting, and read a query plan with explain(). The
examples use Django 6.1, Python 3.15, and the default SQLite database.
The N+1 problem
Relations are loaded lazily. When we read book.author the first time,
Django runs a query for that author. If a loop reads the author of every book, one query
loads the books and one more query runs for each book: N+1 queries for N books. The code
looks innocent and works on a development database with a few rows, but it gets slow
with real data, since every query has a cost of its own, mostly the round trip to the
database. The cure is to tell the ORM in advance what we will need, so that it loads the
related rows together with the main ones. The queries themselves are explained in
Django QuerySets.
The example project
The project is created with uv; the steps are explained in
Django introduction. The commands below create a project
directory, install Django, generate the project with the configuration package
config, start the app shop, and create the folder demos for demo scripts.
$ mkdir queryperformance $ cd queryperformance $ uv init --bare $ uv add django $ uv run django-admin startproject config . $ uv run python manage.py startapp shop $ mkdir demos
The trailing dot in startproject config . creates manage.py and the
config package in the current directory instead of one level deeper. A new app is
ignored by Django until it is listed in INSTALLED_APPS, so we add
the app to the list in config/settings.py; the other
entries are generated by startproject.
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'shop',
]
The listings in this article are of two kinds. A listing with a solid blue border and the label
Project file is a file of the application: models, views, templates, tests, and
settings. A listing with a dashed amber border and the label Demo script is a small
helper in the demos folder; it sets up Django, runs a few lines of code, and prints
the results, so that we can watch the behaviour. Demo scripts are not part of the application;
we run one from the project directory with uv run python -m demos.name.
The models have a many-to-one relation (a book has an author) and a many-to-many relation (a book has tags), as described in Django models.
from django.db import models
class Author(models.Model):
name = models.CharField(max_length=100)
class Meta:
ordering = ['name']
def __str__(self):
return self.name
class Tag(models.Model):
name = models.CharField(max_length=30, unique=True)
def __str__(self):
return self.name
class Book(models.Model):
title = models.CharField(max_length=200)
isbn = models.CharField(max_length=13, db_index=True)
author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
tags = models.ManyToManyField(Tag, related_name='books')
year = models.PositiveSmallIntegerField()
description = models.TextField(blank=True)
class Meta:
ordering = ['title']
def __str__(self):
return self.title
The isbn column has db_index=True, so the database keeps an
index for it; the description is a long text that we do not always need. A script
creates five authors with three books each, and four tags; each book gets two tags.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from shop.models import Author, Book, Tag
tags = [Tag.objects.create(name=name) for name in ['classic', 'scifi', 'satire', 'novel']]
authors = [Author.objects.create(name=f'Author {n}') for n in range(1, 6)]
for number, author in enumerate(authors, start=1):
for n in range(1, 4):
book = Book.objects.create(
title=f'Book {number}.{n}', isbn=f'978{number}{n:09}', author=author,
year=1940 + number * 10 + n, description='Lorem ipsum ' * 200)
book.tags.set(tags[(number + n) % 4:][:2])
print(Author.objects.count(), 'authors,', Book.objects.count(), 'books,',
Tag.objects.count(), 'tags')
The long Lorem ipsum description is repeated 200 times for every
book. The tables are created with makemigrations and
migrate (see Django migrations).
$ uv run python manage.py makemigrations shop
Migrations for 'shop':
shop/migrations/0001_initial.py
+ Create model Author
+ Create model Tag
+ Create model Book
$ uv run python manage.py migrate shop
Operations to perform:
Apply all migrations: shop
Running migrations:
Applying shop.0001_initial... OK
$ uv run python -m demos.seed
5 authors, 15 books, 4 tags
Counting and logging queries
Before optimizing, we measure. There are three tools. The
CaptureQueriesContext context manager from
django.test.utils records the queries that are executed inside its block,
also when DEBUG is false. The list connection.queries holds the
queries of the current connection, but only when DEBUG is true; it is
emptied with reset_queries(). In tests, assertNumQueries() fails when the
block runs a different number of queries. The demo scripts below use the context
manager.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.conf import settings
from django.db import connection, reset_queries
from shop.models import Book
settings.DEBUG = True
reset_queries()
list(Book.objects.filter(year__gt=1970)[:2])
Book.objects.count()
for query in connection.queries:
print(query['sql'])
print(sorted(connection.queries[0]))
The script turns DEBUG on, runs a sliced QuerySet and a
count(), and prints the SQL of the recorded queries. Each entry is a
dictionary with the keys sql and time. The first query shows that
the slice became a LIMIT, the second one that count() is a
single COUNT(*) query. Never leave DEBUG = True in
production: the list of queries grows with every request and uses memory.
$ uv run python -m demos.log SELECT "shop_book"."id", "shop_book"."title", "shop_book"."isbn", "shop_book"."author_id", "shop_book"."year", "shop_book"."description" FROM "shop_book" WHERE "shop_book"."year" > 1970 ORDER BY "shop_book"."title" ASC LIMIT 2 SELECT COUNT(*) AS "__count" FROM "shop_book" ['sql', 'time']
select_related()
The select_related() method follows foreign keys and one-to-one
relations in the same query: the ORM adds a SQL JOIN and fills the related
objects from the extra columns. It is the right tool for relations that point to a single
object. Several relations can be chained, and a double underscore follows them further:
select_related('author__publisher').
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db import connection
from django.test.utils import CaptureQueriesContext
from shop.models import Book
with CaptureQueriesContext(connection) as context:
for book in Book.objects.all():
book.author.name
print('lazy foreign key:', len(context), 'queries')
with CaptureQueriesContext(connection) as context:
for book in Book.objects.select_related('author'):
book.author.name
print('select_related:', len(context), 'query')
print(Book.objects.select_related('author').only('title', 'author__name').query)
The first loop reads the author of every book lazily and runs 16 queries, one for the
books and one for each of the 15 books. With select_related('author')
the same loop needs one query. The printed SQL shows the INNER JOIN; since it
is combined with only(), which is described below, the query selects just the needed
columns.
$ uv run python -m demos.nplus1
lazy foreign key: 16 queries
select_related: 1 query
SELECT "shop_book"."id", "shop_book"."title", "shop_book"."author_id", "shop_author"."id", "shop_author"."name" FROM "shop_book" INNER JOIN "shop_author" ON ("shop_book"."author_id" = "shop_author"."id") ORDER BY "shop_book"."title" ASC
prefetch_related()
Joining works badly for relations that return many rows: a book with several tags
would be repeated for every tag. For many-to-many relations and reverse foreign keys, we
use prefetch_related(). It runs one additional query per relation, with
WHERE id IN (...), and joins the results in Python. The number of queries
is then constant: one for the main objects and one for each prefetched relation. The two
methods are combined freely.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db import connection
from django.test.utils import CaptureQueriesContext
from shop.models import Author, Book
def count_queries(label, func):
with CaptureQueriesContext(connection) as context:
func()
print(f'{label}: {len(context)} queries')
def lazy():
for author in Author.objects.all():
[book.title for book in author.books.all()]
def prefetched():
for author in Author.objects.prefetch_related('books'):
[book.title for book in author.books.all()]
def many_to_many():
for book in Book.objects.prefetch_related('tags'):
[tag.name for tag in book.tags.all()]
def combined():
for book in Book.objects.select_related('author').prefetch_related('tags'):
book.author.name, [tag.name for tag in book.tags.all()]
def filtered_again():
for author in Author.objects.prefetch_related('books'):
author.books.filter(year__gt=1960).count()
count_queries('lazy', lazy)
count_queries('prefetch_related', prefetched)
count_queries('many to many', many_to_many)
count_queries('select_related + prefetch_related', combined)
count_queries('filter on a prefetched relation', filtered_again)
The script counts the queries of five loops. Reading the reverse relation lazily costs
one query for the authors and one for each of the five authors: 6 queries. With
prefetch_related('books') there are two. The many-to-many relation needs two
queries too, and the combination of select_related('author') and
prefetch_related('tags') needs two as well. The last loop shows a pitfall:
author.books.filter(...) builds a new query and ignores the prefetched
cache, so the prefetch is wasted and we get 7 queries. To filter the related objects, we
use a Prefetch object.
$ uv run python -m demos.prefetch lazy: 6 queries prefetch_related: 2 queries many to many: 2 queries select_related + prefetch_related: 2 queries filter on a prefetched relation: 7 queries
Prefetch objects
A Prefetch object customizes the query that loads the related
objects. Its queryset argument filters and orders them, and
to_attr stores the result in a list attribute of our choice, instead of
replacing the default related manager. This also lets us prefetch the same relation twice
with different filters.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db import connection
from django.db.models import Prefetch
from django.test.utils import CaptureQueriesContext
from shop.models import Author, Book
recent = Book.objects.filter(year__gt=1960).order_by('-year')
with CaptureQueriesContext(connection) as context:
authors = Author.objects.prefetch_related(Prefetch('books', queryset=recent, to_attr='recent_books'))
for author in authors:
titles = [book.title for book in author.recent_books]
print(author.name, titles)
print(len(context), 'queries')
print(context.captured_queries[1]['sql'])
The script prefetches only the books published after 1960, newest first, and stores them
in recent_books. All this takes two queries, and the second query printed at
the end shows the WHERE clause with the filter and the list of author ids.
The first author has no books after 1960 and gets an empty list.
$ uv run python -m demos.prefetch_object
Author 1 []
Author 2 ['Book 2.3', 'Book 2.2', 'Book 2.1']
Author 3 ['Book 3.3', 'Book 3.2', 'Book 3.1']
Author 4 ['Book 4.3', 'Book 4.2', 'Book 4.1']
Author 5 ['Book 5.3', 'Book 5.2', 'Book 5.1']
2 queries
SELECT "shop_book"."id", "shop_book"."title", "shop_book"."isbn", "shop_book"."author_id", "shop_book"."year", "shop_book"."description" FROM "shop_book" WHERE ("shop_book"."year" > 1960 AND "shop_book"."author_id" IN (1, 2, 3, 4, 5)) ORDER BY "shop_book"."year" DESC
only(), defer(), and values()
By default Django selects all columns of a table. When a model has a large text
column that we do not display, that is wasted work: the database reads the column and
Python builds the string. The only() method loads just the listed fields
(and the primary key), and defer() loads everything except the listed
ones. The skipped fields are loaded lazily on first access, with one extra query for each object. When we do not
need model instances at all, values() and values_list()
return dictionaries or tuples, which is cheaper still.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db import connection
from django.test.utils import CaptureQueriesContext
from shop.models import Book
print(Book.objects.only('title').query)
print(Book.objects.defer('description').query)
with CaptureQueriesContext(connection) as context:
for book in Book.objects.only('title'):
book.title
print('only(title), title read:', len(context), 'query')
with CaptureQueriesContext(connection) as context:
for book in Book.objects.only('title'):
book.year
print('only(title), year read:', len(context), 'queries')
with CaptureQueriesContext(connection) as context:
titles = list(Book.objects.values_list('title', flat=True))
print('values_list:', len(context), 'query,', titles[:2])
The first two lines print the SQL of only('title') and
defer('description'); the second one selects every column except the long text.
Reading the loaded field costs no query, but reading the deferred
year in the second loop runs a query for each of the 15 books, 16 in all, which is
the N+1 problem again. Defer only the fields that we are sure not to use.
$ uv run python -m demos.only SELECT "shop_book"."id", "shop_book"."title" FROM "shop_book" ORDER BY "shop_book"."title" ASC SELECT "shop_book"."id", "shop_book"."title", "shop_book"."isbn", "shop_book"."author_id", "shop_book"."year" FROM "shop_book" ORDER BY "shop_book"."title" ASC only(title), title read: 1 query only(title), year read: 16 queries values_list: 1 query, ['Book 1.1', 'Book 1.2']
Counting, existence, and bulk operations
Many slow pages are caused by loading more than needed. To count the rows, use
count(), which runs COUNT(*), instead of len(),
which loads all the rows; if the QuerySet is evaluated anyway, len() is fine
because it uses the cache. To test whether any row matches, call exists(),
which stops at the first row, and not bool(queryset), which loads
everything. For large result sets, iterator() streams the rows in chunks and
does not keep them in the QuerySet cache. Writing in a loop is slow, too: use
bulk_create() and bulk_update() instead of calling
save() for every object, and update() with an
F expression when the new value can be computed in SQL.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from django.db import connection
from django.test.utils import CaptureQueriesContext
from shop.models import Book
def count_queries(label, func):
with CaptureQueriesContext(connection) as context:
result = func()
print(f'{label}: {len(context)} query -> {result}')
def with_len():
return len(Book.objects.all())
def with_count():
return Book.objects.count()
def truth_value():
return bool(Book.objects.filter(year__gt=1970))
def with_exists():
return Book.objects.filter(year__gt=1970).exists()
count_queries('len(queryset)', with_len)
count_queries('queryset.count()', with_count)
count_queries('bool(queryset)', truth_value)
count_queries('queryset.exists()', with_exists)
with CaptureQueriesContext(connection) as context:
for book in Book.objects.iterator(chunk_size=5):
pass
print('iterator:', len(context), 'query')
with CaptureQueriesContext(connection) as context:
for book in Book.objects.all():
book.year += 1
book.save()
print('save in a loop:', len(context), 'queries')
books = list(Book.objects.all())
for book in books:
book.year -= 1
with CaptureQueriesContext(connection) as context:
Book.objects.bulk_update(books, ['year'])
print('bulk_update:', [query['sql'].split()[0] for query in context.captured_queries])
Each of the four ways of asking runs one query, but the queries differ: the first and the
third one load all matching rows, while count() and exists() load
a single number. On a table with a million rows this is the difference between
milliseconds and seconds. The loop with save() runs one
UPDATE per book, plus the SELECT: 16 queries. The call to
bulk_update() sends one UPDATE with a
CASE expression inside a transaction; the script prints the types of the
statements. Do not trust the number of queries alone: the amount of data in each
matters too.
$ uv run python -m demos.counting len(queryset): 1 query -> 15 queryset.count(): 1 query -> 15 bool(queryset): 1 query -> True queryset.exists(): 1 query -> True iterator: 1 query save in a loop: 16 queries bulk_update: ['BEGIN', 'UPDATE', 'COMMIT']
Indexes and explain()
A query can be fast or slow depending on whether the database can use an index. The
explain() method of a QuerySet returns the query plan of the database: how
it will find the rows. We create an index with db_index=True on a field,
or with Meta.indexes and models.Index for several fields.
Indexes speed up filtering and ordering, but they slow down writes and use space, so we add
them for the columns that queries actually use.
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()
from shop.models import Book
print('title (no index):')
print(Book.objects.filter(title='Book 1.1').explain())
print('isbn (db_index=True):')
print(Book.objects.filter(isbn='978100000001').explain())
print('year > 1960, ordered by year:')
print(Book.objects.filter(year__gt=1960).order_by('year').explain())
The script explains three queries. The output is the SQLite plan; the last column says
what the database does. SCAN shop_book means that it reads every row, which is what happens when we filter by
the title without an index. SEARCH shop_book USING INDEX ... (isbn=?)
means that it jumps to the rows through the index. USE TEMP B-TREE FOR ORDER
BY says that the rows must be sorted in a temporary structure, because no index
provides the order. The first query needs no sort, since all its rows have the same title. In the
second one the sort comes from the Meta.ordering by title, and in the third one from
the ordering by the year, which has no index. On PostgreSQL, explain(analyze=True) also runs the query and reports real
timings.
$ uv run python -m demos.explain title (no index): 3 0 216 SCAN shop_book isbn (db_index=True): 4 0 61 SEARCH shop_book USING INDEX shop_book_isbn_d60b4a22 (isbn=?) 20 0 0 USE TEMP B-TREE FOR ORDER BY year > 1960, ordered by year: 3 0 216 SCAN shop_book 16 0 0 USE TEMP B-TREE FOR ORDER BY
Testing query counts
A fixed number of queries is a property that can silently break: someone adds a
template line that reads a relation and the page needs a query per row again. The
assertNumQueries() method turns the property into a test.
from django.test import TestCase
from .models import Author, Book, Tag
class QueryCountTests(TestCase):
@classmethod
def setUpTestData(cls):
tags = [Tag.objects.create(name=name) for name in ['a', 'b']]
for n in range(4):
author = Author.objects.create(name=f'Author {n}')
book = Book.objects.create(title=f'Book {n}', isbn=f'{n:013}', author=author, year=2000 + n)
book.tags.set(tags)
def test_lazy_foreign_key_is_n_plus_one(self):
with self.assertNumQueries(1 + 4):
for book in Book.objects.all():
book.author.name
def test_select_related_uses_one_query(self):
with self.assertNumQueries(1):
for book in Book.objects.select_related('author'):
book.author.name
def test_prefetch_related_uses_two_queries(self):
with self.assertNumQueries(2):
for author in Author.objects.prefetch_related('books'):
[book.title for book in author.books.all()]
def test_combined(self):
with self.assertNumQueries(2):
for book in Book.objects.select_related('author').prefetch_related('tags'):
book.author.name, [tag.name for tag in book.tags.all()]
def test_deferred_field_costs_a_query(self):
book = Book.objects.only('title').first()
with self.assertNumQueries(1):
book.year
The tests show the numbers in this article: the lazy loop needs 1 + 4 queries for four
books, select_related() one, prefetch_related() two, and the
combination two. The last test confirms that reading a deferred field costs one query.
$ uv run python manage.py test Found 5 test(s). System check identified no issues (0 silenced). Creating test database for alias 'default'... ..... ---------------------------------------------------------------------- Ran 5 tests in 0.007s OK Destroying test database for alias 'default'...
Source
Django documentation: Database access optimization
Django documentation: select_related()
Django documentation: prefetch_related()
Django documentation: explain()
In this article we have measured queries, fixed the N+1 problem with
select_related() and prefetch_related(), loaded fewer
columns with only() and defer(), chosen between
count(), exists(), and loading rows, and read query plans.
Related topics are Django QuerySets and
Django aggregation and annotation. The
next article deals with raw SQL and transactions.
Author
List all Django tutorials.