altinity/altinity-expert-clickhouse-index-analysis

Diagnostics

Run all queries from checks.sql in this skill's directory 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

| Query Pattern | Index Support | Action | |---------------|---------------|--------| | Filters on ORDER BY prefix | ✅ Good | None | | Filters on non-ORDER BY cols | ⚠️ Skip index? | Add bloom_filter or projection | | Time range + entity | ⚠️ Check order | Time in ORDER BY or partition? | | High-cardinality first in ORDER BY | ❌ Bad | Reorder (low→high cardinality) |

---

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

| Anti-Pattern | Problem | Fix | |--------------|---------|-----| | High-cardinality UUID first | No granule skipping | Move after low-cardinality columns | | DateTime64 microseconds first | Too granular | Use toDate() or toStartOfHour() | | Column in WHERE not in ORDER BY | Full scan | Add to ORDER BY or create projection | | Bloom filter on ORDER BY column | Redundant | Remove skip index | | Time not in ORDER BY or partition | Range queries scan all | Add toDate(ts) to ORDER BY prefix |

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