- 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
dpsubdynamic programming algorithm, which considers all valid join orders, including orders withLEFT,RIGHT,FULL,SEMI, andANTIjoins. The search covers groups of up to 10 tables, set byquery_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. Seequery_plan_optimize_join_order_algorithmandquery_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_mergeby cost, and prefers plans whose sorted output can be used by a later join. Seequery_plan_join_order_use_interesting_orders. - Filter order. Conditions in
WHEREare moved toPREWHEREand 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?
UseEXPLAIN 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.