SKILL.md
Database query performance reviewer
An evidence-first workflow to explain a slow query from its plan, estimates, indexes, and runtime evidence.
- Revision
- 2
- Verified
- 2026-10-20
Compatibility and paths
Codex
skills/query-performance-review/SKILL.mdClaude Code
.claude/skills/query-performance-review/SKILL.mdVS Code
.github/skills/query-performance-review/SKILL.mdTrust and provenance
Curated record reviewed 2026-10-20. Results still depend on the supplied context and target environment.
Generated assetReady to copy or download
---
name: query-performance-review
description: Explains a concrete query using actual row/loop/filter/index evidence and unchanged results. Use before an index or SQL rewrite; separate optimizer cost, executor observations and real application latency.
license: CC-BY-4.0
compatibility: Owned plan lab requires Node.js 22.13+ and @electric-sql/pglite 0.5.8. Its 10000-row in-memory database has no production connection or persistent data directory.
metadata:
author: oneliners
version: "2.0.0"
---
# Database query performance reviewer
## Establish a reproducible comparison
Collect engine/version, exact query and parameter values, schema/indexes, cardinality/distribution, statistics freshness, plan settings and measured symptom. Separate planning, execution, locks, result serialization and application/network time. A query fingerprint with different parameter selectivity is not a comparable performance run. Redact sensitive literals without changing the selectivity assumptions used by the explanation.
## Actual owned planner exercise
Use a new empty local exercise directory, not an application checkout or production database. Node.js 22.13+ is required. Install the pinned library only if this exercise is approved:
npm init -y
npm install --ignore-scripts --save-exact @electric-sql/pglite@0.5.8
Package installation uses the npm registry; the program itself opens only a new in-memory database, with no server address or persisted data directory. The [PGlite documentation](https://pglite.dev/docs/) describes this mode. Save the complete code block below as the named .mjs file in that directory; all schema/data are included, so no missing SQL/data file is required.
Save the following block as query-fixture.mjs and run node query-fixture.mjs. It creates 10000 synthetic rows, executes only the shown local SELECT/EXPLAIN and adds one index inside its new in-memory database. This is not a safe command to point at a production connection.
```js
import assert from "node:assert/strict";
import { PGlite } from "@electric-sql/pglite";
async function runQueryFixture(PGlite) {
const db = new PGlite();
try {
const version = (await db.query("SHOW server_version")).rows[0].server_version;
await db.exec("CREATE TABLE training_query(id integer NOT NULL, bucket text NOT NULL, payload text NOT NULL); INSERT INTO training_query SELECT n, CASE WHEN n%1000=0 THEN 'rare' ELSE 'common' END, repeat('x',50) FROM generate_series(1,10000) AS n; ANALYZE training_query;");
const query = "SELECT id FROM training_query WHERE bucket='rare' ORDER BY id";
const explain = "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON, TIMING OFF) " + query;
const before = (await db.query(explain)).rows[0]["QUERY PLAN"][0].Plan;
const beforeRows = (await db.query(query)).rows;
// Changes only this owned in-memory table, never a production index.
await db.exec("CREATE INDEX training_query_bucket_id ON training_query(bucket,id); ANALYZE training_query;");
const after = (await db.query(explain)).rows[0]["QUERY PLAN"][0].Plan;
const afterRows = (await db.query(query)).rows;
function nodes(root) {
const result = [];
function visit(node) {
result.push({ type: node["Node Type"], index: node["Index Name"] || null, estimatedRows: node["Plan Rows"], actualRows: node["Actual Rows"], loops: node["Actual Loops"], removedByFilter: node["Rows Removed by Filter"] || 0, sharedHits: node["Shared Hit Blocks"] || 0, sharedReads: node["Shared Read Blocks"] || 0 });
for (const child of node.Plans || []) visit(child);
}
visit(root); return result;
}
return { version, rowsEqual: JSON.stringify(beforeRows) === JSON.stringify(afterRows), returnedIds: afterRows.map((row) => row.id), before: nodes(before), after: nodes(after), limitation: "Owned in-memory planner/row evidence, not a production latency or physical-disk benchmark." };
} finally { await db.close(); }
}
const result = await runQueryFixture(PGlite);
assert.equal(result.rowsEqual, true);
assert.deepEqual(result.returnedIds, [1000,2000,3000,4000,5000,6000,7000,8000,9000,10000]);
console.log(JSON.stringify(result, null, 2));
```
## Interpretation rather than a fabricated benchmark
The owned dataset has 10 rare rows among 10000, with the same query, values and ordering before/after one index addition. Both executions return IDs 1000, 2000, …, 10000; the assertion verifies exact result equality, not just a faster-looking plan. The program prints the real backend version and actual selected node types/row estimates/loops/filter and buffer counters from each run; it does not manufacture a plan string or a duration.
Read the observed before Seq Scan and its filter removals, then the index-capable after plan for (bucket,id). A sequential scan is not inherently a bug: this case has a highly selective equality filter and ordering supported by the candidate index. For a common bucket or a tiny table, a different choice may be appropriate. Do not force enable_seqscan=off to claim the optimizer proved an index useful.
Cost units are estimates, not milliseconds. Actual rows are per-loop when loops exceed 1; do not multiply or sum unrelated nodes into a fake total. Parent buffer counters include child work, so adding every plan node double counts it. Shared reads do not by themselves prove physical-disk I/O or an end user's response time. The script intentionally omits benchmark numbers: an in-memory WASM dataset is not the production distribution, concurrency, storage or network path.
Next production evidence, only with approved scope: exact query/parameter values, schema/indexes, row distribution/statistics age, a bounded plan and actual application latency under comparable load. EXPLAIN ANALYZE executes its statement and can have side effects; do not apply it blindly to writes or functions on a live service. Review index size/write amplification, locking/build method and rollback before proposing a real index. This exercise created no remote index and proves only its reported local plan/result observations.
## Rank one hypothesis and one measured change
Locate the node whose estimates, row multiplication, filtering, sort/spill or lookup pattern supports the hypothesis. Keep estimated versus actual rows/loops distinct; inspect skew and stale statistics before proposing an index. Preserve result semantics, ordering/null rules and parameterization while comparing one change. Run cold/warm and representative-load comparisons only where authorized; a faster second run can be cache state, not the proposed optimization. [PostgreSQL EXPLAIN guidance](https://www.postgresql.org/docs/current/using-explain.html) explains the plan/counter limitations; the embedded backend reports its own actual version.
## Acceptance and stop conditions
Return query/parameter identity, plan-node evidence, correctness check, one candidate change, actual measured outcomes, write/storage impact, rollout/revert plan and remaining unknowns. Do not execute unbounded EXPLAIN ANALYZE, VACUUM or index builds against a live database as an automatic review step. Unknown workload shape, non-comparable measurements, incomplete plan or incorrect results prevents a performance claim. Passing this local result assertion is not proof an application improved or an index is safe to deploy.
Real example
Input
Review the exact complete owned query/plan exercise in SKILL.md: 10000 rows, ten rare keys, the same SELECT before/after one index. Preserve result equality and interpret actual printed plan counters; do not claim a production latency improvement or connect to a real database.
Expected result
The owned dataset has 10 rare rows among 10000, with the same query, values and ordering before/after one index addition. Both executions return IDs 1000, 2000, …, 10000; the assertion verifies exact result equality, not just a faster-looking plan. The program prints the real backend version and actual selected node types/row estimates/loops/filter and buffer counters from each run; it does not manufacture a plan string or a duration. Read the observed before Seq Scan and its filter removals, then the index-capable after plan for (bucket,id). A sequential scan is not inherently a bug: this case has a highly selective equality filter and ordering supported by the candidate index. For a common bucket or a tiny table, a different choice may be appropriate. Do not force enable_seqscan=off to claim the optimizer proved an index useful. Cost units are estimates, not milliseconds. Actual rows are per-loop when loops exceed 1; do not multiply or sum unrelated nodes into a fake total. Parent buffer counters include child work, so adding every plan node double counts it. Shared reads do not by themselves prove physical-disk I/O or an end user's response time. The script intentionally omits benchmark numbers: an in-memory WASM dataset is not the production distribution, concurrency, storage or network path. Next production evidence, only with approved scope: exact query/parameter values, schema/indexes, row distribution/statistics age, a bounded plan and actual application latency under comparable load. EXPLAIN ANALYZE executes its statement and can have side effects; do not apply it blindly to writes or functions on a live service. Review index size/write amplification, locking/build method and rollback before proposing a real index. This exercise created no remote index and proves only its reported local plan/result observations.