ClickHouse Boosts Join Optimization for All Joins

Beyond Inner Joins: A Deeper Dive

ClickHouse's new cost-based join optimizer (CBO) with reordering support for non-inner joins represents a substantial leap forward in query optimization. The adoption of DPsub, an exhaustive dynamic programming algorithm, promises to find the optimal join order for queries involving up to 12 tables, a significant improvement over previous greedy approaches. The key innovation lies in extending join reordering capabilities beyond inner joins to include LEFT, RIGHT, FULL OUTER, SEMI, and ANTI joins. This is particularly impactful as non-inner joins previously acted as optimization barriers, often leading to suboptimal execution plans and larger intermediate result sets. The introduction of a 'conflict detector,' proven to be both correct and complete, ensures that reorderings preserve query results while safely expanding the search space. The blog post effectively illustrates the benefits with a TPC-H example, demonstrating a ~496x reduction in intermediate results by allowing a SEMI join to participate in the reordering process. This technical depth, coupled with the practical implications for query performance, makes the article highly valuable.

Key Points

• ClickHouse introduces a new cost-based join optimizer (CBO) leveraging an efficient implementation of DPsub.
• The new optimizer supports reordering for non-inner join types (LEFT, RIGHT, FULL OUTER, SEMI, ANTI), overcoming previous optimization barriers.
• A provably correct and complete 'conflict detector' ensures query result integrity while expanding the search space.
• The system can find the cheapest plan for queries with up to 12 tables under the current cost model.
• This enhancement is expected to significantly reduce intermediate result sizes and improve query performance, as demonstrated by a ~496x improvement in a SEMI join example.

---

📖 Source: The new cost-based join optimizer with reordering support for non-inner joins