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