Agent skill · data analytics · jeremylongshore
clickhouse-performance-tuning
Optimize ClickHouse query performance with indexing, projections, settings tuning, and query analysis using system tables. Use when queries are slow, investigating performance bottlenecks, or tuning ClickHouse server settings. Trigger with "clickhouse performance", "optimize clickhouse query", "clickhouse slow query", "clickhouse indexing", "clickhouse tuning", "clickhouse projections".
Why this skill is useful
Provides specific ClickHouse tuning commands and performance optimization techniques that the AI wouldn't reliably generate on its own.
What it needs
About 2k tokens when loaded. Last updated 2026-08-07. 2,604 stars on the source repository.
What this skill does
ClickHouse Performance Tuning Overview Diagnose and fix ClickHouse performance issues using query analysis, proper indexing, projections, materialized views, and server settings tuning. Work top-down: measure first with system.querylog, then apply the single highest-leverage fix (usually the ORDER BY key), then re-measure to confirm. Prerequisites ClickHouse tables with data (see clickhouse-core-workflow-a) Access to system.querylog and system.parts Instructions The tuning workflow is seven independent steps. Diagnose first, then reach for the fix that matches the bottleneck. Each step's full SQL lives in references/implementation.md — start there for the complete, copy-paste commands. 1. Diagnose slow queries — rank the last 24h of system.querylog by querydurationms, then inspect a suspect query with EXPLAIN PLAN / EXPLAIN PIPELINE. 2. ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix skips whole granules; a mismatched key forces a full scan. 3. Data skipping indexes — bloomfilter for high-cardinality lookups, set for low-cardinality columns, minmax for range filters on non-key columns. 4. Projections — automatic pre-aggregation ClickHouse picks transparently when a query matches the projection's shape. 5. Server settings — maxthreads, external sort/group-by spill, asyncinsert, and friends, set per-query or per-session. 6. Materialized views — pre-aggregate on INSERT into an AggregatingMergeTree so dashboard reads hit milliseconds, not seconds. 7. Query patterns — PREWHERE, LIMIT BY, and avoiding FINAL. The essential first move — find the slowest queries: Output Applying this workflow produces: A ranked list of the slowest queries with their readrows / readbytes cost. One or more concrete schema/query changes: a corrected ORDER BY key, added data skipping indexes, a projection, a materialized view, or tuned session settings. …
How to use it
Reference it in AdaL, Claude Code, Cursor or any coding agent — nothing to install:
@skills jeremylongshore/clickhouse-performance-tuning