# SQL Optimizer

> Diagnose and fix slow SQL — read EXPLAIN ANALYZE plans, find the worst node, propose the right indexes (composite, covering, partial), detect N+1 / OFFSET / LIKE-%prefix anti-patterns, and ship a re-measure protocol. Supports PostgreSQL and MySQL. Outputs a verdict, plan diagnosis, ordered fix plan with DDL, and risk notes. Use to make a slow query fast, audit an EXPLAIN plan, or pre-deployment review of a schema/index change.

- Skill: `viprasol-tech/sql-optimizer` (Agent Skill, multi-file: 6 files)
- Install (CLI): `npx skillmds@latest add viprasol-tech/sql-optimizer`
- Raw SKILL.md: https://api.skillmd.com/api/skills/viprasol-tech/sql-optimizer/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Viprasol-Tech (https://skillmd.com/u/viprasol-tech)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/viprasol-tech/sql-optimizer

---


# SQL Optimizer

A disciplined, repeatable methodology for making slow SQL fast — read the
`EXPLAIN ANALYZE` plan, find the **single worst node**, hypothesize the
**single fix** that moves it, ship it, and re-measure. The skill is grounded
in how senior DBAs actually work: small, ordered, evidence-driven changes —
not a shotgun blast of `CREATE INDEX` statements.

Most "slow query" tickets come down to one of a small number of patterns:
a missing or wrong-order composite index, a stale-statistics cardinality
miss, a sort spilling to disk, or an N+1 in the app layer. This skill
encodes the workflow, the plan-reading glossary, the indexing rules
(leftmost-prefix, covering, partial, expression), the anti-patterns, and a
red-flag scan into a single repeatable pass.

---

## When to Activate

Activate when the user:

- Shares a **slow SQL query** and asks why it's slow or how to speed it up.
- Pastes an **`EXPLAIN ANALYZE`** (or `EXPLAIN FORMAT=JSON`) output and asks
  you to read it.
- Shares a **schema / index DDL diff** for pre-deployment review
  ("should we add this index?", "is this column order right?").
- Pastes an **ORM-generated query** (Django, Rails, SQLAlchemy, Hibernate,
  Prisma, EF Core) and asks why it's slow — or shares logs that look like
  N+1.
- Shares a **`pg_stat_statements`** row, MySQL **slow query log** entry, or
  an APM trace with a hot SQL call.
- Asks any of: *"why is this query slow?"*, *"what index do I need?"*,
  *"is this an N+1?"*, *"how do I read this plan?"*, *"is my composite
  index in the right order?"*

> **Honest scope & limits — read first.**
> - I cannot run the query against a live database. I reason over what you
>   provide: the query, the `EXPLAIN` output, the schema/DDL, row counts,
>   sample data, and engine/version.
> - Plans are **data- and config-dependent**. A plan on a 1k-row dev table
>   says nothing about a 100M-row prod table — **always re-run `EXPLAIN
>   ANALYZE` on prod-sized data**.
> - Index changes are **not free**: every index slows writes and consumes
>   buffer cache. Verify the win before shipping.
> - ⚠️ **`EXPLAIN ANALYZE` on an `INSERT` / `UPDATE` / `DELETE` actually
>   performs the write.** Wrap in `BEGIN; ... ROLLBACK;` or run on a clone.
> - This skill is not a substitute for proper APM + slow-query monitoring
>   in production.

---

## Step 1: Intake & Scope

Before reading a single plan line, establish exactly what you're tuning.

1. **Engine and version.** PostgreSQL 15? MySQL 8.0? SQLite? SQL Server?
   This skill goes deepest on **PostgreSQL** and **MySQL 8+**, with
   coverage of SQLite/SQL Server where the workflow is the same. Version
   matters: PG 12+ inlines CTEs by default, MySQL 8 added hash join and
   histograms, etc.
2. **The query itself.** The exact SQL — not a paraphrase. ORM-generated?
   Capture the actual SQL it emits (`django.db.connection.queries`, Rails
   `ActiveRecord::Base.logger`, Hibernate `show_sql=true`, etc.).
3. **The schema.** `CREATE TABLE` for every table in the query, plus
   **every existing index** (`\d+ tablename` in PG, `SHOW CREATE TABLE` in
   MySQL). Without this you cannot tell what's missing.
4. **Cardinalities.** Row count per table, roughly. "Big" means
   different things at 10M vs 10B. For the worst query, also: rows after
   each predicate (`SELECT count(*) FROM t WHERE …`).
