revision-id: 31815d37666d3ca9fae2df45b99138e1c4507bab (mariadb-10.4.7-33-g31815d37666) parent(s): efb8485d85b19a2a729310adc6779ca649198f29 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-08-25 11:03:19 +0300 message: MDEV-6111: Optimizer Trace: add tracing for semi-join optimizations Added: - "semijoin_strategy_choice" element (actions in advance_sj_state(), name matches the name in MySQL) - semijoin_table_pullout element. --- mysql-test/main/opt_trace.result | 273 +++++++++++++++++++++++++++++++++++++-- mysql-test/main/opt_trace.test | 18 +++ sql/opt_subselect.cc | 101 ++++++++++++--- 3 files changed, 365 insertions(+), 27 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 518da2888cc..ab07db38d05 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2662,6 +2662,11 @@ explain extended select * from t1 where a in (select pk from t10) { } ] }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, { "execution_plan_for_potential_materialization": { "steps": [ @@ -2705,6 +2710,7 @@ explain extended select * from t1 where a in (select pk from t10) { }, "rows_for_plan": 3, "cost_for_plan": 2.6066, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -2720,7 +2726,27 @@ explain extended select * from t1 where a in (select pk from t10) { ] }, "rows_for_plan": 30, - "cost_for_plan": 10.629 + "cost_for_plan": 10.629, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 10.629 + }, + { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 5.2786 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 27.129 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ] } ] }, @@ -2739,6 +2765,7 @@ explain extended select * from t1 where a in (select pk from t10) { }, "rows_for_plan": 10, "cost_for_plan": 4.022, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -2746,7 +2773,7 @@ explain extended select * from t1 where a in (select pk from t10) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t10" @@ -4081,6 +4108,11 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ } ] }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, { "execution_plan_for_potential_materialization": { "steps": [ @@ -4159,6 +4191,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -4175,6 +4208,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1", "t_inner_1"], @@ -4190,7 +4224,27 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, "rows_for_plan": 27, - "cost_for_plan": 13.815 + "cost_for_plan": 13.815, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 26.667 + }, + { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 7.2154 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 18.315 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ] } ] }, @@ -4209,6 +4263,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4228,6 +4283,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -4245,6 +4301,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4252,7 +4309,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_1" @@ -4511,6 +4568,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } ] }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, { "execution_plan_for_potential_materialization": { "steps": [] @@ -4533,6 +4600,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], @@ -4549,6 +4617,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], @@ -4565,6 +4634,21 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 26.759 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.226 + }, + { + "chosen_strategy": "FirstMatch" + } + ], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -4581,6 +4665,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 34.174, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4602,6 +4687,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 52.379, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4623,7 +4709,22 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 200.19 + "cost_for_plan": 200.19, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 251.56 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 313.59 + }, + { + "chosen_strategy": "FirstMatch" + } + ] } ] }, @@ -4647,6 +4748,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 84.79, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4666,6 +4768,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 30.564, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4687,6 +4790,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 48.779, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4709,6 +4813,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 196.59, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 309.99 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], "pruned_by_cost": true } ] @@ -4733,6 +4847,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 48.779, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4752,6 +4867,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 34.174, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4771,6 +4887,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], @@ -4787,6 +4904,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 285.84 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], "pruned_by_cost": true }, { @@ -4804,6 +4931,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 75.231, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4825,6 +4953,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -4847,6 +4976,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -4866,6 +4996,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4885,6 +5016,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 13.815, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], @@ -4901,6 +5033,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 64.431, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -4922,6 +5055,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 503.85, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -4944,6 +5078,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 503.85, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -4963,6 +5098,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 64.431, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -4980,6 +5116,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 64.431, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -4999,6 +5136,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], @@ -5015,6 +5153,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5036,6 +5175,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 611.85, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5058,6 +5198,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 6561, "cost_for_plan": 1486.7, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5077,6 +5218,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 75.231, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5098,6 +5240,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5120,6 +5263,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 2187, "cost_for_plan": 514.65, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5139,6 +5283,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 729, "cost_for_plan": 172.44, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -5160,6 +5305,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5177,6 +5323,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5194,6 +5341,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5211,6 +5359,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -5230,6 +5379,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5247,6 +5397,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5264,6 +5415,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5281,6 +5433,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -5298,6 +5451,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -5305,7 +5459,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "firstmatch", + "semi_join_strategy": "FirstMatch", "join_order": [ { "table": "t_inner_4" @@ -5336,7 +5490,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "semi_join_strategy": "firstmatch", + "semi_join_strategy": "FirstMatch", "join_order": [ { "table": "t_inner_1" @@ -5621,6 +5775,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } ] }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, + { + "semijoin_table_pullout": { + "pulled_out_tables": [] + } + }, { "execution_plan_for_potential_materialization": { "steps": [ @@ -5754,6 +5918,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], @@ -5770,6 +5935,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], @@ -5786,6 +5952,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 26.759 + }, + { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 8.1256 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.226 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ], "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -5802,6 +5988,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 15.541, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5823,6 +6010,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 33.746, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5844,7 +6032,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 181.56 + "cost_for_plan": 181.56, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 232.93 + }, + { + "strategy": "SJ-Materialization", + "records": 27, + "read_time": 22.262 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 294.96 + }, + { + "chosen_strategy": "SJ-Materialize" + } + ] } ] }, @@ -5868,6 +6076,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 243, "cost_for_plan": 66.156, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5887,6 +6096,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 11.931, + "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ @@ -5908,6 +6118,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 30.146, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5930,6 +6141,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 30.146, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -5949,6 +6161,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 15.541, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -5968,6 +6181,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "pruned_by_cost": true }, { @@ -5985,6 +6199,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 13.815, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6002,6 +6217,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 81, "cost_for_plan": 24.626, + "semijoin_strategy_choice": [], "pruned_by_cost": true } ] @@ -6021,6 +6237,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6038,6 +6255,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6055,6 +6273,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 6.4103, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6072,6 +6291,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 27, "cost_for_plan": 10.021, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -6091,6 +6311,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6108,6 +6329,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6125,6 +6347,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6142,6 +6365,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 3, "cost_for_plan": 2.6051, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { @@ -6159,6 +6383,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, "rows_for_plan": 9, "cost_for_plan": 3.8154, + "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] @@ -6166,7 +6391,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_4" @@ -6177,7 +6402,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "semi_join_strategy": "sj_materialize", + "semi_join_strategy": "SJ-Materialization", "join_order": [ { "table": "t_inner_1" @@ -7026,4 +7251,32 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) } ] DROP TABLE t1; +# +# Test for Semi-Join table pullout element +# +create table t1 (a int primary key, b int); +insert into t1 (a) values (1),(2),(3),(4),(5); +create table t2 (a int primary key, b int); +insert into t2 (a) values (1),(2),(3),(4),(5); +create table t3 (a int); +insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +explain +select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) +[ + + { + "pulled_out_tables": + [ + "t2", + "t1" + ] + } +] +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 085b7e0aea8..51950f00781 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -549,4 +549,22 @@ select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) fr DROP TABLE t1; +--echo # +--echo # Test for Semi-Join table pullout element +--echo # +create table t1 (a int primary key, b int); +insert into t1 (a) values (1),(2),(3),(4),(5); + +create table t2 (a int primary key, b int); +insert into t2 (a) values (1),(2),(3),(4),(5); + +create table t3 (a int); +insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); + +explain +select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); + +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 599642b3a26..9205380bd19 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2192,12 +2192,15 @@ int pull_out_semijoin_tables(JOIN *join) TABLE_LIST *sj_nest; DBUG_ENTER("pull_out_semijoin_tables"); List_iterator<TABLE_LIST> sj_list_it(join->select_lex->sj_nests); - + /* Try pulling out of the each of the semi-joins */ while ((sj_nest= sj_list_it++)) { List_iterator<TABLE_LIST> child_li(sj_nest->nested_join->join_list); TABLE_LIST *tbl; + Json_writer_object trace_wrapper(join->thd); + Json_writer_object trace(join->thd, "semijoin_table_pullout"); + Json_writer_array trace_arr(join->thd, "pulled_out_tables"); /* Don't do table pull-out for nested joins (if we get nested joins here, it @@ -2296,7 +2299,8 @@ int pull_out_semijoin_tables(JOIN *join) pulled_a_table= TRUE; pulled_tables |= tbl->table->map; DBUG_PRINT("info", ("Table %s pulled out (reason: func dep)", - tbl->table->alias.c_ptr())); + tbl->table->alias.c_ptr_safe())); + trace_arr.add(tbl->table->alias.c_ptr_safe()); /* Pulling a table out of uncorrelated subquery in general makes makes it correlated. See the NOTE to this funtion. @@ -2778,27 +2782,30 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, { POSITION *pos= join->positions + idx; const JOIN_TAB *new_join_tab= pos->table; - Semi_join_strategy_picker *pickers[]= - { - &pos->firstmatch_picker, - &pos->loosescan_picker, - &pos->sjmat_picker, - &pos->dups_weedout_picker, - NULL, - }; - - if (join->emb_sjm_nest) + if (join->emb_sjm_nest || //(1) + !join->select_lex->have_merged_subqueries) //(2) { /* - We're performing optimization inside SJ-Materialization nest: + (1): We're performing optimization inside SJ-Materialization nest: - there are no other semi-joins inside semi-join nests - attempts to build semi-join strategies here will confuse the optimizer, so bail out. + (2): Don't waste time on semi-join optimizations if we don't have any + semi-joins */ pos->sj_strategy= SJ_OPT_NONE; return; } + Semi_join_strategy_picker *pickers[]= + { + &pos->firstmatch_picker, + &pos->loosescan_picker, + &pos->sjmat_picker, + &pos->dups_weedout_picker, + NULL, + }; + Json_writer_array trace_steps(join->thd, "semijoin_strategy_choice"); /* Update join->cur_sj_inner_tables (Used by FirstMatch in this function and LooseScan detector in best_access_path) @@ -2897,6 +2904,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, *current_read_time= read_time; *current_record_count= rec_count; dups_producing_tables &= ~handled_fanout; + //TODO: update bitmap of semi-joins that were handled together with // others. if (is_multiple_semi_joins(join, join->positions, idx, @@ -2924,6 +2932,30 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx, } } } + + if (unlikely(join->thd->trace_started() && pos->sj_strategy != SJ_OPT_NONE)) + { + Json_writer_object tr(join->thd); + const char *sname; + switch (pos->sj_strategy) { + case SJ_OPT_MATERIALIZE: + sname= "SJ-Materialize"; + break; + case SJ_OPT_MATERIALIZE_SCAN: + sname= "SJ-Materialize-Scan"; + break; + case SJ_OPT_FIRST_MATCH: + sname= "FirstMatch"; + break; + case SJ_OPT_DUPS_WEEDOUT: + sname= "DuplicateWeedout"; + break; + default: + DBUG_ASSERT(0); + sname="Invalid"; + } + tr.add("chosen_strategy", sname); + } } if ((emb_sj_nest= new_join_tab->emb_sj_nest)) @@ -3000,6 +3032,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join, } else { + Json_writer_object trace(join->thd); + trace.add("strategy", "SJ-Materialization"); /* This is SJ-Materialization with lookups */ Cost_estimate prefix_cost; signed int first_tab= (int)idx - mat_info->tables; @@ -3032,6 +3066,11 @@ bool Sj_materialization_picker::check_qep(JOIN *join, *record_count= prefix_rec_count; *handled_fanout= new_join_tab->emb_sj_nest->sj_inner_tables; *strategy= SJ_OPT_MATERIALIZE; + if (unlikely(join->thd->trace_started())) + { + trace.add("records", *record_count); + trace.add("read_time", *read_time); + } return TRUE; } } @@ -3040,6 +3079,8 @@ bool Sj_materialization_picker::check_qep(JOIN *join, if (sjm_scan_need_tables && /* Have SJM-Scan prefix */ !(sjm_scan_need_tables & remaining_tables)) { + Json_writer_object trace(join->thd); + trace.add("strategy", "SJ-Materialization-Scan"); TABLE_LIST *mat_nest= join->positions[sjm_scan_last_inner].table->emb_sj_nest; SJ_MATERIALIZATION_INFO *mat_info= mat_nest->sj_mat_info; @@ -3088,6 +3129,11 @@ bool Sj_materialization_picker::check_qep(JOIN *join, *read_time= prefix_cost; *record_count= prefix_rec_count; *handled_fanout= mat_nest->sj_inner_tables; + if (unlikely(join->thd->trace_started())) + { + trace.add("records", *record_count); + trace.add("read_time", *read_time); + } return TRUE; } return FALSE; @@ -3151,6 +3197,8 @@ bool LooseScan_picker::check_qep(JOIN *join, !(remaining_tables & loosescan_need_tables) && (new_join_tab->table->map & loosescan_need_tables)) { + Json_writer_object trace(join->thd); + trace.add("strategy", "SJ-Materialization-Scan"); /* Ok we have LooseScan plan and also have all LooseScan sj-nest's inner tables and outer correlated tables into the prefix. @@ -3181,6 +3229,11 @@ bool LooseScan_picker::check_qep(JOIN *join, */ *strategy= SJ_OPT_LOOSE_SCAN; *handled_fanout= first->table->emb_sj_nest->sj_inner_tables; + if (unlikely(join->thd->trace_started())) + { + trace.add("records", *record_count); + trace.add("read_time", *read_time); + } return TRUE; } return FALSE; @@ -3260,6 +3313,8 @@ bool Firstmatch_picker::check_qep(JOIN *join, if (in_firstmatch_prefix() && !(firstmatch_need_tables & remaining_tables)) { + Json_writer_object trace(join->thd); + trace.add("strategy", "FirstMatch"); /* Got a complete FirstMatch range. Calculate correct costs and fanout */ @@ -3292,6 +3347,11 @@ bool Firstmatch_picker::check_qep(JOIN *join, *handled_fanout= firstmatch_need_tables; /* *record_count and *read_time were set by the above call */ *strategy= SJ_OPT_FIRST_MATCH; + if (unlikely(join->thd->trace_started())) + { + trace.add("records", *record_count); + trace.add("read_time", *read_time); + } return TRUE; } } @@ -3370,6 +3430,8 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join, double sj_inner_fanout= 1.0; double sj_outer_fanout= 1.0; uint temptable_rec_size; + Json_writer_object trace(join->thd); + trace.add("strategy", "DuplicateWeedout"); if (first_tab == join->const_tables) { prefix_rec_count= 1.0; @@ -3430,6 +3492,11 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join, *record_count= prefix_rec_count * sj_outer_fanout; *handled_fanout= dups_removed_fanout; *strategy= SJ_OPT_DUPS_WEEDOUT; + if (unlikely(join->thd->trace_started())) + { + trace.add("records", *record_count); + trace.add("read_time", *read_time); + } return TRUE; } return FALSE; @@ -3660,7 +3727,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) join->best_positions[first].n_sj_tables= sjm->tables; join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE; Json_writer_object semijoin_strategy(thd); - semijoin_strategy.add("semi_join_strategy","sj_materialize"); + semijoin_strategy.add("semi_join_strategy","SJ-Materialization"); Json_writer_array semijoin_plan(thd, "join_order"); for (uint i= first; i < first+ sjm->tables; i++) { @@ -3709,7 +3776,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) POSITION dummy; join->cur_sj_inner_tables= 0; Json_writer_object semijoin_strategy(thd); - semijoin_strategy.add("semi_join_strategy","sj_materialize_scan"); + semijoin_strategy.add("semi_join_strategy","SJ-Materialization-Scan"); Json_writer_array semijoin_plan(thd, "join_order"); for (i= first + sjm->tables; i <= tablenr; i++) { @@ -3747,7 +3814,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) */ join->cur_sj_inner_tables= 0; Json_writer_object semijoin_strategy(thd); - semijoin_strategy.add("semi_join_strategy","firstmatch"); + semijoin_strategy.add("semi_join_strategy","FirstMatch"); Json_writer_array semijoin_plan(thd, "join_order"); for (idx= first; idx <= tablenr; idx++) { @@ -3785,7 +3852,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) */ join->cur_sj_inner_tables= 0; Json_writer_object semijoin_strategy(thd); - semijoin_strategy.add("semi_join_strategy","sj_materialize"); + semijoin_strategy.add("semi_join_strategy","LooseScan"); Json_writer_array semijoin_plan(thd, "join_order"); for (idx= first; idx <= tablenr; idx++) {