When should I use the PREWHERE clause according to these guidelines?

PREWHERE should be used for highly selective filter conditions, as it reads only the filtered columns first to determine which rows need the full column set read.

How does this skill handle query performance troubleshooting?

It provides specific instructions for using EXPLAIN indexes and monitoring system.query_log to identify the ratio of granules read versus result set size.

Why does this skill recommend putting low-cardinality columns first in ORDER BY?

Placing low-cardinality columns first allows ClickHouse to more effectively prune data at the granule level, enabling the binary search algorithm to skip large sections of irrelevant data.

Can this skill help reduce my database storage costs?

Yes, by recommending optimal data types like UInt8 over Int64 and using LowCardinality(String), it helps minimize the physical footprint of each column on disk.

ClickHouse Query & Schema Optimizer

Name: ClickHouse Query & Schema Optimizer
Author: canopy-network

bycanopy-network

•

数据库管理

Optimizes ClickHouse database performance through expert schema design, primary key ordering, and query tuning.

关于

This skill provides specialized guidance for maximizing ClickHouse performance by implementing columnar storage best practices and efficient indexing strategies. It helps developers master the nuances of MergeTree engine optimization, from designing primary keys based on cardinality to utilizing advanced features like PREWHERE, materialized views, and approximate functions. Whether you are building a new analytical backend or troubleshooting slow queries, this skill ensures your ClickHouse implementation follows critical 'MUST' rules for minimizing disk I/O and maximizing parallel execution efficiency.

主要功能

Cardinality-aware Primary Key (ORDER BY) optimization for 100x performance gains
1 GitHub stars
Memory-optimized data typing using LowCardinality and Nullable avoidance
Advanced performance debugging via EXPLAIN plans and system query log analysis
Granule-efficient query design to maximize sparse index skipping
High-scale aggregation strategies using approximate functions and materialized views

使用场景

Designing high-performance schemas for time-series and large-scale analytical workloads
Implementing data denormalization strategies to eliminate resource-intensive JOIN operations
Refactoring slow SQL queries into optimized ClickHouse-native syntax to reduce latency

关于

主要功能

Cardinality-aware Primary Key (ORDER BY) optimization for 100x performance gains
1 GitHub stars
Memory-optimized data typing using LowCardinality and Nullable avoidance
Advanced performance debugging via EXPLAIN plans and system query log analysis
Granule-efficient query design to maximize sparse index skipping
High-scale aggregation strategies using approximate functions and materialized views

使用场景

Designing high-performance schemas for time-series and large-scale analytical workloads
Implementing data denormalization strategies to eliminate resource-intensive JOIN operations
Refactoring slow SQL queries into optimized ClickHouse-native syntax to reduce latency