5. **The `EXPLAIN` output, if any.** Prefer `EXPLAIN (ANALYZE, BUFFERS)` in
   PG and `EXPLAIN ANALYZE` or `EXPLAIN FORMAT=JSON` in MySQL 8+. Plain
   `EXPLAIN` is a guess; `ANALYZE` runs the query for real. (See the DML
   warning above.)
6. **What "slow" means.** Current p50/p95 latency. Target latency. Is this
   a **hot OLTP** query (every web request) or an **analytical** query
   (nightly job)? The fix calculus is different.
7. **Workload context.** Reads vs writes ratio, prepared statements, app
   connection pool size, replication setup. Tell-tale signs of parameter
   sniffing or plan instability.

If any of these are missing, **ask** — don't invent them.

---

## Step 2: The 6-Step Workflow

```
1. Reproduce on real data        — tiny dev sets always pick the wrong plan.
2. Capture the plan              — EXPLAIN (ANALYZE, BUFFERS) in PG;
                                   EXPLAIN ANALYZE / FORMAT=JSON in MySQL 8+.
3. Diagnose the worst node       — highest (actual_time × loops) or biggest
                                   "Rows Removed by Filter" wins.
4. Hypothesize one fix           — missing index? wrong order? stale stats?
                                   N+1? subquery that should be a join?
5. Fix ONE thing, re-EXPLAIN     — never two changes at once.
6. Re-measure hot AND cold cache — hot run hides disk cost; do both.
```

The order is non-negotiable. Skipping step 1 wastes everyone's afternoon.
Skipping step 6 ships a "fix" that only works while pages are still in
RAM.

---

## Step 3: Reading a Plan — the Glossary

Every PostgreSQL and MySQL plan is built from a small set of operators.
Knowing what each one means — and **when it's a red flag** — is 80% of
the skill.

| Node | What it does | Good when… | Red flag when… |
|---|---|---|---|
| **Seq Scan** | Full table scan | Table is small (<10k rows) or you genuinely need most rows | Big table + selective filter (e.g. `WHERE country='IN'` on 100M rows) |
| **Index Scan** | Walks an index, then fetches the heap | Index is selective and you need columns not in the index | Filter is unselective — you read most of the index *and* most of the heap |
| **Index Only Scan** | Index alone satisfies the query (covering index + visibility map up to date) | Hot read paths with a covering / `INCLUDE` index | (Usually none — this is the target) |
| **Bitmap Index Scan + Bitmap Heap Scan** | Gather many index hits, then read heap in physical order | Medium-selectivity filters; multiple index `OR` combinations | "Lossy" recheck on huge result sets — sometimes worse than Seq Scan |
| **Nested Loop** | For each outer row, probe inner — O(outer × inner-probe-cost) | Outer is tiny AND inner has a usable index on the join key | Outer is huge OR inner has no index ⇒ effectively O(n²) |
| **Hash Join** | Build hash on smaller side, probe with bigger side | Big × big equi-join | `Batches: > 1` ⇒ memory spill — bump `work_mem` |
| **Merge Join** | Both inputs sorted on the join key, scan in parallel | Inputs already sorted by index | Forces a Sort node that spills to disk |
| **Sort** | Order rows | Small in-memory sort | `external merge Disk: 4123kB` — bump `work_mem` or add an ordering index |
| **Hash Aggregate** | Group via hash | Distinct values fit in `work_mem` | Spills — same fix as Hash Join |
| **Group Aggregate** | Group via sorted scan | Input is already sorted | Forces a costly Sort node |
| **Materialize** | Spool inner side for re-use in Nested Loop | Tiny inner | Hides a big inner — usually means the plan picked wrong |
| **CTE Scan** | Reads a materialized CTE | You actually want to materialize | PG 12+ inlines CTEs unless `MATERIALIZED`; an unexpected CTE Scan can be a planner barrier |

Beyond the operators, two diagnostic rules matter more than any single
node:

- **Plan Rows vs Actual Rows mismatch ⇒ stale statistics.** Look at every
  node and compare the planner's `rows=` estimate to the actual rows the
  node returned. A 10×, 100×, or 10000× miss is a flashing red light:
  the planner picked the wrong join order, the wrong join strategy, or
  the wrong index. Fix the stats first, then re-EXPLAIN — often the bad
  plan goes away on its own.
- **`Rows Removed by Filter` is the over-fetching signal.** If a node
  returned 50 rows but `Rows Removed by Filter: 1,200,000`, the planner
  scanned 1.2M rows and threw 99.996% of them away. That's a missing
  index condition: the filter belongs **inside the index**, not on top of
  it.

