# Perf: collect_evidence parallelization + A3b Parquet pushdown

**Date:** 2026-04-17
**PRs:** [#189](https://github.com/tidbcloud/investibook/pull/189), [#191](https://github.com/tidbcloud/investibook/pull/191)
**Affected file:** `agentkit/skills/builtin/tidb-clinic-diagnosis/scripts/collect_evidence.py`

---

## Problem

`collect_evidence.py` took ~58s per run, blocking agent diagnosis flow. A single workspace (`ws-1776402680190`) still took 40s after initial parallelization — traced to the A3b `decoded_plan` query taking 31s.

---

## Root Cause

### 1. Sequential API calls (58s → 11s)

The script made ~26 HTTP calls to Clinic APIs (Prometheus metrics, Athena SQL, Loki logs) **sequentially**. Most calls are independent and can run in parallel.

### 2. A3b `LOWER()` defeats Parquet pushdown (25s → 3s)

The `decoded_plan` query used:

```sql
WHERE LOWER(digest) LIKE LOWER('{prefix}%')
```

`LOWER()` wrapping the column defeats Athena's Parquet row-group statistics — every row group must be scanned. Result: **562 MB scanned, 25s**.

Additionally, TopSQL `sql_digest` is **uppercase 64-char** while `slow_query_logs.digest` is **lowercase 64-char** (same hash, different case). A direct `=` without case conversion returns empty.

---

## Fix

### PR #189: Parallelization

Rewrote `collect()` to use `ThreadPoolExecutor` with a 2-wave dependency graph:

- **Wave 1** (24 workers): All independent requests — A0 metadata, A1 infra (3), A2 metrics (16), A3 slow queries, A4 TopSQL (2), A6 Loki logs (9 sub-queries parallelized internally)
- **Wave 2** (2 workers): Dependent requests — A3b decoded_plan (needs A3+A4), A5 tikv_thread_cpu (needs A2)

Thread-safe timing collection via `_record_timing()` with lock. Stage-level progress to stderr (4 lines). Detailed per-query timing via `COLLECT_EVIDENCE_DEBUG=1` → `/tmp/collect_evidence_timing_*.txt`.

**Result: 58s → 11s (5.3x)**

### PR #191: Parquet predicate pushdown

Lowercase the digest in Python before the SQL query:

```python
plan_digest_lower = plan_digest.lower()
# ...
f"AND digest = '{plan_digest_lower}' "
```

No `LOWER()` function wrapper on the column → Athena can use Parquet row-group statistics for predicate pushdown.

**Result: 25s / 562 MB → 3s / 12 MB (8x faster, 47x less data scanned)**

---

## Benchmarks

### Overall collect_evidence

| Stage | Before | After (parallel) |
|-------|--------|-------------------|
| A0: cluster_meta | 1.8s | 1.8s |
| A1: infra (3 queries) | 7.8s | 2.8s (parallel) |
| A2: metrics (16 queries) | 21.3s | 3.2s (parallel) |
| A3: slow_query | 3.0s | 3.0s |
| A3b: decoded_plan | 25.0s | 3.0s (Parquet fix) |
| A4: TopSQL (2 queries) | 8.6s | 6.8s (parallel) |
| A5: tikv_thread_cpu | 3.2s | 3.2s |
| A6: Loki logs (9 queries) | 5.8s | 1.5s (parallel) |
| **Total** | **~58s** | **~11s** |

### A3b Athena query variants

| Query pattern | Time | Data scanned | Correct? |
|---|---|---|---|
| `LOWER(digest) LIKE LOWER('{prefix}%')` | 25s | 562 MB | Yes |
| `digest LIKE '{lower_prefix}%'` | 3.3s | 8.2 MB | Yes |
| `digest = '{lower_full}'` | 3.6s | 11.8 MB | Yes |
| `digest = '{UPPER_full}'` | 3.1s | 229 KB | No (case mismatch) |

Key insight: TopSQL stores uppercase, slow_query_logs stores lowercase. Same 64-char SHA256 digest, just different case. Python `.lower()` before the query gives exact match + Parquet pushdown.

---

## Verification

```bash
# Run locally with timing
CLINIC_API_KEY=... python3 collect_evidence.py <cluster_id> <start> <end>

# Enable debug timing
COLLECT_EVIDENCE_DEBUG=1 CLINIC_API_KEY=... python3 collect_evidence.py <cluster_id> <start> <end>
# Timing written to /tmp/collect_evidence_timing_<cluster_id>_<timestamp>.txt
```