mirror of
https://github.com/MariaDB/server.git
synced 2025-11-05 13:26:14 +01: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.
44 lines
970 B
Text
44 lines
970 B
Text
call sys.optimizer_switch_on();
|
|
option opt
|
|
condition_pushdown_for_derived on
|
|
condition_pushdown_for_subquery on
|
|
condition_pushdown_from_having on
|
|
cset_narrowing on
|
|
derived_merge on
|
|
derived_with_keys on
|
|
duplicateweedout on
|
|
exists_to_in on
|
|
extended_keys on
|
|
firstmatch on
|
|
hash_join_cardinality on
|
|
index_condition_pushdown on
|
|
index_merge on
|
|
index_merge_intersection on
|
|
index_merge_sort_union on
|
|
index_merge_union on
|
|
in_to_exists on
|
|
join_cache_bka on
|
|
join_cache_hashed on
|
|
join_cache_incremental on
|
|
loosescan on
|
|
materialization on
|
|
optimize_join_buffer_size on
|
|
orderby_uses_equalities on
|
|
outer_join_with_cache on
|
|
partial_match_rowid_merge on
|
|
partial_match_table_scan on
|
|
rowid_filter on
|
|
sargable_casefold on
|
|
semijoin on
|
|
semijoin_with_cache on
|
|
split_materialized on
|
|
subquery_cache on
|
|
table_elimination on
|
|
call sys.optimizer_switch_off();
|
|
option opt
|
|
heuristic_prune_always off
|
|
index_merge_sort_intersection off
|
|
mrr off
|
|
mrr_cost_based off
|
|
mrr_sort_keys off
|
|
not_null_range_scan off
|