--- title: The new cost-based join optimizer with reordering... description: ClickHouse built a new cost-based join optimizer (CBO) using DPsub, an exhaustive dynamic-programming algorithm, and it becomes the default in the 26.10... canonical: https://daily.dev/posts/the-new-cost-based-join-optimizer-with-reordering-support-for-non-inner-joins-sqb0vvrfz twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: The new cost-based join optimizer with reordering support for non-inner joins | daily.dev og:description: ClickHouse built a new cost-based join optimizer (CBO) using DPsub, an exhaustive dynamic-programming algorithm, and it becomes the default in the 26.10... og:url: https://daily.dev/posts/the-new-cost-based-join-optimizer-with-reordering-support-for-non-inner-joins-sqb0vvrfz og:image: https://api.daily.dev/og/posts/Sqb0VVrfZ.png og:image:alt: The new cost-based join optimizer with reordering support for non-inner joins og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # The new cost-based join optimizer with reordering support for non-inner joins **[ClickHouse](https://daily.dev/sources/clickhouse)** · 19 min read · 0 upvotes · 0 comments ## Summary ClickHouse built a new cost-based join optimizer (CBO) using DPsub, an exhaustive dynamic-programming algorithm, and it becomes the default in the 26.10 release. Unlike the old greedy optimizer, it finds the cheapest plan for queries with up to 12 tables and can now reorder LEFT, RIGHT and FULL outer joins plus SEMI and ANTI joins, not just inner joins. A conflict detector proves each reordering preserves correctness, validated across 2.8 million candidate plans with zero invalid or missing results. On a semi-join example it cut the largest intermediate result by about 496 times, and on TPC-H/TPC-DS benchmark queries it cut runtime by 25% to 55% compared to the greedy default. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### What algorithm does ClickHouse's new cost-based join optimizer use to reorder joins? ClickHouse's cost-based join optimizer (CBO) is built on DPsub, an exhaustive dynamic-programming algorithm that enumerates subsets of tables as bitmasks. It finds the cheapest valid join plan for queries with up to 12 tables and becomes the default join order algorithm starting with the 26.10 release, with a greedy optimizer as fallback for larger joins. _daily.dev keeps engineers tuning ClickHouse query plans current on optimizer internals like this._ ### Can ClickHouse reorder LEFT, SEMI, and ANTI joins, not just inner joins? Yes, starting with ClickHouse's new conflict detector, which proves which reorderings of LEFT, RIGHT, FULL outer, SEMI, and ANTI joins preserve query correctness. Previously every non-inner join acted as an optimization barrier, forcing the full intermediate result to be built before a selective semi or anti join could filter it, as seen in a benchmark where reordering shrank the largest intermediate result from 503,768,186 rows to 1,015,092 rows. _developers tuning join-heavy queries can track changes like this via daily.dev._ ### How much faster are ClickHouse queries after enabling the new DPsub join optimizer with conflict detector compared to the greedy optimizer? On TPC-H and TPC-DS benchmark queries, runtime dropped between 25% and 55% compared to the previous greedy default: TPC-H Q2 went from 143 ms to 65 ms (-55%), TPC-DS Q73 from 260 ms to 143 ms (-45%), and TPC-H Q5 from 574 ms to 320 ms (-44%). At 7 tables, the detector also let the optimizer consider 11.5 times more valid plans. _teams benchmarking database upgrades can follow optimizer performance data like this on daily.dev._ ## Similar posts on daily.dev - [How ClickHouse became fast at joins](https://daily.dev/posts/how-clickhouse-became-fast-at-joins-lcg879zkp) · ClickHouse · 0 upvotes · 0 comments - [MySQL 9.7 Community Edition: Smarter Join Planning with the Hypergraph Optimizer](https://daily.dev/posts/mysql-9-7-community-edition-smarter-join-planning-with-the-hypergraph-optimizer-ista0aar3) · Planet MySQL · 4 upvotes · 0 comments - [How the Other Half Plans](https://daily.dev/posts/how-the-other-half-plans-2eb26obic) · Planet PostgreSQL · 4 upvotes · 0 comments - [pg\_clickhouse v0.10: Subquery pushdown and 1000x faster TPC-H queries](https://daily.dev/posts/pg-clickhouse-v0-10-subquery-pushdown-and-1000x-faster-tpc-h-queries-t4gtgjfr1) · Hacker News · 18 upvotes · 0 comments - [Databricks Spatial Joins Now 17x Faster Out-of-the-Box](https://daily.dev/posts/databricks-spatial-joins-now-17x-faster-out-of-the-box-q3st7bvdr) · databricks · 0 upvotes · 0 comments --- Tags: [#sql](https://daily.dev/tags/sql), [#clickhouse](https://daily.dev/tags/clickhouse), [#dynamic-programming](https://daily.dev/tags/dynamic-programming) [View this post on daily.dev](https://daily.dev/posts/the-new-cost-based-join-optimizer-with-reordering-support-for-non-inner-joins-sqb0vvrfz) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"The new cost-based join optimizer with reordering support for non-inner joins","url":"https://daily.dev/posts/the-new-cost-based-join-optimizer-with-reordering-support-for-non-inner-joins-sqb0vvrfz","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/the-new-cost-based-join-optimizer-with-reordering-support-for-non-inner-joins-sqb0vvrfz"},"datePublished":"2026-10-28T14:14:22.196Z","dateModified":"2026-10-28T14:56:10.844Z","description":"ClickHouse built a new cost-based join optimizer (CBO) using DPsub, an exhaustive dynamic-programming algorithm, and it becomes the default in the 26.10...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/305b021629d88ce94b5aad7a40c947d6?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/305b021629d88ce94b5aad7a40c947d6?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"ClickHouse","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"ClickHouse","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/clickhouse","url":"https://daily.dev/sources/clickhouse"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/the-new-cost-based-join-optimizer-with-reordering-support-for-non-inner-joins-sqb0vvrfz","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"sql,clickhouse,dynamic-programming","timeRequired":"PT19M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"ClickHouse","item":"https://daily.dev/sources/clickhouse"},{"@type":"ListItem","position":3,"name":"The new cost-based join optimizer with reordering support for non-inner joins"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/the-new-cost-based-join-optimizer-with-reordering-support-for-non-inner-joins-sqb0vvrfz#faq","mainEntity":[{"@type":"Question","name":"What algorithm does ClickHouse's new cost-based join optimizer use to reorder joins?","acceptedAnswer":{"@type":"Answer","text":"ClickHouse's cost-based join optimizer (CBO) is built on DPsub, an exhaustive dynamic-programming algorithm that enumerates subsets of tables as bitmasks. It finds the cheapest valid join plan for queries with up to 12 tables and becomes the default join order algorithm starting with the 26.10 release, with a greedy optimizer as fallback for larger joins. daily.dev keeps engineers tuning ClickHouse query plans current on optimizer internals like this."}},{"@type":"Question","name":"Can ClickHouse reorder LEFT, SEMI, and ANTI joins, not just inner joins?","acceptedAnswer":{"@type":"Answer","text":"Yes, starting with ClickHouse's new conflict detector, which proves which reorderings of LEFT, RIGHT, FULL outer, SEMI, and ANTI joins preserve query correctness. Previously every non-inner join acted as an optimization barrier, forcing the full intermediate result to be built before a selective semi or anti join could filter it, as seen in a benchmark where reordering shrank the largest intermediate result from 503,768,186 rows to 1,015,092 rows. developers tuning join-heavy queries can track changes like this via daily.dev."}},{"@type":"Question","name":"How much faster are ClickHouse queries after enabling the new DPsub join optimizer with conflict detector compared to the greedy optimizer?","acceptedAnswer":{"@type":"Answer","text":"On TPC-H and TPC-DS benchmark queries, runtime dropped between 25% and 55% compared to the previous greedy default: TPC-H Q2 went from 143 ms to 65 ms (-55%), TPC-DS Q73 from 260 ms to 143 ms (-45%), and TPC-H Q5 from 574 ms to 320 ms (-44%). At 7 tables, the detector also let the optimizer consider 11.5 times more valid plans. teams benchmarking database upgrades can follow optimizer performance data like this on daily.dev."}}]} ```