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.