skip to content

With Django's ORM, what does QuerySet.explain() return, and why is explain(analyze=True) riskier than a plain explain()?

level: middleimportance: should knowfreq 36%

answer

  1. a string, not rows
  2. keyword flags become EXPLAIN options
  3. one flag really executes the statement
  4. triggers and functions still fire

basics

~20 s

QuerySet.explain() returns the database's execution plan for that QuerySet's SQL as a string; analyze=True makes PostgreSQL, MySQL or MariaDB actually run the statement, which costs real time and can change data through triggers or called functions.

solid answer

~40 s

`explain(format=None, **options)` asks the backend for its plan of the `QuerySet`'s statement and returns it as a string, so you can see sequential scans, index use and join strategy without leaving Python; `aexplain()` is the async twin. `format` selects the output format (PostgreSQL accepts TEXT, JSON, YAML and XML), and keyword flags become `EXPLAIN` options: on PostgreSQL `analyze`, `buffers`, `verbose`, `settings`, `wal` and others, with unknown flags raising `ValueError`. Plain `explain()` only plans. With `analyze=True` the database executes the statement to report real timings and row counts, so it takes as long as the query, and Django's docs warn it can change data if triggers fire or a function is called, even for a `SELECT`. It is supported on every built-in backend except Oracle.

code

python · 9 lines
python
from school.models import Lesson

qs = Lesson.objects.filter(room__code="B12", weekday=3).select_related("teacher")

print(qs.explain())
print(qs.explain(format="json"))

# Executes the statement: run on a copy of production data, not production.
print(qs.explain(analyze=True, buffers=True))

go deeper

for a junior

Recall that explain() returns a plan string for a QuerySet, not rows, and that it is how you check whether an index is used.

for a middle

Explain how format and keyword flags map to the backend's EXPLAIN options, and why analyze=True executes the statement while a plain call only plans.

for a senior

Show the workflow: find the slow statement by count or time first, then explain it on production-sized data, and keep analyze away from anything with side effects.

for a principal

Decide where plan analysis belongs in the team's process, for example plans reviewed on a production-sized copy before indexes or query rewrites ship.

## What `explain()` is Every relational database can tell you **how** it intends to execute a statement: which table it scans, whether it uses an index, which join algorithm it picks, how many rows it expects. That description is the **execution plan**, and SQL exposes it through `EXPLAIN`. Django wraps this in `QuerySet.explain(format=None, **options)`. It compiles the QuerySet's SQL, prefixes it with the backend's `EXPLAIN` keyword, runs that, and **returns the plan as a string**. It does not return model instances and does not fill the QuerySet's result cache. `aexplain()` is the asynchronous version. ```python print(Lesson.objects.filter(room__code="B12", weekday=3).explain()) ``` On PostgreSQL this prints something like `Seq Scan on school_lesson ... Filter: ...` or an index scan line, depending on the indexes you have. ## Backends and formats - Supported by all built-in backends **except Oracle**; calling it there raises `NotSupportedError`. - SQLite uses `EXPLAIN QUERY PLAN`, which gives a much terser plan than PostgreSQL. - `format` changes the output format. PostgreSQL supports `'TEXT'`, `'JSON'`, `'YAML'` and `'XML'`; MariaDB and MySQL support `'TEXT'` (also called `'TRADITIONAL'`) and `'JSON'`, and MySQL 8.0.16+ adds `'TREE'`. An unsupported format raises `ValueError` listing the allowed ones. - The output differs significantly between databases, so a plan read on SQLite in development says little about PostgreSQL in production. ## Options: keyword flags become `EXPLAIN` options Extra keyword arguments are passed to the database as options. On PostgreSQL the backend accepts: | Flag | What it adds | Note | |---|---|---| | `analyze=True` | Executes the statement; real times and row counts | Side effects possible | | `buffers=True` | Buffer and cache usage | Most useful with `analyze` | | `verbose=True` | Output columns, schema-qualified names | | | `costs`, `timing`, `summary` | Toggle parts of the output | | | `settings`, `wal` | Changed planner settings, WAL usage | | | `generic_plan=True` | Plan with placeholders | Django 5.1, PostgreSQL 16+ | | `memory=True`, `serialize=...` | Planner memory, output serialization cost | Django 5.2, PostgreSQL 17+ | Flags the backend does not know raise `ValueError: Unknown options: ...` rather than being silently ignored, which is useful: a typo fails loudly. ## Why `analyze=True` is a different animal Plain `EXPLAIN` only **plans**. The estimates it prints come from table statistics, and they can be badly wrong, which is exactly why people reach for `ANALYZE`: it **executes** the statement and reports what actually happened next to what was expected. That execution is real: - **It costs what the query costs.** Explaining a five-second report with `analyze=True` takes five seconds and holds the same locks and resources. - **It can change data.** Django's documentation warns that the `ANALYZE` flag on MariaDB, MySQL and PostgreSQL "could result in changes to data if there are triggers or if a function is called, even for a `SELECT` query". A QuerySet that annotates with a database function which writes, or a statement that fires a trigger, will do so. - **It is not a dry run for writes.** `explain()` is a QuerySet method, so you normally explain reads; but anything with side effects should be analyzed on a copy of the data, not on production. ## Using it well 1. Find the slow statement first (from `connection.queries` or a capture), then rebuild the same QuerySet in a shell and call `explain()` on it. 2. Start with plain `explain()`; move to `analyze=True, buffers=True` on a production-sized copy when the estimates look suspicious. 3. Compare the plan before and after adding an index or rewriting a filter, keeping the same data volume. 4. Remember what it does **not** show: `prefetch_related()` lookups and per-row lazy loads are separate statements, so one plan never explains an N+1 pattern; that is a count problem, not a plan problem. ## Where the Django part ends Reading the plan itself (what a bitmap heap scan is, why the planner skipped your index) is database knowledge, not Django knowledge. The Django skill is getting from a QuerySet to its plan quickly, choosing the right flags for the backend in use, and knowing which flag executes the query.

  • Why can't QuerySet.explain() tell you that a Django list page has an N+1 problem?
    `explain()` describes one statement: the one this QuerySet compiles to. An N+1 is many cheap statements fired later by lazy attribute access, each with a perfectly good plan. The symptom is the count, so you find it with `connection.queries` or `CaptureQueriesContext`, and use `explain()` afterwards on whichever single statement is actually slow.
  • What happens if you pass an option to explain() that the database backend does not support?
    The backend's `explain_query_prefix()` validates it. PostgreSQL maps the options it knows onto `EXPLAIN (...)` and hands any leftovers to the base implementation, which raises `ValueError` naming the unknown options. An unsupported `format` raises `ValueError` listing the allowed formats. On Oracle any call raises `NotSupportedError`.

saying these in an interview costs you the question

  • explain() returns the QuerySet's rows along with the plan
  • analyze=True only estimates and never runs the statement
  • A read-only SELECT under ANALYZE can never change data
  • One explain() output reveals an N+1 pattern in a view
  • Unknown explain() options are silently ignored by Django