How does this skill help with ClickHouse memory errors?

It identifies memory-intensive operations like full table scans or large hash-table JOINs and suggests more efficient alternatives such as Dictionaries, PREWHERE filtering, or specific column selection.

What version of ClickHouse does it support?

It covers core optimization principles applicable to all versions, as well as modern features like the Query Cache introduced in version 22.8 and beyond.

Can it help with slow JOIN operations?

Yes, it provides a decision tree to determine if a JOIN should be replaced by a Dictionary lookup, an IN subquery for filtering, or if data should be denormalized at insert time.

Is this useful for real-time performance monitoring?

Yes, it includes specialized snippets for querying the system.query_log to identify slow queries and full table scans that occurred recently in your cluster.

Does it support index analysis?

Absolutely. It includes rules for verifying index usage via the EXPLAIN command and optimizing ORDER BY clauses to ensure the primary index is used to skip irrelevant granules.

ClickHouse Query Optimizer

Name: ClickHouse Query Optimizer
Author: obsessiondb

byobsessiondb

•

Database Management

Optimizes ClickHouse SQL queries for maximum performance, reduced memory usage, and efficient index utilization.

This skill provides a comprehensive suite of over 20 optimization rules specifically for ClickHouse, designed to achieve 10-100x faster execution and up to 90% less memory consumption. It guides developers through critical performance patterns such as leveraging ORDER BY prefixes, implementing PREWHERE for selective filtering, and replacing resource-heavy JOINs with high-performance Dictionaries. Whether you are debugging slow queries via EXPLAIN or resolving memory limit errors, this skill ensures your analytical workloads run with minimal I/O and maximum efficiency by eliminating full table scans and optimizing execution pipelines.

Key Features

01Guides efficient index usage through EXPLAIN analysis and primary key prefixing.

02Implements 20+ proven ClickHouse optimization rules and best practices.

0312 GitHub stars

04Reduces memory overhead by transitioning complex JOINs to Dictionaries or IN subqueries.

05Provides tools for identifying and profiling slow queries using system logs and query thread logs.

06Optimizes data retrieval using PREWHERE and column-specific SELECT statements.

Use Cases

01Debugging and fixing slow analytical queries or memory limit timeouts in production environments.

02Designing high-performance schemas by aligning ORDER BY clauses with specific query access patterns.

03Refactoring legacy SQL to utilize ClickHouse-specific performance features like Skip Indexes and Bloom filters.

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add obsessiondb/clickhouse-skills clickhouse-query-optimization

For use in Claude.ai and ChatGPT

Download Skill