---

## Step 4: Indexes — the 90% Lever

Most "slow query" tickets are fixed by adding (or fixing the order of) a
single index. Index design is its own discipline; here are the rules
that matter for tuning.

### Index types

| Type | Use for | Notes |
|---|---|---|
| **B-tree** | Equality + range; the default; ASC/DESC orderings | 95% of indexes |
| **Hash** | Equality only | Rarely worth it — B-tree is almost as fast |
| **GIN** | Multi-value: JSONB, `tsvector`, arrays, `pg_trgm` | The fix for `LIKE '%abc%'` |
| **GiST** | Geometric, range types, legacy full-text | Niche |
| **BRIN** | Huge append-only tables physically sorted by a column (time-series) | Tiny index, fast on a range scan |
| **Partial** | `CREATE INDEX … WHERE active = TRUE` | Indexes only the hot subset; smaller + faster |
| **Expression** | `CREATE INDEX … (lower(email))` | Required when the query wraps the column in a function |

### The leftmost-prefix rule for composite indexes

For an index `INDEX (a, b, c)`:

| Query predicate | Uses the index? |
|---|---|
| `WHERE a = ?` | ✅ uses the `a` part |
| `WHERE a = ? AND b = ?` | ✅ uses `a, b` |
| `WHERE a = ? AND b = ? AND c = ?` | ✅ uses the full index |
| `WHERE a = ? AND c = ?` | ⚠️ uses only `a` — `c` becomes a filter (skip-scan possible in some engines but don't rely on it) |
| `WHERE b = ?` | ❌ **does not use the index** — no leftmost match |
| `WHERE b = ? AND c = ?` | ❌ **does not use the index** |
| `ORDER BY a, b` | ✅ index ordering can avoid a Sort |
| `ORDER BY b, a` | ❌ wrong order — Sort is needed |

The practical heuristic for column order:

> **Equality columns first → range columns next → sort columns last.**

So a query like `WHERE country = ? AND created_at > ? ORDER BY created_at
DESC` wants `INDEX (country, created_at DESC)` — not the other way around.

### Covering / `INCLUDE` indexes

```sql
CREATE INDEX idx_users_country_created_at
  ON users (country, created_at DESC)
  INCLUDE (email);
```

The `INCLUDE` columns aren't part of the key but are stored in the index
leaf. This lets the planner pick an **Index-Only Scan** — it never has to
touch the heap. For hot read paths returning a few columns, this is a
massive win.

### Partial indexes

```sql
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';
```

If 95% of `orders` are `complete` and you only ever query `pending`, a
partial index is **smaller, hotter in cache, and faster** than a full
index on `(status, created_at)`.

### Expression indexes

```sql
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Query MUST match the expression:
SELECT * FROM users WHERE lower(email) = 'alice@example.com';
```

A plain `INDEX (email)` is **defeated** by the `lower()` wrap in the
predicate — see pitfall #2.

### The 12 indexing pitfalls

1. **Indexing low-cardinality columns alone** — `INDEX (is_active)` where
   only two values exist; the planner correctly ignores it.
2. **Function-wrap defeats index** — `WHERE upper(email) = ?` defeats
   `INDEX (email)`. Fix with an expression index or stop wrapping.
3. **`OR` chains without a bitmap-or plan** — `WHERE a = ? OR b = ?` can
   force a full scan. Often a `UNION ALL` of two indexed queries is
   faster.
4. **Implicit type cast** — `WHERE id = '42'` where `id` is `bigint`. The
   cast can block index use; pass the right type.
5. **Wrong column order in composite** — see the leftmost rule above. The
   single most common index bug.
6. **Over-indexing** — every index slows every `INSERT` / `UPDATE` /
   `DELETE` and consumes buffer cache. Drop the indexes nobody uses.
7. **Extreme skew without extended stats** — `country = 'US'` matches
   80% of rows; the planner needs to know.
8. **Stale statistics** — `ANALYZE` the table; in PG, raise
   `default_statistics_target` on skewed columns.
9. **`LIKE '%abc'` on a B-tree** — a trailing-wildcard pattern cannot use
   a normal B-tree; needs `pg_trgm` + GIN (PG) or a generated/reverse
   column.
10. **Missing partial index on the hot subset** — the 95/5 case above.
11. **Forgotten unused indexes** — check
    `pg_stat_user_indexes.idx_scan = 0` (or MySQL's
    `sys.schema_unused_indexes`). They cost writes and cache forever.
12. **Invalid after a failed `CONCURRENTLY` build** — `pg_index.indisvalid
    = false`. The index is on disk but the planner ignores it. `DROP
    INDEX CONCURRENTLY` and rebuild.

---

## Step 5: Cardinality & Statistics

Bad estimates ⇒ wrong plans. Period. If the planner thinks a node will
return 100 rows and it returns 10,000,000, it will pick Nested Loop and
your query melts.

### PostgreSQL

```sql
ANALYZE users;                                       -- refresh per-column histograms
ALTER TABLE users ALTER COLUMN country
  SET STATISTICS 1000;                               -- raise sample size for a skewed column
SET default_statistics_target = 500;                 -- session/global default; 100 → 500–1000 on skewed schemas

-- Extended statistics for correlated columns:
CREATE STATISTICS users_country_signup_stats (dependencies, ndistinct)
  ON country, signup_source FROM users;
ANALYZE users;
```

Autovacuum runs `ANALYZE` periodically, but only when row churn exceeds a
threshold — bulk loads and slow-trickle writes often leave stats stale.

### MySQL 8+

```sql
ANALYZE TABLE users;                                 -- refresh InnoDB stats
ANALYZE TABLE users UPDATE HISTOGRAM ON country WITH 64 BUCKETS;  -- skewed-column histogram
```

MySQL 8 introduced histograms — use them for skewed columns the optimizer
otherwise misjudges.

---

## Step 6: N+1 — the App-Layer Killer

The pattern: load 100 parents, then for each parent issue a child query —
101 round-trips instead of 1 or 2.

```ruby
# Rails — N+1
users = User.where(country: "IN")        # 1 query
users.each { |u| puts u.orders.count }   # +100 queries
```

### Detection

- Query log shows the **same prepared statement repeated N times** in
  fast succession (often microseconds apart).
- ORM debug log is the giveaway (`django.db.backends`, Rails
  `ActiveRecord` logger, Hibernate `show_sql`, Prisma `log: ['query']`).
- PG: `pg_stat_statements` row with very high `calls` and low `mean_exec_time`
  for a query that fetches a parent's children.
- MySQL: slow query log + APM trace.
- APM: Datadog / New Relic / Scout will flag it as an "N+1 query" span.

### Fixes (pick the right one for the workload)

- **Eager loading** in ORMs — the right fix when the children are needed
  for display:
  - Rails: `User.where(country: "IN").includes(:orders)`
  - Django: `User.objects.filter(country="IN").prefetch_related("orders")`
    (or `select_related` for forward FK / one-to-one).
  - Hibernate: `LEFT JOIN FETCH` in JPQL/HQL.
  - EF Core: `db.Users.Include(u => u.Orders)`.
  - SQLAlchemy: `selectinload(User.orders)`.
- **Batch query** — `SELECT * FROM orders WHERE user_id IN (?, ?, ?, …)`
  then group in app code.
- **Single JOIN with grouped aggregate** — when you only need a count or
  sum, not the rows:
  ```sql
  SELECT u.id, COUNT(o.id) AS order_count
  FROM users u LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.country = 'IN' GROUP BY u.id;
  ```
- **DataLoader-style batching** in GraphQL — coalesces per-request
  field resolutions into a single batched query.

---

## Step 7: Anti-patterns to Rewrite

| Anti-pattern | Fix |
|---|---|
| `SELECT *` in a hot path | List the columns you actually need; lets covering indexes win |
| `OFFSET 100000` pagination | **Keyset pagination**: `WHERE id > $last_seen_id ORDER BY id LIMIT 50` |
| `OR` across columns | `UNION ALL` of two indexed queries is often a better plan |
| Scalar subquery in `SELECT` (runs once per row) | `JOIN LATERAL` or a CTE that runs once |
| `NOT IN (subquery)` with possible NULLs | `NOT EXISTS` — `NOT IN` returns no rows when the subquery returns even one NULL |
| `LIKE '%abc%'` | `pg_trgm` + GIN index; or full-text search |
| `DISTINCT` masking a missing join key | Find the duplicate-introducing join and fix it |
| `ORDER BY rand()` / `ORDER BY RANDOM()` | `TABLESAMPLE` or keyset random; never sort the whole table |
| Massive `IN (…)` lists (> ~1000 items) | Insert into a temp table and JOIN |
| Uncorrelated subquery that runs once | Lift to a JOIN — usually a free win |

---

## Step 8: Caching & Memory Knobs

These are bigger levers than most micro-index tweaks and are routinely
under-tuned.

**PostgreSQL** (defaults are conservative for any modern server):

| Setting | Typical | What it does |
|---|---|---|
| `shared_buffers` | ~25% of RAM | PG's own page cache |
| `effective_cache_size` | ~60–75% of RAM | Planner's estimate of total cache (PG + OS) — affects index vs seq choice |
| `work_mem` | 4 MB default ⇒ raise per workload | Memory per Sort / Hash node, **per parallel worker**. Too small ⇒ Disk spill. Too big × many concurrent queries ⇒ OOM |
| `maintenance_work_mem` | 256 MB – 1 GB | Used for `CREATE INDEX`, `VACUUM`, etc. |
| `random_page_cost` | 1.1 on SSDs (default 4.0 assumes spinning disks) | Lower value makes the planner prefer index over seq |

**MySQL (InnoDB)**:

| Setting | Typical | What it does |
|---|---|---|
| `innodb_buffer_pool_size` | 50–75% of RAM | InnoDB's page cache; the single biggest knob |
| `innodb_io_capacity` | match your disk | Affects flush + LRU pacing |
| `tmp_table_size` / `max_heap_table_size` | bump for big GROUP BY | Avoid in-memory → disk tmp table spillover |

These move scans **from disk to RAM** — the difference between 50 ms and
50 µs per page is the difference between "slow query" and "fast query."

---

## Step 9: Modern (2025) Features Worth Knowing

**PostgreSQL**

- **JIT compilation** (`jit = on`, default since PG 12) — speeds up
  big-cost analytical plans by JIT-compiling expression evaluators.
  Adds startup latency on small queries — disable for OLTP if it shows up
  as overhead.
- **Parallel query** — sequential scans, hash joins, hash aggregates,
  and many index scans can run with multiple workers. Tune
  `max_parallel_workers_per_gather`.
- **Partitioning** — `PARTITION BY RANGE / LIST / HASH`. With **partition
  pruning**, queries hit only the relevant partitions; combined with
  **partition-wise joins**, very large tables become tractable.

**MySQL 8**

- **Hash join** for inner equi-joins (massive win vs. the old
  block-nested-loop on big × big joins). Outer joins use hash join from
  8.0.20+ when supported.
- **Invisible indexes** — `ALTER TABLE t ALTER INDEX idx INVISIBLE` to
  test removing an index safely (planner ignores it, but it stays on
  disk and is still maintained on writes; flip back instantly if a
  query regresses).
- **Histograms** — see Step 5; required for skewed columns.

**Both**

- **Prepared statements + bind variables** — avoid re-planning the same
  query for every literal value, and protect against SQL injection. But:
  on parameter-sensitive queries this is the source of **parameter
  sniffing** / **plan instability** — same SQL, different plans across
  runs, because the cached plan was made for an unrepresentative bind
  value.

---

## Step 10: Red-Flag Quick Scan

Run this checklist over any plan:

- [ ] Seq Scan on a big table (> 1M rows) with a selective filter.
- [ ] Nested Loop with > 100k outer rows and no inner index.
- [ ] `Rows Removed by Filter` ≫ rows returned (over-fetching).
- [ ] Sort spilling — `external merge Disk: ...kB`.
- [ ] `Index Cond` is empty but `Filter` is heavy (wrong/missing index).
- [ ] Plan rows ≪ actual rows (stale stats).
- [ ] N+1 pattern in app logs / `pg_stat_statements`.
- [ ] `OFFSET` over 5-figure values.
- [ ] `LIKE '%...'` on a non-trigram column.
- [ ] Hash Join with `Batches: > 1` (memory spill).
- [ ] Same query has very different plans across runs (parameter
      sniffing / plan instability).
- [ ] An invalid index (`pg_index.indisvalid = false`).

If any box is ticked, name it in the diagnosis.

---

## Step 11: Output Format — the Tuning Report

Return your review in this exact shape.

### 🟠 Verdict banner

One of:
- ✅ **Well-optimized** — no actionable wins; current plan is appropriate.
- 🟡 **Caveats** — minor tweaks; latency mostly limited by data volume.
- 🟠 **High wins available** — clear bottleneck(s) with a known fix.
- ⛔ **Critical fix needed** — query is broken in production (timeouts,
  locking, missing critical index on a hot path).

Followed by one sentence: *"Verdict because …"*

### Plan summary table

| Node | Actual rows | Actual time (ms) | Loops | Flag |
|---|---|---|---|---|
| Seq Scan on users | 750,000 | 4,820 | 1 | 🚩 over-fetch |
| Hash Join | 50 | 5,100 | 1 | ℹ️ |
| Sort | 50 | 5,180 | 1 | 🚩 external merge Disk: 8 MB |

Worst node first. One row per non-trivial node.

### Diagnosis

Two or three sentences. Root cause(s) only — not the fix yet.

> Example: *"The Seq Scan on `users` filters 50M rows down to ~750k with a
> selective `country = 'IN'` predicate; the planner has no index on
> `country` so it must scan the whole table. The downstream Sort then
> spills to disk because `work_mem` is the 4 MB default."*

### Fix plan — ordered

Numbered list, smallest-blast-radius first. Always include the **exact
DDL** for index changes, the **before/after SQL** for query rewrites, and
the config command for stats/memory changes.

```sql
-- 1. Add the missing predicate index (no table rewrite, online build):
CREATE INDEX CONCURRENTLY idx_users_country_created_at
  ON users (country, created_at DESC);

-- 2. Bump work_mem for this session to keep the sort in RAM:
SET work_mem = '64MB';

-- 3. Refresh statistics on the join column:
ANALYZE users;
```

For N+1 fixes, show the **single resulting SQL** and the ORM change next
to each other:

```python
# Before — 1 + N
users = User.objects.filter(country="IN")
for u in users:
    u.orders.count()

# After — 1
users = (
    User.objects
        .filter(country="IN")
        .annotate(order_count=Count("orders"))
)
```

### Re-measure protocol

The exact commands to confirm the fix:

```sql
-- PG: hot run after warm-up
EXPLAIN (ANALYZE, BUFFERS) <the query>;

-- Cold cache (Linux):  sudo sysctl -w vm.drop_caches=3
-- then re-run EXPLAIN (ANALYZE, BUFFERS).
```

State the target: *"expect Seq Scan → Index Scan, sort in memory, total
time < 50 ms."*

### Risk notes

- **Index build time / locking.** `CREATE INDEX CONCURRENTLY` is online
  but takes ~2× as long, can fail and leave an invalid index, and won't
  finish if other long transactions block it. Plan a window.
- **Write amplification.** Every new index slows every `INSERT` /
  `UPDATE` / `DELETE` that touches its keys. If the table is write-hot,
  measure both sides.
- **Parameter sniffing.** A plan that's great for `country = 'US'` (80%
  of rows) may be terrible for `country = 'IN'` (1.5%). Prepared
  statements + a non-representative first bind ⇒ stuck on the wrong
  plan. Watch for plan instability after the change.
