Interpreting SQLAlchemy-Generated Query Plans #

SQLAlchemy hides the SQL it emits behind a fluent Python API, so the first challenge in tuning an ORM query is simply seeing the statement PostgreSQL actually runs. Once you have the raw SQL and its EXPLAIN output, the plan shape tells you exactly which loader strategy the ORM chose — and whether that choice is quietly multiplying rows or firing an extra round trip you did not expect.

Getting the Raw SQL Out of SQLAlchemy #

Two techniques surface the generated SQL. The first compiles a query object to a string with literal values substituted in:

print(str(query.compile(
    dialect=postgresql.dialect(),
    compile_kwargs={"literal_binds": True}
)))

literal_binds inlines the bound values so the string is runnable as-is. This is a diagnostic convenience only — see the pitfalls section for why it must never touch untrusted input.

The second technique logs every statement the engine executes, including the parameter list, by setting echo=True:

engine = create_engine("postgresql://...", echo=True)

With echo=True you see the parameterized form — WHERE users.id = %(id_1)s — plus the parameter dictionary on the following log line. That parameterized form is what runs in production; the %(id_1)s placeholder is psycopg’s named style and maps to a $1 bind in the PostgreSQL protocol.

Once you have the SQL, wrap it in EXPLAIN (ANALYZE, BUFFERS) and run it through the session:

plan = session.execute(text(
    "EXPLAIN (ANALYZE, BUFFERS) "
    "SELECT ... FROM orders LEFT OUTER JOIN line_items ..."
)).fetchall()
for row in plan:
    print(row[0])

Three Loader Strategies, Three Plan Shapes #

SQLAlchemy’s eager-loading options each produce a distinct plan signature. Recognising the shape is faster than reading the Python, because the plan is what the database charged you for.

The diagram below maps each loader option to the plan it generates.

SQLAlchemy loader strategies mapped to plan shapes Three columns showing joinedload producing one LEFT OUTER JOIN with row multiplication, selectinload producing two queries with a WHERE id IN list, and subqueryload producing a correlated subquery. joinedload one query LEFT OUTER JOIN rows multiply N× selectinload two queries 2nd: WHERE id IN (...) no multiplication subqueryload two queries 2nd: correlated re-runs base as subquery Plan shape reveals the loader — no Python needed anon_1 aliases appear wherever SQLAlchemy wraps a derived table Prefer selectinload for to-many relations

joinedload → a single LEFT OUTER JOIN. The related table is joined into the base query. This keeps everything in one round trip but multiplies parent rows: one order with five line items yields five rows in the raw result. The plan behaves like any hash join or nested-loop join you would write by hand.

selectinload → a second query with WHERE id IN (...). The base query runs first; SQLAlchemy then collects the primary keys and issues a second statement filtering the related table on IN (or = ANY($1) for large lists). No row multiplication, at the cost of one extra round trip.

subqueryload → a correlated subquery. SQLAlchemy re-runs the base query as an inner subquery to drive the related fetch. It is the oldest of the three and usually the least efficient; anon_1 aliases show up densely here.

Annotated joinedload Plan #

Here is a joinedload of Order.line_items under EXPLAIN (ANALYZE, BUFFERS). The anon_1 alias and the outer join are the tells.

Hash Right Join  (cost=1250.00..8900.00 rows=250000 width=96)
                 (actual time=8.2..142.6 rows=250000 loops=1)
  Hash Cond: (line_items.order_id = orders.order_id)   -- ← the joinedload edge
  Buffers: shared hit=4100 read=2600
  ->  Seq Scan on line_items  (cost=0.00..5200.00 rows=250000 width=40)
                              (actual rows=250000 loops=1)
  ->  Hash  (cost=900.00..900.00 rows=50000 width=56)
        ->  Seq Scan on orders  (cost=0.00..900.00 rows=50000 width=56)
                                (actual rows=50000 loops=1)
              Filter: (status = 'SHIPPED')             -- ← 50k parent orders
-- 50,000 parent orders become 250,000 output rows: 5× multiplication
-- width=96: every duplicated parent row re-carries all order columns

Key observations:

When a query eager-loads several to-many relations at once, each join multiplies against the last, and row counts explode combinatorially. The plan-shape tradeoff between one join and several IN queries is the core decision covered in Eager Loading vs Lazy Loading: Plan Impact.

Step-by-Step Resolution Workflow #

