revision-id: 7310eb64d8d3e1431fc14341415cf17682a5cf42 (mariadb-10.1.41-35-g7310eb64d8d) parent(s): 031c695b8c865e5eb6c4c09ced404ae08f98430f author: Varun Gupta committer: Varun Gupta timestamp: 2019-09-11 04:12:42 +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/innodb_icp.result | 4 +- mysql-test/r/range_vs_index_merge.result | 2 +- mysql-test/r/range_vs_index_merge_innodb.result | 2 +- mysql-test/r/selectivity.result | 56 +++++++++++++++++++++++++ mysql-test/r/selectivity_innodb.result | 56 +++++++++++++++++++++++++ mysql-test/t/selectivity.test | 32 ++++++++++++++ sql/opt_range.cc | 11 +++++ sql/sql_select.cc | 2 + sql/table.cc | 20 +++++++++ sql/table.h | 1 + 10 files changed, 182 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index a5215bf9f0d..0c95f31ae95 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -679,7 +679,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -690,7 +690,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index bc46a4fdd0b..4f3c36b7660 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1795,7 +1795,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index a6ec200538d..08b7df66c67 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -1796,7 +1796,7 @@ SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range state,capital state 71 NULL 10 Using index condition; Using where +1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 3f5db42d341..db8ff2c53c7 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1668,4 +1668,60 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# 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=2; +SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.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 A INNER JOIN t2 ON t2.a = A.id +WHERE A.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 A ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.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 A INNER JOIN t2 ON t2.a = A.id +WHERE A.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 A ref PRIMARY,a a 5 test.t1.a 1 +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +drop table t1,t2; +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; # End of 10.1 tests diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 1d73c2f5d50..87c47434b5e 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1678,6 +1678,62 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# 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=2; +SELECT * FROM t1 +WHERE +EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id +WHERE A.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 A INNER JOIN t2 ON t2.a = A.id +WHERE A.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 A ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.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 A INNER JOIN t2 ON t2.a = A.id +WHERE A.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 A ref PRIMARY,a a 5 test.t1.a 1 Using index +2 DEPENDENT SUBQUERY t2 ref a,b a 5 test.A.id 1 Using where +drop table t1,t2; +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; # End of 10.1 tests set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index f1c9d6b31b8..6f79927e5d6 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1124,5 +1124,37 @@ drop table t1; set use_stat_tables= @save_use_stat_tables; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--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=2; + +let $query= SELECT * FROM t1 + WHERE + EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id + WHERE A.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_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; + --echo # End of 10.1 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e8421ad052a..118e063e487 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -10131,6 +10131,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init, sel_arg_range_seq_next, 0, 0}; handler *file= param->table->file; ha_rows rows= HA_POS_ERROR; + ha_rows table_records= param->table->stat_records(); uint keynr= param->real_keynr[idx]; DBUG_ENTER("check_quick_select"); @@ -10179,6 +10180,16 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, bufsize, mrr_flags, cost); if (rows != HA_POS_ERROR) { + /* + Capping the estimates of range access to atmost total records of table. + Also the estimate of range access is always atleast 1. + */ + if (rows > table_records) + { + rows= table_records; + set_if_bigger(rows, 1); + } + param->quick_rows[keynr]= rows; param->possible_keys.set_bit(keynr); if (update_tbl_stats) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5b96c15bff5..089c8e96e49 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7644,6 +7644,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, keyparts++; } sel /= (double)table->quick_rows[key] / (double) table->stat_records(); + set_if_smaller(sel, 1.0); used_range_selectivity= true; } } @@ -7759,6 +7760,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, sel*= table_multi_eq_cond_selectivity(join, idx, s, rem_tables, keyparts, ref_keyuse_steps); + DBUG_ASSERT(sel > 0 && sel <= 1); return sel; } 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]); }