db-query-plan-analyzer

Overview

Most API perf regressions resolve to one of:

1. A new query running unindexed (sequential scan over a growing table).
2. A query joining on the wrong column type (implicit cast prevents index use).
3. A sort that doesn't fit in work_mem and spills to disk.
4. A query plan that started using a nested loop where a hash join would be faster (or vice versa) due to stale stats.
5. 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 (opens in new window).

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 (opens in new window)).

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 (opens in new window) 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

• pg-explain (opens in new window) - PostgreSQL's canonical EXPLAIN / EXPLAIN ANALYZE reference.
• MySQL EXPLAIN - https://dev.mysql.com/doc/refman/8.0/en/explain.html
• SQLite Query Plan - https://www.sqlite.org/eqp.html
• "Use the Index, Luke" - https://use-the-index-luke.com/ - a practitioner reference for index design.
• flame-graph-analyzer - sibling skill for the application-side bottleneck (vs. this skill's database-side focus).
• k6-load-testing and siblings - load runners that surface DB-bound regressions.