---
name: "sql_query_tuning"
description: "Diagnose and fix slow SQL queries: read EXPLAIN / EXPLAIN ANALYZE output, choose and verify indexes, and fix common performance anti-patterns (N+1, SELECT *, functions on indexed columns, leading-wildcard LIKE, OR without indexes, accidental cross joins). Use when a query is slow, when asked to 'optimize this SQL', 'add an index', 'read this EXPLAIN', or 'why is this query slow'."
metadata: { "includeInPrompt": true }
---

Differs from etl-pipelines because it fixes one slow query at a time (plan → hypothesis → index → re-measure), not the architecture of a data pipeline; and from data-validation because it is about query speed, not data correctness.

# SQL Query Tuning

## Purpose
Turn a slow query into a measured, verified fix: reproduce → read the plan → hypothesize → change → re-measure, with every number observed, not guessed.

## Workflow

### Step 0 — Know your dialect before you start
- **SQLite:** use `EXPLAIN QUERY PLAN <query>;`. Read lines like `SCAN orders` (full scan) vs `SEARCH orders USING INDEX idx (col=?)` (index seek). The bundled SQLite 3.45.1 build rejects `EXPLAIN ANALYZE` (verified locally — syntax error), so time queries manually (see helper script below). `USE TEMP B-TREE FOR ORDER BY` means an explicit sort step.
- **Postgres:** use `EXPLAIN (ANALYZE, BUFFERS) <query>;` — actual row counts, ms, and buffer hits. `Seq Scan` ≈ SQLite's `SCAN`; `Index Scan` / `Index Only Scan` ≈ `SEARCH USING INDEX`. (Postgres specifics below are standard documented behavior; only the SQLite numbers in this skill were measured locally.)

### Step 1 — Reproduce the slow query in isolation
Run the exact query alone, 3–5 times, and take the best (median-ish) wall time. Cold cache inflates run 1; report the steady-state number and say which you used.

### Step 2 — Read the plan
Run `EXPLAIN QUERY PLAN` (SQLite) or `EXPLAIN (ANALYZE, BUFFERS)` (Postgres) and answer three questions:
1. **Where is the row explosion?** Look for the step with the biggest row count or a `SCAN`/`Seq Scan` over a large table.
2. **Is there an index seek?** `SEARCH ... USING INDEX` good; bare `SCAN` on a big table bad. `SCAN ... USING COVERING INDEX` is still a scan — it reads the whole index instead of the table, cheaper but not a seek.
3. **Is there a sort or hash step?** `USE TEMP B-TREE FOR ORDER BY` / `Sort` / `Hash Join` on large inputs is where time hides when the plan looks "indexed" but still slow.

### Step 3 — Form a hypothesis, then change ONE thing
Pick one fix from the index rules or anti-pattern table below. Make the change, re-run the plan, re-run the timing. If the plan improved but time didn't (or got worse), the hypothesis was wrong — roll back and try the next one.

**Index rules (B-tree basics):**
- Equality columns first in a composite index, range/sort columns after: `CREATE INDEX i ON orders(region, created_at)` serves `WHERE region=? AND created_at>=?`.
- A *covering index* (all selected columns in the index) can answer the query without touching the table — watch for `USING COVERING INDEX`.
- An index helps when it is **selective** (matches a small fraction of rows). An index that matches 25% of the table can be slower than a full scan — always re-measure.
- Indexes cost writes: every `INSERT`/`UPDATE`/`DELETE` maintains each index. On write-heavy tables, fewer, narrower indexes.

### Step 4 — Re-measure and write the report
Fill the Output Contract below with observed numbers only. Keep the reproduction script so the fix can be re-verified.

## Worked example (all numbers measured locally, SQLite 3.45.1, 100k-row table)

Save as `qperf_setup.py`, then run it — it builds the table, runs the slow query, and prints plan + timings:

```python
import sqlite3, time, random
random.seed(42)
con = sqlite3.connect("/tmp/qperf.db")
con.execute("DROP TABLE IF EXISTS orders")
con.execute("""CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER,
  email TEXT, status TEXT, region TEXT, total_cents INTEGER,
  created_at TEXT, notes TEXT)""")
rows = []
for i in range(1, 100001):
    rows.append((i, random.randint(1,20000),
        f"u{random.randint(1,99999)}@example.com",
        random.choice(["pending","paid","shipped","refunded","cancelled"]),
        random.choice(["midwest","south","west","northeast"]),
        random.randint(100,50000),
        f"2026-{random.randint(1,9):02d}-{random.randint(1,28):02d} 10:00:00",
        "note-"+str(i)))
    if len(rows) == 10000:
        con.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?,?,?)", rows); rows = []
con.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?,?,?)", rows); con.commit()

def plan(q, p=()):
    return [r[3] for r in con.execute("EXPLAIN QUERY PLAN "+q, p)]
def best_ms(q, p=(), n=5):
    b = 1e9
    for _ in range(n):
        t = time.perf_counter(); con.execute(q, p).fetchall()
        b = min(b, (time.perf_counter()-t)*1000)
    return b

t = con.execute("SELECT email FROM orders LIMIT 1 OFFSET 50000").fetchone()[0]
q = "SELECT id, status, total_cents FROM orders WHERE email = ?"
print("gen+insert 100k rows done")
print("BEFORE:", plan(q,(t,)), "-> %.1f ms" % best_ms(q,(t,)))
c = time.perf_counter(); con.execute("CREATE INDEX idx_orders_email ON orders(email)"); con.commit()
print("CREATE INDEX: %.1fs" % (time.perf_counter()-c))
print("AFTER: ", plan(q,(t,)), "-> %.3f ms" % best_ms(q,(t,)))
```

