Query Optimization

When to use this skill

Load when users ask about slow queries, optimization strategies, or query performance tuning.

PREWHERE Optimization

• PREWHERE filters rows before reading all columns (MergeTree only)
• ClickHouse auto-promotes simple WHERE conditions to PREWHERE
• Manually use PREWHERE for complex conditions on indexed columns
• Best when filtering on columns NOT in the SELECT list

JOIN Strategies

• Put smaller table on RIGHT side of JOIN
• Use IN subquery for simple lookups instead of JOIN
• Filter both sides before joining to reduce intermediate data
• GLOBAL JOIN for distributed queries — broadcasts small table to all shards
• JOIN ... USING is cleaner than ON for same-name columns
• Consider join_algorithm setting: hash (default), partial_merge, auto