Sergei Petrunia added a comment - 2024-12-16 12:31 - edited ... Yes, and the cause seems to be the * pruned_by_hanging_leaf * optimization from here: commit b729896d00e022f6205399376c0cc107e1ee0704 Author: Monty <monty@mariadb.org> Date: Tue May 10 11:47:20 2022 +0300   MDEV-28073 Query performance degradation in newer MariaDB versions when using many tables The issue was that best_extension_by_limited_search() had to go through too many plans with the same cost as there where many EQ_REF tables. Fixed by shortcutting EQ_REF (AND REF) when the result only contains one row. This got the optimization time down from hours to sub seconds. The only known downside with this patch is that in some cases a table with ref and 1 record may be used before on EQ_REF table. The faster optimzation phase should compensate for this. pruned_by_hanging_leaf is enabled unconditionally.

Dave Gosselin added a comment - 2024-12-16 16:25 psergei here's an idea I had for toggling pruned_by_hanging_leaf functionality with a session var, please let me know what you think. (Obviously this is a WIP and not fully tested, etc. It restores the query time of this particular case): diff --git a/sql/sql_class.h b/sql/sql_class.h index a347700f72f..66af438bf74 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -900,6 +900,7 @@ typedef struct system_variables   vers_asof_timestamp_t vers_asof_timestamp; ulong vers_alter_history; + my_bool prune_by_hanging_leaf; } SV;   /** diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 379cec84b1c..59a0bfbfe7b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10645,7 +10645,8 @@ best_extension_by_limited_search(JOIN *join, check_if_edge_table(join->positions+ idx, pushdown_cond_selectivity) != SEARCH_FOUND_EDGE) - best_res= SEARCH_OK; + best_res= join->thd->variables.prune_by_hanging_leaf ? + SEARCH_OK : SEARCH_FOUND_EDGE; } else { @@ -10684,7 +10685,8 @@ best_extension_by_limited_search(JOIN *join, restore_prev_sj_state(remaining_tables, s, idx); if (best_res == SEARCH_FOUND_EDGE) { - trace_one_table.add("pruned_by_hanging_leaf", true); + trace_one_table.add("pruned_by_hanging_leaf", + join->thd->variables.prune_by_hanging_leaf); goto end; } } diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 42ec52b38a9..a43317cb3e9 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -6879,3 +6879,11 @@ static Sys_var_ulonglong Sys_max_rowid_filter_size( SESSION_VAR(max_rowid_filter_size), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(128*1024), BLOCK_SIZE(1)); +static Sys_var_mybool Sys_prune_by_hanging_leaf( + "prune_by_hanging_leaf", + "Enabled by default. When disabled, limits the recursion on " + "partial query plans when refining edge tables by taking the first " + "satisfactory edge and not refining it further", + SESSION_VAR(prune_by_hanging_leaf), + NO_CMD_LINE, + DEFAULT(TRUE));

Sergei Petrunia added a comment - 2025-01-08 12:57 - edited Backported Optimizer_join_prefixes_check_calls from 10.11 into 10.6, 10.5-before-slowdown and 10.5-after-slowdown. 10.5-after-slowdown 1 min 51.913 sec +-------------------------------------+----------+ | Variable_name | Value | +-------------------------------------+----------+ | Optimizer_join_prefixes_check_calls | 56 331 728 | +-------------------------------------+----------+ 10.5-before-slowdown 8.509 sec MariaDB [test]> show status like '%prefix%'; +-------------------------------------+---------+ | Variable_name | Value | +-------------------------------------+---------+ | Optimizer_join_prefixes_check_calls | 4 623 141 | +-------------------------------------+---------+ 10.6.21(slow) 8 min 48.014 sec +-------------------------------------+---------+ | Variable_name | Value | +-------------------------------------+---------+ | Optimizer_join_prefixes_check_calls | 251 178 443 | +-------------------------------------+---------+ Note that 10.6 has pruned_by_hanging_leaf optimization and I can see it fire, but it doesn't help. Actually, 10.6 is even slower than 10.5-after-slowdown.

Michael Widenius added a comment - 2025-01-08 14:02 In 10.11, the big difference is that we do linked eq_ref optimization at any stage in the query while in 10.6 we only ignore any combinations of eq_ref at the end of the query plan. This is done in get_costs_for_tables() that also sort tables according to cost, which also makes things faster.

Sergei Petrunia added a comment - 2025-01-09 10:10 Backported Optimizer_join_prefixes_check_calls from 10.11 into 10.6, 10.5 The patch is here: psergey-10.5-optimizer_join_prefixes_check_calls.diff -