# Expected Diagnosis: GH-264 (Cross-Workspace Consistency for O11Y-2426677)

> This case tests cross-workspace consistency. Both workspace sessions analyzing the same ticket should reach the same correct conclusion.

## Root Cause

Missing index on `dh_account_basic.last_update_timestamp` causing `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), triggering the expensive query alert (100+ queries > 60s in 1 min).

## Evidence Chain

1. **TiDB slow query logs**: expensive queries (query_time > 60s) on `dh_account_basic` with `last_update_timestamp` filter (ANALYZE queries filtered out)
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
5. **Hot-node correlation**: TopSQL shows `dh_account_basic` query (digest `9C0FA76B`) running on db-tikv-67/74 — the exact hot nodes

## 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

## L3 Expert Recommendation

- **Add composite index** `(last_update_timestamp, birthday_secret)` on `dh_account_basic`
- **SQL Binding** as interim measure while DDL is scheduled

## Recommendations

1. **P0: Add composite index** `(last_update_timestamp, birthday_secret)` on `dh_account_basic` (L3 expert confirmed)
2. **P1: Run ANALYZE TABLE** to fix 76x statistics divergence (estRows=690K vs actRows=52M)
3. **P2: SQL Binding** as interim measure while DDL is scheduled
4. **P3: Review EBS IOPS** allocation for db-tikv-67/74

## Scoring Rubric

> Checklist for regression testing. The Agent must satisfy ALL items to pass.
> This rubric also serves as the cross-workspace consistency test: any workspace analyzing O11Y-2426677 must satisfy these.

- [ ] Query TiDB slow query logs / expensive query logs as primary data source for expensive query alert
- [ ] Filter out ANALYZE-related queries from the expensive query list
- [ ] Identify the specific expensive SQL (SELECT on `dh_account_basic` with `last_update_timestamp` filter)
- [ ] Correlate hot TiKV nodes (db-tikv-67/74) with the specific SQL causing load on those nodes
- [ ] Analyze execution plan and identify `IndexFullScan` as the bottleneck
- [ ] Identify actRows/estRows divergence (76x) indicating statistics or index problem
- [ ] Identify missing index on `last_update_timestamp` as root cause
- [ ] Recommend adding index (composite `(last_update_timestamp, birthday_secret)` preferred) as P0 fix
- [ ] NOT attribute the hotspot to a different SQL running on different nodes (e.g., `dh_message_setting` on db-tikv-30)

## Cross-Workspace Consistency Requirements

This case was created because two independent sessions produced different conclusions. The key consistency requirement is:

**Hot-node attribution rule**: When TopSQL shows multiple hot SQL patterns across different nodes, the agent MUST cross-reference which SQL is running on the specific hot nodes identified in metrics. The SQL with highest cpu_pct on any random node is NOT necessarily the SQL causing the hot nodes. The agent must:

1. Identify hot nodes from metrics (db-tikv-67/74 in this case)
2. Filter TopSQL results to those specific hot nodes
3. Rank by cpu_ms on those specific nodes, not by cpu_pct on unrelated nodes
4. Deep-dive the top SQL on the hot nodes (execution plan, actRows/estRows)