Skip to content
  • Oystein Grovlen's avatar
    1cde3fe3
    WL#8244 Hints for subquery strategies. · 1cde3fe3
    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.
    1cde3fe3
    WL#8244 Hints for subquery strategies.
    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.
Loading