Query Performance Analysis
Diagnose SELECT query performance issues, analyze query patterns, and identify optimization opportunities.
Diagnostics
Run all queries from the file checks.sql 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