Step 1 — Extract the raw SQL #

Compile with literal_binds for a copy-pasteable string, or run with echo=True to capture the parameterized statement and its bind values from the engine log.

Step 2 — Run it under EXPLAIN #

session.execute(text("EXPLAIN (ANALYZE, BUFFERS) " + raw_sql))

Record actual rows on the top node, width, and Buffers. These are your before-state numbers.

Step 3 — Identify the loader from the shape #

A LEFT OUTER JOIN (or Hash Right Join) folding the related table in means joinedload. A second statement with WHERE ... IN (...) or = ANY($1) means selectinload. A correlated subquery wrapping the base query means subqueryload.

Step 4 — Map aliases to relationships #

Trace each anon_1 alias to the join or IN condition it appears in. %(param_1)s in the compiled string and $1 in the protocol are the same bind; match them to the ORM filter that produced them.

Step 5 — Detect multiplication #

Compare the plan’s actual output rows against the number of parent rows you expected. A multiple of the parent count is the joinedload row-multiplication signature.

Step 6 — Switch loader and re-validate #

# from:
session.query(Order).options(joinedload(Order.line_items))
# to:
session.query(Order).options(selectinload(Order.line_items))

Re-run EXPLAIN (ANALYZE, BUFFERS). You should now see two clean plans, neither of which multiplies rows.

Before/After Plan Comparison #

-- BEFORE: joinedload — one query, 5× row multiplication
Hash Right Join  (actual rows=250000)  Hash Cond: (line_items.order_id = orders.order_id)
  -> Seq Scan on orders  (actual rows=50000)  Filter: (status = 'SHIPPED')

-- AFTER: selectinload — two queries, no multiplication
-- query 1:
Seq Scan on orders  (actual rows=50000)  Filter: (status = 'SHIPPED')
-- query 2:
Index Scan using idx_line_items_order_id on line_items  (actual rows=250000)
  Index Cond: (order_id = ANY ($1))     -- $1 = array of 50k parent keys

The join’s 250k-row intermediate result disappears; the second query fetches the same line items keyed by an index on order_id, and each parent row is materialised exactly once.

Common Pitfalls #

literal_binds on untrusted input is a SQL injection risk. Inlining values into the SQL string is fine for reading a plan on trusted data, but if any bound value originates from a user it becomes an injection vector. Keep literal_binds strictly for diagnostics; run production queries with bound parameters, which PostgreSQL also caches and re-plans more efficiently.

The default lazy='select' silently causes N+1. A relationship() without an explicit loader defaults to lazy='select', which fires a separate query the first time each parent’s attribute is touched in a loop. The plan for the base query looks clean, but pg_stat_statements shows a high calls count on the child SELECT. Detecting that pattern is the subject of Detecting N+1 Queries in the Django ORM; the SQLAlchemy signature is identical.

contains_eager without a matching .join() breaks the plan. contains_eager tells SQLAlchemy to populate a relationship from columns already present in the query, but it does not add the join for you. Forget the explicit .join(Order.line_items) and you get either an error or a plan that silently re-fetches the relation, defeating the point.

selectinload still costs a second round trip. It removes row multiplication but does not make the query free — it issues an additional statement. On a latency-bound connection, two round trips can be slower than one multiplied join for small child sets. Read the actual timings, not just the row counts, before deciding.

Frequently Asked Questions #

What do the anon_1 aliases in a SQLAlchemy plan mean? anon_1, anon_2, and similar names are anonymous aliases SQLAlchemy assigns to subqueries and derived tables when it wraps a query — most often for selectinload’s inner SELECT or a subqueryload correlated block. They map back to the eager-load construct, not to a table you named. Match the alias to the relationship by reading the join or IN condition it participates in.

Why does joinedload return more rows than my query should? joinedload emits a single LEFT OUTER JOIN to the related table, so a parent with N children appears N times in the raw result set. SQLAlchemy de-duplicates parents in Python, but PostgreSQL still materializes every duplicated parent row, inflating width and buffers. When a query joins several to-many relations at once, use selectinload instead to avoid the multiplication.

Is literal_binds safe to use in production? No. compile_kwargs={"literal_binds": True} inlines parameter values directly into the SQL string, which is convenient for reading a plan but bypasses parameterization and exposes you to SQL injection if any value is user-supplied. Use it only for diagnostics on trusted input; run production queries with bound parameters.

Related