Plan And Verify

Plan-and-Verify

Use this discipline for any task that spans 3 or more distinct actions. The goal is to avoid the two most common agent mistakes: stating a finding before it is confirmed, and losing track of what has actually been done.

When to Plan

Use update_plan when the work genuinely has multiple phases:

• Investigations: 3+ queries or tool calls needed to reach a conclusion
• Changes / recommendations: any action where a wrong answer has real cost (e.g., index advice, setting change, schema alter)
• Anomaly diagnosis: root cause requires cross-checking multiple signals
• Multi-table analysis: correlating data across system tables or time windows

Skip it for single-shot answers — if one query call settles the question, call it and respond directly. update_plan exists to make complex work transparent, not to add ceremony to simple requests.

Using the update_plan Tool

Call once up front to lay out the full plan. Set the first step to in_progress and every other step to pending (omitting status defaults to pending):

update_plan(steps=[
  { title: "Scan query_log for slow queries (last 24 h)", status: "in_progress" },
  { title: "Check merge backlog on affected tables" },
  { title: "Verify finding against a narrower time window" },
  { title: "Summarize confirmed findings" },
])

Call again after each step completes to advance the checklist. Mark the finished step completed, the next one in_progress, and leave the rest pending:

update_plan(steps=[
  { title: "Scan query_log for slow queries (last 24 h)", status: "completed" },
  { title: "Check merge backlog on affected tables",       status: "in_progress" },
  { title: "Verify finding against a narrower window" },
  { title: "Summarize confirmed findings" },
])

Rules:

• Exactly one step is in_progress at any moment.
• Keep plans to ≤ 7 steps. If something needs more, it is two separate tasks.
• Titles are action-oriented and short (≤ 140 chars): "Scan query_log …", "Run EXPLAIN on both versions", "Cross-check baseline".
• Add a note field when the current status needs a one-line callout: note: "High merge backlog confirmed — checking root cause".

The VERIFY Discipline

Produce a result. Then confirm it before reporting it. "Looked right" is not verification.

Data findings — re-run a tighter query

A wide-window query identifies a candidate. Before calling it a finding, re-run against a narrower window or a second system table to confirm the signal is real and not an artifact of the aggregation window.

-- Initial: top tables by read_bytes, last 7 days
SELECT tables[1] AS tbl, avg(read_bytes) FROM system.query_log
WHERE event_date >= today() - 7 AND type = 'QueryFinish'
GROUP BY tbl ORDER BY avg(read_bytes) DESC LIMIT 10

-- Verify: same table, last 1 day — does the pattern hold?
SELECT tables[1] AS tbl, count(), avg(read_bytes)
FROM system.query_log
WHERE event_date = today() AND type = 'QueryFinish' AND tables[1] = '<candidate>'
GROUP BY tbl

If the narrower window contradicts the wide one, report the discrepancy — do not average the two.

Query rewrites — compare with EXPLAIN

Never claim a rewrite is "faster" without evidence. Use explain_query on both the original and the rewrite. Compare rows_read estimates. Report the ratio, not just "better".

explain_query(query="SELECT ... -- original")
explain_query(query="SELECT ... -- rewrite")

If rows_read is identical, the rewrite does not improve scan cost — say so even if the SQL looks cleaner.

Settings / schema recommendations — state the measurement

A recommendation without a measurable effect is a hypothesis. Every recommendation must include:

1. The expected effect (e.g., "reduces part count in this partition from ~800 to ~200 over one merge cycle")
2. How the user can measure it: the exact query or metric to check before and after

Example: recommending a lower merge_max_block_size:

Expected: smaller memory peaks per merge. Measure: SELECT max(memory_usage) FROM system.merges before and ~30 min after applying the setting.

Anomaly claims — confirm baseline and signal-to-noise

Before flagging a metric as anomalous:

1. Establish the baseline window (e.g., same hour yesterday, or last 7-day average).
2. Confirm the current value exceeds the baseline by a meaningful margin (not just rounding noise).
3. Check whether the anomaly is isolated to one host or cluster-wide.

-- Baseline: avg query duration same hour yesterday
SELECT avg(query_duration_ms) FROM system.query_log
WHERE type = 'QueryFinish' AND event_time BETWEEN yesterday() + INTERVAL 14 HOUR AND yesterday() + INTERVAL 15 HOUR

-- Current: same hour today
SELECT avg(query_duration_ms) FROM system.query_log
WHERE type = 'QueryFinish' AND event_time >= now() - INTERVAL 1 HOUR

If the current value is 1.05× the baseline, it is noise. If it is 4×, it is a finding.