revision-id: ab43861b65e9eb2b9a98c202c9dd122aef5e43cf (mariadb-10.2.31-722-gab43861b65e) parent(s): 474ad6beb5461fa09f1d4892a66e7f21e37ad34f author: Varun Gupta committer: Varun Gupta timestamp: 2021-01-30 13:58:01 +0530 message: MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 Make sure to use the histogram statistics only when optimizer_use_condition_selectivity > 3 even if they were read. This may happen that the histogram statistics were read when optimizer_use_condition_selectivity was > 3 and then optimizer_use_condition_selectivity was set to < 4). --- mysql-test/r/selectivity.result | 35 ++++++++++++++++++++++++++++++++++ mysql-test/r/selectivity_innodb.result | 35 ++++++++++++++++++++++++++++++++++ mysql-test/t/selectivity.test | 30 +++++++++++++++++++++++++++++ sql/sql_statistics.cc | 15 ++++++++------- sql/table.cc | 1 + sql/table.h | 7 ++++++- 6 files changed, 115 insertions(+), 8 deletions(-) diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 10af9265649..cd5d2a525ad 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1887,4 +1887,39 @@ a b set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1; # End of 10.1 tests +# +# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 +# +CREATE TABLE t1(a int); +INSERT INTO t1 values (1),(2),(2),(3),(4); +SET optimizer_use_condition_selectivity=4; +SET histogram_size= 255; +set use_stat_tables='preferably'; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 39.84 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +SET optimizer_use_condition_selectivity=3; +# filtered should show 25 % +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +DROP TABLE t1; +# End of 10.2 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index ee0f56ae7ed..8125c61ff1e 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1897,6 +1897,41 @@ a b set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1; # End of 10.1 tests +# +# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 +# +CREATE TABLE t1(a int); +INSERT INTO t1 values (1),(2),(2),(3),(4); +SET optimizer_use_condition_selectivity=4; +SET histogram_size= 255; +set use_stat_tables='preferably'; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 39.84 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +SET optimizer_use_condition_selectivity=3; +# filtered should show 25 % +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +DROP TABLE t1; +# End of 10.2 tests set @@global.histogram_size=@save_histogram_size; 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 a31573edb8e..3397126cae9 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1286,6 +1286,36 @@ drop table t1; --echo # End of 10.1 tests +--echo # +--echo # MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 +--echo # + +CREATE TABLE t1(a int); +INSERT INTO t1 values (1),(2),(2),(3),(4); +SET optimizer_use_condition_selectivity=4; +SET histogram_size= 255; + +set use_stat_tables='preferably'; + +ANALYZE TABLE t1 PERSISTENT FOR ALL; + +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +SET optimizer_use_condition_selectivity=3; + +--echo # filtered should show 25 % + +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +FLUSH TABLES; + +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; + +DROP TABLE t1; + +--echo # End of 10.2 tests + # # Clean up # diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index b63172045e6..3b1d71b8363 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3040,7 +3040,7 @@ int read_histograms_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables) } stats_cb->end_histograms_load(); } - table->histograms_are_read= true; + table->histograms_are_usable= true; DBUG_RETURN(0); } @@ -3130,8 +3130,9 @@ int read_statistics_for_tables(THD *thd, TABLE_LIST *tables) { if (!tl->table->stats_is_read) dump_stats_from_share_to_table(tl->table); - tl->table->histograms_are_read= - table_share->stats_cb.histograms_are_ready(); + tl->table->histograms_are_usable= + (thd->variables.optimizer_use_condition_selectivity > 3 && + table_share->stats_cb.histograms_are_ready()); if (table_share->stats_cb.histograms_are_ready() || thd->variables.optimizer_use_condition_selectivity <= 3) continue; @@ -3748,7 +3749,7 @@ double get_column_range_cardinality(Field *field, col_stats->min_max_values_are_provided()) { Histogram *hist= &col_stats->histogram; - if (hist->is_available()) + if (table->histograms_are_usable && hist->is_available()) { store_key_image_to_rec(field, (uchar *) min_endp->key, field->key_length()); @@ -3792,10 +3793,10 @@ double get_column_range_cardinality(Field *field, max_mp_pos= 1.0; Histogram *hist= &col_stats->histogram; - if (!hist->is_available()) - sel= (max_mp_pos - min_mp_pos); - else + if (table->histograms_are_usable && hist->is_available()) sel= hist->range_selectivity(min_mp_pos, max_mp_pos); + else + sel= (max_mp_pos - min_mp_pos); res= col_non_nulls * sel; set_if_bigger(res, col_stats->get_avg_frequency()); } diff --git a/sql/table.cc b/sql/table.cc index 2ec1fc24271..3245b3638d4 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4652,6 +4652,7 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) cond_selectivity_sampling_explain= NULL; quick_condition_rows=0; initialize_quick_structures(); + histograms_are_usable= FALSE; #ifdef HAVE_REPLICATION /* used in RBR Triggers */ master_had_triggers= 0; diff --git a/sql/table.h b/sql/table.h index a5d412c2c08..6034b54b9a5 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1409,7 +1409,12 @@ struct TABLE #endif uint max_keys; /* Size of allocated key_info array. */ bool stats_is_read; /* Persistent statistics is read for the table */ - bool histograms_are_read; + /* + Set to TRUE when histogram statistics is read for a table and is usable + for a query + Gets reset in TABLE::init() + */ + bool histograms_are_usable; MDL_ticket *mdl_ticket; inline void reset() { bzero((void*)this, sizeof(*this)); }