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.

주요 기능

01Cardinality-aware Primary Key (ORDER BY) optimization for 100x performance gains

021 GitHub stars

03Memory-optimized data typing using LowCardinality and Nullable avoidance

04Advanced performance debugging via EXPLAIN plans and system query log analysis

05Granule-efficient query design to maximize sparse index skipping

06High-scale aggregation strategies using approximate functions and materialized views

사용 사례

01Designing high-performance schemas for time-series and large-scale analytical workloads

02Implementing data denormalization strategies to eliminate resource-intensive JOIN operations

03Refactoring slow SQL queries into optimized ClickHouse-native syntax to reduce latency

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add canopy-network/launchpad clickhouse

For use in Claude.ai and ChatGPT

Download Skill