Tune a Slow Query with EXPLAIN-Verified AI Proposals

Tier: Core · Workflow area: SQL Authoring & Query Tuning · ~35 minutes

AI is a superb tuning hypothesis generator and an unreliable tuning authority. It has read every optimization pattern ever blogged, and it cannot see your data distribution, your write load or your version's optimizer quirks. This playbook wires the strength to the weakness: broad hypothesis generation, ruthless per-proposal verification, and a written verdict table.

When to use

• A specific query is provably slowyou have the statement, its actual plan, and a latency number that matters to someone. Targeted tuning is where the loop shines.
• You are out of first-guess ideasthe obvious index exists, the plan still disappoints. AI's breadth (rewrites, prefetch-friendly orderings, partial indexes, statistics targets) beats memory.
• A tuning decision needs a defensible paper trailthe verdict table doubles as the change-ticket evidence.

When NOT to use

• The system is slow, not a queryfleet-wide latency means waits, config or hardware first (pb-07, pb-08). Tuning one query during an I/O storm is rearranging deck chairs.
• You cannot test on a copyif the only place to try proposals is production, stop and build a staging copy or use hypothetical indexes only. AI proposals unverified in prod is the anti-pattern this track exists to kill.
• The query is wrong, not slowverify correctness first (pb-01). A fast wrong answer is worse than a slow right one.

The recipe

1. 1.

   Assemble the evidence pack. The model's proposal quality tracks the quality of its inputs. Provide all four:

   Copy

   -- 1. The query, exactly as the app runs it
   -- 2. The actual plan
   EXPLAIN (ANALYZE, BUFFERS) <query>;
   -- 3. DDL + existing indexes for every table involved
   SELECT indexdef FROM pg_indexes WHERE tablename IN ('orders','order_lines');
   -- 4. Table sizes and stats freshness
   SELECT relname, n_live_tup, n_dead_tup, last_autoanalyze
   FROM pg_stat_user_tables WHERE relname IN ('orders','order_lines');
   

2. 2.

   Ask for ranked, falsifiable hypotheses. The prompt shape that works demands predictions, because a prediction can be checked:

   Copy

   You are a PostgreSQL 16 tuning engineer. Evidence pack below: query,
   actual EXPLAIN (ANALYZE, BUFFERS) plan, DDL, index list, table stats.
   
   Produce up to 5 tuning hypotheses, ranked by expected impact. For each:
   - The change (rewrite, index DDL, statistics change) — exact SQL.
   - WHY, tied to specific plan nodes and numbers from my plan.
   - PREDICTION: what the new plan should look like and roughly how
     buffers/time should move.
   - RISK: what this could make worse (writes, other queries, memory).
   Do not recommend config changes. Do not assume columns exist
   beyond the DDL provided.
   

3. 3.

   Test index hypotheses hypothetically first. hypopg lets the planner evaluate an index that costs nothing to "create":

   Copy

   CREATE EXTENSION IF NOT EXISTS hypopg;
   SELECT hypopg_create_index(
     'CREATE INDEX ON order_lines (order_id) INCLUDE (amount)');
   EXPLAIN <query>;   -- does the planner even choose it?
   SELECT hypopg_reset();
   

   If the planner ignores the hypothetical index, the proposal is dead — no build cost paid, verdict recorded.

4. 4.

   Verify survivors on a copy with before/after numbers. For each proposal that passes the hypopg screen (and every rewrite), run the real comparison on a restored copy or staging clone with production-scale data:

   Copy

   -- BEFORE (3 runs, take the stable one, note buffers)
   EXPLAIN (ANALYZE, BUFFERS) <original query>;
   -- apply ONE change
   CREATE INDEX CONCURRENTLY idx_ol_order_amount
     ON order_lines (order_id) INCLUDE (amount);
   ANALYZE order_lines;
   -- AFTER
   EXPLAIN (ANALYZE, BUFFERS) <query>;
   

   One change at a time — two changes per test means an unattributable result.

5. 5.

   Fill the verdict table. This is the artifact. A real one looks like:

   Copy

   # | proposal                          | prediction        | result                    | verdict
   1 | covering index on order_lines     | idx-only scan,    | 3105ms -> 214ms,          | SHIP
     |                                   | buffers -90%      | shared read 412k -> 9k    |
   2 | rewrite NOT IN as anti-join       | hash anti-join    | 3105ms -> 2980ms, plan    | REJECT
     |                                   | replaces filter   | unchanged (already        | (no effect)
     |                                   |                   | transformed by planner)   |
   3 | index on orders(status, ordered_at)| nested loop keyed | 3105ms -> 4470ms — planner| REJECT
     |                                   | on status         | picked it, misestimated,  | (made it
     |                                   |                   | row explosion at join     | worse)
   

   Row 3 is why this playbook exists. Roughly one confident proposal in a handful degrades performance; without the before/after ritual it ships anyway, because it sounded right.

6. 6.

   Ship through change control with the evidence attached. The winning DDL goes to production as a normal change — CREATE INDEX CONCURRENTLY, off-peak, with the verdict table linked in the ticket and a rollback (DROP INDEX CONCURRENTLY) written down.

7. 7.

   Close the loop after deploy. Re-capture the production plan and the app-side latency the next business day. A tuning win that exists only in staging is a hypothesis, still.

Make it yours

• SQL ServerSSMS Copilot or raw LLM proposals go through the same gauntlet: SET STATISTICS IO, TIME ON for before/after, hypothetical indexes via DBCC AUTOPILOT-era tooling are fragile, so lean on the Database Engine Tuning Advisor for what-if and a restored copy for truth; watch for proposals that would duplicate an existing index with a different key order.
• MySQLno hypothetical indexes in mainline; use a copy with production-scale data and EXPLAIN ANALYZE before/after, and treat "invisible indexes" (ALTER TABLE ... ALTER INDEX ... INVISIBLE) as your cheap rollback: create visible, flip invisible if it misbehaves, drop later.
• Oracleuse SQL Tuning Advisor output as one more "AI-ish" proposal stream through the same verdict table; validate with DBMS_XPLAN.DISPLAY_CURSOR A-Rows before/after, and test index proposals as invisible indexes (CREATE INDEX ... INVISIBLE + OPTIMIZER_USE_INVISIBLE_INDEXES at session level).

Quality check

• Every proposal got a verdict — including the rejectsthe failure log is what trains your judgment about where this model overreaches on your workload.
• Write cost was weighed, not just read speeda new index taxes every INSERT/UPDATE forever. Check pg_stat_user_indexes a week later; an unused shipped index is a regression you created.
• The copy was production-scaleplans flip with data volume. A 10k-row staging table validates nothing about a 500M-row production table.
• No AI DDL ran anywhere unreviewedyou read every statement before executing it, even on the copy. Generated DDL has dropped the wrong index before.