altinity-expert-clickhouse-index-analysis

Diagnostics

Run all queries from the file checks.sql and analyze the results.

Deep Dive Queries (Placeholder-Based)

EXPLAIN Index Usage for Specific Query

EXPLAIN indexes = 1
{query_without_format}

Look for:

• PrimaryKey condition should not be true (means no filtering)
• Granules: X/Y ratio shows selectivity (low X/Y = good)
• Skip indexes should reduce parts/granules further

Column Cardinality Analysis

SELECT 
    {columns} APPLY uniq
FROM {database}.{table}
WHERE {time_column} > now() - INTERVAL {days} DAY

Optimal ORDER BY ordering: Low cardinality columns first, high cardinality last.

Query Pattern WHERE Columns Extraction

WITH
    any(query) AS q,
    arrayJoin(extractAll(query, '\\b(?:PRE)?WHERE\\s+(.*?)\\s+(?:GROUP BY|ORDER BY|UNION|SETTINGS|FORMAT|$)')) AS w,
    arrayFilter(x -> (position(w, extract(x, '\\.(`[^`]+`|[^\\.]+)$')) > 0), columns) AS c,
    arrayJoin(c) AS c2
SELECT
    c2,
    count() AS usage_count
FROM system.query_log
WHERE event_time >= now() - toIntervalDay({days})
  AND arrayExists(x -> x LIKE '%{table}%', tables)
  AND query ILIKE 'SELECT%'
  AND type = 'QueryFinish'
GROUP BY c2
ORDER BY usage_count DESC
FORMAT PrettyCompactMonoBlock

Normalized WHERE Clause Patterns

WITH
    arrayJoin(extractAll(normalizeQuery(query), '\\b(?:PRE)?WHERE\\s+(.*?)\\s+(?:GROUP BY|ORDER BY|UNION|SETTINGS|FORMAT|$)')) AS w
SELECT
    w AS where_pattern,
    count() AS frequency
FROM system.query_log
WHERE event_time >= now() - toIntervalDay({days})
  AND arrayExists(x -> x LIKE '%{table}%', tables)
  AND query ILIKE 'SELECT%'
  AND type = 'QueryFinish'
GROUP BY w
ORDER BY frequency DESC
LIMIT 20

Granule Selectivity from Query Log

SELECT
    query_id,
    normalized_query_hash,
    selected_parts,
    selected_marks,
    read_rows,
    round(read_rows / nullIf(selected_marks, 0)) AS rows_per_mark,
    query_duration_ms,
    formatReadableSize(read_bytes) AS read_bytes
FROM system.query_log
WHERE event_time >= now() - toIntervalDay({days})
  AND arrayExists(x -> x LIKE '%{table}%', tables)
  AND query ILIKE 'SELECT%'
  AND type = 'QueryFinish'
ORDER BY selected_marks DESC
LIMIT 20

High selected_marks / total marks = poor index utilization.

Analysis Workflow

Step 1: Check Current Indexes

-- Table structure with ORDER BY, PRIMARY KEY, indexes
SHOW CREATE TABLE {database}.{table}

-- Skipping indexes
SELECT name, type, expr, granularity
FROM system.data_skipping_indices
WHERE database = '{database}' AND table = '{table}'

Step 2: Extract Query Patterns

Run the WHERE column extraction and normalized pattern queries to understand:

• Which columns appear most frequently in WHERE clauses
• What condition combinations are common

Step 3: Check Column Cardinalities

Compare cardinalities of columns in:

• Current ORDER BY key
• Frequently filtered columns from Step 2

Step 4: Evaluate Index Alignment

ORDER BY Design Guidelines

Column Order Principles

1. Lowest cardinality first – maximizes granule skipping
2. Most frequently filtered – columns in WHERE should be in ORDER BY
3. Time column considerations:
   • If most queries filter on time ranges → include in ORDER BY (possibly with lower resolution like toDate(ts))
   • If partition key handles time filtering → may not need in ORDER BY

Common Anti-Patterns

Cardinality Ordering Example

Given cardinalities:

• entity_type: 6
• entity: 18,588
• cast_hash: 335,620

Recommended ORDER BY: (entity_type, entity, cast_hash, ...)

Skipping Index Guidelines

When Skip Indexes Help

• Column NOT in ORDER BY
• Column values correlate with physical data order
• Low false-positive rate for the index type

When Skip Indexes Don’t Help

• Column already in ORDER BY prefix (use PRIMARY KEY instead)
• Column values randomly distributed (no correlation with ORDER BY)
• Very high cardinality with set/bloom_filter