altinity-expert-clickhouse-reporting

name	altinity-expert-clickhouse-reporting
description	Diagnose ClickHouse SELECT query performance, analyze query patterns, identify slow queries, and find optimization opportunities. Use for query latency and timeout issues.
license	Apache-2.0

Query Performance Analysis

Diagnose SELECT query performance issues, analyze query patterns, and identify optimization opportunities.

---

Diagnostics

Run all queries from checks.sql in this skill's directory and analyze the results.

---

Query Optimization Hints

Index Usage Check

-- Check if data skipping indices exist
select
    database,
    table,
    name as index_name,
    type,
    expr,
    granularity
from system.data_skipping_indices
where database = '{database}' and table = '{table}'

Mark Count for Query

For a specific slow query, check how many marks (granules) were read:

select
    query_id,
    read_rows,
    selected_marks,
    selected_parts,
    formatReadableSize(read_bytes) as read_bytes,
    round(read_rows / nullIf(selected_marks, 0)) as rows_per_mark
from system.query_log
where query_id = '{query_id}'
  and type = 'QueryFinish'

High selected_marks relative to result = index not selective enough.

---

Ad-Hoc Query Guidelines

Required Safeguards

-- Always time-bound
where event_date >= today() - 1
-- or
where event_time > now() - interval 1 hour

-- Always limit
limit 100

-- Filter by type
where type = 'QueryFinish'  -- completed
where type like 'Exception%'  -- failed

Useful Filters

-- By user
where user = 'analytics_user'

-- By query pattern
where query ilike '%SELECT%FROM my_table%'

-- By duration threshold
where query_duration_ms > 10000  -- > 10 seconds

-- By normalized hash (for specific query pattern)
where normalized_query_hash = 1234567890

---

Cross-Module Triggers

Finding	Load Module	Reason
High memory queries	altinity-expert-clickhouse-memory	Memory limits/optimization
Reading too many parts	altinity-expert-clickhouse-merges	Part consolidation
Poor index selectivity	altinity-expert-clickhouse-schema	Index/ORDER BY design
Cache misses	altinity-expert-clickhouse-caches	Cache sizing
MV slow	altinity-expert-clickhouse-ingestion	MV optimization

---

Settings Reference

Setting	Scope	Notes
max_execution_time	Query	Query timeout
max_rows_to_read	Query	Limit rows scanned
max_bytes_to_read	Query	Limit bytes scanned
max_threads	Query	Parallelism
use_query_cache	Query	Enable query result caching
log_queries	Server	Enable query logging
log_queries_min_query_duration_ms	Server	Log threshold