OneLinersCommand workbench
AI
Back to skills
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
Save or explore
Save to collectionCreate a collection in the sidebar first.

Compatibility and paths

Codexskills/query-performance-review/SKILL.md
Claude Code.claude/skills/query-performance-review/SKILL.md
VS Code.github/skills/query-performance-review/SKILL.md

Trust 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.

Source evidence

Agent Skills specificationofficialPostgreSQL EXPLAINofficialPGlite in-memory PostgreSQL modeofficial