Live EXPLAIN plan analysis
JauntyQ's static performance diagnostics (JNT8001–JNT8011) reason about queries
against the committed snapshot, entirely offline. They cannot see what the
database's actual planner will do: row-count estimates, chosen access paths,
sequential scans on large tables, or expensive sorts. The database already knows
all of this via EXPLAIN.
jauntyq explain closes that gap: point it at a representative database and
it runs each .sql query in your corpus through the dialect's estimate-only
EXPLAIN, parses the plan, and reports plan-level problems by severity, attributed to the query file that produced them.
export DEV_DB='Host=localhost;Database=shop;Username=dev;Password=...'
jauntyq explain --provider postgres --connection-env DEV_DB --queries db/tables
jauntyq explain, postgres (estimated plans; costs are planner estimates from the connected database at run time)
Orders/GetByCustomer.sql: Seq Scan on orders [est. cost 23345]
[high] sequential scan on 'orders' (est. 1,200,000 rows), consider an index covering the filtered column(s)
Articles/GetBySlug.sql: Index Scan on articles [est. cost 8.3]
1 analyzed, 1 cached, 0 skipped, 0 not analyzed (cap 10); 1 high / 0 informational problem(s).
This is a premium feature (entitlement live-explain); without an entitling
license the verb exits 3 and does nothing.
The contract: opt-in, estimates only, never breaks a build
- Never in the generator. The Roslyn source generator stays fully offline;
it never opens a database connection and a normal
dotnet buildis completely unaffected by this feature.jauntyq explainis a separate, on-demand CLI tool. - Estimate-only. Every dialect uses the plan-without-executing variant:
Postgres
EXPLAIN (FORMAT JSON)(neverANALYZE), SQLiteEXPLAIN QUERY PLAN, MySQLEXPLAIN FORMAT=JSON, SQL Server estimatedSET SHOWPLAN_XML ON. No query runs; no data is read or mutated. Write statements (INSERT/UPDATE/DELETE/DDL, including data-modifying CTEs) are skipped entirely, safety over coverage. - Never breaks. Report-only by default (exit
0). An unreachable database, auth failure, or unexplainable query is a per-query skip with a note, never an error. With no connection string configured the run is a clean no-op, which is what makes the optional CI wiring below safe. - Capped. At most
--max(default10) live EXPLAIN calls per run; any remainder is listed explicitly asnot analyzed (cap reached), no silent truncation. - Cached. Results are memoized in
.jaunty/explain-cache.json(gitignored), keyed by (dialect, normalized-SQL fingerprint, schema-snapshot fingerprint). Unchanged queries cost nothing on repeat runs, and cached queries do not consume the cap, successive runs walk further through a large corpus. Editing a query or re-pulling the snapshot invalidates exactly the affected entries.
Parameters
EXPLAIN needs bindable placeholders, not real values. The analyzer scans each
query for @Name / :Name / $Name (and Postgres $1 positional)
placeholders and binds representative typed nulls, plans are estimates, so
sample data is unnecessary.
Severity heuristics
| Finding | Severity |
|---|---|
| Sequential/full table scan, est. rows ≥ threshold (default 10,000) | high |
| Sequential/full table scan below the threshold | informational |
| Full scan where the dialect exposes no row estimate (SQLite) | informational |
| Sort step whose est. cost is ≥ 50% of the plan's total cost | high |
| Sort step below the dominance ratio | informational |
SQLite USE TEMP B-TREE FOR ORDER BY/GROUP BY (always means no supporting index) |
high |
| Index-backed access (seeks, index/covering-index scans) | not flagged |
Costs and row counts are planner estimates from the connected database at run time, they vary with statistics, data volume, and engine version. Thresholds compare within a single run; do not treat costs as comparable across environments.
Failing a pipeline on purpose: --fail-on
By default the exit code is 0 no matter what the report says. To gate a
dedicated CI stage, opt in:
jauntyq explain --provider postgres --connection-env CI_EXPLAIN_DB \
--queries db/tables --fail-on high
# exit 0 = nothing at/above the threshold; exit 2 = a high-severity plan problem
--fail-on informational fails on any finding; --fail-on high (recommended)
fails only on high-severity ones.
Optional MSBuild wiring (off by default)
Exactly like the documented schema verify wiring: an explicit Exec target
guarded by a connection variable. When the variable is unset, the default
everywhere, including CI, the target does not run and the build is untouched.
<Target Name="ExplainJauntyQueries" AfterTargets="Build"
Condition="'$(JAUNTY_EXPLAIN_CONN)' != ''">
<!-- No fail-on switch: report-only, never fails the build. -->
<Exec Command="jauntyq explain --provider postgres --connection-env JAUNTY_EXPLAIN_CONN --queries db/tables" />
</Target>
Even if the variable is set and the database is down, the command exits 0
(clean skip), the build cannot break.
What it will not do
- Run inside the Roslyn generator, ever (the generator has no connection code).
- Execute or mutate anything (
EXPLAIN ANALYZEis deliberately unsupported). - Explain write statements (skipped with a note).
- Prescribe DDL, it reports the problem and the hint, not a migration.
See the CLI reference for the full option table and exit codes.