ClickHouse Query Analyzer Skill

This skill provides a structured workflow for analyzing and optimizing ClickHouse queries. When a user provides a query for analysis, follow these steps:

Step 1: Schema Assessment

Review the table definition to understand the physical data layout.

• •Tools: SHOW CREATE TABLE {table} or DESCRIBE TABLE {table}.
• •Checklist:
  • •Identify Partition Key: Is the query filtering by it?
  • •Identify Sorting Key (Primary Key): Is the query leveraging the prefix of the sorting key in WHERE/PREWHERE?
  • •Check Data Types: Are there LowCardinality strings or Nullable columns that could be optimized?

Step 2: Query Plan Analysis

Use ClickHouse's built-in EXPLAIN to see how the engine intends to execute the query.

• •Commands:
  • •EXPLAIN indexes=1 {query}: Check if any marks/parts are being skipped by indexes.
  • •EXPLAIN actions=1 {query}: See the detailed execution steps.
  • •EXPLAIN PIPELINE {query}: Check the level of parallelism.

Step 3: Runtime Performance Audit

If the query has been run, analyze its actual resource consumption.

• •Source: system.query_log.
• •Key Metrics to Inspect:
  • •read_rows vs result_rows: High ratio indicates inefficient filtering.
  • •read_bytes: Total I/O overhead.
  • •memory_usage: Peak memory consumed (crucial for large JOINs or Aggregations).
  • •query_duration_ms: Total latency.

Step 4: Common Optimization Strategies

Apply these patterns to improve performance:

1. •Leverage PREWHERE: Move filters on primary key columns or small columns to PREWHERE to prune data before reading large columns.
2. •**Avoid SELECT ***: Specify only necessary columns to minimize I/O in the columnar storage.
3. •Optimize Joins: ClickHouse prefers JOINs where the right-side table fits in memory. Consider using Dictionaries for high-performance lookups.
4. •Partition Pruning: Ensure filters on partition keys (usually time-based) are present to avoid scanning all data parts.
5. •Function Pushdown: Avoid wrapping columns in functions in the WHERE clause (e.g., use date >= '2023-01-01' instead of toYear(date) = 2023).

Analysis Report Format

When providing your analysis, structure it as follows:

1. •Summary: High-level assessment (e.g., "I/O bound", "Memory intensive").
2. •Schema Audit: Insights from table definition.
3. •Execution Plan: Insights from EXPLAIN.
4. •Bottlenecks: Specific causes of slowness.
5. •Recommendations: Numbered list of actionable SQL changes or schema improvements.