Open source · PostgreSQL
Investigate slow PostgreSQL queries with evidence
Understand the execution plan, evaluate a candidate rewrite or index, compare before and after, and verify what actually changed before anyone touches production SQL.
Walk through a real investigation
Six steps, shown with output from one actual run against this repository's own demo dataset (make demo, scenario "slow-dashboard"), captured through the running API while writing this page. One before/after run of one query, not a benchmark suite average.
Pasted directly, or pulled from pg_stat_statements
Query as received
SELECT product_category, SUM(total_amount) AS revenue
FROM demo.sales
WHERE DATE_TRUNC('month', date) = DATE '2026-08-01'
GROUP BY product_category
ORDER BY revenue DESCEXPLAIN evidence: seq scans, spills, partition pruning defeated
Sequential scan across 50 partitions — DATE_TRUNC('month', date) wraps the partition key, so PostgreSQL can't prune and has to scan every partition.
Proposed from the query's own parse tree, not a language model
Proposed rewrite
SELECT product_category, sum(total_amount) AS revenue
FROM demo.sales
WHERE date >= '2026-08-01'::date AND date < '2026-09-01'::date
GROUP BY product_category
ORDER BY revenue DESCUnwrap DATE_TRUNC('month') equality to a sargable range predicate so PostgreSQL can prune partitions and use indexes on the column.
Both plans run through EXPLAIN (ANALYZE), measured, not estimated
| Evidence | Before | After | Change |
|---|---|---|---|
| Execution time (median)Median of 3 runs each; observed spread up to 35ms, smaller than the 41ms difference between medians. | 47ms (3 runs, 26ms–60ms) | 5ms (3 runs, 5ms–6ms) | −88.2% |
| Planner cost (estimate)Arbitrary units, not a time and not a speed multiple. Execution time above is the actual measurement. | 8.5k | 841.4 | −90.1% |
| Partitions scanned | 50 | 1 | 50 → 1 |
Checked that both queries return the same rows
VerifiedEqual — 5 rows, matched by an order-independent checksum over every row.
A deterministic template citing the evidence above
A deterministic template built from the plan findings, the candidate SQL, the before/after metrics, and the equivalence status above. No LLM writes it, and nothing here is applied to a database automatically; the report is what a reviewer reads before deciding to run the DDL or ship the rewritten query.
What it actually does
Execution-plan findings
Reads EXPLAIN output and flags concrete plan problems: sequential scans, spills to disk, partition pruning defeated by a wrapped column.
Rewrite candidates
Proposes bounded SQL rewrites, such as unwrapping DATE_TRUNC on a filtered column, from the query's own AST when one applies. Not every query gets one.
Index candidates
Surfaces index DDL for review. Cost is projected through HypoPG when installed, otherwise labeled as an unverified heuristic. Nothing is applied automatically.
Measured comparison
Runs EXPLAIN (ANALYZE) on both the original and candidate query and reports real execution time and plan shape, not planner cost alone.
Result verification
Confirms the candidate returns the same rows as the original, by full checksum or a bounded sample, and says which one happened.
Investigation report
Packages the findings, candidate, comparison, and verification status into a report generated from a deterministic template.
Estimate is not measurement. Proposal is not verified improvement.
A candidate rewrite existing is not evidence that it is faster, and a faster measured run is not evidence that it returns the same rows. PgQueryNarrative keeps these four states distinct instead of collapsing them into a single confidence score.
- Proposed
- A candidate rewrite or index exists. It has not been run.
- Estimated
- A planner cost, or a HypoPG projection when the extension is installed. A cost, not a time.
- Measured
- EXPLAIN (ANALYZE) actually ran both queries. Real execution time and plan shape.
- Verified
- The candidate's result rows were checked against the original: a full checksum match, or an explicit bounded sample.
Compared to reading EXPLAIN by hand, or an AI SQL assistant
Capability, not a speed claim. None of this is measured against a competing tool.
| PgQueryNarrative | Manual EXPLAIN reading | Generic AI SQL assistant | |
|---|---|---|---|
| Finds plan problems | Parses EXPLAIN output for seq scans, spills, and defeated pruning | Whatever the reader notices | Whatever the prompt surfaces |
| Proposes a fix | From the query's own parse tree, against a fixed, auditable rule set | The engineer writes it | A language model generates SQL |
| Before/after comparison | EXPLAIN (ANALYZE) run on both sides, reported as a labeled metric table | Only if someone remembers to run it | Rarely run automatically |
| Result verification | States VerifiedEqual, SampleMatch, Different, or Unverified — never silent | Manual, easy to skip | Usually not checked at all |
| Applies the change | Never — a person decides | The engineer applies it | Varies; some tools do |
Where AI fits
An LLM is optional and scoped to three features. It plays no part in plan findings, the rewrite engine, index candidates, plan comparison, or result verification, which are the "compared to" table above and the worked example further up this page.
Ask
Natural language to SQL to a narrative report, in one step. Requires an LLM.
Report narrative
The prose in a Workbench report (headline, takeaways, drivers). Falls back to a deterministic, metrics-only narrative if the call fails or its output can't be trusted.
Plain-English SQL explanation
One or two sentences describing what a query does. Requires an LLM.
Bring your own provider: local Ollama by default, or an opt-in cloud provider (Claude, OpenAI, Gemini, Groq) gated behind an explicit data-egress flag, with PII redaction and a token/cost budget. See LLM providers in the docs.
UI Preview
Prefer a terminal? Try pqn
A smaller, separate tool that does the same core job as the app above, but lives inside PostgreSQL itself instead of a browser: no server to run, just a command line against your database.
- 1
Finds the slow ones
Reads pg_stat_statements and ranks what's actually costing time on your database right now.
- 2
Proposes a fix
The same rewrite engine as the app above: a fix proposed from the statement's own execution plan, not a guess.
- 3
Proves it, or says it can't
Runs both versions for real and reports one of four verdicts: Proven, NotFaster, Different, or Unverified. Never a guess dressed up as a result.
Read-only by design: it only runs SELECT statements, and only against columns a database admin has explicitly exposed to that person. See it end to end in the ten-minute pqn quick start.
Open source
PgQueryNarrative is a public, MIT-licensed project. There's no gated core, no CLA, and no paid tier — what's on GitHub is the whole product.
Source
Public repository
Every rewrite rule, plan-finding check, and report template referenced on this page is in the repository, not held back.
Read the code, not the pitch
The investigation loop, the rewrite rules, and the verification logic are all in the repository.