Find and fix N+1 queries with your tests

N+1 queries run one query per row. Fix them with select_related/prefetch_related or SQLAlchemy's selectinload/joinedload, and lock the fix in with assertNumQueries.

An N+1 query is a loop that touches a relation the ORM hasn't loaded yet: one query fetches 50 orders, then each order.customer fetches its customer, 51 queries in all. Each query is quick, which is why nobody notices, but the round trips add up: in a test, in a view under load, in a nightly job over 100,000 rows.

This is the one speed problem in this series where the fix goes in application code, not in the tests. Fixing it makes the tests faster and production faster.

Why it happens

ORMs load relations lazily by default. Django runs a query the first time you access a foreign key or a reverse relation on an instance that doesn't have it cached; SQLAlchemy does the same for relationships with the default lazy="select" loading. Code that's fine for one object becomes N queries inside a loop, a template {% for %}, or a serializer that renders a list.

# One query for the books, then one per book for its author
def titles():
    return [f"{book.title} by {book.author.name}" for book in Book.objects.all()]

In a test with three books that's four queries. The test is a good place to see it because you control exactly how many rows there are.

The fix in Django: select_related and prefetch_related

Use select_related() for foreign keys and one-to-one fields: Django joins the related table into the same query.

def titles():
    return [f"{book.title} by {book.author.name}" for book in Book.objects.select_related("author")]

Use prefetch_related() for reverse foreign keys and many-to-many relations: Django runs one extra query for all of them and attaches the results in Python.

def books_by_author():
    authors = Author.objects.prefetch_related("books")
    return {author.name: [book.title for book in author.books.all()] for author in authors}

That's two queries however many authors there are. To filter or order the prefetched rows, pass a Prefetch object, e.g. Prefetch("books", queryset=Book.objects.order_by("title")). Filtering author.books.filter(...) inside the loop runs a new query per author and undoes the prefetch.

Other common sources: counting related rows in a loop (use annotate(Count("books"))), and calling .get() or .exists() per item (fetch everything once into a dict).

Lock it in with assertNumQueries

Once a view or function runs a fixed number of queries, assert it, so the next change that brings the N+1 back fails a test:

from django.test import TestCase
from model_bakery import baker

from shop.models import Author, Book
from shop.reports import titles


class TitlesTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        author = baker.make(Author)
        baker.make(Book, author=author, _quantity=10)

    def test_titles_runs_one_query(self):
        with self.assertNumQueries(1):
            result = titles()
        self.assertEqual(len(result), 10)

Create more than one or two related rows, or an N+1 looks like a constant. With pytest-django, the fixtures do the same:

import pytest
from model_bakery import baker

from shop.models import Book
from shop.reports import titles


@pytest.mark.django_db
def test_titles_runs_one_query(django_assert_num_queries):
    baker.make(Book, _quantity=10)
    with django_assert_num_queries(1):
        titles()

django_assert_max_num_queries asserts an upper bound instead, which is less brittle for views whose query count legitimately changes (sessions, permission checks). When the count is wrong, assertNumQueries lists the queries that ran, and the pytest-django fixtures do too when you run pytest with -v, so the repeated one is easy to spot.

The fix in SQLAlchemy: selectinload and joinedload

SQLAlchemy 2.0 sets loading strategies per query with options():

from sqlalchemy import select
from sqlalchemy.orm import joinedload, selectinload

# Collections (one-to-many, many-to-many): one extra SELECT ... WHERE author_id IN (...)
authors = session.scalars(select(Author).options(selectinload(Author.books))).all()

# Many-to-one: joined into the same query
books = session.scalars(select(Book).options(joinedload(Book.author))).all()

selectinload is the usual choice for collections; joinedload suits many-to-one relations. You can also set a default on the relationship, relationship(lazy="selectin"), or make accidental lazy loads fail with raiseload("*") in tests that should load everything up front. When the loop only needs an aggregate, replace per-row queries with one grouped query (a join with GROUP BY).

SQLAlchemy has no assertNumQueries, but an engine event gives you one:

from contextlib import contextmanager

from sqlalchemy import event, select
from sqlalchemy.orm import selectinload

from library.models import Author


@contextmanager
def count_queries(engine):
    statements = []

    def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
        statements.append(statement)

    event.listen(engine, "before_cursor_execute", before_cursor_execute)
    try:
        yield statements
    finally:
        event.remove(engine, "before_cursor_execute", before_cursor_execute)


def test_authors_with_books_runs_two_queries(engine, session):
    with count_queries(engine) as statements:
        authors = session.scalars(select(Author).options(selectinload(Author.books))).all()
        assert all(author.books for author in authors)
    assert len(statements) == 2

Keep the tests correct

This changes application code, so it speeds up production too; the tests stay as they are. Two things to check:

Eager loading the wrong thing costs more than it saves: select_related on a large many-to-one joins wide rows, and a prefetch nobody reads is a wasted query. Load what the code actually uses.
Query-count assertions should run against realistic data: several rows and the relations the code touches, so the count proves the query doesn't grow with N.

How flakytest finds it

flakytest counts every test's queries through Django's execute_wrapper and SQLAlchemy's engine events, normalizes the SQL (numbers, strings and IN (...) lists become placeholders) and shows the statements a test repeats in the report's Database section. When a test runs the same SELECT 10 or more times, it suggests Repeated queries (N+1) in the code under test, names the tests with the most repeats and shows the repeated SQL, e.g. 25x in test_order_list: SELECT ... FROM "shop_customer" WHERE "shop_customer"."id" = ?. The estimated saving scales each of those tests' query time down as if its most repeated statement ran once.

Related guides

Create shared test data in setUpTestData instead of setUp: Django runs setUp before every test, so its rows are inserted again each time. setUpTestData creates them once per TestCase class, and every test still starts clean.
Faster factory_boy and model_bakery factories: Slow factory_boy and model_bakery factories? Create shared data once, use build() or baker.prepare() instead of saving, bulk create rows, reuse one Faker instance and tune Hypothesis.
Django testing best practices: How to write fast, reliable Django tests: TestCase vs TransactionTestCase, setUpTestData, model_bakery, force_login, test settings, assertNumQueries and parallel runs.
SQLAlchemy testing best practices: How to test SQLAlchemy 2.0 code with pytest: schema once per session, savepoint rollback per test, factory_boy factories, statement counting, AsyncSession and Alembic checks.

Updated October 18, 2026 · Suggest a change