Reading EXPLAIN for ORM-Generated SQL #

You cannot tune a statement you have never seen. When a query is generated by an ORM, the SQL PostgreSQL executes is often structurally different from what the application code implies — extra joins from prefetch, a subselect wrapped around an annotation, an ORDER BY id nobody asked for. This page is the bridge: how to capture the actual generated SQL from Django, Rails, and SQLAlchemy, run EXPLAIN (ANALYZE, BUFFERS) on it, and map every machine alias and surprise subquery in the plan back to the ORM construct that produced it.

Capturing the Generated SQL, Per Framework #

Each ORM exposes at least one hook to see what it emits, and often a direct EXPLAIN shortcut.

Django. The queryset speaks EXPLAIN natively:

qs = Order.objects.filter(status="shipped").select_related("customer")
print(qs.explain(analyze=True, buffers=True))   # runs EXPLAIN (ANALYZE, BUFFERS) for you
print(str(qs.query))                             # the raw SQL, to run EXPLAIN manually

With DEBUG=True, django.db.connection.queries logs every statement and its wall time; the Django Debug Toolbar surfaces the same list per request and flags duplicates.

Rails / ActiveRecord. The relation exposes both the SQL and a plan:

rel = Order.where(status: "shipped").includes(:customer)
puts rel.to_sql          # the generated SQL text
puts rel.explain         # EXPLAIN output
# On Rails 7+, request EXPLAIN ANALYZE explicitly:
puts rel.explain(:analyze)

The auto_explain extension logs plans automatically for any statement exceeding auto_explain.log_min_duration, which catches ORM statements you did not think to inspect.

SQLAlchemy. Turn on echo=True to log emitted SQL, or compile a statement to raw text:

engine = create_engine(url, echo=True)   # logs every statement

stmt = select(Order).where(Order.status == "shipped").options(joinedload(Order.customer))
raw = str(stmt.compile(engine, compile_kwargs={"literal_binds": True}))
print(raw)                               # SQL with parameters inlined

# Or run EXPLAIN through the connection directly:
from sqlalchemy import text
plan = conn.execute(text("EXPLAIN (ANALYZE, BUFFERS) " + raw)).fetchall()

In all three, the goal is the same: a concrete statement you can hand to the planner.

The Capture-to-Interpret Pipeline #

The overall flow from an ORM call to a readable plan has four transformations, each of which can introduce a surprise.

ORM call to generated SQL to EXPLAIN plan pipeline Four stages left to right: the ORM call, the generated SQL carrying machine aliases and subquery wrapping, the EXPLAIN plan with scan nodes labeled by those aliases, and a mapping legend connecting aliases back to models. ORM call Order.objects .select_related ("customer") generated SQL SELECT ... FROM orders JOIN customers T3 ON ... + ORDER BY orders.id (alias T3, added sort) EXPLAIN plan Hash Join -> Seq Scan orders -> Hash -> Seq Scan T3 alias mapping T3 → customers (from select_related("customer")) anon_1 → subselect wrapping an .annotate() aggregate ORDER BY orders.id → model default ordering (Meta.ordering)

The plan on the right labels its scan node T3, not customers. Reading the plan means keeping the generated SQL’s FROM/JOIN clauses beside it to resolve those aliases.

Mapping Generated SQL Back to ORM Constructs #

Four recurring artifacts account for most of the “where did that come from” moments:

Machine aliases (T3, U0, anon_1). Django numbers each joined table T2, T3, T4 in the order it adds them to the query; a subquery pushdown gets a U0 alias. SQLAlchemy names anonymous subqueries anon_1, anon_2. These aliases appear verbatim on the plan’s scan nodes. To decode one, find it in the generated SQL’s JOIN clause — JOIN customers T3 tells you T3 is the customer relationship you added with select_related.

Joins from prefetch and eager loading. A select_related/joinedload becomes a JOIN in the generated SQL and a join node in the plan. A prefetch_related/selectinload becomes a separate second statement with a WHERE fk = ANY('{...}') condition — you will only see it if you inspect the second query, which is why capturing all of connection.queries matters, not just the first. The trade-offs between these strategies are the subject of N+1 query detection.

Subquery wrapping from .annotate(). An aggregate annotation wraps the base query in an outer SELECT. In the plan this is a Subquery Scan or a GroupAggregate node above the original scan. The wrapping matters because a predicate in the outer query may no longer reach the index available on the inner one.

Parameter placeholders. Generated SQL uses $1, %s, or :param placeholders. EXPLAIN needs concrete values, and the value you choose changes the plan — see the workflow below.

Here is a representative annotated plan for a prefetch’s batched second statement, the shape you get from prefetch_related("line_items"):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_id, sku, quantity
FROM   line_items
WHERE  order_id = ANY ('{91820,91821,91822,91823}'::bigint[]);
Index Scan using line_items_order_id_idx on line_items
        (cost=0.43..48.90 rows=72 width=40)
        (actual time=0.024..0.140 rows=71 loops=1)
  Index Cond: (order_id = ANY ('{91820,91821,91822,91823}'::bigint[]))  -- ← batched prefetch, NOT N+1
  Buffers: shared hit=28
