+---------------------------------+ | section | +---------------------------------+ | PARAMETER SNAPSHOT BEFORE SETUP | +---------------------------------+ +-----------------+------------------------------+-------------------------------+-------------------------+------------------------+-----------------------+-----------------------+--------------------------------------------+ | mariadb_version | optimizer_prune_level_before | optimizer_search_depth_before | join_cache_level_before | use_stat_tables_before | histogram_size_before | histogram_type_before | optimizer_use_condition_selectivity_before | +-----------------+------------------------------+-------------------------------+-------------------------+------------------------+-----------------------+-----------------------+--------------------------------------------+ | 12.3.2-MariaDB | 2 | 62 | 2 | PREFERABLY_FOR_QUERIES | 254 | JSON_HB | 4 | +-----------------+------------------------------+-------------------------------+-------------------------+------------------------+-----------------------+-----------------------+--------------------------------------------+ +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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,not_null_range_scan=off,hash_join_cardinality=on,cset_narrowing=on,sargable_casefold=on,reorder_outer_joins=off | +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------+ | section | +--------------------------------------+ | PARAMETER SNAPSHOT AFTER SETUP KNOBS | +--------------------------------------+ +-----------------------+------------------------+------------------+-----------------+----------------+----------------+-------------------------------------+ | optimizer_prune_level | optimizer_search_depth | join_cache_level | use_stat_tables | histogram_size | histogram_type | optimizer_use_condition_selectivity | +-----------------------+------------------------+------------------+-----------------+----------------+----------------+-------------------------------------+ | 0 | 62 | 0 | PREFERABLY | 254 | JSON_HB | 5 | +-----------------------+------------------------+------------------+-----------------+----------------+----------------+-------------------------------------+ +--------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_join_order_fanout_misestimation.t0 | analyze | status | Engine-independent statistics collected | | mariadb_join_order_fanout_misestimation.t0 | analyze | status | OK | | mariadb_join_order_fanout_misestimation.t1 | analyze | status | Engine-independent statistics collected | | mariadb_join_order_fanout_misestimation.t1 | analyze | status | OK | | mariadb_join_order_fanout_misestimation.t3 | analyze | status | Engine-independent statistics collected | | mariadb_join_order_fanout_misestimation.t3 | analyze | status | OK | +--------------------------------------------+---------+----------+-----------------------------------------+ +---------------------------------------+ | section | +---------------------------------------+ | SANITY CHECKS BEFORE PERSISTENT STATS | +---------------------------------------+ +------------+---------+---------+---------+ | check_name | t0_rows | t1_rows | t3_rows | +------------+---------+---------+---------+ | row-counts | 300006 | 1000006 | 20003 | +------------+---------+---------+---------+ +------------+------------------+-----------+-----------+-----------+ | check_name | t0_c3_prefix_ndv | t1_c0_ndv | t3_c0_ndv | t3_c3_ndv | +------------+------------------+-----------+-----------+-----------+ | ndv-check | 17576 | 4 | 20003 | 4 | +------------+------------------+-----------+-----------+-----------+ +-------------+---------------+---------------+-------------------+ | check_name | t1_t3_matches | t3_t0_matches | final_result_rows | +-------------+---------------+---------------+-------------------+ | join-counts | 6 | 6 | 12 | +-------------+---------------+---------------+-------------------+ +--------------------------------------------------+ | section | +--------------------------------------------------+ | BASE EXPLAIN FORMAT=JSON BEFORE PERSISTENT STATS | +--------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2983528.464, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 1000006, "cost": 147.735382, "filtered": 100, "attached_condition": "t1.c0 is not null", "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "rows": 1, "cost": 1820.270863, "filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "rows": 3000, "cost": 2981560.457, "filtered": 100, "attached_condition": "t0.c3 = t3.c3" } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------+ | section | +---------------------------------------------------------------------------------+ | FORCED_FAST EXPLAIN FORMAT=JSON BEFORE PERSISTENT STATS: STRAIGHT_JOIN t3,t0,t1 | +---------------------------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2201419665, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i5", "i6"], "loops": 1, "rows": 20003, "cost": 3.3534046, "filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null and t3.c0 is not null" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 20003, "rows": 3000, "cost": 59642.07759, "filtered": 100, "attached_condition": "t0.c3 = t3.c3" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c0"], "loops": 60010200.18, "rows": 250001, "cost": 2201360019, "filtered": 100, "using_index": true } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------+ | section | +---------------------------------------------------------------------------------+ | FORCED_SLOW EXPLAIN FORMAT=JSON BEFORE PERSISTENT STATS: STRAIGHT_JOIN t1,t3,t0 | +---------------------------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2983528.464, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 1000006, "cost": 147.735382, "filtered": 100, "attached_condition": "t1.c0 is not null", "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "rows": 1, "cost": 1820.270863, "filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "rows": 3000, "cost": 2981560.457, "filtered": 100, "attached_condition": "t0.c3 = t3.c3" } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------+ | section | +--------------------------------------------------+ | BASE ANALYZE FORMAT=JSON BEFORE PERSISTENT STATS | +--------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.057945971 }, "query_block": { "select_id": 1, "cost": 2983528.464, "r_loops": 1, "r_total_time_ms": 1077.470424, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 1000006, "r_rows": 1000006, "cost": 147.735382, "r_table_time_ms": 242.7407981, "r_other_time_ms": 75.78381975, "r_engine_stats": { "pages_accessed": 1265 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "r_loops": 1000006, "rows": 1, "r_rows": 5.999964e-6, "cost": 1820.270863, "r_table_time_ms": 732.871316, "r_other_time_ms": 25.68842699, "r_engine_stats": { "pages_accessed": 2000024 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null", "r_filtered": 100 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "r_loops": 6, "rows": 3000, "r_rows": 23, "cost": 2981560.457, "r_table_time_ms": 0.348617582, "r_other_time_ms": 0.033569878, "r_engine_stats": { "pages_accessed": 432 }, "filtered": 100, "r_total_filtered": 8.695652174, "attached_condition": "t0.c3 = t3.c3", "r_filtered": 8.695652174 } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------+ | section | +---------------------------------------------------------------------------------+ | FORCED_FAST ANALYZE FORMAT=JSON BEFORE PERSISTENT STATS: STRAIGHT_JOIN t3,t0,t1 | +---------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.079776302 }, "query_block": { "select_id": 1, "cost": 2201419665, "r_loops": 1, "r_total_time_ms": 36.96458572, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i5", "i6"], "loops": 1, "r_loops": 1, "rows": 20003, "r_rows": 20003, "cost": 3.3534046, "r_table_time_ms": 7.468493627, "r_other_time_ms": 2.796355056, "r_engine_stats": { "pages_accessed": 92 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null and t3.c0 is not null", "r_filtered": 100 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 20003, "r_loops": 20003, "rows": 3000, "r_rows": 0.003449483, "cost": 59642.07759, "r_table_time_ms": 26.07099718, "r_other_time_ms": 0.608088781, "r_engine_stats": { "pages_accessed": 60216 }, "filtered": 100, "r_total_filtered": 8.695652174, "attached_condition": "t0.c3 = t3.c3", "r_filtered": 8.695652174 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c0"], "loops": 60010200.18, "r_loops": 6, "rows": 250001, "r_rows": 2, "cost": 2201360019, "r_table_time_ms": 0.015016974, "r_other_time_ms": 0.002985126, "r_engine_stats": { "pages_accessed": 18 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------+ | section | +---------------------------------------------------------------------------------+ | FORCED_SLOW ANALYZE FORMAT=JSON BEFORE PERSISTENT STATS: STRAIGHT_JOIN t1,t3,t0 | +---------------------------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.08164977 }, "query_block": { "select_id": 1, "cost": 2983528.464, "r_loops": 1, "r_total_time_ms": 1071.829078, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 1000006, "r_rows": 1000006, "cost": 147.735382, "r_table_time_ms": 241.8078577, "r_other_time_ms": 74.76452349, "r_engine_stats": { "pages_accessed": 1265 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "r_loops": 1000006, "rows": 1, "r_rows": 5.999964e-6, "cost": 1820.270863, "r_table_time_ms": 729.5400635, "r_other_time_ms": 25.45985659, "r_engine_stats": { "pages_accessed": 2000024 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null", "r_filtered": 100 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "r_loops": 6, "rows": 3000, "r_rows": 23, "cost": 2981560.457, "r_table_time_ms": 0.23935357, "r_other_time_ms": 0.014660732, "r_engine_stats": { "pages_accessed": 432 }, "filtered": 100, "r_total_filtered": 8.695652174, "attached_condition": "t0.c3 = t3.c3", "r_filtered": 8.695652174 } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------+ | section | +--------------------------------------------+ | BASE DIRECT TIMING BEFORE PERSISTENT STATS | +--------------------------------------------+ +------------+---------------+ | ref0 | ref1 | +------------+---------------+ | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | +------------+---------------+ +-----------------------------------------+ | base_elapsed_ms_before_persistent_stats | +-----------------------------------------+ | 1104.5100 | +-----------------------------------------+ +---------------------------------------------------------------------------+ | section | +---------------------------------------------------------------------------+ | FORCED_FAST DIRECT TIMING BEFORE PERSISTENT STATS: STRAIGHT_JOIN t3,t0,t1 | +---------------------------------------------------------------------------+ +------------+---------------+ | ref0 | ref1 | +------------+---------------+ | 2000000001 | match_key_000 | | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000000 | match_key_000 | | 2000000003 | match_key_001 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000002 | match_key_001 | | 2000000005 | match_key_002 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | | 2000000004 | match_key_002 | +------------+---------------+ +------------------------------------------------+ | forced_fast_elapsed_ms_before_persistent_stats | +------------------------------------------------+ | 34.3630 | +------------------------------------------------+ +---------------------------------------------------------------------------+ | section | +---------------------------------------------------------------------------+ | FORCED_SLOW DIRECT TIMING BEFORE PERSISTENT STATS: STRAIGHT_JOIN t1,t3,t0 | +---------------------------------------------------------------------------+ +------------+---------------+ | ref0 | ref1 | +------------+---------------+ | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | +------------+---------------+ +------------------------------------------------+ | forced_slow_elapsed_ms_before_persistent_stats | +------------------------------------------------+ | 1063.8900 | +------------------------------------------------+ +----------------------------------------------------+ | section | +----------------------------------------------------+ | CREATE MARIADB PERSISTENT ENGINE-INDEPENDENT STATS | +----------------------------------------------------+ +--------------------------------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------------+---------+----------+-----------------------------------------+ | mariadb_join_order_fanout_misestimation.t0 | analyze | status | Engine-independent statistics collected | | mariadb_join_order_fanout_misestimation.t0 | analyze | status | OK | | mariadb_join_order_fanout_misestimation.t1 | analyze | status | Engine-independent statistics collected | | mariadb_join_order_fanout_misestimation.t1 | analyze | status | OK | | mariadb_join_order_fanout_misestimation.t3 | analyze | status | Engine-independent statistics collected | | mariadb_join_order_fanout_misestimation.t3 | analyze | status | OK | +--------------------------------------------+---------+----------+-----------------------------------------+ +--------------------------------------+ | section | +--------------------------------------+ | SANITY CHECKS AFTER PERSISTENT STATS | +--------------------------------------+ +------------+---------+---------+---------+ | check_name | t0_rows | t1_rows | t3_rows | +------------+---------+---------+---------+ | row-counts | 300006 | 1000006 | 20003 | +------------+---------+---------+---------+ +-------------+---------------+---------------+-------------------+ | check_name | t1_t3_matches | t3_t0_matches | final_result_rows | +-------------+---------------+---------------+-------------------+ | join-counts | 6 | 6 | 12 | +-------------+---------------+---------------+-------------------+ +-------------------------------------------------+ | section | +-------------------------------------------------+ | BASE EXPLAIN FORMAT=JSON AFTER PERSISTENT STATS | +-------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2983528.462, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 1000006, "cost": 147.735382, "filtered": 100, "attached_condition": "t1.c0 is not null", "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "rows": 1, "cost": 1820.270044, "filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "rows": 3000, "cost": 2981560.457, "filtered": 100, "attached_condition": "t0.c3 = t3.c3" } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------+ | section | +--------------------------------------------------------------------------------+ | FORCED_FAST EXPLAIN FORMAT=JSON AFTER PERSISTENT STATS: STRAIGHT_JOIN t3,t0,t1 | +--------------------------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2201419665, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i5", "i6"], "loops": 1, "rows": 20003, "cost": 3.3534046, "filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null and t3.c0 is not null" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 20003, "rows": 3000, "cost": 59642.07677, "filtered": 100, "attached_condition": "t0.c3 = t3.c3" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c0"], "loops": 60010200.18, "rows": 250001, "cost": 2201360019, "filtered": 100, "using_index": true } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------+ | section | +--------------------------------------------------------------------------------+ | FORCED_SLOW EXPLAIN FORMAT=JSON AFTER PERSISTENT STATS: STRAIGHT_JOIN t1,t3,t0 | +--------------------------------------------------------------------------------+ +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost": 2983528.462, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "rows": 1000006, "cost": 147.735382, "filtered": 100, "attached_condition": "t1.c0 is not null", "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "rows": 1, "cost": 1820.270044, "filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null" } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "rows": 3000, "cost": 2981560.457, "filtered": 100, "attached_condition": "t0.c3 = t3.c3" } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------------+ | section | +-------------------------------------------------+ | BASE ANALYZE FORMAT=JSON AFTER PERSISTENT STATS | +-------------------------------------------------+ +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.057776071 }, "query_block": { "select_id": 1, "cost": 2983528.462, "r_loops": 1, "r_total_time_ms": 1131.329206, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 1000006, "r_rows": 1000006, "cost": 147.735382, "r_table_time_ms": 252.8780866, "r_other_time_ms": 82.47072466, "r_engine_stats": { "pages_accessed": 1265 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "r_loops": 1000006, "rows": 1, "r_rows": 5.999964e-6, "cost": 1820.270044, "r_table_time_ms": 768.3100667, "r_other_time_ms": 27.40671497, "r_engine_stats": { "pages_accessed": 2000024 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null", "r_filtered": 100 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "r_loops": 6, "rows": 3000, "r_rows": 23, "cost": 2981560.457, "r_table_time_ms": 0.244778497, "r_other_time_ms": 0.015978827, "r_engine_stats": { "pages_accessed": 432 }, "filtered": 100, "r_total_filtered": 8.695652174, "attached_condition": "t0.c3 = t3.c3", "r_filtered": 8.695652174 } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------+ | section | +--------------------------------------------------------------------------------+ | FORCED_FAST ANALYZE FORMAT=JSON AFTER PERSISTENT STATS: STRAIGHT_JOIN t3,t0,t1 | +--------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.089266043 }, "query_block": { "select_id": 1, "cost": 2201419665, "r_loops": 1, "r_total_time_ms": 37.47949436, "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", "possible_keys": ["i5", "i6"], "loops": 1, "r_loops": 1, "rows": 20003, "r_rows": 20003, "cost": 3.3534046, "r_table_time_ms": 7.569238886, "r_other_time_ms": 2.911471486, "r_engine_stats": { "pages_accessed": 92 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null and t3.c0 is not null", "r_filtered": 100 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 20003, "r_loops": 20003, "rows": 3000, "r_rows": 0.003449483, "cost": 59642.07677, "r_table_time_ms": 26.34725469, "r_other_time_ms": 0.619883134, "r_engine_stats": { "pages_accessed": 60216 }, "filtered": 100, "r_total_filtered": 8.695652174, "attached_condition": "t0.c3 = t3.c3", "r_filtered": 8.695652174 } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c0"], "loops": 60010200.18, "r_loops": 6, "rows": 250001, "r_rows": 2, "cost": 2201360019, "r_table_time_ms": 0.023011411, "r_other_time_ms": 0.00448317, "r_engine_stats": { "pages_accessed": 18 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------+ | section | +--------------------------------------------------------------------------------+ | FORCED_SLOW ANALYZE FORMAT=JSON AFTER PERSISTENT STATS: STRAIGHT_JOIN t1,t3,t0 | +--------------------------------------------------------------------------------+ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.133743323 }, "query_block": { "select_id": 1, "cost": 2983528.462, "r_loops": 1, "r_total_time_ms": 1102.979259, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["i2"], "key": "i2", "key_length": "9", "used_key_parts": ["c0"], "loops": 1, "r_loops": 1, "rows": 1000006, "r_rows": 1000006, "cost": 147.735382, "r_table_time_ms": 247.3435033, "r_other_time_ms": 78.16980495, "r_engine_stats": { "pages_accessed": 1265 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t1.c0 is not null", "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "t3", "access_type": "ref", "possible_keys": ["i5", "i6"], "key": "i5", "key_length": "9", "used_key_parts": ["c0"], "ref": ["mariadb_join_order_fanout_misestimation.t1.c0"], "loops": 1000006, "r_loops": 1000006, "rows": 1, "r_rows": 5.999964e-6, "cost": 1820.270044, "r_table_time_ms": 750.8275325, "r_other_time_ms": 26.3730731, "r_engine_stats": { "pages_accessed": 2000024 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t3.c1 >= -2066281069 and t3.c3 is not null", "r_filtered": 100 } }, { "table": { "table_name": "t0", "access_type": "ref", "possible_keys": ["i7"], "key": "i7", "key_length": "15", "used_key_parts": ["c3"], "ref": ["mariadb_join_order_fanout_misestimation.t3.c3"], "loops": 1000006, "r_loops": 6, "rows": 3000, "r_rows": 23, "cost": 2981560.457, "r_table_time_ms": 0.241043436, "r_other_time_ms": 0.018813235, "r_engine_stats": { "pages_accessed": 432 }, "filtered": 100, "r_total_filtered": 8.695652174, "attached_condition": "t0.c3 = t3.c3", "r_filtered": 8.695652174 } } ] } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-------------------------------------------+ | section | +-------------------------------------------+ | BASE DIRECT TIMING AFTER PERSISTENT STATS | +-------------------------------------------+ +------------+---------------+ | ref0 | ref1 | +------------+---------------+ | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | +------------+---------------+ +----------------------------------------+ | base_elapsed_ms_after_persistent_stats | +----------------------------------------+ | 1058.4940 | +----------------------------------------+ +--------------------------------------------------------------------------+ | section | +--------------------------------------------------------------------------+ | FORCED_FAST DIRECT TIMING AFTER PERSISTENT STATS: STRAIGHT_JOIN t3,t0,t1 | +--------------------------------------------------------------------------+ +------------+---------------+ | ref0 | ref1 | +------------+---------------+ | 2000000001 | match_key_000 | | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000000 | match_key_000 | | 2000000003 | match_key_001 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000002 | match_key_001 | | 2000000005 | match_key_002 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | | 2000000004 | match_key_002 | +------------+---------------+ +-----------------------------------------------+ | forced_fast_elapsed_ms_after_persistent_stats | +-----------------------------------------------+ | 35.1560 | +-----------------------------------------------+ +--------------------------------------------------------------------------+ | section | +--------------------------------------------------------------------------+ | FORCED_SLOW DIRECT TIMING AFTER PERSISTENT STATS: STRAIGHT_JOIN t1,t3,t0 | +--------------------------------------------------------------------------+ +------------+---------------+ | ref0 | ref1 | +------------+---------------+ | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000001 | match_key_000 | | 2000000000 | match_key_000 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000003 | match_key_001 | | 2000000002 | match_key_001 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | | 2000000005 | match_key_002 | | 2000000004 | match_key_002 | +------------+---------------+ +-----------------------------------------------+ | forced_slow_elapsed_ms_after_persistent_stats | +-----------------------------------------------+ | 1041.6560 | +-----------------------------------------------+ +-------------+ | section | +-------------+ | FINAL NOTES | +-------------+ +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | note | details | +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Interpretation guide | If BASE remains t1->t3->t0 and is close to FORCED_SLOW while much slower than FORCED_FAST, this demonstrates a join-order cost ranking inversion caused by equality-join fanout misestimation. If persistent stats do not change the plan/estimate, classify it as fanout misestimation despite MariaDB persistent engine-independent statistics. | +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+