Django / Correctness and production evidence / Lesson 14

Query performance and measurement

Measure bounded representative work, inspect query shape and plans, and preserve business meaning while improving cost.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Choose a metric and a representative task
Count a known local relationship query
Evaluate total cost and preserve the contract

Choose a metric and a representative task

A slow page can spend time in database execution, network transfer, Python object construction, templates, or external services. Define latency, query count, returned rows, memory, or throughput before changing the ORM expression. A low query count can still hide one enormous join, and a fast tiny fixture cannot prove performance at production scale. Capture the relevant workload and environment with synthetic or approved data. Start with a correctness baseline so a purported optimization cannot improve time merely by dropping required records, counts, or permission checks. Keep measurement and domain validation connected throughout the change.

Inspect the shape before adding indexes blindly

Review selected columns, filters, joins, ordering, aggregation grain, and materialization. QuerySet.explain can expose the backend’s plan, but options and costs differ by engine and version, and an analyzing plan may execute work. Do not run production plan experiments casually. An index helps particular access patterns while adding storage and write-maintenance cost; it is not a universal fix for every slow query. Compare row estimates and actual evidence in an appropriate isolated environment. Keep structural ordering choices on an allowlist, and preserve stable pagination semantics when changing an order or access path.

Count a known local relationship query

The first program uses CaptureQueriesContext around a narrowly defined read after creating a disposable fixture. select_related retrieves the foreign-key target in the same SQL query for this simple model shape, so the captured read count is one. Table setup and inserts occur outside the measured block and are not hidden as if they cost nothing in a real workflow. The result demonstrates what is being counted, not a time or scale claim. Custom managers, extra property accesses, and other application work can change real totals. Inspect both the returned values and the query evidence so an incorrectly empty result cannot masquerade as an efficient query.

python Isolated executable example

from django.conf import settings
settings.configure(
    SECRET_KEY="synthetic-course-key",
    USE_I18N=False, USE_TZ=True,
    INSTALLED_APPS=[],
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
    DEFAULT_AUTO_FIELD="django.db.models.AutoField",
    ALLOWED_HOSTS=["testserver"],
)
import django
django.setup()

from django.db import connection, models
from django.test.utils import CaptureQueriesContext

class Instructor(models.Model):
    name = models.CharField(max_length=60)
    class Meta:
        app_label = "lab"

class Course(models.Model):
    instructor = models.ForeignKey(Instructor, on_delete=models.PROTECT)
    class Meta:
        app_label = "lab"

with connection.schema_editor() as editor:
    editor.create_model(Instructor)
    editor.create_model(Course)
owner = Instructor.objects.create(name="Mira")
Course.objects.create(instructor=owner)
Course.objects.create(instructor=owner)
with CaptureQueriesContext(connection) as queries:
    names = [row.instructor.name for row in Course.objects.select_related("instructor").order_by("pk")]
print(names)
print(len(queries))

Expected output

['Mira', 'Mira']
1

Only the bounded read is measured. The fixture setup is outside the capture and only the temporary in-memory database is used.

Aggregate at the intended reporting grain

The second program groups synthetic courses by category and counts their rows. values establishes the grouping keys and annotate adds the count, while explicit ordering stabilizes output. A report with multiple one-to-many joins can multiply rows and distort sums, so define the grain before adding distinct as a reflex. Count-distinct can answer one question while leaving another aggregate wrong. Consider pre-aggregation or subqueries when separate detail collections must be combined. A local correct count is evidence for this fixture; it does not establish the optimal plan for every engine or distribution of categories.

python Isolated executable example

from django.conf import settings
settings.configure(
    SECRET_KEY="synthetic-course-key",
    USE_I18N=False, USE_TZ=True,
    INSTALLED_APPS=[],
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
    DEFAULT_AUTO_FIELD="django.db.models.AutoField",
    ALLOWED_HOSTS=["testserver"],
)
import django
django.setup()

from django.db import connection, models
from django.db.models import Count

class Course(models.Model):
    category = models.CharField(max_length=30)
    class Meta:
        app_label = "lab"

with connection.schema_editor() as editor:
    editor.create_model(Course)
Course.objects.bulk_create([Course(category="web"), Course(category="web"), Course(category="data")])
rows = Course.objects.values("category").annotate(total=Count("pk")).order_by("category")
print([(row["category"], row["total"]) for row in rows])

Expected output

[('data', 1), ('web', 2)]

Grouping is by category, with one count per group. The deterministic literal fixture contains one data row and two web rows.

Evaluate total cost and preserve the contract

After a targeted change, rerun the same correctness cases and representative measurement. Compare fetched rows and retained memory as well as statement count. only or defer can reduce selected fields but later access can trigger additional queries, so inspect the actual rendering path. Streaming iteration can reduce queryset caching without making every downstream consumer bounded. Cache only with explicit freshness, identity, and invalidation rules. Remove an optimization that adds complexity without a demonstrated gain. Keep backend-specific evidence separate from the in-memory SQLite demonstrations, and never describe a clean local query count as proof of production concurrency or universal performance.

Common errors and fixes

A lower query count hides a huge response

Why it happens: Only statement count was measured.
How to fix it: Compare rows, memory, and user latency too.
Prevent it next time: Name the performance metric before optimizing.

An aggregate doubles after another detail join

Why it happens: The join changed the reporting grain.
How to fix it: Pre-aggregate or redesign joins under the intended grain.
Prevent it next time: Test independent multi-row detail collections.

Deferred fields cause unexpected extra reads

Why it happens: The rendering path later accesses omitted values.
How to fix it: Measure the complete consumer path.
Prevent it next time: Treat projections as a contract with consumers.

Check your understanding

1. Why can one query be worse than several smaller queries?

Show worked solution

It may return a multiplied join, transfer many unnecessary columns, or require expensive sorting and aggregation. Compare the actual rows, plan, latency, and memory under the same contract. Statement count is useful evidence but not a complete cost model.

2. What should a regression test protect when optimizing an ownership-scoped report?