MDEV-28929: Plan selection takes forever with MDEV-28852 ...bb-10.10-spetrunia2bb-10.10-mdev28929-v4

Part #2: Extend heuristic pruning to use multiple tables as the
"Model tables".

Before the patch, heuristic pruning uses only one "Model table":
The table which had the best cost AND record became the "Model table".
After that, if a table's cost and record were both worse than
those of the Model Table, the table would be pruned away.

This didn't work well when the first table (the optimizer sorts them
by record_count) had low record_count but relatively high cost: nothing
could be pruned afterwards.

The patch adds the two additional "Model tables": one with the least
cost and the other with the least record_count.
(In both cases, a table can be pruned away if BOTH its cost and
record_count are worse than those of a Model table)

The new pruning is active when the number of tables to consider for
the prefix is higher than @@optimizer_extra_pruning_depth.

One can see the new pruning in the Optimizer Trace as
- "pruned_by_heuristic":"min_record_count", or
- "pruned_by_heuristic":"min_read_time".
Old heuristic pruning shows as "pruned_by_heuristic":1.

1 files changed, 7 insertions, 0 deletions

diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 43b6f83d568..fc9b4262394 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2781,6 +2781,13 @@ static Sys_var_ulong Sys_optimizer_search_depth(
        SESSION_VAR(optimizer_search_depth), CMD_LINE(REQUIRED_ARG),
        VALID_RANGE(0, MAX_TABLES+1), DEFAULT(MAX_TABLES+1), BLOCK_SIZE(1));
 
+static Sys_var_ulong Sys_optimizer_extra_pruning_depth(
+       "optimizer_extra_pruning_depth",
+       "If the optimizer needs to enumerate join prefix of this size or "
+       "larger, then it will try agressively prune away the search space.",
+       SESSION_VAR(optimizer_extra_pruning_depth), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(0, MAX_TABLES+1), DEFAULT(8), BLOCK_SIZE(1));
+
 /* this is used in the sigsegv handler */
 export const char *optimizer_switch_names[]=
 {