Reading an EXPLAIN plan or a Spark UI is a skill most teams have in exactly one person. LLMs read them fluently: hand one the plan plus table statistics and it will spot the unpruned scan, the exploding join, the missing clustering — and propose fixes.
Tier: Core · Workflow area: Orchestration, ops & cost
The catch is that plausible tuning advice is cheap and wrong tuning advice costs real money, so this playbook wraps the AI diagnosis in the only loop that matters: measure before, change one thing, measure after.
Skip it for queries that are slow because they process what they must — AI can't optimize physics, and "make it faster" on an already-tight plan yields confident noise.
Capture the evidence, not a description of it. For warehouse queries: the query text, the actual execution plan or profile (post-execution profile beats estimated EXPLAIN — it has real row counts and spill), and table facts: row counts, partition/cluster keys, sizes. For Spark: the failing/slow stage's task-duration distribution, shuffle read/write sizes, spill metrics, and the job's config (executor memory/cores, AQE settings) — exported text/JSON from the Spark UI, not a screenshot description.
Baseline first. Record current runtime, bytes scanned, and cost (warehouse credit or cluster-minutes). Without the baseline the after-measurement is theater.
Prompt for ranked diagnoses tied to plan evidence.
Diagnose this slow query from the attached execution profile and table stats.
Rules:
- Cite the specific plan node / metric that supports each diagnosis
(e.g. "join node 7: build side 400M rows, estimated 10k — stats stale or
filter not pushed").
- Rank findings by expected impact; give ONE recommended change per finding.
- Distinguish query-level fixes (rewrite, filter pushdown, join order) from
table-level fixes (clustering/partitioning, stats refresh) — table-level
changes affect other workloads and cost money to maintain; say so.
- For each recommendation, state the metric that should move and by roughly
how much, so I can verify.
- If the plan looks already-optimal, say so instead of inventing changes.
For Spark, the same shape, with the fix vocabulary being broadcast hints vs. sort-merge, key salting for skew, partition-count changes, AQE toggles, and memory config — and the same demand: every diagnosis cites a metric (e.g. "max task 48 min vs. median 40 s in stage 12 → skew on join key").
Apply one change, re-measure. One variable at a time, same data volume (rerun on the same partition/date), compare runtime + bytes scanned + spill against the baseline. If the predicted metric didn't move, the diagnosis was wrong — revert and take the next ranked finding back to the model with the new evidence. This loop usually converges in one to three iterations.
Check the blast radius of table-level changes. Reclustering, repartitioning, or changing file sizes helps this query and taxes others (and clustering has ongoing maintenance cost). Before adopting, test the top other consumers of the table, and re-run the cost math over a week, not one execution.
Bank the result. Record before/after numbers in the PR/ticket and add the pattern (e.g. "date filter must be on the partition column, not a function of it") to your team's SQL review notes — the LLM finds instances; the pattern note prevents recurrences.
Give the model the executed profile with actual row counts, not the estimated EXPLAIN. Half of real-world slowness is actual-vs-estimate divergence (stale stats, correlated filters), and the model can only see that when both numbers are in front of it.
SELECT count(*), sum(hash(...))-style checksums on before/after outputs — a fast wrong query is still wrong.Query text and plans reveal schema design and business logic; table stats reveal scale — treat all of it as client-confidential. Where external assistants aren't approved, use the platform-native option inside the client's tenancy (Genie on Databricks, the warehouse's own copilot) so nothing leaves the boundary; otherwise sanitize (rename tables/columns to neutral placeholders consistently — plan structure survives renaming) before prompting an external model, and keep the measurement loop entirely client-side. Report improvements in the client's currency — credits or cluster-hours saved per month at current volumes, with the before/after evidence — because "the query is faster" is an anecdote and "₹80k/month off the warehouse bill" is a deliverable.