Slow Query Investigation: How an Agent Routes the Work

Most slow queries belong to two or three patterns. The agent that classifies, then routes to specialised remediation paths, and the eval suite for each path.

Classify the slow query

Pattern A: query has changed (new index missing, plan regression). Look at recent EXPLAIN diffs.

Pattern B: data has changed (table grew, statistics stale). Look at row counts and statistics age.

Pattern C: contention (lock waits, IO queue). Look at wait events.

Pattern D: external (slow downstream, slow disk). Look at correlations with infrastructure metrics.

Route to specialised remediation

Pattern A: index recommendation. Specific index DDL proposed; human approves; index applied during low-traffic window.

Pattern B: statistics refresh. Run ANALYZE on the table; cheap and usually safe.

Pattern C: lock contention. Identify the blocking session; surface the dependency chain; let humans decide whether to kill or wait.

Pattern D: escalate. The slow query is a symptom of an upstream problem; the database team is not the right owner.

Eval suite per path

Pattern A test: query with missing index. Agent identifies, proposes correct DDL.

Pattern B test: query slow due to stale stats. Agent identifies, proposes ANALYZE.

Pattern C test: query blocked by lock. Agent identifies blocker, surfaces the chain.

Pattern D test: query slow due to disk pressure. Agent identifies and escalates without proposing.

Audit the routing

Track which pattern was identified vs which was correct in production. Disagreement is signal: the classifier needs work.

Track time-to-resolution per pattern. Pattern B usually resolves fastest; Pattern D is the slowest.

Track human acceptance rate per pattern. High rejection on Pattern A often means the agent is suggesting indexes that have hidden costs (write amplification).

Trust path

Read-only proposals for the first 90 days. Operators see the proposed action and apply it manually.

Auto-apply Pattern B (ANALYZE) after 90 days; it is low-risk.

Pattern A (index DDL) auto-apply only after 180 days and only during defined windows. Index changes are not low-risk.