Lab 02 · Plan-cache triage with AI annotation

Arvind inherited a "the database is slow" ticket with no query attached — the wholesale version of tuning, where the unit of work is the workload, not the query. The sp_BlitzCache @AI pattern from the SQL Server world generalizes cleanly to Postgres: statistics view in, per-query LLM annotation out, human prune in the middle. The prune is the deliverable. An annotation pass over ten queries reliably produces false leads — advice that ignores call frequency, indexes a tiny table, or optimizes a query whose real crime is being called 200,000 times — and this workload has those false leads planted.

Time & tier

~75 minutes hands-on · Core tier (maps to module M1 · Verification-First AI Tuning & Diagnostics).

Setup

Reuse the gauntlet database from Lab 01 (schema and 1M-row orders table), or re-run its seed block. Enable statement tracking:

alter system set shared_preload_libraries = 'pg_stat_statements';

docker restart tuning-lab && sleep 5
docker exec -it tuning-lab psql -U postgres -d gauntlet \
  -c "create extension pg_stat_statements"

Add one small lookup table the workload will hammer:

create table status_labels (
  status text primary key,
  label  text not null
);
insert into status_labels values
  ('delivered','Delivered'), ('shipped','In transit'), ('failed','Failed');

Save workload.sh — a five-round replay mixing genuinely bad queries with high-frequency cheap ones:

#!/usr/bin/env bash
PSQL="docker exec -i tuning-lab psql -U postgres -d gauntlet -q"
$PSQL -c "select pg_stat_statements_reset();"
for round in 1 2 3 4 5; do
  # W1: unanchored search — genuinely bad (leading wildcard, seq scan)
  $PSQL -c "select count(*) from orders where customer_ref like '%42%';"
  # W2: per-city aggregate with a cast join — moderately bad
  $PSQL -c "select c.city, avg(o.amount) from orders o
            join customers c on o.customer_ref = c.customer_id::text
            group by c.city;"
  # W3: cheap query, brutal frequency — 400 calls/round
  for i in $(seq 1 400); do
    $PSQL -c "select label from status_labels where status='delivered';" >/dev/null
  done
  # W4: sort spill — order the world to show three rows
  $PSQL -c "select * from orders order by amount desc limit 3;"
  # W5: date-function-on-column — index-defeating by construction
  $PSQL -c "select count(*) from orders
            where date_trunc('day', placed_at) = date_trunc('day', now());"
done
echo "replay complete"

Run bash workload.sh (a few minutes — W3's loop is the point, not a bug).

Guided steps

1. 1.

   Pull the offender list two ways. Total time is not the only axis that matters, so export both orderings:

   Copy

   select left(query, 70) as q, calls,
          round(total_exec_time::numeric, 1) as total_ms,
          round(mean_exec_time::numeric, 2)  as mean_ms,
          rows, shared_blks_read + shared_blks_hit as blks
   from pg_stat_statements
   where query not like '%pg_stat%'
   order by total_exec_time desc limit 10;
   

   Then the same with order by mean_exec_time desc. Expected: W1/W2/W5 dominate mean time; W3 climbs the total-time board on calls alone (2,000 calls of a sub-millisecond query). Save both as offenders.md.

2. 2.

   Annotate with the LLM — one query per row, stats attached. For each of the top eight, feed your assistant the query text plus its calls / mean_ms / total_ms / rows / blks row and the table DDL, with the instruction: "One paragraph: likely bottleneck, one proposed fix, and what the fix could make worse. Do not assume call frequency is a problem you can fix in the database." Collect the eight annotations verbatim into annotations.md — no editing, no cherry-picking. This is the raw material the prune works on.

3. 3.

   Prune every row: accept / reject / needs-testing. One line of reasoning per verdict, written next to the annotation. Hunt specifically for these planted false leads: advice to index status_labels (three rows — it has a primary key and lives in cache; any index advice here is noise); advice to tune W3's query text (the query is fine — the fix is application-side caching or batching, which no SQL edit delivers); advice proposing an index on date_trunc('day', placed_at) for W5 (works, but rewriting the predicate to a range — placed_at >= date_trunc('day', now()) and placed_at < date_trunc('day', now()) + interval '1 day' — uses Lab 01's partial index with zero new write cost); and any trigram/pg_trgm proposal for W1 that omits the write and storage cost of a GIN index on a million-row hot table (that one is needs-testing, not accept). Expected outcome: roughly half the annotations survive as written.

4. 4.

   Pick the single highest-value fix and defend the choice. Highest value = total-time impact × confidence × cheapness. The intended winner is W5's predicate rewrite: big total time, zero-risk change, existing index. (W2's composite-index fix from Lab 01 is a legitimate alternate if you did not build it there.) Write two sentences in triage-sheet.md on why the winner beat the runner-up.

5. 5.

   Implement and measure the fix. Capture explain (analyze, buffers) for the old and new form of the chosen query. Expected for W5: seq scan (~100k+ buffer accesses) becomes an index scan on the partial index, with time dropping an order of magnitude. Paste both plans into the sheet.

6. 6.

   Prove it at the workload level. Reset statistics, re-run bash workload.sh with the fixed query edited into the script, and re-pull the top-10. Expected: the fixed query drops out of the top offenders; W3 still sits high on total time — correctly, because you rejected fixing it in the database. A triage sheet that "fixed" W3 with SQL is a failed prune.

7. 7.

   Finalize the triage sheet. triage-sheet.md: the two offender lists, eight annotation rows each with verdict + one-line reason, the implemented fix with before/after plans, and the re-run offender list proving the change landed. Add a two-line note for next on-call: which rejected annotations are traps that will be re-proposed by the next AI pass, and why they stay rejected.

Verify

• •
  offenders.md has both orderings, and you can state which query tops each and why they differ.
• •
  All eight annotations carry a verdict and a one-line reason — no blank accepts.
• •
  The three planted false leads (tiny-table index, W3 SQL "fix", function-index-instead-of-rewrite) are all rejected or downgraded with correct reasoning.
• •
  The implemented fix has before/after plans and a post-replay offender list showing it gone from the top.

Self-grading rubric (pass = 7/10): dual-axis offender pull (0–1); annotation pass complete and unedited (0–2); all three planted false leads caught with correct reasoning (0–3); W1's trigram advice marked needs-testing with the cost concern named (0–1); fix implemented with plan proof and workload-level re-measure (0–3).

Stuck?

• pg_stat_statements view is emptythe extension must be in shared_preload_librariesand the container restarted before create extension; re-check with show shared_preload_libraries; and re-run the replay after a reset.
• W3 does not appear in the top-10 by total timeyour loop probably ran against a warm connection pool faster than expected; either raise the inner loop to 800 iterations or lower the limit and confirm it appears by calls ordering, which is the finding either way.
• The two orderings look identicalyou replayed too few rounds; the separation between mean-time offenders and call-count offenders needs the full five rounds to show.
• Your assistant refuses to annotate without more contextgood instinct, wrong moment: give it the DDL from Lab 01's setup block, but resist pasting EXPLAIN output at this stage; the triage pass ranks hypotheses, and plans belong to the one fix you implement.
• The rewritten W5 still seq-scanscheck that Lab 01's partial index survived (\di orders*); if you skipped Lab 01, create a plain index on placed_at and note the write-cost caveat in the sheet.