diff options
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 682 |
1 files changed, 603 insertions, 79 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 634ef3343a4..1342bc65f17 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -515,6 +515,11 @@ select * from v2 { ] }, { + "check_split_materialized": { + "not_applicable": "no candidate field can be accessed through ref" + } + }, + { "best_join_order": ["t1"], "rows": 1, "cost": 0.010978597 @@ -901,6 +906,11 @@ explain select * from v1 { ] }, { + "check_split_materialized": { + "not_applicable": "group list has no candidates" + } + }, + { "best_join_order": ["t1"], "rows": 10, "cost": 0.017968034 @@ -4266,7 +4276,7 @@ explain delete from t0 where t0.a<3 { "select_id": 1, "steps": [ { - "expanded_query": "select from dual where t0.a < 3" + "expanded_query": "delete from t0 using dual where t0.a < 3" } ] } @@ -4346,7 +4356,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "select_id": 1, "steps": [ { - "expanded_query": "select from t0 join t1 where t0.a = t1.a and t1.a < 3" + "expanded_query": "delete from t0,t1 using t0 join t1 where t0.a = t1.a and t1.a < 3" } ] } @@ -4792,7 +4802,7 @@ explain select * from (select rand() from t1)q { "derived": { "table": "q", "select_id": 2, - "algorithm": "merged" + "algorithm": "materialized" } }, { @@ -4806,7 +4816,7 @@ explain select * from (select rand() from t1)q { } }, { - "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q" + "expanded_query": "/* select#1 */ select q.`rand()` AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q" } ] } @@ -4816,14 +4826,6 @@ explain select * from (select rand() from t1)q { "select_id": 1, "steps": [ { - "derived": { - "table": "q", - "select_id": 2, - "algorithm": "materialized", - "cause": "Random function in the select" - } - }, - { "join_optimization": { "select_id": 2, "steps": [ @@ -11586,6 +11588,518 @@ SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); a DROP TABLE t1; # +# MDEV-31085: multi-update using view with optimizer trace enabled +# +SET SESSION optimizer_trace = 'enabled=on'; +CREATE TABLE t (a int, b int); +CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c; +INSERT INTO t VALUES (0,4),(5,6); +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a; +SELECT * FROM information_schema.optimizer_trace; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { + "steps": [ + { + "view": { + "table": "v", + "select_id": 2, + "algorithm": "materialized" + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select 1 AS c" + } + ] + } + }, + { + "join_preparation": { + "select_id": 3, + "steps": [ + { + "expanded_query": "/* select#3 */ select 2 AS c" + } + ] + } + }, + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ update t join v set t.b = t.a,t.a = v.c where v.c < t.a" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "v.c < t.a", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "v.c < t.a" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "v.c < t.a" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "v.c < t.a" + } + ] + } + }, + { + "join_optimization": { + "select_id": 2, + "steps": [] + } + }, + { + "join_optimization": { + "select_id": 3, + "steps": [] + } + }, + { + "table_dependencies": [ + { + "table": "t", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "<derived2>", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t", + "table_scan": { + "rows": 2, + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 + } + }, + { + "table": "<derived2>", + "table_scan": { + "rows": 2, + "read_cost": 0.012350033, + "read_and_compare_cost": 0.012418701 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": "", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.01034841, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 2, + "rows_out": 2, + "cost": 0.01034841, + "uses_join_buffering": false + } + } + }, + { + "best_access_path": { + "table": "<derived2>", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.012418701, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 2, + "rows_out": 2, + "cost": 0.012418701, + "uses_join_buffering": false + } + } + } + ] + }, + { + "plan_prefix": "", + "table": "t", + "rows_for_plan": 2, + "cost_for_plan": 0.01034841, + "rest_of_plan": [ + { + "plan_prefix": "t", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "<derived2>", + "plan_details": { + "record_count": 2 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.012911897, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 2, + "rows_out": 2, + "cost": 0.012911897, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t", + "table": "<derived2>", + "rows_for_plan": 4, + "cost_for_plan": 0.023260307 + } + ] + }, + { + "plan_prefix": "", + "table": "<derived2>", + "rows_for_plan": 2, + "cost_for_plan": 0.012418701, + "pruned_by_heuristic": true + } + ] + }, + { + "best_join_order": ["t", "<derived2>"], + "rows": 4, + "cost": 0.023260307 + }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "v.c < t.a" + } + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t", + "attached_condition": null + }, + { + "table": "<derived2>", + "attached_condition": "v.c < t.a" + } + ] + } + }, + { + "make_join_readinfo": [] + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [ + { + "join_execution": { + "select_id": 2, + "steps": [] + } + }, + { + "join_execution": { + "select_id": 3, + "steps": [] + } + }, + { + "join_preparation": { + "select_id": "fake", + "steps": [ + { + "expanded_query": "select c AS c from dual" + } + ] + } + }, + { + "join_optimization": { + "select_id": "fake", + "steps": [ + { + "table_dependencies": [ + { + "table": "union", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "union", + "table_scan": { + "rows": 2, + "read_cost": 0.010020701, + "read_and_compare_cost": 0.010084701 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": "", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "union", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.010084701, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 2, + "rows_out": 2, + "cost": 0.010084701, + "uses_join_buffering": false + } + } + } + ] + }, + { + "plan_prefix": "", + "table": "union", + "rows_for_plan": 2, + "cost_for_plan": 0.010084701 + } + ] + }, + { + "best_join_order": ["union"], + "rows": 2, + "cost": 0.010084701 + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "union", + "attached_condition": null + } + ] + } + }, + { + "make_join_readinfo": [] + } + ] + } + }, + { + "join_execution": { + "select_id": "fake", + "steps": [] + } + } + ] + } + } + ] +} 0 0 +SELECT * FROM t; +a b +0 4 +1 5 +SET optimizer_trace=DEFAULT; +DROP VIEW v; +DROP TABLE t; +# +# MDEV-26301: Split optimization improvements: Optimizer Trace coverage +# +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +create table t10 ( +grp_id int, +col1 int, +key(grp_id) +); +insert into t10 +select +A.seq, +B.seq +from +seq_1_to_100 A, +seq_1_to_100 B; +create table t11 ( +col1 int, +col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; +analyze table t10,t11 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status Table is already up to date +test.t11 analyze status Engine-independent statistics collected +test.t11 analyze status OK +set optimizer_trace=1; +explain +select * from +( +(t1 left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id) T on T.grp_id=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 +2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS +from information_schema.optimizer_trace; +JS +[ + { + "split_candidates": + ["t10.grp_id"] + } +] +select +json_detailed( +json_remove( +json_extract(trace, '$**.choose_best_splitting') +, '$[0].split_plan_search[0]' + ) +) as JS +from information_schema.optimizer_trace; +JS +[ + { + "considered_keys": + [ + { + "table_name": "t10", + "index": "grp_id", + "rec_per_key": 100, + "param_tables": 1 + } + ], + "refills": 5, + "spl_pd_boundary": 2, + "split_plan_search": + [], + "split_materialized": + { + "table": "t10", + "key": "grp_id", + "org_cost": 1.159965, + "postjoin_cost": 4.020888502, + "one_splitting_cost": 5.180853502, + "unsplit_postjoin_cost": 32.78652054, + "unsplit_cost": 148.7830205, + "rows": 100, + "refills": 5, + "total_splitting_cost": 25.90426751, + "chosen": true + } + } +] +drop table t1,t2,t3,t10,t11; +set optimizer_trace=DEFAULT; +# # End of 10.4 tests # set optimizer_trace='enabled=on'; @@ -12017,84 +12531,94 @@ from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.choose_best_splitting')) [ - [ - { - "considered_execution_plans": - [ - { - "plan_prefix": "", - "get_costs_for_tables": - [ - { - "best_access_path": + { + "considered_keys": + [] + }, + { + "considered_keys": + [ + { + "table_name": "t2", + "index": "idx_a", + "rec_per_key": 1.8367, + "param_tables": 1 + } + ], + "refills": 4, + "spl_pd_boundary": 2, + "split_plan_search": + [ + { + "considered_execution_plans": + [ + { + "plan_prefix": "", + "get_costs_for_tables": + [ { - "table": "t2", - "plan_details": + "best_access_path": { - "record_count": 1 - }, - "considered_access_paths": - [ + "table": "t2", + "plan_details": { - "access_type": "ref", - "index": "idx_a", - "used_range_estimates": false, - "reason": "not available", - "rows": 1.8367, - "cost": 0.002051185, - "chosen": true + "record_count": 1 }, + "considered_access_paths": + [ + { + "access_type": "ref", + "index": "idx_a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1.8367, + "cost": 0.002051185, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": { - "type": "scan", - "chosen": false, - "cause": "cost" + "type": "ref", + "rows_read": 1.8367, + "rows_out": 1.8367, + "cost": 0.002051185, + "uses_join_buffering": false } - ], - "chosen_access_method": - { - "type": "ref", - "rows_read": 1.8367, - "rows_out": 1.8367, - "cost": 0.002051185, - "uses_join_buffering": false } } - } - ] - }, - { - "plan_prefix": "", - "table": "t2", - "rows_for_plan": 1.8367, - "cost_for_plan": 0.002051185, - "cost_for_sorting": 0.001155201 - } - ] - }, - { - "split_materialized": - { - "table": "t2", - "key": "idx_a", - "org_cost": 0.002051185, - "postjoin_cost": 0.001135418, - "one_splitting_cost": 0.003186603, - "unsplit_postjoin_cost": 0.036032575, - "unsplit_cost": 0.060625425, - "rows": 1.8367, - "outer_rows": 4, - "total_splitting_cost": 0.012746412, - "chosen": true + ] + }, + { + "plan_prefix": "", + "table": "t2", + "rows_for_plan": 1.8367, + "cost_for_plan": 0.002051185, + "cost_for_sorting": 0.001155201 + } + ] } + ], + "split_materialized": + { + "table": "t2", + "key": "idx_a", + "org_cost": 0.002051185, + "postjoin_cost": 0.001135418, + "one_splitting_cost": 0.003186603, + "unsplit_postjoin_cost": 0.036032575, + "unsplit_cost": 0.060625425, + "rows": 1.8367, + "refills": 4, + "total_splitting_cost": 0.012746412, + "chosen": true } - ] + } ] -select -json_detailed(json_extract(trace, '$**.lateral_derived')) -from -information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.lateral_derived')) -NULL drop table t1,t2; # # Test table functions. |