- **DML EXPLAIN warning.** ⚠️ Don't `EXPLAIN ANALYZE` an `UPDATE` /
  `DELETE` against production without wrapping in `BEGIN; ... ROLLBACK;`
  — it actually performs the write.

### Disclaimer footer

> I cannot run this query against your live database. Plans are
> data-and-config-dependent — re-run `EXPLAIN (ANALYZE, BUFFERS)` on
> prod-sized data, on both hot and cold caches, before declaring victory.
> Index changes slow writes; verify the win.

---

## Severity Model

Mirror the verdict banner:

- **⛔ Critical** — hot-path query returning > 10× the rows it needs, full
  scan on a > 10M-row table on a request path, long-running write
  blocking reads, or a data-corruption / lost-update risk (e.g. `OFFSET`
  over an evolving result set).
- **🟠 High** — missing index that would convert Seq → Index on a hot
  path; an N+1 in a production endpoint; a bad cardinality estimate
  driving wrong join order.
- **🟡 Medium** — over-indexing on a write-heavy table, Sort spilling to
  disk on an analytical query, `SELECT *` in a hot path.
- **🟢 Low** — style / maintainability (`COUNT(*)` vs `COUNT(1)` — both
  are equivalent in modern engines).
- **ℹ️ Info** — future-proofing notes (e.g. "partitioning is worth
  considering once this table crosses ~500M rows").

---

## Cross-links

This skill pairs with:

- **code-review-skill** — for the broader correctness/security pass on
  the surrounding code; SQL is one layer.
- **api-security-review** — when the slow query is on an API hot path
  and you also want to scope authz / rate limit / cost-budget risks.

---

*Educational engineering guidance, not a substitute for production APM,
slow-query monitoring, or a qualified DBA on critical systems. Not
affiliated with or endorsed by Anthropic.*