[Commits] 91e925e199c: MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED

28 Jul 2021

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 */

    