clickhouse-autoresearch-campaign

What this skill does

# ClickHouse Autoresearch Campaign

This skill packages the orchestration for optimizing one ClickHouse query on one git branch.

## Required reads

Before taking action, read `orchestration.md` (sibling of this file) completely. Treat it as the operating contract.

## Preconditions

This skill assumes:

- `pi-autoresearch` is installed and its tools are available
- the current directory is a git repository
- you have a target query or enough context to identify one
- the operator will provide or help configure `.clickhouse-autoresearch/adapter.json`

## Branch rule

One campaign = one git branch.

If the current branch is not a dedicated campaign branch yet, create one before initializing the workspace.

## Workspace rule

Use a single workspace at:

```text
.clickhouse-autoresearch/
```

The branch is the campaign boundary. The workspace is just the artifact layout.

If `autoresearch.config.json` exists in the current working directory, read its `workingDir` field and use that path as the workspace instead of the default above. Automated orchestrators (for example PostHog's `run_campaign.py`) initialize the workspace at `/tmp/autoresearch-campaign/` and write the config alongside it.

## Pre-initialized workspace detection

**Before doing anything in the Setup sequence, check whether the workspace has already been prepared by an external orchestrator.** If the resolved workspace contains **all** of:

- `adapter.json`
- `baseline/metrics.json`
- `query/original.sql`

…then the workspace is pre-initialized. In that case:

- **Skip the entire Setup sequence (steps 1–6).** Do not ask the operator for a target query, connection details, or anything else — the orchestrator has already supplied them.
- Jump directly to step 7 of the Setup sequence (read the baseline and seed the first lanes and hypotheses), then continue with steps 8–9 and the normal campaign loop.
- Operate headlessly: at no point prompt the operator for input. If a decision requires judgment, apply the skill's default guidance and record the choice in `state.json` / `autoresearch.md`.

Only fall back to the interactive Setup sequence below when the workspace is empty or partially initialized.

## Adapter capabilities (what you can and cannot run)

Campaign queries flow through the adapter configured in `adapter.json`. Every campaign script (`ch_capture_baseline.py`, `ch_run_candidate.py`, any ad-hoc probe) ultimately submits SQL through this adapter, and the adapter enforces what ClickHouse sees.

When `adapter.json` has `type: "coordinator"`, your SQL is routed to whichever ClickHouse the host-side coordinator is pointed at — typically a read-only test cluster or a local dev ClickHouse. Either way the cluster runs SQL under a profile that pins `readonly = 2`, so writes (INSERT, ALTER, CREATE, OPTIMIZE, SYSTEM, TRUNCATE, DROP, ATTACH, DETACH) will fail with a ClickHouse error. **Read `GET /v1/info` (or check `autoresearch.md` — the coordinator's prompt addendum is prepended there)** before issuing any predicate that depends on a specific `team_id`: the prompt addendum tells you which `team_id` the target cluster has data for, and you must rewrite team_id predicates to match it. For experiments treat every read-only statement form as available:

- `SELECT …` — arbitrary subqueries, CTEs, joins
- `WITH … SELECT …`
- `EXPLAIN …` — every variant ClickHouse supports. Use them before proposing rewrites:
  - `EXPLAIN SELECT …` (default: PLAN)
  - `EXPLAIN AST SELECT …`
  - `EXPLAIN SYNTAX SELECT …`
  - `EXPLAIN QUERY TREE SELECT …` (post-analyzer logical tree; invaluable on modern ClickHouse)
  - `EXPLAIN PIPELINE SELECT …` — processor-level pipeline, headers, expressions
  - `EXPLAIN ESTIMATE SELECT …` — per-part row/mark estimates before execution
  - `EXPLAIN PLAN indexes = 1, actions = 1, json = 1 SELECT …` — primary-key and skip-index use, JSON for machine parsing
  - `EXPLAIN TABLE OVERRIDE …`
- `SHOW …` — `SHOW CREATE TABLE events`, `SHOW COLUMNS FROM events`, `SHOW INDEX FROM events`, `SHOW SETTINGS ILIKE '%mark_cache%'`, etc.
- `DESCRIBE` / `DESC …`

**Timeout**: every submission is wrapped with `SETTINGS max_execution_time = 60`. Keep ad-hoc probes short. If the target query itself routinely exceeds 60s, use range narrowing (see Setup step 6) and only then start the campaign.

**Cluster scoping**: depends on the coordinator's `target`. The prompt addendum prepended to `autoresearch.md` tells you which `team_id` the target cluster has data for and asks you to rewrite team_id predicates accordingly. Read the addendum first.

**Profiling ClickHouse's perspective**:

After a run, the campaign scripts capture client-side `elapsed_ms`, `rows_read`, `bytes_read`, and the server-minted `query_id` from the proxy response (persisted as `query_id` in `runs/run-XXXX-*/metrics.json` and `baseline/metrics.json`). The `autoresearch` CH user can read four `system.*` profiling tables for its own queries (RESTRICTIVE row policies hide every other user's rows):

| Table                     | What's in it                                                                                                                             |
| ------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| `system.query_log`        | One row per finished query: `query_duration_ms`, `read_rows`, `read_bytes`, `memory_usage`, `ProfileEvents` (map of low-level counters). |
| `system.query_thread_log` | Per-thread breakdown of the query: CPU time, wait time, peak memory per thread.                                                          |
| `system.text_log`         | Server-side log lines tagged with `query_id` (planner messages, mark-cache hits, merge-trigger).                                         |
| `system.trace_log`        | Sampled stack traces during query execution. Lit up by `query_profiler_real_time_period_ns`.                                             |

Look up a finished query's headline stats:

```sql
SELECT query_duration_ms, read_rows, read_bytes, memory_usage, ProfileEvents
FROM system.query_log
WHERE type = 'QueryFinish' AND query_id = '<query_id from metrics.json>'
ORDER BY event_time DESC
LIMIT 1
```

Useful `ProfileEvents` for query-perf work:

- `SelectedParts`, `SelectedRanges`, `SelectedMarks` — how much of the table the planner chose to read (lower = better-pruned predicates).
- `OSReadChars` vs `OSReadBytes` — disk-cache hit ratio.
- `RealTimeMicroseconds`, `UserTimeMicroseconds`, `SystemTimeMicroseconds` — wall vs CPU time (gap = waiting on I/O or locks).
- `Merge*`, `*Mark*`, `*PrimaryKey*` — index usage and compaction cost.

Per-thread to find tail latency:

```sql
SELECT thread_id, query_duration_ms, peak_memory_usage, ProfileEvents['OSReadBytes']
FROM system.query_thread_log
WHERE query_id = '<query_id>'
ORDER BY query_duration_ms DESC
```

Server-side log lines for a single query (planner choices, parts pruning, etc.):

```sql
SELECT event_time, level, logger_name, message
FROM system.text_log
WHERE query_id = '<query_id>'
ORDER BY event_time
```

Sampled stack traces (only useful if `query_profiler_real_time_period_ns > 0` was active for the run):

```sql
SELECT count(), arrayStringConcat(arrayMap(x -> demangle(addressToSymbol(x)), trace), '\n') AS frames
FROM system.trace_log
WHERE query_id = '<query_id>'
GROUP BY frames
ORDER BY count() DESC
LIMIT 20
```

`system.query_log` is readable under `readonly = 2`. Use it to compare ProfileEvents across iterations, not only wall-clock latency.

**Schema inspection** before proposing rewrites is free and often decisive:

```sql
SHOW CREATE TABLE events
DESCRIBE events
SELECT name, type, default_expression, codec_expression, is_in_primary_key, is_in_partition_key
FROM system.columns WHERE database = currentDatabase() AND table = 'events'
SELECT name, expr, type FROM system.data_skipping_indices WHERE table = 'events'
```

Use these