Planning Time: 0.088 ms
Execution Time: 0.191 ms

Annotations:

When a genuinely expensive single generated statement turns up — a wide multi-join from a long select_related chain — interpret its hot node with the plan bottleneck workflow and check the cost vs actual time reading exactly as you would for hand-written SQL.

Numbered Workflow #

Step 1 — Get the parameterized SQL.

# Django
print(str(Order.objects.filter(status="shipped").query))
# Rails
puts Order.where(status: "shipped").to_sql
# SQLAlchemy
print(str(stmt.compile(compile_kwargs={"literal_binds": True})))

Step 2 — Substitute representative parameters.

Replace $1/%s/:param with values that match production selectivity. A status value in the column’s most-common-values list yields a different estimate than a rare one, so pick a realistic literal — not the first value that comes to hand.

Step 3 — Run EXPLAIN (ANALYZE, BUFFERS).

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...             -- the substituted generated SQL
FROM orders
WHERE status = 'shipped';

Step 4 — Map aliases and subqueries back.

For every unfamiliar alias (T3, anon_1) in the plan, find it in the generated SQL’s FROM/JOIN/subselect and record which model or ORM construct it represents. Note any Subquery Scan and trace it to the .annotate(), DISTINCT, or pagination that created it.

Step 5 — Fix and re-run.

Adjust the ORM call — drop a needless DISTINCT, replace a to-many JOIN with a prefetch, remove a default ORDER BY via .order_by() — then re-capture the SQL and re-run EXPLAIN to confirm the plan changed as intended.

Common Pitfalls #

1. ORM-added ORDER BY id forcing a sort. Diagnostic: the plan has a Sort node you did not ask for. Fix: a model’s default ordering (Django Meta.ordering, a Rails default_scope) injects ORDER BY into every query. Override with .order_by() (empty) when the order is not needed, or add an index that satisfies it.

2. DISTINCT from a many-to-many join. Diagnostic: the generated SQL has SELECT DISTINCT and the plan shows a costly Unique or HashAggregate above the join. Fix: joining a to-many relationship duplicates parent rows, so the ORM adds DISTINCT to de-duplicate. Use a prefetch_related/selectinload batched load instead of a JOIN to avoid the duplication entirely.

3. Subselect wrapping defeating an index. Diagnostic: an index that helps the inner query is unused once .annotate() wraps it. Fix: the outer subselect’s predicate no longer maps to the inner index. Restructure so the selective predicate sits on the inner query, or add an index that matches the wrapped shape.

4. Parameter type casts. Diagnostic: EXPLAIN VERBOSE shows a cast like (order_id)::bigint = $1 and the index is not used. Fix: the ORM bound a parameter with a type that does not match the column, forcing a runtime cast that can disable index use. Align the model field type with the column type.

5. EXPLAINing with an unrepresentative parameter. Diagnostic: the plan differs from production for the same statement. Fix: you substituted a value with atypical selectivity. Use a parameter drawn from the real data distribution, ideally one present in the column’s most_common_vals.

6. Inspecting only the first statement. Diagnostic: a prefetch’s second IN query never appears in your analysis. Fix: prefetch_related/selectinload emit additional statements. Capture the full connection.queries list or enable echo, not just the primary query’s SQL.

Frequently Asked Questions #

How do I get the exact SQL an ORM will run so I can EXPLAIN it? #

Each framework exposes the raw statement. Django offers QuerySet.explain(analyze=True, buffers=True) to EXPLAIN directly, plus connection.queries to log emitted SQL. Rails offers relation.explain and relation.to_sql. SQLAlchemy offers echo=True and str(query.compile(compile_kwargs={"literal_binds": True})) to render the SQL with parameters inlined. Take that raw SQL and prefix it with EXPLAIN (ANALYZE, BUFFERS).

What do aliases like T3 and anon_1 mean in the plan? #

They are machine-generated table and subquery aliases. Django names joined tables T3, T4 and so on in the order it adds them; SQLAlchemy names anonymous subqueries anon_1. In an EXPLAIN plan these appear as the relation alias on scan nodes, so mapping T3 back to the model requires reading the generated SQL’s FROM and JOIN clauses alongside the plan.

Why does my ORM query show a subquery in the plan I did not write? #

Aggregate annotations, DISTINCT on many-to-many joins, and pagination with window functions all cause the ORM to wrap your query in an outer subselect. In the plan this appears as a Subquery Scan or a nested aggregate node. The wrapping can defeat an index the inner query would otherwise use, so it is worth recognizing which ORM construct produced it.

Should I EXPLAIN with the real parameters or with placeholders? #

Use realistic parameter values, not just any value. The planner’s row estimates depend on the specific literal, because a value in the most-common-values list produces a very different selectivity estimate than a rare value. Substitute a representative parameter that matches production data distribution before running EXPLAIN ANALYZE.

Up: ORM Translation Pitfalls