# Postmortem: O11Y-2426677 (Expensive Query — Missing TiDB Log Analysis)

> Workspace: `lark-ws-a7da5d80-9dce-456b-b947-627f49eb03df`
> Date: 2026-04-20
> Reviewer: claude-code
> Source: GitHub issue #248

## Issue Summary

| Field | Value |
|-------|-------|
| Issue ID | O11Y-2426677 |
| Alert Type | Expensive query (100+ queries > 60s in 1 min) |
| Cluster Type | dedicated |
| Time Window | 2026-04-19 09:01 UTC+8 |
| Environment | prod |
| User Feedback | "中间没找到合适的分析内容，第二次回答的内容丢失了" + "expensive query 是要找 tidb 对应日志的，并且去掉 analyze 相关 query" |

**Issue Description**: Lark auto-diagnosis for an expensive query alert. Agent performed a reasonable first-round diagnosis identifying missing index and hot TiKV nodes, but the user pointed out that for expensive query alerts, the agent should look at TiDB expensive query logs (slow query logs) directly and filter out ANALYZE-related queries. Additionally, the user's second reply triggered a response that was lost (not persisted).

## Actual Root Cause

The agent's diagnosis of the root cause (missing index on `last_update_timestamp` causing IndexFullScan) appears correct. The user's feedback was about the **diagnostic approach**, not the conclusion:
1. Agent should have queried TiDB expensive query logs (slow_query) to find the actual expensive queries, rather than relying primarily on TopSQL/metrics
2. Agent should have excluded ANALYZE-related queries from the expensive query list (ANALYZE appears in TopSQL but is not a user query)

## Agent Diagnosis Summary

> Lark auto-diagnosis, 0 human interventions (user feedback came after diagnosis).

The agent:
1. Collected metrics: TiKV gRPC P99, CPU, EBS IOPS
2. Identified hot nodes: db-tikv-67 (90% CPU), db-tikv-74 (77% CPU)
3. Found top SQL via TopSQL TiKV: `SELECT ... FROM dh_account_basic WHERE last_update_timestamp > ...`
4. Analyzed execution plan: IndexFullScan on `idx_dh_account_basic_site_code_id`, stats divergence 76x
5. **Concluded**: Missing index on `last_update_timestamp` causing full index scan of ~52M rows
6. Recommendations: Add index (P0), ANALYZE (P1), SQL binding (P2), EBS IOPS review (P3)

**What agent got right**:
- Correct root cause identification (missing index)
- Good execution plan analysis with actRows/estRows divergence
- Comprehensive recommendations with priority ordering
- Identified hot TiKV nodes and EBS IOPS throttling

**What agent missed**:
- Did not query TiDB expensive query logs (slow_query) — for expensive query alerts, this is the primary data source
- Did not filter out ANALYZE-related queries from the analysis
- Second response was lost (message persistence issue)

## Scores

| Dimension | Score (1-5) | Notes |
|-----------|-------------|-------|
| Root Cause Accuracy | 4 | Correct root cause (missing index), but approached via TopSQL instead of expensive query logs |
| Evidence Completeness | 3 | Good metrics + TopSQL + plan analysis, but missed TiDB slow query logs for expensive query alert |
| Causal Chain | 4 | Clear chain: missing index -> IndexFullScan -> hot TiKV -> expensive query alert |
| Actionability | 5 | Excellent: specific SQL for index creation, ANALYZE command, SQL binding, priority ordered |
| Efficiency | 4 | Single-round auto-diagnosis, no wasted tool calls; but used wrong primary data source |
| **Overall** | **4.0** | |

## Human Guidance Log

### Intervention 1: Use TiDB expensive query logs
- **Round**: #2 (after first diagnosis)
- **User said**: "expensive query 是要找 tidb 对应日志的，并且去掉 analyze 相关 query"
- **Agent's mistake**: Used TopSQL/metrics as primary data source instead of TiDB slow query logs for an expensive query alert
- **Correct behavior**: For "expensive query" alerts, first query TiDB slow_query logs filtered by `query_time > 60s` to find the actual expensive queries. Then filter out ANALYZE-related queries (they appear in metrics but are internal operations).
- **Skill**: `tidb-clinic-diagnosis`
- **Deficiency type**: FLOW

## Data Source Coverage

- [x] Metrics (TiKV CPU, gRPC latency, EBS IOPS)
- [x] TopSQL TiKV
- [ ] TopSQL TiDB
- [ ] SlowQuery logs / expensive query logs (MISSED - should be primary for this alert type)
- [x] Execution plan analysis (decoded_plan)
- [x] Logs (checked, none found)
- [x] Component health (no restarts)

## Lessons Learned

1. **Expensive query alerts should prioritize TiDB slow query logs** — the alert fires when queries exceed a duration threshold, so the natural diagnostic path is to check slow_query logs filtered by the threshold, not TopSQL which aggregates by CPU time.
2. **ANALYZE queries should be filtered out** — ANALYZE operations appear in TopSQL and slow query logs but are internal maintenance operations, not user workload. Agent should recognize and exclude them from the "problem query" list (relates to existing ic-001 pitfall).
3. **Message persistence issue persists** — user's second response was lost (ISS-0030), confirming this is a recurring production issue.