Reading EXPLAIN output for a SQLAlchemy query

Compile the statement with literal_binds, prefix it with EXPLAIN (ANALYZE, BUFFERS), and read the plan bottom-up — the three things that explain most slow ORM queries are a sequential scan where you expected an index, an estimate that disagrees with reality, and a nested loop over far more rows than the planner predicted. This is the diagnosis step after instrumenting and observing async queries has told you which statement to look at.

Quick Answer

Before — explaining a hand-typed approximation of the query:

-- Retyped from memory in psql, so it is not quite the query the ORM emits:
-- the ORM version has an extra join for the eager load and a different ORDER BY.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4821;

After — explaining the statement the ORM actually runs:

from __future__ import annotations

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.sql import Executable


async def explain(session: AsyncSession, stmt: Executable, analyze: bool = True) -> str:
    """Return the PostgreSQL plan for a Core or ORM statement.

    literal_binds inlines the parameter values, so the plan reflects the values
    that are actually slow — a generic plan for a bound parameter can be entirely
    different. Development and diagnosis only: this builds SQL by interpolation.
    """
    compiled = stmt.compile(
        dialect=session.bind.dialect,
        compile_kwargs={"literal_binds": True},
    )
    options = "ANALYZE, BUFFERS, VERBOSE" if analyze else "VERBOSE"
    result = await session.execute(text(f"EXPLAIN ({options}) {compiled}"))
    return "\n".join(row[0] for row in result)

# Usage — the statement object, not a string
stmt = (
    select(Order)
    .where(Order.customer_id == 4821, Order.status == "outstanding")
    .options(selectinload(Order.items))
    .order_by(Order.created_at.desc())
    .limit(20)
)
print(await explain(session, stmt))

What comes back is the plan for the query the ORM emits, including the joins the loader options add — which is frequently not the query anyone would have retyped by hand.

Execution Context & Async Workflow Integration

Two cautions come with ANALYZE, and both are easy to trip over in an async codebase where the helper is a coroutine that looks harmless.

ANALYZE executes the statement. Explaining a DELETE or an UPDATE with analyze=True performs it. The safe pattern is a nested transaction that is always rolled back:

# Async — explain a write statement without performing it
async def explain_write(session: AsyncSession, stmt: Executable) -> str:
    """ANALYZE runs the statement, so run it inside a savepoint and discard it."""
    async with session.begin_nested() as savepoint:
        plan = await explain(session, stmt, analyze=True)
        await savepoint.rollback()
    return plan

literal_binds interpolates values into SQL. That is exactly what makes the plan honest, and exactly what makes the helper unsafe to expose to user input. Keep it out of request handlers; it belongs in a management command, a test, or a REPL.

There is also a subtlety about which statement gets explained when a loader option is involved. selectinload issues a second statement for the collection, and explain() only sees the first. To see both, run the query with the counting listener from the parent guide enabled, capture the statements it records, and explain each — the second statement, with its IN list, is frequently the slower of the two and is invisible to a naive explain of the original select().

# Capture what actually ran, then explain each statement
token = query_stats.set(QueryStats())
try:
    await session.execute(stmt)
    statements = list(query_stats.get().statements)
finally:
    query_stats.reset(token)

for sql in statements:
    rows = await session.execute(text(f"EXPLAIN {sql}"))
    print("\n".join(row[0] for row in rows))

Resolving Warnings, Errors & Common Mistakes

Advanced: The Three Readings That Solve Most Cases

Plans are large, and nearly all of the value comes from three specific comparisons. Read bottom-up — the most indented node executes first — and check these in order.

Compare estimated rows to actual rows on every node. The plan prints rows=N (the estimate) alongside actual rows=M. A node where these differ by more than about an order of magnitude has misled every node above it.

Nested Loop  (cost=0.86..8412.31 rows=12 width=48)
              (actual time=0.041..1893.220 rows=48291 loops=1)
  ->  Index Scan using ix_orders_customer_id on orders
        (cost=0.43..102.11 rows=12 width=16)
        (actual time=0.019..14.882 rows=48291 loops=1)

The planner expected twelve rows and got forty-eight thousand, so it chose a nested loop that then ran forty-eight thousand times. The fix is not the join strategy; it is the estimate. ANALYZE orders is the first thing to try, and for a correlated predicate the answer is often extended statistics: CREATE STATISTICS ON (customer_id, status) FROM orders.

Find the node with the largest self time. Total time on a node includes its children, so the expensive node is the one whose time minus its children's time is largest. That is where to look, and it is frequently not the node at the top.

Check the buffer counts. BUFFERS prints shared hit (found in cache) and shared read (fetched from disk). A node reading a hundred thousand blocks from disk is doing physical I/O, and no query rewriting fixes that — it is a missing index, a table that needs to be smaller, or a machine that needs more memory. A node with a high hit count and slow timing is CPU-bound instead, which points at a function in the predicate or a sort that spilled to disk.

One more pattern worth recognising: Rows Removed by Filter far exceeding the rows returned means the query reads a great deal and discards most of it. That is the signature of an index that gets you to roughly the right place but not precisely — usually fixed by adding the filtered column to the index, or by making it partial over the condition that always holds for the queried set.

Turning a Plan Into a Change You Can Ship

A plan explains what happened; deciding what to do about it is a separate step, and the options are narrower than they first appear.

Add or change an index when the plan shows a sequential scan or a large Rows Removed by Filter on a genuinely selective predicate. Verify by re-explaining afterwards, and remember a new index is maintained on every write — the reasoning in high-performance bulk inserts and updates applies in reverse. Build it with CONCURRENTLY, which needs the migration arrangement in zero-downtime schema migration strategies.

Fix the statistics when estimates are wrong. This is the cheapest fix available and the one most often skipped, because it feels too simple to be the answer. It frequently is.

Rewrite the query when the plan reveals the ORM emitted something unintended — a cartesian product from a missing ON clause, a subquery where a join belongs, or a loader option producing a join that multiplies rows. Those are query bugs wearing a performance costume, and the fixes live in complex joins and relationship loading strategies.

Change the shape of the work when none of the above applies: paginate with a keyset rather than an offset, stream instead of materialising, or precompute an aggregate. These are larger changes and are the right answer when the plan is optimal and the query is still slow, which usually means the query is asking for too much.

What is almost never the answer is a planner hint or a disabled scan type. Those change the plan without changing the reason it was chosen, and they persist long after the underlying cause — usually the statistics — has been fixed.

Frequently Asked Questions

Related

• Instrumenting and Observing Async Queries — The parent guide: which statement to explain, and the instruments that identify it.
• Logging slow async queries with SQLAlchemy events — Capturing the statement text a plan is then produced for.
• Complex joins and relationship loading strategies — When the plan reveals the ORM emitted a join you did not intend.
• Zero-downtime schema migration strategies — Shipping the index a plan says you need, without locking the table.