Skill for analyzing and optimizing ClickHouse queries in clickhouse-manager.
This skill provides a structured workflow for analyzing and optimizing ClickHouse queries. When a user provides a query for analysis, follow these steps:
Review the table definition to understand the physical data layout.
SHOW CREATE TABLE {table} or DESCRIBE TABLE {table}.WHERE/PREWHERE?LowCardinality strings or Nullable columns that could be optimized?Use ClickHouse's built-in to see how the engine intends to execute the query.
EXPLAINEXPLAIN 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.If the query has been run, analyze its actual resource consumption.
system.query_log.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.Apply these patterns to improve performance:
PREWHERE to prune data before reading large columns.JOINs where the right-side table fits in memory. Consider using Dictionaries for high-performance lookups.WHERE clause (e.g., use date >= '2023-01-01' instead of toYear(date) = 2023).When providing your analysis, structure it as follows:
EXPLAIN.