revision-id: 0da66e563076e9675a545757b76215c43258ad99 (mariadb-10.5.0-426-g0da66e56307) parent(s): 8955a27cb0915f6f8a11e10575d5b382e1c2d8c4 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-22 17:05:15 +0300 message: MDEV-21992: queries in innodb_ext_keys use sub-optimal plan with rowid filter Don't construct rowid filter from indexes on which we do index-only scans. The idea is that in this case we can just use the scan that we've used to construct the filter. --- mysql-test/main/innodb_ext_key.result | 18 +++++++++--------- sql/rowid_filter.cc | 16 ++++++++++++++++ 2 files changed, 25 insertions(+), 9 deletions(-) diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index f29f81c0ee4..ff92b4506ba 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -13,7 +13,7 @@ set optimizer_switch='extended_keys=off'; explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity|i_l_shipdate 4|4 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 const 5 Using where flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; count(*) @@ -21,9 +21,9 @@ count(*) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 5 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -33,7 +33,7 @@ set optimizer_switch='extended_keys=on'; explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|8 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; count(*) @@ -41,9 +41,9 @@ count(*) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 2 +Handler_read_next 1 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -274,7 +274,7 @@ explain select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity|i_l_shipdate 4|4 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 const 5 Using where flush status; select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; @@ -283,9 +283,9 @@ max(l_linenumber) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 5 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index 865f22b431a..6fb7035a460 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -350,7 +350,19 @@ void TABLE::init_cost_info_for_usable_range_rowid_filters(THD *thd) From all indexes that can be used for range accesses select only such that - range filter pushdown is supported by the engine for them (1) - they are not clustered primary (2) + - they are not covering (2a) - the range filter containers for them are not too large (3) + + (2a) is based on this logic: if the index $IDX is covering, and we are + going to do a scan on it to build the filter, we can just use $IDX for + reading rows, and not as a filter for some other index. + In ideal world, we would not need to use this condition explicilty because + + cost(scan on $IDX) < cost(scan on $IDX)+cost(build the filter)+ ... + + cost(doing a scan on other index) + + however in reality cost formulas in different parts of the optimizer do not + agree with each other so this is needed. */ while ((key_no= it++) != key_map::Iterator::BITMAP_END) { @@ -358,6 +370,10 @@ void TABLE::init_cost_info_for_usable_range_rowid_filters(THD *thd) continue; if (key_no == s->primary_key && file->primary_key_is_clustered()) // !2 continue; + + if (covering_keys.is_set(key_no)) // !2a + continue; + if (quick_rows[key_no] > get_max_range_rowid_filter_elems_for_table(thd, this, SORTED_ARRAY_CONTAINER)) // !3