Optimize Clickhouse Sql

---
name: optimize-clickhouse-sql
description: Optimize slow queries, analyze SQL performance, and collect evidence for expensive workloads.
metadata:
  author: System
  show-in-sql-editor-quick-action: true
---

# SQL Optimization Skill

Workflow is evidence-driven: collect evidence with tools, then recommend based on evidence only.

## Pre-flight Check

1. **HAS SQL**: Conversation contains a SQL query -> Go to WORKFLOW step 2 (Collect Evidence).
2. **HAS QUERY_ID**: Conversation contains query_id -> Go to WORKFLOW step 2 (Call `collect_sql_optimization_evidence` immediately).
3. **DISCOVERY REQUEST**: User asks to optimize the slowest/heaviest queries but does not provide SQL/query_id -> Go to WORKFLOW step 1 (Discovery).
4. **NEITHER**: Call `ask_user_question` with exactly one question:
   - `header`: `Please provide one of the following for optimization`
   - `options`:
     - `{ "id": "sql", "label": "Provide SQL", "input": "text" }`
     - `{ "id": "query_id", "label": "Provide query_id", "input": "text" }`
     - `{ "id": "resource", "label": "Find the query that consumes the most", "input": "select", "choices": ["duration", "cpu", "memory", "disk"] }`
   After the tool returns:
   - If `optionId` is `sql`, treat `value` as the SQL text and continue with evidence collection.
   - If `optionId` is `query_id`, treat `value` as the query_id and continue with evidence collection.
   - If `optionId` is `resource`, treat `value` as the ranking metric and run discovery for the top 1 query in the last 1 day before continuing.

## Discovery

- Prefer `search_query_log` for discovery from `system.query_log` (slowest, most expensive, user-scoped, database-scoped, text-scoped, etc.).
- If `search_query_log` cannot express the request, then load the `clickhouse-system-queries` skill, immediately call `skill_resource` for `references/system-query-log.md`, and follow that reference strictly.
- Do NOT write ad-hoc SQL against `system.query_log` from this skill when `search_query_log` can satisfy the request.
- Extract `query_id` from the discovery results for the next step (evidence collection).

## Time Filtering

- `time_window`: Relative minutes from now (e.g., 60 = last hour).
- `time_range`: Absolute range `{ from: "ISO date", to: "ISO date" }`.
- When calling `collect_sql_optimization_evidence` after discovery, you MUST pass the same time_window or time_range used in discovery.

## Mode Selection

- Default `collect_sql_optimization_evidence` to light mode for the first pass.
- Prefer omitting the `mode` argument entirely unless full detail is required.
- Use `mode: "full"` only when the user explicitly asks for detailed/raw evidence or the light pass is insufficient.
- Do not choose `full` just because the request says "optimize", "analyze", or "investigate".

## Workflow

1. **Discovery (if needed)**: Prefer `search_query_log` to find candidates. If the request exceeds the tool's schema, then load `clickhouse-system-queries`, load `references/system-query-log.md` via `skill_resource`, and use that reference. Extract `query_id` from the results.
2. **Collect Evidence**: Call `collect_sql_optimization_evidence` with query_id (preferred) or sql (and same time params if coming from discovery).
3. **Analyze**: Review evidence for optimization opportunities.
4. **Recommendations**: Rank by Impact/Risk/Effort. Prefer low-risk query rewrites first.
5. **Validate**: Use `validate_sql` for any proposed SQL changes. Add inline comments (`-- comment`) to highlight key changes.

## Table Schema Evidence

- Use table_schema fields: columns, engine, partition_key, primary_key, sorting_key, secondary_indexes.
- When `optimization_target` is present, treat it as the real local-table schema behind a `Distributed` table and base key/index recommendations on it.
- Suggest secondary indexes only when evidence shows frequent WHERE filters on selective columns and the index type fits the predicate.
  - Use `minmax` for range predicates on sorted columns.
  - Use `set` for low-cardinality equality filters.
  - Use `bloom_filter` for high-cardinality equality filters (e.g., trace_id, user_id).
  - Use `tokenbf_v1` for frequent token-based text search.

## Rules

- Do NOT recommend based on assumptions. If evidence is missing, collect it with tools.
- If tools return NO meaningful evidence, output only a brief 3-5 sentence message explaining what's missing.
- Always validate proposed SQL with `validate_sql` before recommending.
- If discovery results include both query text and query_id, prefer query_id to avoid truncation issues.
- If the SQL appears incomplete (truncated/ellipsized/ends mid-clause), use `query_id` instead of sql.
- When both `query_id` and SQL are available, prefer `query_id` to reduce tokens and avoid truncation issues.