revision-id: 2b3fd5dff04c699a4743867f73a1bacc8549abe1 (mariadb-10.5.2-487-g2b3fd5dff04) parent(s): 4903031baa196dfc9a75638d141b515883cd254c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-18 21:04:33 +0300 message: MDEV-23677: Optimizer trace: remove "no predicate for first keypart" (not) Don't remove (reasons given in Jira), instead add test coverage. Improve other printout in best_access_path. --- mysql-test/main/opt_trace.result | 38 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 30 ++++++++++++++++++++++++++++++ sql/sql_select.cc | 12 +++++------- 3 files changed, 73 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 8f912e0500d..36bbea8ae78 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -4010,6 +4010,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "ref", "index": "a", + "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 2, @@ -8793,5 +8794,42 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) ] set in_predicate_conversion_threshold=@tmp; drop table t0; +# # End of 10.5 tests +# +# +# MDEV-23677: Optimizer trace ... (test coverage) +# +create table t1(a int, b int, c int, primary key (a,b,c)); +insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); +create table t3 (a int, b int, c int); +insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); +explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t3.a,test.t2.b 1 Using index +set @trace=(select trace from information_schema.optimizer_trace); +set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')); +set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); +select @sub_path; +@sub_path +$.steps[1].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0] +select +json_detailed(json_extract( +@trace, +concat(@sub_path,'.best_access_path.considered_access_paths[0]') +)) as S; +S +{ + "access_type": "ref", + "index": "PRIMARY", + "rows": 1.79769e308, + "cost": 1.79769e308, + "chosen": false, + "cause": "no predicate for first keypart" +} +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index ecb6658e338..83fd5ac0f40 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -696,5 +696,35 @@ from information_schema.optimizer_trace; set in_predicate_conversion_threshold=@tmp; drop table t0; +--echo # --echo # End of 10.5 tests +--echo # + +--echo # +--echo # MDEV-23677: Optimizer trace ... (test coverage) +--echo # +create table t1(a int, b int, c int, primary key (a,b,c)); +insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); + +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); + +create table t3 (a int, b int, c int); +insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); + +explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a; + +set @trace=(select trace from information_schema.optimizer_trace); +set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')); +set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); + +select @sub_path; +select + json_detailed(json_extract( + @trace, + concat(@sub_path,'.best_access_path.considered_access_paths[0]') + )) as S; + +drop table t1,t2,t3; + set optimizer_trace='enabled=off'; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 28b4a5f9b36..5447c398b66 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7637,6 +7637,7 @@ best_access_path(JOIN *join, { if (!(records= keyinfo->actual_rec_per_key(key_parts-1))) { /* Prefer longer keys */ + trace_access_idx.add("rec_per_key_stats_missing", true); records= ((double) s->records / (double) rec * (1.0 + @@ -7663,7 +7664,7 @@ best_access_path(JOIN *join, records > (double) table->opt_range[key].rows) { records= (double) table->opt_range[key].rows; - trace_access_idx.add("used_range_estimates", true); + trace_access_idx.add("used_range_estimates", "clipped down"); } else { @@ -7780,19 +7781,15 @@ best_access_path(JOIN *join, if (!found_ref && // (1) records < rows) // (3) { - trace_access_idx.add("used_range_estimates", true); + trace_access_idx.add("used_range_estimates", "clipped up"); records= rows; } } - else /* (table->quick_key_parts[key] < max_key_part) */ - { - trace_access_idx.add("chosen", true); - cause= "range uses less keyparts"; - } } } else { + trace_access_idx.add("rec_per_key_stats_missing", true); /* Assume that the first key part matches 1% of the file and that the whole key matches 10 (duplicates) or 1 @@ -7856,6 +7853,7 @@ best_access_path(JOIN *join, const_part)) && records > (double) table->opt_range[key].rows) { + trace_access_idx.add("used_range_estimates", true); records= (double) table->opt_range[key].rows; } }