From ef76f81c982bdbcfa4797ce26224db9c016ddebd Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 29 Aug 2019 15:37:49 +0300 Subject: MDEV-20109: Optimizer ignores distinct key created for materialized... (Backported to 10.3, addressed review input) Sj_materialization_picker::check_qep(): fix error in cost/fanout calculations: - for each join prefix, add #prefix_rows / TIME_FOR_COMPARE to the cost, like best_extension_by_limited_search does - Remove the fanout produced by the subquery tables. - Also take into account join condition selectivity optimize_wo_join_buffering() (used by LooseScan and FirstMatch) - also add #prefix_rows / TIME_FOR_COMPARE to the cost of each prefix. - Also take into account join condition selectivity --- sql/sql_select.cc | 15 +++++++++++++-- 1 file changed, 13 insertions(+), 2 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9d74505cb74..0cbb480e245 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8244,6 +8244,7 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables, record_count= COST_MULT(record_count, best_positions[i].records_read); read_time= COST_ADD(read_time, best_positions[i].read_time); } + /* TODO: Take into account condition selectivities here */ } *read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE; *record_count_arg= record_count; @@ -15975,10 +15976,20 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, reopt_remaining_tables &= ~rs->table->map; rec_count= COST_MULT(rec_count, pos.records_read); cost= COST_ADD(cost, pos.read_time); - - + cost= COST_ADD(cost, rec_count / (double) TIME_FOR_COMPARE); + //TODO: take into account join condition selectivity here + double pushdown_cond_selectivity= 1.0; + table_map real_table_bit= rs->table->map; + if (join->thd->variables.optimizer_use_condition_selectivity > 1) + { + pushdown_cond_selectivity= table_cond_selectivity(join, i, rs, + reopt_remaining_tables & + ~real_table_bit); + } + (*outer_rec_count) *= pushdown_cond_selectivity; if (!rs->emb_sj_nest) *outer_rec_count= COST_MULT(*outer_rec_count, pos.records_read); + } join->cur_sj_inner_tables= save_cur_sj_inner_tables; -- cgit v1.2.1