# Expected Diagnosis: O11Y-2426677

## Root Cause

Expensive query alert triggered by a SELECT on `dh_account_basic` with filter on `last_update_timestamp` — the column lacks an independent index, forcing `IndexFullScan` on `idx_dh_account_basic_site_code_id` scanning ~52M rows (~100M keys). This concentrated read load on db-tikv-67/74, saturating CPU (90%/77%) and hitting EBS IOPS limits (33% throttled).

## Evidence Chain

1. **TiDB slow query logs**: expensive queries (query_time > 60s) on `dh_account_basic` with `last_update_timestamp` filter
2. **Execution plan**: `IndexFullScan` on wrong index, actRows=52M vs estRows=690K (76x divergence)
3. **TiKV metrics**: db-tikv-67 CPU 90%, db-tikv-74 CPU 77%, gRPC P99 2.8s/1.5s
4. **EBS IOPS**: db-tikv-67 throttled at 33% — disk I/O bottleneck compounding the read hotspot

## Causality

Missing index on `last_update_timestamp` -> optimizer chooses IndexFullScan on site_code prefix index -> scans ~52M rows / ~100M keys -> read traffic concentrated on db-tikv-67/74 -> CPU saturation + EBS IOPS throttling -> query_time=180s -> 100+ expensive queries in 1 min -> alert fires

## Severity

High — single query pattern saturated 2 TiKV nodes and caused cluster-wide expensive query alert.

## Recommendations

1. **Add index on `last_update_timestamp`** (or composite `(last_update_timestamp, birthday_secret)`)
2. **Run ANALYZE TABLE** to fix 76x statistics divergence
3. **SQL Binding** as interim measure while DDL is scheduled
4. **Review EBS IOPS** allocation for db-tikv-67/74

## Scoring Rubric

> Checklist for regression testing. The Agent must satisfy these to pass.

- [ ] Query TiDB slow query logs / expensive query logs as primary data source for expensive query alert
- [ ] Identify the specific expensive SQL (SELECT on dh_account_basic with last_update_timestamp filter)
- [ ] Filter out ANALYZE-related queries from the expensive query list
- [ ] Analyze execution plan and identify IndexFullScan as the bottleneck
- [ ] Identify missing index on `last_update_timestamp` as root cause
- [ ] Identify hot TiKV nodes (db-tikv-67/74) with CPU and IOPS evidence
- [ ] Recommend adding index as P0 fix