[Commits] d6fcc71e774: MDEV-20900: IN predicate to IN subquery conversion causes performance regression
revision-id: d6fcc71e774a127902570ba335b68383356bbf21 (mariadb-10.3.18-119-gd6fcc71e774) parent(s): 8a46b706aab428998e0a3d0315c7c3d67752fddc author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-04 13:49:12 +0530 message: MDEV-20900: IN predicate to IN subquery conversion causes performance regression Disable the IN predicate to IN subquery conversion when the types on the left and right hand side of the IN predicate are not of comparable type. --- mysql-test/main/opt_tvc.result | 53 +++++++++++++++++++++++++++++++++++++---- mysql-test/main/opt_tvc.test | 31 ++++++++++++++++++++++++ sql/item_cmpfunc.h | 1 + sql/sql_tvc.cc | 54 +++++++++++++++++++++++++++++++++++++++++- 4 files changed, 134 insertions(+), 5 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 5329a9f64be..a68e70e8a25 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -629,11 +629,9 @@ SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); i EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) -3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`_col_1` +Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL) SET in_predicate_conversion_threshold= default; DROP TABLE t1; # @@ -687,3 +685,50 @@ f1 f2 1 1 DROP TABLE t1,t2,t3; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-20900: IN predicate to IN subquery conversion causes performance regression +# +create table t1(a int, b int); +insert into t1 select seq-1, seq-1 from seq_1_to_10; +set in_predicate_conversion_threshold=2; +explain select * from t1 where t1.a IN ("1","2","3","4"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where t1.a IN ("1","2","3","4"); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=0; +explain select * from t1 where t1.a IN ("1","2","3","4"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where t1.a IN ("1","2","3","4"); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=2; +explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=0; +explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +a b +1 1 +2 2 +3 3 +4 4 +drop table t1; +SET @@in_predicate_conversion_threshold= default; diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index 7319dbdc9e8..e4e8c6d7919 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -3,6 +3,7 @@ # source include/have_debug.inc; source include/default_optimizer_switch.inc; +source include/have_sequence.inc; create table t1 (a int, b int); @@ -397,3 +398,33 @@ SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1)); DROP TABLE t1,t2,t3; SET @@in_predicate_conversion_threshold= default; + +--echo # +--echo # MDEV-20900: IN predicate to IN subquery conversion causes performance regression +--echo # + +create table t1(a int, b int); +insert into t1 select seq-1, seq-1 from seq_1_to_10; + +set in_predicate_conversion_threshold=2; + +let $query= select * from t1 where t1.a IN ("1","2","3","4"); +eval explain $query; +eval $query; + +set in_predicate_conversion_threshold=0; +eval explain $query; +eval $query; + +set in_predicate_conversion_threshold=2; +let $query= select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +eval explain $query; +eval $query; + +set in_predicate_conversion_threshold=0; +eval explain $query; +eval $query; + +drop table t1; +SET @@in_predicate_conversion_threshold= default; + diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 24a9991640a..29c32be1c80 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2418,6 +2418,7 @@ class Item_func_in :public Item_func_opt_neg, bool to_be_transformed_into_in_subq(THD *thd); bool create_value_list_for_tvc(THD *thd, List< List<Item> > *values); Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg); + uint32 max_length_of_left_expr(); }; class cmp_item_row :public cmp_item diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 816c6fe1089..8d0ded8f7cb 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -796,6 +796,38 @@ bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) } +/* + @brief + Check whether the items are of comparable type or not + + @details + This check are done because materialization is not performed + if the left expr and right expr are of the same types. + @see subquery_types_allow_materialization() + + @retval + 0 comparable + 1 not comparable +*/ + +static bool cmp_row_types(Item* item1, Item* item2) +{ + uint n= item1->cols(); + if (item2->check_cols(n)) + return true; + + for (uint i=0; i < n; i++) + { + Item *inner= item1->element_index(i); + Item *outer= item2->element_index(i); + if (!inner->type_handler()->subquery_type_allows_materialization(inner, + outer)) + return true; + } + return false; +} + + /** @brief Transform IN predicate into IN subquery @@ -840,10 +872,20 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, /* SELECT_LEX object where the transformation is performed */ SELECT_LEX *parent_select= lex->current_select; uint8 save_derived_tables= lex->derived_tables; + + /* + Make sure that create_tmp_table will not fail due to too long keys. + Here the strategy would mainly use materialization, so we need to make + sure that the materialized table can be created. + */ + uint32 length= max_length_of_left_expr(); + if (!length || length > tmp_table_max_key_length() || + args[0]->cols() > tmp_table_max_key_parts()) + return this; for (uint i=1; i < arg_count; i++) { - if (!args[i]->const_item()) + if (!args[i]->const_item() || cmp_row_types(args[0], args[i])) return this; } @@ -948,6 +990,16 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, } +uint32 Item_func_in::max_length_of_left_expr() +{ + uint n= args[0]->cols(); + uint32 length= 0; + for (uint i=0; i < n; i++) + length+= args[0]->element_index(i)->max_length; + return length; +} + + /** @brief Check if this IN-predicate can be transformed in IN-subquery
Hi Varun, Please add comments: - inside Item_func_in::in_predicate_to_in_subs_transformer() a comment saying that the check is the same as in subquery_type_allows_materialization() - inside subquery_type_allows_materialization() saying that the check here must be kept in sync with the one in Item_func_in::in_predicate_to_in_subs_transformer(). Ok to push after this is addressed. On Wed, Dec 04, 2019 at 01:50:15PM +0530, Varun wrote:
revision-id: d6fcc71e774a127902570ba335b68383356bbf21 (mariadb-10.3.18-119-gd6fcc71e774) parent(s): 8a46b706aab428998e0a3d0315c7c3d67752fddc author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-04 13:49:12 +0530 message:
MDEV-20900: IN predicate to IN subquery conversion causes performance regression
Disable the IN predicate to IN subquery conversion when the types on the left and right hand side of the IN predicate are not of comparable type.
--- mysql-test/main/opt_tvc.result | 53 +++++++++++++++++++++++++++++++++++++---- mysql-test/main/opt_tvc.test | 31 ++++++++++++++++++++++++ sql/item_cmpfunc.h | 1 + sql/sql_tvc.cc | 54 +++++++++++++++++++++++++++++++++++++++++- 4 files changed, 134 insertions(+), 5 deletions(-)
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 5329a9f64be..a68e70e8a25 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -629,11 +629,9 @@ SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); i EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) -3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`_col_1` +Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL) SET in_predicate_conversion_threshold= default; DROP TABLE t1; # @@ -687,3 +685,50 @@ f1 f2 1 1 DROP TABLE t1,t2,t3; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-20900: IN predicate to IN subquery conversion causes performance regression +# +create table t1(a int, b int); +insert into t1 select seq-1, seq-1 from seq_1_to_10; +set in_predicate_conversion_threshold=2; +explain select * from t1 where t1.a IN ("1","2","3","4"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where t1.a IN ("1","2","3","4"); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=0; +explain select * from t1 where t1.a IN ("1","2","3","4"); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where t1.a IN ("1","2","3","4"); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=2; +explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +a b +1 1 +2 2 +3 3 +4 4 +set in_predicate_conversion_threshold=0; +explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +a b +1 1 +2 2 +3 3 +4 4 +drop table t1; +SET @@in_predicate_conversion_threshold= default; diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index 7319dbdc9e8..e4e8c6d7919 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -3,6 +3,7 @@ # source include/have_debug.inc; source include/default_optimizer_switch.inc; +source include/have_sequence.inc;
create table t1 (a int, b int);
@@ -397,3 +398,33 @@ SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1)); DROP TABLE t1,t2,t3;
SET @@in_predicate_conversion_threshold= default; + +--echo # +--echo # MDEV-20900: IN predicate to IN subquery conversion causes performance regression +--echo # + +create table t1(a int, b int); +insert into t1 select seq-1, seq-1 from seq_1_to_10; + +set in_predicate_conversion_threshold=2; + +let $query= select * from t1 where t1.a IN ("1","2","3","4"); +eval explain $query; +eval $query; + +set in_predicate_conversion_threshold=0; +eval explain $query; +eval $query; + +set in_predicate_conversion_threshold=2; +let $query= select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); +eval explain $query; +eval $query; + +set in_predicate_conversion_threshold=0; +eval explain $query; +eval $query; + +drop table t1; +SET @@in_predicate_conversion_threshold= default; + diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 24a9991640a..29c32be1c80 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2418,6 +2418,7 @@ class Item_func_in :public Item_func_opt_neg, bool to_be_transformed_into_in_subq(THD *thd); bool create_value_list_for_tvc(THD *thd, List< List<Item> > *values); Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg); + uint32 max_length_of_left_expr(); };
class cmp_item_row :public cmp_item diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 816c6fe1089..8d0ded8f7cb 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -796,6 +796,38 @@ bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) }
+/* + @brief + Check whether the items are of comparable type or not + + @details + This check are done because materialization is not performed + if the left expr and right expr are of the same types. + @see subquery_types_allow_materialization() + + @retval + 0 comparable + 1 not comparable +*/ + +static bool cmp_row_types(Item* item1, Item* item2) +{ + uint n= item1->cols(); + if (item2->check_cols(n)) + return true; + + for (uint i=0; i < n; i++) + { + Item *inner= item1->element_index(i); + Item *outer= item2->element_index(i); + if (!inner->type_handler()->subquery_type_allows_materialization(inner, + outer)) + return true; + } + return false; +} + + /** @brief Transform IN predicate into IN subquery @@ -840,10 +872,20 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, /* SELECT_LEX object where the transformation is performed */ SELECT_LEX *parent_select= lex->current_select; uint8 save_derived_tables= lex->derived_tables; + + /* + Make sure that create_tmp_table will not fail due to too long keys. + Here the strategy would mainly use materialization, so we need to make + sure that the materialized table can be created. + */ + uint32 length= max_length_of_left_expr(); + if (!length || length > tmp_table_max_key_length() || + args[0]->cols() > tmp_table_max_key_parts()) + return this;
for (uint i=1; i < arg_count; i++) { - if (!args[i]->const_item()) + if (!args[i]->const_item() || cmp_row_types(args[0], args[i])) return this; }
@@ -948,6 +990,16 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, }
+uint32 Item_func_in::max_length_of_left_expr() +{ + uint n= args[0]->cols(); + uint32 length= 0; + for (uint i=0; i < n; i++) + length+= args[0]->element_index(i)->max_length; + return length; +} + + /** @brief Check if this IN-predicate can be transformed in IN-subquery _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
-- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
participants (2)
-
Sergey Petrunia -
Varun