Django ORM Performance skill for Codex

Done well means: the slow path has a measured before and after (query count, total SQL time, memory), each fix is the smallest ORM change that addresses the measured cause, and a test pins the query count so the regression cannot return unnoticed.

Workflow

1. Read AGENTS.md for the settings module, test runner (pytest with pytest-django or manage.py test) and database (Postgres, MySQL, SQLite behave differently).
2. Reproduce with realistic volume: at least a few hundred parent rows and children.
3. Measure (table below). Record query count, the top 3 slowest statements, and time.
4. Classify the cause: too many queries, one slow query, too much data per query, or too much Python work per row.
5. Apply the matching fix from the sections below. Change one thing at a time.
6. Re-measure, then add an assertNumQueries test.

Measuring

Need	Tool
Queries per request in dev	django-debug-toolbar SQL panel
Queries in a test	with self.assertNumQueries(4): or pytest-django django_assert_num_queries(4)
Ad hoc in a shell	from django.db import connection, reset_queries with DEBUG=True; len(connection.queries)
Capture without DEBUG	with connection.execute_wrapper(fn): to log or count every statement
Plan of one queryset	print(qs.explain(analyze=True, buffers=True)) on Postgres; qs.query shows SQL
Production	pg_stat_statements or APM traces grouped by view

explain(analyze=True) executes the query. Do not run it on a queryset that ends in an update or delete, and run it against a replica or local copy when the query is heavy.

Too many queries

• Forward FK and one-to-one used in output: select_related("customer", "customer__plan").
• Reverse FK and many-to-many: prefetch_related(...).
• Filtered or ordered children: Prefetch("items", queryset=Item.objects.filter(active=True).only("id", "sku", "order_id"), to_attr="active_items"). Keep the FK column in only() or Django re-fetches it per row.
• Calling .filter(), .order_by() or .count() on a prefetched relation issues a new query per row. Use to_attr lists or len().
• DRF: put the select_related/prefetch_related in the viewset’s get_queryset, matched to every nested serializer field. Full N+1 playbook: $n-plus-one-queries.

Push work into SQL

from django.db.models import Count, Sum, Q, F, OuterRef, Subquery, Exists, Value
from django.db.models.functions import Coalesce

latest_payment = Payment.objects.filter(order=OuterRef("pk")).order_by("-created_at")

orders = (
    Order.objects.filter(tenant=tenant)
    .annotate(
        item_count=Count("items", distinct=True),
        paid=Exists(Payment.objects.filter(order=OuterRef("pk"), status="settled")),
        last_paid_at=Subquery(latest_payment.values("created_at")[:1]),
    )
    .only("id", "number", "created_at")
)

• Two Count/Sum annotations across different multi-valued relations multiply rows (join fan-out) and inflate sums. Use Subquery with a grouped inner query, or distinct=True for counts only (it does not fix Sum).
• Sum over no rows returns None: wrap in Coalesce(Sum("amount"), Value(0)) with an explicit output_field when types differ.
• Updates without loading: Product.objects.filter(pk=pk).update(stock=F("stock") - 1). This also removes the read-modify-write race.
• Existence: qs.exists(); a single row: qs.first(); a count shown to users: qs.count(). len(qs) and if qs: load every row.

Fetch less data

Situation	Use
Need a few fields of model instances	.only("id", "name") or .defer("body", "payload")
Read-only dicts or tuples for an API or export	.values(...) / .values_list(..., flat=True)
Lookup by many ids	Model.objects.in_bulk(ids)
Iterating 100k+ rows in a task	.iterator(chunk_size=2000) (server-side cursor on Postgres)
Large text or JSON column not shown in a list	.defer() it

Accessing a deferred field later costs one query per instance; check templates and serializers before deferring.

Bulk writes

Item.objects.bulk_create(objs, batch_size=1000)
Item.objects.bulk_create(
    objs, batch_size=1000,
    update_conflicts=True, unique_fields=["tenant", "sku"], update_fields=["price", "updated_at"],
)
Item.objects.bulk_update(objs, ["price"], batch_size=500)

bulk_create and update() skip save(), model signals and auto_now; set timestamps explicitly and confirm no signal handler is required. Wrap multi-step writes in transaction.atomic().

Indexes from models

class Order(models.Model):
    class Meta:
        indexes = [
            models.Index(fields=["tenant", "-created_at"], name="order_tenant_created_idx"),
            models.Index(fields=["created_at"], name="order_pending_idx",
                         condition=Q(status="pending")),
        ]
        constraints = [
            models.UniqueConstraint(fields=["tenant", "number"], name="order_tenant_number_uq"),
        ]

• ForeignKey creates an index by default; composite indexes that start with the FK can make that single-column index redundant.
• Text search or JSON containment on Postgres: django.contrib.postgres.indexes.GinIndex (with opclasses=["gin_trgm_ops"] for icontains after enabling pg_trgm).
• Large Postgres tables: generate the migration, then replace AddIndex with AddIndexConcurrently from django.contrib.postgres.operations and set atomic = False on the migration class. Use sqlmigrate app 00xx to review the SQL.
• Default Meta.ordering adds ORDER BY to every query, including ones that do not need it. Remove it or call .order_by() to clear it on hot aggregate queries.

Pagination and admin

• Paginator runs COUNT(*) on every page. For large tables use keyset pagination (filter(id__lt=last_id).order_by("-id")[:50]) or DRF CursorPagination.
• Admin changelist: set list_select_related, show_full_result_count = False, use autocomplete_fields or raw_id_fields for large FK choices, and avoid callables in list_display that query per row (annotate in get_queryset instead).

Connections

• Django 5.1+ with psycopg 3 supports pooling: "OPTIONS": {"pool": True} (or a dict of pool options). Do not combine it with CONN_MAX_AGE.
• Otherwise use CONN_MAX_AGE (for example 60) with CONN_HEALTH_CHECKS = True.
• Behind PgBouncer in transaction mode, set DISABLE_SERVER_SIDE_CURSORS = True (.iterator() otherwise breaks).

Gotchas

• A queryset is lazy and cached after evaluation; re-filtering it creates a new query. Evaluate once into a list if you reuse the rows.
• prefetch_related with .iterator() needs chunk_size (Django 4.1+); without it the prefetch is skipped or errors depending on version.
• select_related() with no arguments follows every non-null FK; name relations explicitly.
• distinct() combined with order_by on a related field returns duplicates or errors on Postgres; check the generated SQL.
• get_or_create under concurrency needs a unique constraint to be correct.

Verification

• Query count and total SQL time for the scenario, before and after, at two data sizes (for example 20 and 200 parents): the count must not grow.
• EXPLAIN ANALYZE for the slowest remaining statement shows index use where expected.
• Response payload unchanged (snapshot or contract test).
• New test with assertNumQueries(n) passes and fails if the fix is reverted.

Output

**Path:** <view/endpoint/task>
**Before:** <N queries, X ms SQL, Y ms total> at <data size>
**Cause:** <N+1 on serializer field / fan-out sum / missing index / full-row fetch>
**Changes:** <queryset changes, indexes + migration name>
**After:** <N queries, X ms SQL, Y ms total>
**Guard:** <test name with assertNumQueries>