Skip to main content
Yes. ClickHouse has a cost-based optimizer (CBO). It estimates the number of rows produced by each step of a query from column statistics and uses those estimates to choose between equivalent query plans. The cost-based decisions are:
  • JOIN order. For a query that joins several tables, the planner searches for the order of joins with the lowest estimated cost. By default it uses the dpsub dynamic programming algorithm, which considers all valid join orders, including orders with LEFT, RIGHT, FULL, SEMI, and ANTI joins. The search covers groups of up to 10 tables, set by query_plan_optimize_join_order_limit: a query that joins more tables is split into groups that are optimized separately. If the limit is raised above 12, a group of more than 12 tables is planned by a greedy algorithm instead. See query_plan_optimize_join_order_algorithm and query_plan_optimize_join_order_limit.
  • JOIN build side. For a hash join, the planner places the input with the smaller estimated size into the hash table, regardless of the order in which the tables are written in the query. See query_plan_join_swap_table.
  • JOIN algorithm. When the joined tables can be read in the order of the join key, the planner chooses between a hash join and full_sorting_merge by cost, and prefers plans whose sorted output can be used by a later join. See query_plan_join_order_use_interesting_orders.
  • Filter order. Conditions in WHERE are moved to PREWHERE and ordered so that cheap and selective conditions are evaluated first, based on the compressed size of the columns and the estimated selectivity of each condition.

Where do the estimates come from?

MergeTree tables collect column statistics automatically for each data part when parts are merged, and also on insert into tables smaller than materialize_statistics_on_insert_max_table_size (25 GiB by default), so there is no need to run ANALYZE or to maintain the statistics manually. The statistics types are set by the auto_statistics_types table setting, which defaults to basic (minimum and maximum, number of default and NULL values, total length of strings) and uniq_v2 (number of distinct values). Additional types, such as tdigest (value distribution) and countmin (frequencies of individual values), improve the estimates for range and equality conditions on skewed data. See Column statistics. Statistics are used when use_statistics is enabled, which is the default. Besides column statistics, the JOIN order optimizer can use the sizes of hash tables observed in previous runs of the same join (use_hash_table_stats_for_join_reordering), and the statistics in Iceberg manifest files (use_datalake_metadata_statistics).

How can I see what the optimizer did?

Use EXPLAIN to see the chosen plan, and EXPLAIN ANALYZE to run the query and see the actual metrics of each step. For join steps, it also compares the estimated row counts with the actual ones.

When does the optimizer matter?

Much of the performance of ClickHouse does not depend on the optimizer: the primary key, skip indexes, partition pruning, and vectorized execution make a query fast even without plan search. The cost-based optimizer matters most for queries that join many tables, such as the TPC-H, TPC-DS, and Join Order Benchmark workloads.
For the full list of settings that control JOIN planning, see JOIN settings.