query-explain-buddy

See the SQL your ORM actually runs — and the plan the database actually chose.

query-explain-buddy takes a SQLAlchemy 2.0 Core or ORM statement, shows you the exact compiled SQL for your target driver, runs EXPLAIN ANALYZE, and annotates the query plan with plain-English, actionable notes: a missing index causing a sequential scan, an expensive nested loop that looks like a hidden N+1, a sort spilling to disk, or an estimate that is wildly off the actual row count. It closes the gap between the Python you write and the plan Postgres runs.

It works against PostgreSQL for full EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) plan analysis, and falls back to SQLite EXPLAIN QUERY PLAN so you can try it — and run its whole test suite — with no external database.

Not published on PyPI. Install from GitHub (see Install).

Install

pip install "git+https://github.com/async-workflows/query-explain-buddy.git"

Optional extras:

# Prettier, colourised terminal output via rich
pip install "query-explain-buddy[pretty] @ git+https://github.com/async-workflows/query-explain-buddy.git"

You also need a driver for your database — for example psycopg or asyncpg for PostgreSQL. SQLite support is built into Python.

Quickstart

Compile a statement to real SQL

from sqlalchemy import select
from query_explain_buddy import compile_sql
from myapp.models import User

stmt = select(User).where(User.id == 5)

print(compile_sql(stmt, dialect="postgresql+asyncpg"))
# SELECT users.id, users.name, users.email
# FROM users
# WHERE users.id = 5

By default bound parameters are inlined (literal_binds=True) so the output is readable and directly runnable. Pass literal_binds=False to keep placeholders.

Explain and annotate a plan

from sqlalchemy import create_engine, select
from query_explain_buddy import explain
from myapp.models import Order

engine = create_engine("postgresql+psycopg://localhost/shop")
stmt = select(Order).where(Order.status == "open")

result = explain(stmt, engine)
print(result.render())

Which prints the plan tree plus a suggestions list, for example:

Suggestions:
  [!]  Sequential scan over "orders" reads 42,000 rows (total cost 5,200).
       If this feeds a selective filter or join, consider an index on the
       filter/join column(s).
      docs: https://www.postgresql.org/docs/current/indexes-intro.html
  [!!] Index Scan on "orders" estimated 10 rows but actually returned 500
       (off by 50x). The planner is working from stale statistics; run ANALYZE
       on the table.
      docs: https://www.postgresql.org/docs/current/sql-analyze.html

Library API

Function	Purpose
compile_sql(stmt, dialect=..., literal_binds=True)	Compile a SQLAlchemy statement to a SQL string for postgresql+asyncpg, postgresql+psycopg, or sqlite.
explain(stmt_or_sql, engine)	Run EXPLAIN against a sync engine and return an annotated ExplainResult.
explain_async(stmt_or_sql, engine)	The same, for an AsyncEngine.
analyze_plan(plan_json)	Pure analyzer: turn a parsed PostgreSQL EXPLAIN (FORMAT JSON) plan into a list of Annotations — no live database needed.
RULES	The list of built-in Rule objects powering the analysis.

ExplainResult exposes .sql, .backend, .annotations, .plan_json (PostgreSQL) or .plan_rows (SQLite), .critical, and a .render() method.

Each Annotation carries a rule_id, severity (info / warning / critical), the node_type, the table it applies to, a message with the numbers that triggered it, and a doc_link.

CLI usage

The package installs a query-explain-buddy console script.

# Fully self-contained demo — builds a temporary SQLite database,
# compiles sample statements, runs EXPLAIN QUERY PLAN, prints annotations.
query-explain-buddy demo

# Compile the `statement` variable from a Python file to SQL.
query-explain-buddy compile --stmt-file query.py --dialect postgresql+asyncpg

The --stmt-file module simply needs to expose a module-level statement:

# query.py
from sqlalchemy import select
from myapp.models import User

statement = select(User).where(User.email == "a@example.com")

Annotation rules

The rule engine walks every node of the plan tree. Each rule is a small, independent matcher, so the set is easy to read and extend.

PostgreSQL (from EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON))

Rule id	Flags when	Why it matters
seq_scan	A Seq Scan reads many rows or has high total cost.	A sequential scan feeding a selective filter or join usually wants a supporting index.
nested_loop_large_outer	A Nested Loop drives a large number of outer rows.	This is the shape a hidden N+1 produces; the inner side is probed once per outer row and should be indexed.
sort_disk	A Sort uses Sort Method: external merge Disk.	The sort exceeded work_mem and spilled to disk; raise work_mem or add an index that returns rows pre-sorted.
rows_removed_by_filter	A node discards far more rows via filter than it keeps.	The filter is not index-backed and the database is reading rows only to throw them away.
estimate_mismatch	Estimated vs actual rows differ by more than 10x.	The planner is working from stale statistics — a signal to run ANALYZE.
hash_join_big	A Hash Join produces a very large result.	Usually fine, but the hash table must fit in work_mem to avoid spilling to disk.

SQLite (from EXPLAIN QUERY PLAN)

Rule id	Flags when	Why it matters
sqlite_full_scan	A SCAN step has no usable index.	SQLite is reading every row; an index on the WHERE/JOIN columns turns this into a SEARCH.
sqlite_index_search	A SEARCH ... USING INDEX step.	Informational — confirms the step is index-backed.

PostgreSQL vs SQLite behaviour

• PostgreSQL gives the full picture. explain() runs EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), which executes the statement and reports real timings, real row counts, buffer usage, and estimate-vs-actual gaps. All six PostgreSQL rules apply. Because ANALYZE runs the query, use it against a safe database (or wrap writes in a rolled-back transaction).
• SQLite is the zero-setup fallback. EXPLAIN QUERY PLAN does not execute the query and returns a compact SCAN/SEARCH breakdown, so only the two lighter SQLite rules apply. This is what powers the demo command and the test suite, and it is genuinely useful for catching missing indexes locally.

Limitations

• EXPLAIN ANALYZE executes the statement on PostgreSQL. Do not point it at a statement with side effects you cannot afford, or run it inside a transaction you roll back.
• The rule thresholds (row counts, cost, mismatch factor) are heuristics tuned for typical OLTP workloads; they live in query_explain_buddy.rules and can be adjusted.
• literal_binds=True inlines parameters for readability. It is intended for inspection, not for building SQL you then execute from untrusted input.
• SQLite analysis is deliberately lighter than PostgreSQL: EXPLAIN QUERY PLAN simply exposes less than a PostgreSQL JSON plan.

Further reading

If the annotations point at an ORM-shaped problem, these deeper guides help you fix the query, not just the plan: reworking advanced query patterns and bulk data operations, choosing between selectinload and joinedload to prevent N+1 queries, streaming millions of rows with yield_per in async, and pushing aggregation into the database with window functions and analytical queries.

License

MIT © 2026 async-workflows