mirror of
https://github.com/MariaDB/server.git
synced 2025-08-30 22:31:36 +02:00
b3e70bde38This is done by no longer merging inner table dep with outer nested_join ones. This expands the search space of outer joins and introduces suboptimal join order choices already existing in inner joins (e.g. MDEV-36331, MDEV-37346) to outer joins, caused by heuristic pruning. To mitigate this issue, we add a switch that turns on heuristic pruning always (existing behaviour), and make is so that when the switch is off (default) joins with limited number of tables or limited search space* do not perform heuristic pruning. This improves join order optimization for these joins, where otherwise heuristic pruning may remove good joins - see added test cases in the test main.greedy_optimizer. *: about the same as 10 tables with default search depth (7) when optimizer_search_depth=0. If we completely disable heuristic pruning, or use the upper bound 10!, the test main.greedy_optimizer will grind to almost hanging for optimizer_search_depth=0 when more than 20 tables are involved. If we only compare the number of tables with 10, then it will prune when search space is low due to a low search depth when we can afford not to prune.
18 lines
1.4 KiB
PHP
18 lines
1.4 KiB
PHP
# This file sets the default optimizer flags and optimizer variables that
|
|
# most test expects. In most cases the variables should match the community
|
|
# server defaults.
|
|
# The purpose if this file is to allow users to change these defaults
|
|
# without having to update all tests.
|
|
|
|
--disable_query_log
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set @save_join_cache_level=@@join_cache_level;
|
|
|
|
set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,heuristic_prune_always=off";
|
|
|
|
set optimizer_use_condition_selectivity=4;
|
|
set optimizer_search_depth=62;
|
|
set join_buffer_space_limit=2097152;
|
|
set join_cache_level=2;
|
|
set join_buffer_size=262144;
|
|
--enable_query_log
|