[branch-53] sql_planner benchmark slower in 53 than 52 #21186
Description
Describe the bug
While writing up the 53 release blog I did some profiling:
It turns out that datafusion 53 is significantly slower for planning that 52 on our benchmarks
To Reproduce
Commands:
git checkout -f 52.4.0
cargo bench --bench sql_planner -- --save-baseline 52.4.0
git checkout -f 53.0.0-rc3
cargo bench --bench sql_planner -- --save-baseline 53.0.0
critcmp 52.4.0 53.0.0Output:
alamb@aal-dev:~/arrow-datafusion$ critcmp 52.4.0 53.0.0
group 52.4.0 53.0.0
----- ------ ------
logical_aggregate_with_join 1.01 637.2±3.89µs ? ?/sec 1.00 633.5±5.20µs ? ?/sec
logical_plan_struct_join_agg_sort 1.00 288.2±2.91µs ? ?/sec
logical_select_all_from_1000 1.02 10.6±0.04ms ? ?/sec 1.00 10.4±0.03ms ? ?/sec
logical_select_one_from_700 1.00 418.3±6.86µs ? ?/sec 1.00 416.8±1.91µs ? ?/sec
logical_trivial_join_high_numbered_columns 1.01 375.9±0.92µs ? ?/sec 1.00 371.6±1.12µs ? ?/sec
logical_trivial_join_low_numbered_columns 1.01 361.2±1.32µs ? ?/sec 1.00 356.9±2.08µs ? ?/sec
physical_intersection 1.00 825.1±2.06µs ? ?/sec 1.88 1549.3±10.38µs ? ?/sec
physical_join_consider_sort 1.00 1389.6±4.69µs ? ?/sec 1.58 2.2±0.00ms ? ?/sec
physical_join_distinct 1.01 354.6±0.86µs ? ?/sec 1.00 351.1±0.61µs ? ?/sec
physical_many_self_joins 1.00 9.6±0.03ms ? ?/sec 1.28 12.2±0.03ms ? ?/sec
physical_plan_clickbench_all 1.00 195.0±1.29ms ? ?/sec
physical_plan_clickbench_q1 1.00 1588.4±12.35µs ? ?/sec 1.31 2.1±0.02ms ? ?/sec
physical_plan_clickbench_q10 1.00 2.5±0.01ms ? ?/sec 1.45 3.5±0.02ms ? ?/sec
physical_plan_clickbench_q11 1.00 2.6±0.01ms ? ?/sec 1.50 4.0±0.01ms ? ?/sec
physical_plan_clickbench_q12 1.00 2.7±0.01ms ? ?/sec 1.47 4.1±0.02ms ? ?/sec
physical_plan_clickbench_q13 1.00 2.4±0.00ms ? ?/sec 1.51 3.6±0.03ms ? ?/sec
physical_plan_clickbench_q14 1.00 2.6±0.01ms ? ?/sec 1.52 4.0±0.03ms ? ?/sec
physical_plan_clickbench_q15 1.00 2.5±0.01ms ? ?/sec 1.49 3.7±0.06ms ? ?/sec
physical_plan_clickbench_q16 1.00 2.3±0.01ms ? ?/sec 1.53 3.5±0.02ms ? ?/sec
physical_plan_clickbench_q17 1.00 2.4±0.00ms ? ?/sec 1.51 3.6±0.02ms ? ?/sec
physical_plan_clickbench_q18 1.00 1944.7±6.73µs ? ?/sec 1.34 2.6±0.02ms ? ?/sec
physical_plan_clickbench_q19 1.00 2.8±0.01ms ? ?/sec 1.43 4.0±0.02ms ? ?/sec
physical_plan_clickbench_q2 1.00 2.0±0.01ms ? ?/sec 1.35 2.7±0.01ms ? ?/sec
physical_plan_clickbench_q20 1.00 1768.8±2.75µs ? ?/sec 1.17 2.1±0.00ms ? ?/sec
physical_plan_clickbench_q21 1.00 2.1±0.01ms ? ?/sec 1.31 2.7±0.01ms ? ?/sec
physical_plan_clickbench_q22 1.00 2.6±0.01ms ? ?/sec 1.45 3.8±0.02ms ? ?/sec
physical_plan_clickbench_q23 1.00 2.9±0.01ms ? ?/sec 1.43 4.1±0.01ms ? ?/sec
physical_plan_clickbench_q24 1.00 4.0±0.01ms ? ?/sec 1.18 4.7±0.02ms ? ?/sec
physical_plan_clickbench_q25 1.00 2.2±0.02ms ? ?/sec 1.53 3.4±0.02ms ? ?/sec
physical_plan_clickbench_q26 1.00 1986.5±18.75µs ? ?/sec 1.43 2.8±0.02ms ? ?/sec
physical_plan_clickbench_q27 1.00 2.2±0.01ms ? ?/sec 1.54 3.4±0.02ms ? ?/sec
physical_plan_clickbench_q28 1.00 3.0±0.02ms ? ?/sec 1.42 4.3±0.05ms ? ?/sec
physical_plan_clickbench_q29 1.00 3.3±0.02ms ? ?/sec 1.38 4.5±0.02ms ? ?/sec
physical_plan_clickbench_q3 1.00 1971.6±16.03µs ? ?/sec 1.25 2.5±0.01ms ? ?/sec
physical_plan_clickbench_q30 1.00 14.6±0.12ms ? ?/sec 1.07 15.7±0.11ms ? ?/sec
physical_plan_clickbench_q31 1.00 3.0±0.02ms ? ?/sec 1.46 4.3±0.02ms ? ?/sec
physical_plan_clickbench_q32 1.00 3.0±0.02ms ? ?/sec 1.45 4.3±0.02ms ? ?/sec
physical_plan_clickbench_q33 1.00 2.5±0.02ms ? ?/sec 1.44 3.5±0.02ms ? ?/sec
physical_plan_clickbench_q34 1.00 2.1±0.01ms ? ?/sec 1.49 3.1±0.02ms ? ?/sec
physical_plan_clickbench_q35 1.00 2.2±0.00ms ? ?/sec 1.48 3.2±0.01ms ? ?/sec
physical_plan_clickbench_q36 1.00 2.9±0.01ms ? ?/sec 1.41 4.1±0.02ms ? ?/sec
physical_plan_clickbench_q37 1.00 3.1±0.01ms ? ?/sec 1.44 4.5±0.03ms ? ?/sec
physical_plan_clickbench_q38 1.00 3.2±0.02ms ? ?/sec 1.44 4.5±0.03ms ? ?/sec
physical_plan_clickbench_q39 1.00 3.0±0.02ms ? ?/sec 1.32 3.9±0.06ms ? ?/sec
physical_plan_clickbench_q4 1.00 1698.9±9.62µs ? ?/sec 1.25 2.1±0.01ms ? ?/sec
physical_plan_clickbench_q40 1.00 3.8±0.03ms ? ?/sec 1.22 4.6±0.04ms ? ?/sec
physical_plan_clickbench_q41 1.00 3.2±0.01ms ? ?/sec 1.29 4.1±0.02ms ? ?/sec
physical_plan_clickbench_q42 1.00 3.1±0.01ms ? ?/sec 1.29 4.0±0.02ms ? ?/sec
physical_plan_clickbench_q43 1.00 3.7±0.01ms ? ?/sec 1.16 4.3±0.02ms ? ?/sec
physical_plan_clickbench_q44 1.00 1795.4±13.58µs ? ?/sec 1.26 2.3±0.02ms ? ?/sec
physical_plan_clickbench_q45 1.00 1802.6±12.26µs ? ?/sec 1.24 2.2±0.01ms ? ?/sec
physical_plan_clickbench_q46 1.00 2.2±0.01ms ? ?/sec 1.42 3.1±0.05ms ? ?/sec
physical_plan_clickbench_q47 1.00 2.8±0.01ms ? ?/sec 1.62 4.6±0.03ms ? ?/sec
physical_plan_clickbench_q48 1.00 3.6±0.02ms ? ?/sec 1.36 4.9±0.03ms ? ?/sec
physical_plan_clickbench_q49 1.00 3.8±0.03ms ? ?/sec 1.34 5.1±0.04ms ? ?/sec
physical_plan_clickbench_q5 1.00 1889.8±12.24µs ? ?/sec 1.29 2.4±0.01ms ? ?/sec
physical_plan_clickbench_q50 1.00 4.1±0.03ms ? ?/sec
physical_plan_clickbench_q51 1.00 3.4±0.01ms ? ?/sec
physical_plan_clickbench_q6 1.00 1899.9±14.08µs ? ?/sec 1.29 2.4±0.01ms ? ?/sec
physical_plan_clickbench_q7 1.00 1590.1±9.13µs ? ?/sec 1.27 2.0±0.01ms ? ?/sec
physical_plan_clickbench_q8 1.00 2.5±0.00ms ? ?/sec 1.33 3.3±0.01ms ? ?/sec
physical_plan_clickbench_q9 1.00 2.3±0.01ms ? ?/sec 1.51 3.5±0.02ms ? ?/sec
physical_plan_struct_join_agg_sort 1.00 3.1±0.01ms ? ?/sec
physical_plan_tpcds_all 1.00 1032.6±3.55ms ? ?/sec 1.75 1807.9±9.18ms ? ?/sec
physical_plan_tpch_all 1.00 61.9±0.27ms ? ?/sec 1.95 120.4±0.41ms ? ?/sec
physical_plan_tpch_q1 1.00 2.1±0.01ms ? ?/sec 1.35 2.9±0.01ms ? ?/sec
physical_plan_tpch_q10 1.00 3.8±0.00ms ? ?/sec 1.80 6.8±0.03ms ? ?/sec
physical_plan_tpch_q11 1.00 3.5±0.02ms ? ?/sec 2.31 8.1±0.07ms ? ?/sec
physical_plan_tpch_q12 1.00 1792.3±10.54µs ? ?/sec 1.63 2.9±0.01ms ? ?/sec
physical_plan_tpch_q13 1.00 1391.3±7.50µs ? ?/sec 2.08 2.9±0.01ms ? ?/sec
physical_plan_tpch_q14 1.00 1973.2±7.86µs ? ?/sec 1.45 2.9±0.01ms ? ?/sec
physical_plan_tpch_q16 1.00 2.3±0.01ms ? ?/sec 2.15 4.9±0.01ms ? ?/sec
physical_plan_tpch_q17 1.00 2.6±0.01ms ? ?/sec 2.07 5.3±0.02ms ? ?/sec
physical_plan_tpch_q18 1.00 2.6±0.01ms ? ?/sec 2.23 5.7±0.02ms ? ?/sec
physical_plan_tpch_q19 1.00 3.1±0.01ms ? ?/sec 1.61 5.0±0.02ms ? ?/sec
physical_plan_tpch_q2 1.00 5.5±0.01ms ? ?/sec 2.10 11.6±0.06ms ? ?/sec
physical_plan_tpch_q20 1.00 3.0±0.01ms ? ?/sec 2.54 7.7±0.02ms ? ?/sec
physical_plan_tpch_q21 1.00 4.0±0.02ms ? ?/sec 2.44 9.7±0.05ms ? ?/sec
physical_plan_tpch_q22 1.00 2.8±0.02ms ? ?/sec 2.26 6.2±0.05ms ? ?/sec
physical_plan_tpch_q3 1.00 2.6±0.00ms ? ?/sec 2.06 5.4±0.02ms ? ?/sec
physical_plan_tpch_q4 1.00 1421.4±33.29µs ? ?/sec 2.03 2.9±0.02ms ? ?/sec
physical_plan_tpch_q5 1.00 3.2±0.01ms ? ?/sec 1.79 5.7±0.02ms ? ?/sec
physical_plan_tpch_q6 1.00 863.0±5.05µs ? ?/sec 1.77 1531.7±3.46µs ? ?/sec
physical_plan_tpch_q7 1.00 4.0±0.02ms ? ?/sec 1.69 6.8±0.04ms ? ?/sec
physical_plan_tpch_q8 1.00 5.3±0.02ms ? ?/sec 1.66 8.8±0.07ms ? ?/sec
physical_plan_tpch_q9 1.00 3.8±0.01ms ? ?/sec 1.65 6.3±0.03ms ? ?/sec
physical_select_aggregates_from_200 1.00 16.6±0.06ms ? ?/sec 1.04 17.3±0.05ms ? ?/sec
physical_select_all_from_1000 1.00 23.1±0.06ms ? ?/sec 1.01 23.4±0.07ms ? ?/sec
physical_select_one_from_700 1.00 1083.1±14.86µs ? ?/sec 1.21 1308.4±20.82µs ? ?/sec
physical_sorted_union_order_by_10_int64 1.00 6.4±0.02ms ? ?/sec 1.50 9.6±0.03ms ? ?/sec
physical_sorted_union_order_by_10_uint64 1.00 15.7±0.05ms ? ?/sec 1.70 26.8±0.13ms ? ?/sec
physical_sorted_union_order_by_50_int64 1.12 170.8±1.00ms ? ?/sec 1.00 153.2±1.05ms ? ?/sec
physical_sorted_union_order_by_50_uint64 1.00 878.6±8.51ms ? ?/sec 1.06 932.7±6.14ms ? ?/sec
physical_theta_join_consider_sort 1.00 1764.0±13.45µs ? ?/sec 1.46 2.6±0.01ms ? ?/sec
physical_unnest_to_join 1.00 1760.6±11.16µs ? ?/sec 1.72 3.0±0.01ms ? ?/sec
physical_window_function_partition_by_12_on_values 1.00 1234.5±10.03µs ? ?/sec 1.00 1230.8±2.72µs ? ?/sec
physical_window_function_partition_by_30_on_values 1.26 2.6±0.01ms ? ?/sec 1.00 2.1±0.01ms ? ?/sec
physical_window_function_partition_by_4_on_values 1.00 744.3±3.45µs ? ?/sec 1.23 913.5±6.01µs ? ?/sec
physical_window_function_partition_by_7_on_values 1.00 914.4±7.71µs ? ?/sec 1.12 1025.0±4.52µs ? ?/sec
physical_window_function_partition_by_8_on_values 1.00 983.2±7.58µs ? ?/sec 1.09 1072.5±8.04µs ? ?/sec
with_param_values_many_columns
Expected behavior
I expect planning to be as fast or faster on datafusion 53
Additional context
No response