maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

As far as I understand, the intent of the patch is that this check:

+  for (uint i=0; i < n; i++)
+  {
+    if (item1->element_index(i)->cmp_type() !=
+        item2->element_index(i)->cmp_type())

matches this check in subquery_types_allow_materialization()

    if (!inner->type_handler()->subquery_type_allows_materialization(inner,
                                                                     outer))

which has implementations like so:

  bool Type_handler_real_result::
         subquery_type_allows_materialization(const Item *inner,
                                              const Item *outer) const
  {
    DBUG_ASSERT(inner->cmp_type() == REAL_RESULT);
    return outer->cmp_type() == REAL_RESULT;
  }
  // .. and the same for other datatypes ...

I would like to see comments in both places (cmp_row_types and
subquery_types_allow_materialization()) explaining that.

Note that some Type_handler::subquery_types_allow_materialization() have a more
complex implementation, for example
Type_handler_string_result::subquery_types_allow_materialization() compares
collations.

Is there any reasons why cmp_row_types() doesn't use the same call as
subquery_types_allow_materialization does:

    if (!inner->type_handler()->subquery_type_allows_materialization(inner,
                                                                     outer))

?


On Mon, Nov 04, 2019 at 04:38:02PM +0530, Varun wrote:
> revision-id: 3a11b49bb5b716538f98c6a212bbbfa6fc9b7a88 (mariadb-10.3.17-145-g3a11b49bb5b)
> parent(s): 162f475c4be81dfbceed093ad03d114b4c69a3c0
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2019-11-04 16:28:25 +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/sql_tvc.cc                 | 27 ++++++++++++++++++++-
>  3 files changed, 106 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/sql_tvc.cc b/sql/sql_tvc.cc
> index 816c6fe1089..78c7c34a81a 100644
> --- a/sql/sql_tvc.cc
> +++ b/sql/sql_tvc.cc
> @@ -796,6 +796,31 @@ 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
> +
> +  @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++)
> +  {
> +    if (item1->element_index(i)->cmp_type() !=
> +        item2->element_index(i)->cmp_type())
> +      return true;
> +  }
> +  return false;
> +}
> +
> +
>  /**
>    @brief
>      Transform IN predicate into IN subquery
> @@ -843,7 +868,7 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
>    
>    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;
>    }
>  
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> 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