Django QuerySet explain() Options #

A Django view is slow. Copying its SQL into psql and running EXPLAIN ANALYZE shows a fast plan, so the query “is fine” and the investigation stalls. The SQL copied by hand had literal values; the application sends parameters, uses a different role and a connection with different settings. QuerySet.explain() removes that gap by asking the database through the same path the application uses.

Capturing plans from ORMs in general is covered in ORM-generated EXPLAIN output. This page is the Django specifics.

The Condition #

QuerySet.explain() executes EXPLAIN for the queryset and returns the output as a string. It accepts the backend’s options as keyword arguments:

print(Order.objects.filter(account_id=42).explain(analyze=True, buffers=True, settings=True, verbose=True))

Options available on the PostgreSQL backend mirror the server’s: analyze, verbose, costs, settings, buffers, wal, timing, summary, generic_plan (PostgreSQL 16+) and format. Django passes them through, so support follows the server version.

Two caveats shape how the result should be read:

explain() also works on .union(), aggregates and update querysets, and honours .only(), .defer(), select_related and prefetch_related — though prefetch_related issues separate queries, and explain() reports only the main one.

From queryset to plan Four stages. The queryset is compiled to SQL with parameters. The explain call prefixes it with EXPLAIN and the requested options. The driver sends it on the application's own connection with that role's settings. The server returns the plan for the bound values. queryset filters, joins, only() explain(options) EXPLAIN (…) prefix driver + connection app role and settings plan returned for these bound values prefetch_related's extra queries are not included

Annotated Evidence #

qs = (Order.objects
      .filter(account_id=42, placed_at__gte=timezone.now() - timedelta(days=7))
      .select_related('customer')
      .only('id', 'total', 'customer__email')
      .order_by('-placed_at'))

print(qs.explain(analyze=True, buffers=True, settings=True))
Sort  (cost=18204.42..18294.10 rows=35872 width=48) (actual time=142.6..146.2 rows=36104 loops=1)
  Sort Key: orders.placed_at DESC
  Sort Method: quicksort  Memory: 4210kB
  Buffers: shared hit=18402 read=2104
  ->  Hash Join  (actual time=8.1..118.4 rows=36104 loops=1)
        Hash Cond: (orders.customer_id = customers.id)
        ->  Index Scan using orders_account_placed_idx on orders  (actual rows=36104 loops=1)
              Index Cond: ((account_id = 42) AND (placed_at >= '2026-09-10 …'::timestamptz))
        ->  Hash  (actual rows=41204 loops=1)
              ->  Seq Scan on customers  (actual rows=41204 loops=1)
Settings: effective_cache_size = '48GB', work_mem = '32MB'
Planning Time: 0.9 ms
Execution Time: 148.1 ms

Reading it back to the ORM:

For machine processing:

plan = qs.explain(analyze=True, buffers=True, format='JSON')   # str containing JSON
Mapping plan nodes to queryset calls Three items are annotated. A Sort node corresponds to order_by without a matching index. A Hash Join with a full customers scan corresponds to select_related over a broad filter. The Settings line shows the application role's planner parameters, which a shell session may not share. Sort Sort Key: orders.placed_at DESC .order_by() with no index Hash Join … Seq Scan on customers .select_related('customer') Settings: work_mem = '32MB' the app role's parameters every node traces back to something the queryset asked for

Step-by-Step Resolution #

  1. Capture through the ORM, not the shell. Add a temporary management command or a debug view that prints explain() for the queryset in question.

  2. Use the full option set — analyze=True, buffers=True, settings=True — so estimates, I/O and configuration are all visible.

  3. Wrap write querysets in a transaction:

    with transaction.atomic():
    print(Order.objects.filter(status='draft').explain(analyze=True))
    transaction.set_rollback(True)

  4. Compare with generic_plan=True when the deployment uses server-side prepared statements or a pooler that reuses them, to see the plan a cached statement would get; the difference is explained in EXPLAIN GENERIC_PLAN for parameterized SQL.

  5. Check prefetch_related separately. Its extra queries do not appear; capture them with connection.queries in debug mode or auto_explain.

  6. Map each plan node back to a queryset method and change the queryset — or add the index — rather than rewriting SQL by hand.

  7. Keep the capture in the codebase as a management command, so the next investigation starts from the same ground truth.

Why the shell disagreed The hand-copied query in psql took 4 milliseconds because it used literals, the psql role's settings and a warm cache. The same queryset captured with explain in the application took 148 milliseconds. After adding the composite index, the application capture takes 6 milliseconds. psql, literals, warm 4 ms app capture (explain) 148 ms app capture, indexed 6 ms only the application capture reflects the real role and parameters

Before and After #

-- BEFORE: sorting 36k rows after a hash join
Sort  Sort Method: quicksort  Memory: 4210kB → Hash Join → Index Scan       Execution Time: 148.1 ms

-- AFTER: index on (account_id, placed_at DESC)
Index Scan Backward using orders_account_placed_desc_idx → Nested Loop      Execution Time: 6.2 ms

What explain() cannot tell you #

explain() reports one statement. A Django view issuing twelve queries needs twelve captures, and the queries generated by prefetch_related, by lazy attribute access, and by the ORM’s own transaction management are not among them. For a whole-request picture, connection.queries in debug mode lists every statement with its duration, and auto_explain on the server captures plans for all of them without touching application code — the approach in capturing ORM plans with auto_explain.

It also cannot show what the query would cost at production scale when run against a development database. A plan captured on 5,000 rows is a plan for 5,000 rows, and the access paths it chooses often differ from production’s, for the reasons in why small tables get sequential scans. Capture against a production-sized copy, or accept that the output shows correctness of shape rather than performance.

A reusable capture command #

Making the capture repeatable is worth the ten lines it takes. A management command that accepts a queryset-building function by name, runs explain() with a standard option set, and prints both text and JSON gives everyone the same starting point and removes the temptation to paste SQL into a shell:

class Command(BaseCommand):
    def handle(self, *args, **options):
        qs = build_queryset(options['name'])
        with transaction.atomic():
            self.stdout.write(qs.explain(analyze=True, buffers=True, settings=True))
            transaction.set_rollback(True)

Storing the resulting plans next to the code — in the pull request that changes a query, or in an incident write-up — also makes later comparisons possible. Plans are the only artefact that shows why a query was fast, and they are cheap to keep.

Common Pitfalls #

Copying SQL into psql. Literals, role and settings all differ. Diagnostic signal: fast in the shell, slow in the app. Fix: explain() through the ORM.

Running analyze=True on a write queryset. The write happens. Diagnostic signal: data changed during investigation. Fix: wrap in a rolled-back transaction.

Explaining on development data. Plans differ at small scale. Diagnostic signal: sequential scans everywhere locally. Fix: capture against production-sized data.

Forgetting prefetch queries. They are separate statements. Diagnostic signal: a fast main query and a slow endpoint. Fix: inspect connection.queries or use auto_explain.

Frequently Asked Questions #

How do I see the execution plan of a Django queryset? #

Call .explain() on the queryset, passing the options you need — for example explain(analyze=True, buffers=True, settings=True). Django prefixes the compiled SQL with EXPLAIN and returns the server’s output.

Does Django’s explain() run the query? #

Only with analyze=True, which executes it exactly as EXPLAIN ANALYZE does — including any writes. Use a transaction you roll back when explaining update or delete querysets.

Why is the plan from psql different from the one Django reports? #

The shell usually sends literals instead of parameters, runs as a different role with different planner settings, and may hit a warmer cache. Capturing through the ORM removes those differences.

Up: ORM-Generated EXPLAIN Output