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

query-tuning-advisor

Diagnoses root causes of slow ClickHouse queries via EXPLAIN and query_log.

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

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This Skill eliminates guesswork when troubleshooting slow, resource-heavy ClickHouse queries that degrade application performance and waste cluster resources, replacing random optimization attempts with evidence-based diagnosis.

Core Features & Use Cases

  • Evidence-Based Diagnosis: Uses EXPLAIN INDEXES, EXPLAIN PLAN, and system.query_log metrics to identify root causes like full scans, missing predicate pushdown, and inefficient JOINs.
  • Concrete Rewrites: Provides actionable before/after SQL changes including PREWHERE optimization, JOIN algorithm and order fixes, skip index recommendations, and aggregation tuning.
  • Real-World Use Case: A user with a 45-second query reading 2 billion rows receives a tuned version that runs in seconds with 10x lower memory usage by applying PREWHERE, removing unnecessary JOINs, and switching to approximate aggregation functions.

Quick Start

Use the query-tuning-advisor skill to optimize the slow ClickHouse query you are currently debugging that is reading excessive rows and using too much memory.

Frequently Asked Questions about query-tuning-advisor

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

FAQPage Schema
How do I fix slow ClickHouse queries that read excessive rows and use too much memory?▼

Fix slow ClickHouse queries by diagnosing root causes with EXPLAIN output and system.query_log metrics, then applying concrete SQL rewrites like PREWHERE optimization and JOIN algorithm adjustments to reduce runtime and memory.

What is the best way to optimize a ClickHouse SELECT query with missing predicate pushdown?▼

Optimize a ClickHouse SELECT query with missing predicate pushdown by analyzing EXPLAIN INDEXES and EXPLAIN PLAN outputs to identify full table scans, then rewriting the SQL to push filters down and reduce scanned rows.

How does PREWHERE optimization improve ClickHouse query performance?▼

PREWHERE optimization improves ClickHouse query performance by filtering data early in the execution pipeline, which drastically reduces the volume of rows processed and lowers overall memory consumption before applying full WHERE clauses.

Can I tune ClickHouse aggregations to reduce memory consumption for high-resource queries?▼

Tune ClickHouse aggregations to reduce memory consumption by substituting exact aggregation functions with approximate alternatives, adjusting JOIN algorithms, and leveraging skip indexes to minimize the data loaded during query execution.

Does this query tuning approach work for both self-hosted and managed ClickHouse environments?▼

Yes, this query tuning approach works for both self-hosted and managed ClickHouse environments, diagnosing slow SELECT queries with JOINs and aggregations using standard EXPLAIN commands and system.query_log performance metrics.

Why does my ClickHouse query with JOINs result in a full table scan?▼

A ClickHouse query with JOINs results in a full table scan due to missing predicate pushdown or inefficient join algorithms, which you can identify using EXPLAIN PLAN output and resolve with join order fixes and skip index recommendations.

Related Skills

Explore More Data & Analytics Skills→
rahmatrdnrahmatrdn
4

query-analyzer

Analyze ClickHouse queries using EXPLAIN and system.query_log for optimization.

Community
Intermediate
ntk148vntk148v
42

altinity-expert-clickhouse-reporting

Diagnose slow ClickHouse queries by analyzing system.query_log and related system views.

Community
Advanced
ivanshamaevivanshamaev
14

trino-query-optimization

Optimizes slow Trino SQL queries via pushdown enforcement and join tuning.

Community
Advanced
obsessiondbobsessiondb
12

clickhouse-query-optimization

Optimize ClickHouse queries with over 20 rules for speed and memory.

Official
Advanced