revision-id: f98c6a9db8c1c8e40761141aadafe1a2ff579526 (mariadb-10.1.41-34-gf98c6a9db8c) parent(s): 0e38cd37c7be46ac2b57344476d68a6dc59bc3a4 author: Varun Gupta committer: Varun Gupta timestamp: 2019-09-08 23:01:47 +0530 message: MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 The issue here was that inside the function table_cond_selectivity we were trying to use the ranges estimate for a key for which no sargable condition was present. The fix for this issue is to make reset the quick structures when we read a TABLE structure from the table cache. This would ensure that we don't read some estimate of previous queries --- mysql-test/r/stat_tables.result | 45 ++++++++++++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 45 ++++++++++++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 26 ++++++++++++++++++++ sql/table.cc | 20 +++++++++++++++ sql/table.h | 1 + 5 files changed, 137 insertions(+) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index d26221b5f8d..81c93203b4a 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -739,6 +739,51 @@ db_name table_name index_name prefix_arity avg_frequency a b test t1 k1 1 1.0000 2 2 test t1 k1 1 1.0000 3 3 drop table t1; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=4; +select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +drop table t1,t2; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set @save_optimizer_switch=@@optimizer_switch; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index b8bed681465..227aada41fd 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -766,6 +766,51 @@ db_name table_name index_name prefix_arity avg_frequency a b test t1 k1 1 1.0000 2 2 test t1 k1 1 1.0000 3 3 drop table t1; +# +# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +# +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=4; +select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 +1 SIMPLE B ref a a 5 const 1 Using index +explain select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 5 NULL 100 Using where; Using index +2 DEPENDENT SUBQUERY t3 ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.t3.id 1 Using where +drop table t1,t2; set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set @save_optimizer_switch=@@optimizer_switch; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index e9f37698a73..2c6ea556e28 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -498,6 +498,32 @@ analyze table t1; select * from mysql.index_stats, t1 where index_name='k1' and t1.a > 1 and t1.b > 1; drop table t1; +--echo # +--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +--echo # + +--source include/have_sequence.inc + +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; + +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; + +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=4; + +let $query= select * from t1 where EXISTS (select * from t1 t3 INNER JOIN t2 ON t2.a = t3.id where t3.a=t1.a and t2.b < 20); + +eval $query; +eval explain $query; + +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; + +eval explain $query; + +drop table t1,t2; + set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set @save_optimizer_switch=@@optimizer_switch; diff --git a/sql/table.cc b/sql/table.cc index 94cd174ffd7..ac46ff6a42c 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4162,6 +4162,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) created= TRUE; cond_selectivity= 1.0; cond_selectivity_sampling_explain= NULL; + quick_condition_rows= 0; + initialize_quick_structures(); #ifdef HAVE_REPLICATION /* used in RBR Triggers */ master_had_triggers= 0; @@ -7546,3 +7548,21 @@ bool fk_modifies_child(enum_fk_option opt) static bool can_write[]= { false, false, true, true, false, true }; return can_write[opt]; } + +/* + @brief + Initialize all the quick structures that are used to stored the + estimates when the range optimizer is run. + @details + This is specifically needed when we read the TABLE structure from the + table cache. There can be some garbage data from previous queries + that need to be reset here. +*/ + +void TABLE::initialize_quick_structures() +{ + bzero(quick_rows, sizeof(quick_rows)); + bzero(quick_key_parts, sizeof(quick_key_parts)); + bzero(quick_costs, sizeof(quick_costs)); + bzero(quick_n_ranges, sizeof(quick_n_ranges)); +} diff --git a/sql/table.h b/sql/table.h index 98ec9f005ea..44803b5aacd 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1450,6 +1450,7 @@ struct TABLE } bool update_const_key_parts(COND *conds); + void initialize_quick_structures(); my_ptrdiff_t default_values_offset() const { return (my_ptrdiff_t) (s->default_values - record[0]); }