-
Oystein Grovlen authored
Part#1: Add optimizer_switch for DuplicateWeedout strategy. Change the join order optimization so that it also possible to turn off DuplicateWeedout semi-join strategy, and add an optimizer_switch to use to turn it on/off. Since for some join orders, DuplicateWeedout may be the only possible strategy, we allow for it to be selected even if strategy is turned off. However, if we later find a join order that support an enabled strategy, we switch to this even if it is more expensive than the join order with DuplicateWeedout. For this to work, we will have to do some changes to how plans are pruned during greedy search. See below for details. sql/sql_const.h sql/sys_vars.cc Add optimizer_switch duplicateweedout sql/sql_planner.h Add Optimize_table_order::found_plan_with_allowed_sj. If DuplicateWeedout is disabled, this will be set to false until a plan without DuplicateWeedout has been found. sql/sql_planner.cc Optimize_table_order::greedy_search Initialize found_plan_without_dupsweedout. It is not sufficient to set it in constructor since if greedy search is done in several iterations, due to setting of optimizer_search_depth, it needs to be reset for each iteration. Optimize_table_order::consider_plan If DuplicateWeedout is disabled, check if current join order requires this strategy. If it does, only select it if it is cheaper than current best plan AND no plan without DuplicateWeedout has been found. If it does not, select it if is cheaper than current best plan OR no plan without DuplicateWeedout has been found. Added a line to Optimizer Trace to explain why a plan that is not cheaper is still selected. Optimize_table_order::best_extension_by_limited_search If DuplicateWeedout strategy is disabled, and current selected plan use DuplicateWeedout, do not prune other plans. Optimize_table_order::advance_sj_state If DuplicateWeedout strategy is disabled, do not select it when there are other applicable strategies for this join order. mysql-test/t/subquery_sj_firstmatch.test mysql-test/t/subquery_sj_loosescan.test mysql-test/t/subquery_sj_mat.test Use new optimizer_switch to turn off DuplicateWeedout strategy for these tests. mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result No plans with DuplicateWeedout anymore mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result Less plans with DuplicateWeedout, but some plans still use DuplicateWeedout since LooseScan and Materialization is not applicable for all queries. Note that plans that still use DuplicateWeedout may have changed to a different plan using DuplicateWeedout since pruning is no longer done in such cases and a cheaper plan may now be found. mysql-test/suite/opt_trace/include/subquery.inc mysql-test/suite/opt_trace/r/subquery_no_prot.result mysql-test/suite/opt_trace/r/subquery_ps_prot.result Also turns off DuplicateWeedout for a test case. Test now trace LooseScan which seems to be the original intention of the test. It also shows the line added to optimizer trace when one switch to a more expensive plan because the previous plan used a disabled semijoin strategy. mysql-test/r/index_merge_myisam.result mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/r/optimizer_switch.result mysql-test/suite/sys_vars/r/optimizer_switch_basic.result Changes in result since output of optimizer_switch now contains an additional switch.
Oystein Grovlen authoredPart#1: Add optimizer_switch for DuplicateWeedout strategy. Change the join order optimization so that it also possible to turn off DuplicateWeedout semi-join strategy, and add an optimizer_switch to use to turn it on/off. Since for some join orders, DuplicateWeedout may be the only possible strategy, we allow for it to be selected even if strategy is turned off. However, if we later find a join order that support an enabled strategy, we switch to this even if it is more expensive than the join order with DuplicateWeedout. For this to work, we will have to do some changes to how plans are pruned during greedy search. See below for details. sql/sql_const.h sql/sys_vars.cc Add optimizer_switch duplicateweedout sql/sql_planner.h Add Optimize_table_order::found_plan_with_allowed_sj. If DuplicateWeedout is disabled, this will be set to false until a plan without DuplicateWeedout has been found. sql/sql_planner.cc Optimize_table_order::greedy_search Initialize found_plan_without_dupsweedout. It is not sufficient to set it in constructor since if greedy search is done in several iterations, due to setting of optimizer_search_depth, it needs to be reset for each iteration. Optimize_table_order::consider_plan If DuplicateWeedout is disabled, check if current join order requires this strategy. If it does, only select it if it is cheaper than current best plan AND no plan without DuplicateWeedout has been found. If it does not, select it if is cheaper than current best plan OR no plan without DuplicateWeedout has been found. Added a line to Optimizer Trace to explain why a plan that is not cheaper is still selected. Optimize_table_order::best_extension_by_limited_search If DuplicateWeedout strategy is disabled, and current selected plan use DuplicateWeedout, do not prune other plans. Optimize_table_order::advance_sj_state If DuplicateWeedout strategy is disabled, do not select it when there are other applicable strategies for this join order. mysql-test/t/subquery_sj_firstmatch.test mysql-test/t/subquery_sj_loosescan.test mysql-test/t/subquery_sj_mat.test Use new optimizer_switch to turn off DuplicateWeedout strategy for these tests. mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result No plans with DuplicateWeedout anymore mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result Less plans with DuplicateWeedout, but some plans still use DuplicateWeedout since LooseScan and Materialization is not applicable for all queries. Note that plans that still use DuplicateWeedout may have changed to a different plan using DuplicateWeedout since pruning is no longer done in such cases and a cheaper plan may now be found. mysql-test/suite/opt_trace/include/subquery.inc mysql-test/suite/opt_trace/r/subquery_no_prot.result mysql-test/suite/opt_trace/r/subquery_ps_prot.result Also turns off DuplicateWeedout for a test case. Test now trace LooseScan which seems to be the original intention of the test. It also shows the line added to optimizer trace when one switch to a more expensive plan because the previous plan used a disabled semijoin strategy. mysql-test/r/index_merge_myisam.result mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/r/optimizer_switch.result mysql-test/suite/sys_vars/r/optimizer_switch_basic.result Changes in result since output of optimizer_switch now contains an additional switch.
Loading