Query Tuning Advisor

Query Tuning Advisor

Use this skill when a user shares a specific slow, expensive, or high-memory query and wants it made faster. The goal is a concrete before → after rewrite, not general advice. Load query-optimization for reference tables on EXPLAIN output and skip index types.

1. Diagnose First

Never guess. Collect evidence before proposing rewrites.

Step 1 — EXPLAIN INDEXES (call explain_query tool, type INDEXES):

EXPLAIN INDEXES = 1 <the query>

Read the output for:

• Granules: N/M — N granules selected out of M total. N ≈ M means a full scan; skip indexes are not firing.
• Keys: <expr> — confirms which primary key ranges were used.
• Missing keys = predicates don't align with the table's ORDER BY.

Step 2 — EXPLAIN PLAN (type PLAN, actions=1):

EXPLAIN actions = 1 <the query>

Look for: Filter, ReadFromMergeTree with no pushdown, large Aggregating steps, or a JOIN where the build side is large.

Step 3 — Find it in query_log (call query tool):

SELECT
    query_duration_ms,
    read_rows,
    result_rows,
    read_rows / nullIf(result_rows, 0) AS scan_ratio,
    memory_usage,
    ProfileEvents['SelectedMarks']        AS marks_read,
    ProfileEvents['SelectedRangesOfMarks'] AS ranges_read,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND is_initial_query = 1
  AND normalized_query_hash = cityHash64('<the query with literals replaced by ?>')
ORDER BY event_time DESC
LIMIT 5

Key signals:

• scan_ratio > 100 → reading far more rows than returned; likely full scan or missing PREWHERE.
• marks_read close to total table marks → primary key not used.
• memory_usage > 1 GiB → GROUP BY or JOIN materializing too much.

2. PREWHERE and Predicate Placement

ClickHouse evaluates PREWHERE before reading all columns — it reads only the filter column(s) first, skips non-matching granules, then fetches the rest. The optimizer promotes simple WHERE conditions automatically, but it doesn't always get it right.

Rules:

• Move the most selective, cheapest-to-read condition into PREWHERE manually when the optimizer misses it.
• Avoid expressions in PREWHERE that reference non-stored columns or require decompression of wide columns.
• Never use PREWHERE with FINAL on a ReplacingMergeTree — it can produce wrong results.

-- Before
SELECT url, status, body
FROM access_log
WHERE toDate(event_time) = today()
  AND status = 500

-- After: push the narrow int filter to PREWHERE
SELECT url, status, body
FROM access_log
PREWHERE status = 500
WHERE toDate(event_time) = today()

Also: move date/time range filters to align with the primary key order so they prune granules before PREWHERE even runs.

3. Better JOINs

Join order

ClickHouse's hash join builds a hash table from the right table and probes with the left table. Put the smaller table on the right.

-- Before: large table on right (built into hash table)
SELECT * FROM small_dim JOIN large_fact USING (id)

-- After: large table on left (probed), small on right (built)
SELECT * FROM large_fact JOIN small_dim USING (id)

Choose the right join_algorithm

Set per-query: SELECT ... FROM a JOIN b USING (k) SETTINGS join_algorithm = 'partial_merge'

IN / semi-join instead of JOIN

When you only need to filter rows (not project columns from the right side), IN is cheaper than JOIN — it avoids materializing the joined columns:

-- Before: full JOIN just to filter
SELECT l.* FROM orders l JOIN vip_customers r ON l.customer_id = r.id

-- After: semi-join via IN
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM vip_customers)

Avoid cartesian blowups

• Always specify ON or USING. A missing condition produces a cross join.
• Check read_rows in query_log — if it equals left_rows × right_rows, you have a cartesian product.
• For many-to-many relationships, pre-aggregate one side before joining.

4. Avoiding Full Scans

Align filters with ORDER BY / primary key

ClickHouse primary key = ORDER BY columns. Filters on those columns prune granules; filters on other columns scan everything.

-- Table: ORDER BY (tenant_id, event_date, event_type)
-- Bad: event_type filter alone cannot prune granules
WHERE event_type = 'purchase'

-- Good: leading columns first, then event_type
WHERE tenant_id = 42 AND event_date >= '2024-01-01' AND event_type = 'purchase'

Skip indexes

Add a skip index when you often filter on a non-primary-key column:

-- For low-cardinality status columns
ALTER TABLE events ADD INDEX idx_status (status) TYPE set(100) GRANULARITY 4;

-- For high-cardinality string equality (e.g. trace_id)
ALTER TABLE events ADD INDEX idx_trace (trace_id) TYPE bloom_filter GRANULARITY 1;

-- For range queries on a secondary numeric column
ALTER TABLE events ADD INDEX idx_latency (latency_ms) TYPE minmax GRANULARITY 4;

After adding, materialize: ALTER TABLE events MATERIALIZE INDEX idx_status;

Verify it fires: EXPLAIN INDEXES = 1 <query> — look for the index name in the output and a reduced granule count.