sql_query_tuning v1.0.0

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):

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.