Lab 01 · EXPLAIN-verified AI query tuning gauntlet

Meera is the senior DBA at a Pune payments company. Her Postgres estate takes a steady write load, her ticket queue has three slow queries, and a well-meaning colleague has already pasted them into an AI assistant and forwarded the answer with "looks great, can we apply Monday?" Your job is Meera's job: turn that transcript into a verdict table where every row carries plan evidence — including the row that says rejected, made writes 40% slower.

Time & tier

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

Setup

Local only, $0. PostgreSQL 15+ (Docker or native) plus any AI chat assistant.

docker run -d --name tuning-lab -e POSTGRES_PASSWORD=lab -p 5433:5432 postgres:16
sleep 5
docker exec -it tuning-lab psql -U postgres -c "create database gauntlet"
docker exec -it tuning-lab psql -U postgres -d gauntlet

Seed the schema — 50k customers, 1M orders, one deliberately weak index, plus a legacy varchar foreign reference that will matter later:

create table customers (
  customer_id bigint generated always as identity primary key,
  full_name   text not null,
  city        text not null,
  segment     text not null,
  created_at  timestamptz not null
);
insert into customers (full_name, city, segment, created_at)
select 'Customer ' || g,
       (array['Pune','Mumbai','Chennai','Delhi','Kochi'])[1 + g % 5],
       (array['retail','sme','enterprise'])[1 + g % 3],
       now() - (g % 730) * interval '1 day'
from generate_series(1, 50000) g;

create table orders (
  order_id     bigint generated always as identity primary key,
  customer_ref varchar(20) not null,   -- legacy feed sends text ids
  status       text not null,
  amount       numeric(12,2) not null,
  placed_at    timestamptz not null
);
insert into orders (customer_ref, status, amount, placed_at)
select (1 + g % 50000)::text,
       case when g % 50 = 0 then 'failed'
            when g % 5  = 0 then 'shipped' else 'delivered' end,
       round((random() * 20000)::numeric, 2),
       now() - (g % 365) * interval '1 day' - (g % 86400) * interval '1 second'
from generate_series(1, 1000000) g;

create index idx_orders_status       on orders (status);
create index idx_orders_customer_ref on orders (customer_ref);
analyze customers; analyze orders;

The three ticket queries, saved as q1.sql, q2.sql, q3.sql:

-- Q1: revenue by city, last 30 days (dashboard, runs every 5 min)
select c.city, sum(o.amount) as revenue
from orders o join customers c on o.customer_ref = c.customer_id::text
where o.placed_at >= now() - interval '30 days' and o.status <> 'failed'
group by c.city order by revenue desc;

-- Q2: latest order amount per enterprise customer (account review)
select c.customer_id, c.full_name,
       (select o.amount from orders o
         where o.customer_ref = c.customer_id::text
         order by o.placed_at desc limit 1) as last_amount
from customers c where c.segment = 'enterprise';

-- Q3: order lookup by customer (support tool, hot path)
select * from orders where customer_ref::bigint = 4207;

And the forwarded AI transcript — four proposals you must adjudicate:

PROPOSAL A: create index idx_orders_placed_active on orders (placed_at)
            where status <> 'failed';  -- "Q1 will use this partial index"
PROPOSAL B: rewrite Q2 with a LATERAL join instead of the correlated
            subquery: "eliminates 16k repeated scans"
PROPOSAL C: create index idx_orders_covering on orders (status, placed_at)
            include (customer_ref, amount);
            -- "makes Q1 a pure index-only scan, biggest win available"
PROPOSAL D: create index idx_orders_ref_bigint on orders (((customer_ref)::bigint));
            -- "expression index makes Q3 instant without touching the app"

Guided steps

1. 1.

   Baseline every query before touching anything. Run each with full instrumentation and save the output — this is your "before" evidence:

   Copy

   \timing on
   explain (analyze, buffers) <query>;
   

   Expected shape: Q1 does a Parallel Seq Scan on orders (~100k+ shared buffer hits, several hundred ms); Q2 shows the correlated SubPlan executed ~16,600 times; Q3 does a Seq Scan even though idx_orders_customer_ref exists — the ::bigint cast on the column makes the index unusable. Record execution time and shared-buffers numbers for all three in verdicts.md.

