skills.rest
chmonitor
chmonitorOFFICIAL
Explore all skills by this author →
WebsiteGitHub

query-optimization

Analyze slow ClickHouse queries and generate ranked optimization recommendations.

255|42|Updated Nov 16, 2023
Search Tags:#query optimization#explain plan#clickhouse#query profiling#materialized view#slow database#skip index
One-click install
npx skills add https://github.com/chmonitor/chmonitor --skill query-optimization-chmonitor
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: query-optimization
Source: https://github.com/chmonitor/chmonitor/tree/main/.agents/skills/query-optimization
Command: npx skills add https://github.com/chmonitor/chmonitor --skill query-optimization-chmonitor

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Manually diagnosing slow ClickHouse performance and tuning inefficient queries requires deep expertise in ClickHouse internals, index types, and optimizer settings that most database users and administrators lack, leading to wasted time and suboptimal performance.

Core Features & Use Cases

  • Autonomous Slow Database Diagnose Loop: Automatically identifies the top slow query patterns from system tables without a user-provided query, flagging high total duration, tail latency outliers, and error-prone patterns.
  • EXPLAIN Analysis Guidance: Provides structured interpretation of EXPLAIN PLAN, PIPELINE, and INDEXES outputs to identify full table scans, missing index usage, and excessive granule reads.
  • Ranked Optimization Recommendations: Generates actionable, reviewable suggestions for skip indexes, projections, partition keys, PREWHERE rewrites, and materialized views, complete with DDL text, impact estimates, and risk assessments.
  • Use Case: A ClickHouse admin noticing cluster slowdown can use this skill to automatically find the worst offending query patterns, interpret their execution plans, and get safe DDL recommendations to improve performance without risking accidental schema changes.

Quick Start

Use the query-optimization skill to diagnose why your ClickHouse database is running slow and receive safe, reviewable optimization recommendations tailored to your workload.

Frequently Asked Questions about query-optimization

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I diagnose slow ClickHouse queries without deep internals expertise?▼

Diagnose slow ClickHouse queries by automatically identifying top slow query patterns from system tables, flagging high total duration and tail latency outliers without manual deep expertise. It extracts offending patterns automatically to pinpoint performance bottlenecks.

How do I interpret a ClickHouse EXPLAIN plan to find full table scans?▼

Interpret a ClickHouse EXPLAIN plan using structured guidance for PLAN, PIPELINE, and INDEXES outputs to identify full table scans, missing index usage, and excessive granule reads. This isolates inefficient execution steps causing database slowdowns.

What's the best way to optimize ClickHouse performance without risking schema changes?▼

Optimize ClickHouse performance safely using recommendation-only workflows that generate reviewable DDL text for skip indexes, projections, and materialized views. Suggestions include impact estimates and risk assessments without auto-applying changes.

Does query profiling work for both self-hosted and managed ClickHouse deployments?▼

Query profiling applies to all self-hosted and managed ClickHouse deployments. It provides ranked optimization suggestions with impact and risk assessments, ensuring safe tuning across different infrastructure environments.

Why does my ClickHouse database have high tail latency and error-prone query patterns?▼

High tail latency and error-prone patterns stem from inefficient query execution lacking proper skip index usage or PREWHERE optimization. Autonomous diagnostic loops analyze system tables to group slow query patterns and identify root causes.

When should I not use automated query optimization recommendations on my ClickHouse cluster?▼

Do not use automated query optimization when immediate DDL changes are required, as this workflow is recommendation-only and never auto-applies schema modifications. Review impact estimates and risk assessments before manually applying any suggested tuning.

Related Skills

Explore More Data & Analytics Skills→
ntk148vntk148v
42

altinity-expert-clickhouse-ingestion

Diagnose and optimize ClickHouse INSERT performance and ingestion pipelines.

Community
Advanced
chmonitorchmonitor
255

schema-design-advisor

Analyze ClickHouse table metrics to recommend schema optimizations.

Official
Intermediate
ivanshamaevivanshamaev
14

clickhouse-olap

Design ClickHouse OLAP table engines and query patterns for analytics.

Community
Advanced
cattboycattboy

investigate

Identify software issue root causes through a four-phase investigate, analyze, hypothesize, and implement workflow.

Community
Advanced