Query Optimization

JOIN Strategies

• join_algorithm setting: hash (default, in-memory), partial_merge (spills to disk for large right table), auto (lets ClickHouse decide)
• JOIN ... USING avoids repeated column names vs ON for same-name columns
• Filter both sides before joining to reduce intermediate data
• GLOBAL JOIN broadcasts the right table to all shards for distributed queries

EXPLAIN Analysis

• EXPLAIN PLAN — logical plan, shows projection/pushdown transformations
• EXPLAIN PIPELINE — physical execution with parallelism info and port counts
• EXPLAIN INDEXES — which indexes fire, granules selected vs total
• Look for: full table scans, missing index usage, excessive granule reads