summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergei Petrunia <psergey@askmonty.org>2021-03-29 00:33:27 +0300
committerSergei Petrunia <psergey@askmonty.org>2021-03-29 00:33:27 +0300
commit0133e374b60404b8e71a673de980db23daba5a91 (patch)
tree2ad3516e4739c3c3219802b844032f63dea1b9fe
parente538cb095f6290c40e8928e3813db6ac679740a2 (diff)
downloadmariadb-git-bb-10.6-mdev24325.tar.gz
MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVEDbb-10.6-mdev24325
Provide basic coverage in the Optimizer Trace
-rw-r--r--mysql-test/main/opt_trace.result116
-rw-r--r--mysql-test/main/opt_trace.test49
-rw-r--r--sql/opt_split.cc17
-rw-r--r--sql/sql_select.cc2
4 files changed, 183 insertions, 1 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 4d00e32c4a7..f9560b46e3e 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -9070,4 +9070,120 @@ json_detailed(json_extract(trace, '$**.condition_processing'))
}
]
drop table t1,t2,t3;
+#
+# 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 b351699c7a6..13be2d6f604 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -788,4 +788,53 @@ from
drop table t1,t2,t3;
+
+--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 2aa65bdf03b..45053053002 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 &&
(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) &&
@@ -988,6 +990,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);
@@ -1014,6 +1026,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 794bc4d69ae..34d7958f20e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7441,7 +7441,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);
@@ -7449,6 +7448,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 */