revision-id: 93e964a8f5545c1688fcf1101ea298403cc6dbe1 (mariadb-10.5.11-55-g93e964a8f55) parent(s): 91e925e199ce61623f8413bfa789d0e7098c3d72 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-30 22:53:00 +0300 message: Better Optimize Trace support for LATERAL DERIVED optimization --- sql/opt_split.cc | 86 ++++++++++++++++++++++++++++++++++++++++++------------- sql/sql_array.h | 2 ++ sql/sql_select.cc | 5 ++++ 3 files changed, 73 insertions(+), 20 deletions(-) diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 41b8acf5dcb..61e4eed1b6f 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -188,6 +188,7 @@ #include "mariadb.h" #include "sql_select.h" #include "opt_trace.h" +#include "sql_test.h" /* for print_keyuse_array_for_trace */ /* Info on a splitting field */ struct SplM_field_info @@ -343,6 +344,9 @@ bool JOIN::check_for_splittable_materialized() if (!partition_list) return false; + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_split(thd, "check_lateral_derived"); + ORDER *ord; Dynamic_array<SplM_field_ext_info> candidates(PSI_INSTRUMENT_MEM); @@ -388,8 +392,10 @@ bool JOIN::check_for_splittable_materialized() } } if (candidates.elements() == 0) // no candidates satisfying (8.1) && (8.2) + { + trace_split.add("not_applicable", "group list has no candidates"); return false; - + } /* For each table from this join find the keys that can be used for ref access of the fields mentioned in the 'array candidates' @@ -447,7 +453,11 @@ bool JOIN::check_for_splittable_materialized() } if (!spl_field_cnt) // No candidate field can be accessed by ref => !(9) + { + trace_split.add("not_applicable", + "no candidate field can be accessed through ref"); return false; + } /* Create a structure of the type SplM_opt_info and fill it with @@ -465,16 +475,20 @@ bool JOIN::check_for_splittable_materialized() spl_opt_info->tables_usable_for_splitting= 0; spl_opt_info->spl_field_cnt= spl_field_cnt; spl_opt_info->spl_fields= spl_field; - for (cand= cand_start; cand < cand_end; cand++) { - if (!cand->is_usable_for_ref_access) - continue; - spl_field->producing_item= cand->producing_item; - spl_field->underlying_field= cand->underlying_field; - spl_field->mat_field= cand->mat_field; - spl_opt_info->tables_usable_for_splitting|= - cand->underlying_field->table->map; - spl_field++; + Json_writer_array trace_range(thd, "split_variants"); + for (cand= cand_start; cand < cand_end; cand++) + { + if (!cand->is_usable_for_ref_access) + continue; + spl_field->producing_item= cand->producing_item; + trace_range.add(cand->producing_item); + spl_field->underlying_field= cand->underlying_field; + spl_field->mat_field= cand->mat_field; + spl_opt_info->tables_usable_for_splitting|= + cand->underlying_field->table->map; + spl_field++; + } } /* Attach this info to the table T */ @@ -738,7 +752,16 @@ void JOIN::add_keyuses_for_splitting() spl_opt_info->unsplit_cost= best_positions[table_count-1].read_time + oper_cost; - + { + Json_writer_object obj(thd); + { + //Json_writer_object(thd, "added_keyuses"); + //print_keyuse_array_for_trace(thd, &ext_keyuses_for_splitting); + } + obj.add("unsplit_cost", spl_opt_info->unsplit_cost); + obj.add("unsplit_card", spl_opt_info->unsplit_card); + obj.add("rec_len", (ulonglong) rec_len); + } if (!(save_qep= new Join_plan_state(table_count + 1))) goto err; @@ -894,7 +917,10 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, SplM_plan_info *spl_plan= 0; uint best_key= 0; uint best_key_parts= 0; - + + Json_writer_array spl_trace(thd, "lateral_plan_choice"); + Json_writer_array wrapper(thd); + Json_writer_array trace_indexes(thd, "indexes_for_splitting"); /* Check whether there are keys that can be used to join T employing splitting and if so, select the best out of such keys @@ -939,6 +965,13 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, key_info->actual_rec_per_key(keyuse_ext->keypart); if (rec_per_key < best_rec_per_key) { + Json_writer_object trace(thd); + trace.add_table_name(keyuse_ext->table); + trace.add("index", + keyuse_ext->table->key_info[keyuse_ext->key].name.str); + trace.add("parts", (longlong)keyuse_ext->keypart + 1); + trace.add("rec_per_key", rec_per_key); + best_table= keyuse_ext->table; best_key= keyuse_ext->key; best_key_parts= keyuse_ext->keypart + 1; @@ -951,17 +984,19 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, } while (keyuse_ext->table == table); } + trace_indexes.end(); spl_opt_info->last_plan= 0; + if (best_table) { /* The key for splitting was chosen, look for the plan for this key in the cache */ - Json_writer_array spl_trace(thd, "choose_best_splitting"); spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts); if (!spl_plan) { + Json_writer_array spl_trace(thd, "build_split_plan"); /* The plan for the chosen key has not been found in the cache. Build a new plan and save info on it in the cache @@ -1010,12 +1045,9 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, if (unlikely(thd->trace_started())) { Json_writer_object wrapper(thd); - Json_writer_object find_trace(thd, "best_splitting"); - find_trace.add("table", best_table->alias.c_ptr()); - find_trace.add("key", best_table->key_info[best_key].name); - find_trace.add("record_count", record_count); + Json_writer_object find_trace(thd, "found_split_plan"); find_trace.add("cost", spl_plan->cost); - find_trace.add("unsplit_cost", spl_opt_info->unsplit_cost); + find_trace.add("output_cardinality", split_card); } memcpy((char *) spl_plan->best_positions, (char *) join->best_positions, @@ -1023,8 +1055,19 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table, best_key, remaining_tables, false); } + else + { + Json_writer_object wrapper(thd); + Json_writer_object find_trace(thd, "cached_split_plan_found"); + find_trace.add("cost", spl_plan->cost); + } if (spl_plan) { + Json_writer_object wrapper(thd); + Json_writer_object choice(thd, "split_plan_choice"); + choice.add("split_cost", spl_plan->cost); + choice.add("record_count_for_split", record_count); + choice.add("unsplit_cost", spl_opt_info->unsplit_cost); if(record_count * spl_plan->cost < spl_opt_info->unsplit_cost - 0.01) { /* @@ -1032,7 +1075,10 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, the plan without splitting */ spl_opt_info->last_plan= spl_plan; + choice.add("split_chosen", true); } + else + choice.add("split_chosen", false); } } @@ -1044,9 +1090,9 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, startup_cost= record_count * spl_plan->cost; records= (ha_rows) (records * spl_plan->split_sel); - Json_writer_object trace(thd, "lateral_derived"); + Json_writer_object trace(thd, "chosen_lateral_derived"); trace.add("startup_cost", startup_cost); - trace.add("splitting_cost", spl_plan->cost); + trace.add("lateral_cost", spl_plan->cost); trace.add("records", records); } else diff --git a/sql/sql_array.h b/sql/sql_array.h index b6de1b18d78..244d4a5be06 100644 --- a/sql/sql_array.h +++ b/sql/sql_array.h @@ -289,6 +289,8 @@ template <class Elem> class Dynamic_array { my_qsort2(array.buffer, array.elements, sizeof(Elem), (qsort2_cmp)cmp_func, data); } + + DYNAMIC_ARRAY *impl() { return &array; } }; typedef Bounds_checked_array<Item*> Ref_ptr_array; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7057ed1b5e1..538696b2c7a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5484,7 +5484,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, s->scan_time(); if (s->table->is_splittable()) + { + Json_writer_object trace(thd); + trace.add_table_name(s); + Json_writer_object trace_lateral(thd, "lateral_derived"); s->add_keyuses_for_splitting(); + } /* Set a max range of how many seeks we can expect when using keys