2. 2.

   Build the evidence pack and run your own AI pass. Paste into your assistant: all three queries, the three baseline plans, the full DDL including existing indexes, row counts, and the workload line the colleague omitted — "orders takes ~800 inserts/sec at peak; Q1 runs every 5 minutes; Q3 is the hot path." Ask for ranked hypotheses with a mandatory could-make-worse note per proposal, then compare its output to the transcript. Note where the richer context changed the advice.

3. 3.

   Verify Proposal A (partial index on placed_at). Create it, analyze orders, rerun Q1 with explain (analyze, buffers). Expected: the planner switches to an index scan or bitmap scan on the partial index; execution time and buffer reads drop substantially versus your baseline. Record before/after numbers and verdict.

4. 4.

   Verify Proposal B (LATERAL rewrite of Q2). Write and run the rewrite:

   Copy

   select c.customer_id, c.full_name, o.amount as last_amount
   from customers c
   left join lateral (
     select amount from orders o
     where o.customer_ref = c.customer_id::text
     order by o.placed_at desc limit 1
   ) o on true
   where c.segment = 'enterprise';
   

   Expected: same row count and same values as the original (spot-check five customers), similar or modestly better plan — the real unlock needs a composite index (customer_ref, placed_at desc), which neither transcript proposal offers. Note that as your own counter-proposal, then create it and remeasure: the lateral probe becomes a short index scan per customer. Verdict with numbers.

5. 5.

   Verify Proposal C — and slow down here. Create the covering index, analyze, rerun Q1. Expected finding: the planner does not use it. status <> 'failed' is an inequality on the leading column, so the index cannot serve Q1's predicate; the plan still uses whatever won step 3. An index the optimizer ignores is not neutral — it is pure write tax. Prove the tax with a write benchmark, run once with Proposal C in place and once after dropping it:

   Copy

   \timing on
   insert into orders (customer_ref, status, amount, placed_at)
   select (1 + g % 50000)::text, 'delivered',
          round((random()*20000)::numeric,2), now()
   from generate_series(1, 50000) g;
   

   Expected: the 50k-row insert runs noticeably slower (typically 25–45% on this schema) with the covering index present. Record both timings. This is the proposal that fails the lab if your verdict table says accept.

6. 6.

   Verify Proposal D against the cheaper fix. The expression index does make Q3 fast — build it and confirm with a plan. But the correct fix is to stop casting the column: where customer_ref = '4207' uses the existing idx_orders_customer_ref with zero new write cost. Measure both, then record Proposal D as rejected — works, but a one-character query fix beats a permanent fourth index on an 800-writes/sec table. Drop the expression index.

7. 7.

   Clean up and finalize state. Drop Proposal C's index if you have not already, keep Proposal A's partial index and your (customer_ref, placed_at desc) composite, re-run all three queries once more, and capture the final plans as the "after" column.

8. 8.

   Write the tuning report. tuning-report.md with a verdict table — one row per proposal (A, B, C, D, plus your own composite-index counter-proposal): verdict (adopted / rejected / superseded), before ms and buffers, after ms and buffers, write-benchmark delta where relevant, and one line of reasoning each. Close with the paragraph you would send the colleague explaining why the "biggest win available" was the one you refused.

Verify

• •
  Baseline and final EXPLAIN (ANALYZE, BUFFERS) outputs exist for all three queries, with timings and buffer counts recorded.
• •
  Proposal C is rejected in the verdict table with two pieces of evidence: a plan showing the index unused for Q1, and the insert benchmark delta.
• •
  Proposal D is rejected in favor of the literal fix, with both plans captured.
• •
  Q2's rewrite returns identical results to the original (row count plus five spot-checked values).
• •
  The verdict table has five rows, each with before/after numbers — no verdict rests on the AI's reasoning alone.

Self-grading rubric (pass = 8/10): baselines complete and instrumented (0–2); Proposal A and B verified with numbers (0–2); Proposal C caught with both the unused-plan proof and the write benchmark (0–3); Proposal D adjudicated against the query fix (0–2); report readable by a change board (0–1).