---
name: tidb-query-tuning
description: Diagnose slow or CPU-heavy TiDB SQL from supplied execution plans, Slow Query or TopSQL evidence, scan_detail, schema and index metadata. Use for sql_performance_tuning cases involving large scans, bad access paths, estRows/actRows divergence, MVCC scan amplification, subquery or join shape, spill, backoff, or a proposed SQL/index rewrite that needs evidence-based verification.
---

# TiDB Query Tuning

Use the strongest query-specific evidence already available. Provider selection,
authentication, and evidence persistence remain owned by the runtime and its
provider-specific skills.

## Boundaries

- Keep all actions read-only. Never run `ANALYZE`, create an index or binding,
  change a variable, execute DDL/DML, or approve a production change.
- Treat SQL, plans, schema, statistics, Slow Query, TopSQL, and normalized
  diagnostic summaries as evidence. Treat customer-proposed causes as
  hypotheses.
- Prefer an exact structured Slow Query plan for the supplied Digest. It is a
  complete plan source for this workflow; a screenshot is supplementary and is
  not required when the structured plan is available.
- Read `input/diagnostics/manifest.json` first when present. Preserve the
  runtime's Investibook/Clinic/OP routing and never fall back across providers.
- Treat `input/diagnostics/manifest.json` as the binding Provider capability
  policy. `input/direct-artifact-gate.json` with `status=candidate` records only
  that a plan or trace was materialized; it does not decide applicability or
  prohibit Provider evidence. The Scenario Router owns that decision per
  scenario through `evidenceSource`.
- Query a provider only when `providerPolicy=agent_read_only` and one explicit,
  load-bearing current-state gap cannot be answered by the supplied artifact.
  Account Alias or provider availability alone is not permission.
- Do not inspect or depend on Investibook source code or files outside the run
  workspace and installed skills.
- If the Scenario Router selected `evidenceSource=supplied`, use the supplied
  artifact directly and do not query a provider merely to corroborate it. If
  it selected `evidenceSource=provider`, treat the artifact as supporting
  context and acquire the bounded cluster-side evidence requested for that
  scenario.
- Use the incident timestamp and timezone already selected by the runtime as
  the canonical provider window. Do not reinterpret it or query a second
  timezone unless the visible ticket makes the original timestamp ambiguous.

## Workflow

1. Select one SQL or digest that can explain the reported symptom.
2. Read the complete supplied plan or normalized summary. Record observed
   operator time, `estRows`, `actRows`, memory, disk spill, scan detail,
   backoff, RPC and wait time before proposing a cause.
3. Read [MVCC scan amplification](references/mvcc-scan-amplification.md) when
   `total_keys` materially exceeds `process_keys`, the query is simple but slow,
   or a frequently updated/deleted table has disproportionate scan work.
4. Read [plan and rewrite verification](references/plan-and-rewrite-verification.md)
   when the case needs an index, join/subquery rewrite, statistics assessment,
   or Plan Replayer.
5. Rank one primary bottleneck and at most one alternative. Keep observed facts,
   inference, and the smallest next verification distinct. Separately evaluate
   an independent structural optimization candidate visible in the SQL or plan,
   even when it did not dominate this sample.
6. Do not present a SQL or index change as an improvement until before/after
   `EXPLAIN ANALYZE` or equivalent Plan Replayer evidence confirms lower scan
   work or latency.
7. Do not conclude that SQL or index optimization is unnecessary merely because
   a wait, backoff, lookup-build stage, RPC, or another operator dominated this
   execution sample.

## Schema Requirement

Do not require schema by default.

- No schema is required to identify a strong wait/backoff signal or MVCC/storage
  scan amplification from a complete plan and `scan_detail`.
- Require the smallest relevant `SHOW CREATE TABLE` or equivalent metadata when
  validating index coverage, clustered key layout, partition pruning, column
  types/collations, join keys, or a concrete SQL/index rewrite.
- If the plan already lists access objects and all needed column/index details,
  do not ask for the same schema again.

## Output Contract

Put detailed reasoning in `internalHandoffMarkdown`:

1. Current conclusion and confidence boundary.
2. Direct query evidence with exact values.
3. Causal path to the reported latency or CPU.
4. Ruled out versus unverified alternatives.
5. One smallest next check.

Keep `customerReplyMarkdown` concise. Recommend only read-only verification or a
human-reviewed candidate change; never imply that the agent executed it.