Observed on this machine (table build: 1.0 s for 100k rows; index build: 0.1 s):

| | Plan | Time |
|---|---|---|
| Before | `SCAN orders` | 8.4–11.0 ms |
| After | `SEARCH orders USING INDEX idx_orders_email (email=?)` | 0.012–0.055 ms |

Roughly a 150–900× speedup for a point lookup — the canonical "missing index" fix.

**Selectivity caveat, also observed:** `WHERE region=? AND created_at>=? ORDER BY total_cents DESC LIMIT 50` with a composite index on `(region, created_at)` produced the "right" plan — `SEARCH orders USING INDEX idx_orders_region_created (region=? AND created_at>?)` — but ran **slower** (21–33 ms) than the no-index full scan (11.9 ms), because `region='west'` matched ~25k of 100k rows and the temp B-tree sort dominated. Same table, selective predicate `customer_id=123` (few rows): 0.01 ms via `SEARCH`. The plan is a hypothesis; the timer is the verdict.

## Anti-patterns: before / after (each verified locally unless noted)

**1. Function on an indexed column.** `WHERE lower(email) = ?` with a plain index on `email` gave `SCAN orders USING COVERING INDEX idx_orders_email` — 14.2 ms. Fix: expression index `CREATE INDEX idx_orders_email_lower ON orders(lower(email))` → `SEARCH orders USING INDEX idx_orders_email_lower (<expr>=?)` — 0.012 ms. (Postgres supports the same; alternatively store a normalized column.)

**2. Leading-wildcard LIKE.** `WHERE email LIKE '%hamilton%'` → full scan, 12.4 ms. A leading `%` cannot use a B-tree. Fixes, in order of preference: full-text index (Postgres `pg_trgm` GIN — documented, not measured here), restructure the search, or accept the scan on small tables. Note: on this SQLite build even the trailing-wildcard `LIKE 'barbara%'` stayed a covering-index scan — verify your engine, don't assume.

**3. N+1 queries.** 500 parent rows, one child query each (501 round trips): 29.7 ms → one `WHERE customer_id IN (...)` batch: 9.0 ms, same 4512 rows, 3.3× locally. Across a real network the gap is dominated by round-trip latency and is typically far larger — that multiplier is an expectation, not a measurement. Fix: `JOIN` or `IN` batching; in ORMs use eager loading (`select_related`/`preload`).

**4. OR across columns, one side unindexed.** `WHERE email=? OR status=?` (only `email` indexed) → `SCAN orders`, 20.6 ms. When both sides are indexed SQLite used `MULTI-INDEX OR` (two seeks merged); Postgres uses bitmapOr the same way (documented). If your engine won't merge, rewrite as `... UNION ALL ...` — verified to produce two seeks.

**5. SELECT *.** `SELECT *` over 25k rows: 88.1 ms vs `SELECT id, status`: 50.2 ms on the same rows. Selecting only needed columns also enables covering-index plans. Real gap scales with row width and network.

**6. Accidental cross join / missing JOIN condition.** If row counts multiply (plan shows nested loops over two large scans with no `USING INDEX` on the join key), check the `ON` clause. Fix: add the missing condition; then index the foreign-key column (`CREATE INDEX ... ON child(parent_id)`) so the join becomes a seek per outer row.

**7. Index on a write-heavy table.** 1000 single-row inserts: 0.005 s with 1 index vs 0.009 s with 5 indexes (~1.8×) on this box. Small tables hide the cost; on hot tables, unused indexes are pure write tax — drop indexes no plan ever uses.

## Output Contract

Write the tuning report in this format. Every number must be observed; mark estimates as estimates.

```
QUERY: <the SQL, verbatim>
BASELINE: plan=<key EXPLAIN lines> time=<best of N runs, N stated> rows=<rows returned>
DIAGNOSIS: <one sentence: what the plan shows and why it is slow>
CHANGE: <exact DDL or rewritten SQL applied>
AFTER: plan=<key EXPLAIN lines> time=<best of N runs> rows=<must equal baseline>
VERDICT: <fixed / partially improved / no improvement — and what to try next if not fixed>
CAVEAT: timings are from <engine + version> on <this machine>; they are relative, not
production predictions. Re-run EXPLAIN on production data volume before shipping an index.
```

## Operating Rules

1. Never claim a speedup you did not measure. Best-of-N runs, same machine, same data; state N.
2. Re-run `EXPLAIN QUERY PLAN` / `EXPLAIN (ANALYZE, BUFFERS)` after every change — a better-looking plan that is slower is a failed hypothesis, not a win.
3. Change one thing at a time (one index, one rewrite). Compound changes can't be attributed.
4. Row counts must match before and after; a "faster" query that returns different rows is a bug.
5. Label the dialect on every plan snippet (`SQLite` vs `Postgres`) — plan output is not portable.
6. Prefer the smallest index that fixes the query: fewer columns, composite only when the query needs it, drop indexes no query uses.
7. On write-heavy tables, weigh read gains against insert/update cost before adding indexes.
8. Do not invent flags, functions, or syntax. When unsure an engine supports something (e.g. SQLite's `EXPLAIN ANALYZE`), test it on the spot and report what actually happened.