[Commits] 7d8d37c31d9: MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4
revision-id: 7d8d37c31d9cabaf31576eb3edeae6580ffb16b4 (mariadb-10.0.36-8-g7d8d37c31d9) parent(s): bcc677bb7264db08b22284998706b44c377ed8ec author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-23 16:01:58 +0530 message: MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 and use_stat_tables= PREFERABLY Currently the code that calculates selectivity for a table does not take into account the case when we can have GROUP BY optimization (looses index scan). --- mysql-test/r/group_min_max.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/group_min_max.test | 17 +++++++++++++++++ sql/opt_range.cc | 13 ++++++++++++- 3 files changed, 57 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 25cd4a25279..34e1e256faa 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -3733,5 +3733,33 @@ id MIN(a) MAX(a) 4 2001-01-04 2001-01-04 DROP TABLE t1; # +# Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +# and use_stat_tables= PREFERABLY +# +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +drop table t1; +# # End of 10.0 tests # diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 8c9be0ca8db..72a556c9bc2 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1519,6 +1519,23 @@ ALTER TABLE t1 ADD KEY(id,a); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id; DROP TABLE t1; +--echo # +--echo # Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +--echo # and use_stat_tables= PREFERABLY +--echo # + +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +drop table t1; --echo # --echo # End of 10.0 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f1d84e5c623..734adfbc9d1 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3501,7 +3501,18 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) table->cond_selectivity= 1.0; - if (!cond || table_records == 0) + if (table_records == 0) + DBUG_RETURN(FALSE); + + QUICK_SELECT_I *quick; + if ((quick=table->reginfo.join_tab->quick) && + quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) + { + table->cond_selectivity*= (quick->records/table_records); + DBUG_RETURN(FALSE); + } + + if (!cond) DBUG_RETURN(FALSE); if (table->pos_in_table_list->schema_table)
Hi Varun, Please mention MDEV# in the comment in the testcase. Ok to push after this is addressed. On Thu, Aug 23, 2018 at 04:09:20PM +0530, Varun wrote:
revision-id: 7d8d37c31d9cabaf31576eb3edeae6580ffb16b4 (mariadb-10.0.36-8-g7d8d37c31d9) parent(s): bcc677bb7264db08b22284998706b44c377ed8ec author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-23 16:01:58 +0530 message:
MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 and use_stat_tables= PREFERABLY
Currently the code that calculates selectivity for a table does not take into account the case when we can have GROUP BY optimization (looses index scan).
--- mysql-test/r/group_min_max.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/group_min_max.test | 17 +++++++++++++++++ sql/opt_range.cc | 13 ++++++++++++- 3 files changed, 57 insertions(+), 1 deletion(-)
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 25cd4a25279..34e1e256faa 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -3733,5 +3733,33 @@ id MIN(a) MAX(a) 4 2001-01-04 2001-01-04 DROP TABLE t1; # +# Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +# and use_stat_tables= PREFERABLY +# +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`) +drop table t1; +# # End of 10.0 tests # diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 8c9be0ca8db..72a556c9bc2 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1519,6 +1519,23 @@ ALTER TABLE t1 ADD KEY(id,a); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id; DROP TABLE t1;
+--echo # +--echo # Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +--echo # and use_stat_tables= PREFERABLY +--echo # + +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +drop table t1;
--echo # --echo # End of 10.0 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f1d84e5c623..734adfbc9d1 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3501,7 +3501,18 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond)
table->cond_selectivity= 1.0;
- if (!cond || table_records == 0) + if (table_records == 0) + DBUG_RETURN(FALSE); + + QUICK_SELECT_I *quick; + if ((quick=table->reginfo.join_tab->quick) && + quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) + { + table->cond_selectivity*= (quick->records/table_records); + DBUG_RETURN(FALSE); + } + + if (!cond) DBUG_RETURN(FALSE);
if (table->pos_in_table_list->schema_table) _______________________________________________ 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