altinity/altinity-expert-clickhouse-reporting

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 |