db-query-plan-analyzer
Overview
Most API perf regressions resolve to one of:
- A new query running unindexed (sequential scan over a growing
table).
- A query joining on the wrong column type (implicit cast prevents
index use).
- A sort that doesn't fit in
work_mem and spills to disk.
- A query plan that started using a nested loop where a hash join
would be faster (or vice versa) due to stale stats.
- A query touching N+1 rows because of an ORM N+1 access pattern.
EXPLAIN ANALYZE (PostgreSQL), EXPLAIN ANALYZE (MySQL 8.0+),
or EXPLAIN QUERY PLAN (SQLite) reveals which case you're in. This
skill reads that output and proposes the fix. The query-plan
vocabulary (Sequential Scan, Index Scan, Nested Loop) comes from
PostgreSQL's Using EXPLAIN.
When to use
- A load test showed DB-bound latency growth.
- An APM tool (Datadog / New Relic / Grafana) flagged a slow query.
- A production incident traced to a specific query.
- An
EXPLAIN ANALYZE was captured but the team can't tell which
cost is dominant.
If the bottleneck is connection-pool exhaustion or replication lag,
this skill doesn't apply - those are infrastructure concerns.
Step 1 - Get a real EXPLAIN ANALYZE
Always use ANALYZE (it runs the query and reports actual rows +
time); plain EXPLAIN gives only planner estimates, which can be
wildly wrong.
PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON, SETTINGS)
SELECT o.id, o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'test@example.com'
AND o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 50;
BUFFERS adds I/O statistics; FORMAT JSON produces machine-
readable output; SETTINGS shows session-specific settings that
affected the plan (pg-explain).
MySQL 8.0+
EXPLAIN ANALYZE SELECT ...;
Output is a tree representation similar to PostgreSQL.
SQLite
EXPLAIN QUERY PLAN SELECT ...;
SQLite's output is simpler - no costs, just the access strategy
(Index / Sequential / Search).
Step 2 - Identify the dominant cost
Read the plan from the innermost nodes outward (the deepest
nesting is what runs first). The dominant cost is the node with the
highest actual time in PostgreSQL or the highest cumulative cost
in MySQL. Match the node to its meaning using the cost-signature
table in references/plan-signatures.md.
Step 3 - Match cost to fix
Look up the diagnosis in the diagnosis-to-fix table in
references/plan-signatures.md (Seq
Scan -> add index, type cast -> fix app code, sort spill -> pre-sorted
index or larger work_mem, N+1 -> eager-load at the ORM, etc.).
Step 4 - Emit the candidate index / rewrite
Per pg-explain index-creation conventions:
-- Single-column index on a high-selectivity column
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Composite index - order matters; leading column is the most selective
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);
-- Partial index - covers only the rows the query touches
CREATE INDEX idx_orders_active ON orders(created_at DESC) WHERE status = 'active';
-- Functional index - for predicates with a function on the column
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Covering index (PostgreSQL 11+) - avoids a heap fetch
CREATE INDEX idx_orders_summary ON orders(customer_id) INCLUDE (status, total);
For which column leads, when to use partial vs. functional indexes,
and low-cardinality pitfalls, see the index-choice principles in
references/plan-signatures.md.
Output format
## Slow query analysis - `<query-id-or-snippet>`
**Database:** postgresql 17 | mysql 8.0 | sqlite
**Query:** (excerpt)
```sql
SELECT o.id, o.status FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'test@example.com' AND o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC LIMIT 50;
```
### Plan summary
| Node | Actual time | Rows | Cost driver |
|------------------------------|------------:|------:|-------------|
| Seq Scan on orders (filter) | 2.3s | 1.2M | **DOMINANT** - no index on (customer_id, created_at) |
| Index Scan on customers (email) | 1ms | 1 | (covered) |
### Diagnosis
The query joins `orders` to `customers` on `customer_id`, but
`orders` has no index on `customer_id` - the planner falls back to a
sequential scan over 1.2M rows, then post-filters by date.
### Recommended fix
```sql
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);
```
Composite index ordered by `customer_id` first (the join predicate),
then `created_at` descending - satisfies both the filter and the
ORDER BY without a separate sort.
### Expected impact
- Plan changes from Seq Scan to Index Scan.
- Actual time drops from ~2.3s to ~50ms (rows-touched falls from
1.2M to ~50).
- `Buffers: shared read` drops correspondingly; less I/O on the
database.
### Validation
After applying:
```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
```
Confirm the new plan uses `idx_orders_customer_created` and the
actual time is below the budget.
Anti-patterns
See the anti-patterns table in
references/plan-signatures.md
(speculative indexing, reading EXPLAIN without ANALYZE, optimizing a
non-dominant node, skipping ANALYZE after a bulk load).
Limitations
- Vendor-specific output. PostgreSQL's
EXPLAIN is the most
detailed; MySQL 8.0+ caught up; SQLite is sparse. The skill is
most useful for postgres-flavored databases.
- Distributed databases. Spanner, CockroachDB, Citus produce
different plans - the skill's heuristics translate but the
specific node names differ.
- Doesn't replace a DBA. For complex multi-CTE queries with
recursive sub-plans, a human DBA is faster than this skill's
pattern matching.
References