revision-id: 91e925e199ce61623f8413bfa789d0e7098c3d72 (mariadb-10.5.11-54-g91e925e199c) parent(s): 0805cdebd33eb39515d05041a924df12396ebc69 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-28 19:56:51 +0300 message: MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED (cherry-pick into 10.5) Provide basic coverage in the Optimizer Trace --- mysql-test/main/opt_trace.result | 116 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 49 +++++++++++++++++ sql/opt_split.cc | 17 ++++++ sql/sql_select.cc | 2 +- 4 files changed, 183 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 8f912e0500d..9bf1cda18a3 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8794,4 +8794,120 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) set in_predicate_conversion_threshold=@tmp; drop table t0; # End of 10.5 tests +# +# MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED +# +create table t1 (a int, b int, index idx_b(b)) engine=myisam; +insert into t1 values +(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3), +(9,3), (8,1), (4,5), (2,3); +create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam; +insert into t2 values +(7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'), +(4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'), +(11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'), +(17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'), +(24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'), +(31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'), +(37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'), +(54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'), +(61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v'); +insert into t2 select a+10, b+10, concat(c,'f') from t2; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain +select t1.a,t.s,t.m +from t1 join +(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t +on t1.a=t.a +where t1.b < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range idx_b idx_b 5 NULL 4 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 1 +select +json_detailed(json_extract(trace, '$**.choose_best_splitting')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.choose_best_splitting')) +[ + + [ + + { + "considered_execution_plans": + [ + + { + "plan_prefix": + [ + ], + "table": "t2", + "best_access_path": + { + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "idx_a", + "used_range_estimates": false, + "cause": "not available", + "rows": 1.8367, + "cost": 2.000585794, + "chosen": true + }, + + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": + { + "type": "ref", + "records": 1.8367, + "cost": 2.000585794, + "uses_join_buffering": false + } + }, + "rows_for_plan": 1.8367, + "cost_for_plan": 2.367925794, + "cost_for_sorting": 1.8367, + "estimated_join_cardinality": 1.8367 + } + ] + }, + + { + "best_splitting": + { + "table": "t2", + "key": "idx_a", + "record_count": 4, + "cost": 2.488945919, + "unsplit_cost": 25.72361682 + } + } + ] +] +select +json_detailed(json_extract(trace, '$**.lateral_derived')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.lateral_derived')) +[ + + { + "startup_cost": 9.955783677, + "splitting_cost": 2.488945919, + "records": 1 + } +] +drop table t1,t2; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index ecb6658e338..66a333d7dc5 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -697,4 +697,53 @@ set in_predicate_conversion_threshold=@tmp; drop table t0; --echo # End of 10.5 tests + +--echo # +--echo # MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED +--echo # +create table t1 (a int, b int, index idx_b(b)) engine=myisam; +insert into t1 values +(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3), +(9,3), (8,1), (4,5), (2,3); + +create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam; +insert into t2 values + (7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'), + (4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'), + (11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'), + (17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'), + (24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'), + (31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'), + (37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'), + (54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'), + (61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v'); + +insert into t2 select a+10, b+10, concat(c,'f') from t2; + +analyze table t1,t2; + +explain +select t1.a,t.s,t.m +from t1 join + (select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t + on t1.a=t.a +where t1.b < 3; + +# +# Just show that choose_best_splitting function has coverage in the +# optimizer trace and re-optmization of child select inside it is distinct +# from the rest of join optimization. +select + json_detailed(json_extract(trace, '$**.choose_best_splitting')) +from + information_schema.optimizer_trace; + +# Same as above. just to show that splitting plan has some coverage in the +# trace. +select + json_detailed(json_extract(trace, '$**.lateral_derived')) +from + information_schema.optimizer_trace; + +drop table t1,t2; set optimizer_trace='enabled=off'; diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 316919c4a81..41b8acf5dcb 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -187,6 +187,7 @@ #include "mariadb.h" #include "sql_select.h" +#include "opt_trace.h" /* Info on a splitting field */ struct SplM_field_info @@ -957,6 +958,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, 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) { @@ -1005,6 +1007,16 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, spl_plan->cost= join->best_positions[join->table_count-1].read_time + + oper_cost; + 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); + find_trace.add("cost", spl_plan->cost); + find_trace.add("unsplit_cost", spl_opt_info->unsplit_cost); + } memcpy((char *) spl_plan->best_positions, (char *) join->best_positions, sizeof(POSITION) * join->table_count); @@ -1031,6 +1043,11 @@ 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"); + trace.add("startup_cost", startup_cost); + trace.add("splitting_cost", spl_plan->cost); + trace.add("records", records); } else startup_cost= spl_opt_info->unsplit_cost; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 80bc24cd3cd..7057ed1b5e1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7449,7 +7449,6 @@ best_access_path(JOIN *join, DBUG_ENTER("best_access_path"); Json_writer_object trace_wrapper(thd, "best_access_path"); - Json_writer_array trace_paths(thd, "considered_access_paths"); bitmap_clear_all(eq_join_set); @@ -7457,6 +7456,7 @@ best_access_path(JOIN *join, if (s->table->is_splittable()) spl_plan= s->choose_best_splitting(record_count, remaining_tables); + Json_writer_array trace_paths(thd, "considered_access_paths"); if (s->keyuse) { /* Use key if possible */