diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 6391 |
1 files changed, 4537 insertions, 1854 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 3c90e97c..7d77bb9d 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -59,7 +59,7 @@ select * from v1 { } }, { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1" + "expanded_query": "/* select#1 */ select v1.a AS a,v1.b AS b from v1" } ] } @@ -118,7 +118,8 @@ select * from v1 { "table": "t1", "table_scan": { "rows": 2, - "cost": 2.004394531 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -126,23 +127,30 @@ select * from v1 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 1, - "cost": 2.204394531, + "rows": 2, + "rows_after_filter": 1, + "rows_out": 1, + "cost": 0.01034841, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 1, - "cost": 2.204394531, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01034841, "uses_join_buffering": false } } @@ -150,15 +158,17 @@ select * from v1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 2.404394531 + "cost_for_plan": 0.01034841 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 1, + "cost": 0.01034841 }, { "substitute_best_equal": { @@ -172,10 +182,13 @@ select * from v1 { "attached_conditions_summary": [ { "table": "t1", - "attached": "t1.a = 1" + "attached_condition": "t1.a = 1" } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -217,7 +230,7 @@ select * from (select * from t1 where t1.a=1)q { } }, { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q" + "expanded_query": "/* select#1 */ select q.a AS a,q.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q" } ] } @@ -276,7 +289,8 @@ select * from (select * from t1 where t1.a=1)q { "table": "t1", "table_scan": { "rows": 2, - "cost": 2.004394531 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -284,23 +298,30 @@ select * from (select * from t1 where t1.a=1)q { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 1, - "cost": 2.204394531, + "rows": 2, + "rows_after_filter": 1, + "rows_out": 1, + "cost": 0.01034841, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 1, - "cost": 2.204394531, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01034841, "uses_join_buffering": false } } @@ -308,15 +329,17 @@ select * from (select * from t1 where t1.a=1)q { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 2.404394531 + "cost_for_plan": 0.01034841 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 1, + "cost": 0.01034841 }, { "substitute_best_equal": { @@ -330,10 +353,13 @@ select * from (select * from t1 where t1.a=1)q { "attached_conditions_summary": [ { "table": "t1", - "attached": "t1.a = 1" + "attached_condition": "t1.a = 1" } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -439,7 +465,8 @@ select * from v2 { "table": "t1", "table_scan": { "rows": 2, - "cost": 2.004394531 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -447,24 +474,31 @@ select * from v2 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 1, - "cost": 2.204394531, + "rows": 2, + "rows_after_filter": 1, + "rows_out": 1, + "cost": 0.01034841, + "index_only": false, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", - "records": 1, - "cost": 2.204394531, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01034841, "uses_join_buffering": false } } @@ -472,11 +506,11 @@ select * from v2 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 2.404394531, - "cost_for_sorting": 1 + "cost_for_plan": 0.01034841, + "cost_for_sorting": 6.301866e-4 } ] }, @@ -486,7 +520,9 @@ select * from v2 { } }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 1, + "cost": 0.010978597 }, { "substitute_best_equal": { @@ -500,12 +536,15 @@ select * from v2 { "attached_conditions_summary": [ { "table": "t1", - "attached": "t1.a = 1" + "attached_condition": "t1.a = 1" } ] } }, { + "make_join_readinfo": [] + }, + { "test_if_skip_sort_order": [] } ] @@ -527,7 +566,8 @@ select * from v2 { "table": "<derived2>", "table_scan": { "rows": 2, - "cost": 2 + "read_cost": 0.012350033, + "read_and_compare_cost": 0.012418701 } } ] @@ -535,23 +575,30 @@ select * from v2 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "<derived2>", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 2, - "cost": 2, + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.012418701, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 2, - "cost": 2, + "rows_read": 2, + "rows_out": 2, + "cost": 0.012418701, "uses_join_buffering": false } } @@ -559,15 +606,17 @@ select * from v2 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "<derived2>", "rows_for_plan": 2, - "cost_for_plan": 2.4 + "cost_for_plan": 0.012418701 } ] }, { - "best_join_order": ["<derived2>"] + "best_join_order": ["<derived2>"], + "rows": 2, + "cost": 0.012418701 }, { "attaching_conditions_to_tables": { @@ -575,10 +624,13 @@ select * from v2 { "attached_conditions_summary": [ { "table": "<derived2>", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -646,7 +698,7 @@ explain select * from v2 { } }, { - "expanded_query": "/* select#1 */ select t2.a AS a from v2" + "expanded_query": "/* select#1 */ select v2.a AS a from v2" } ] } @@ -671,7 +723,8 @@ explain select * from v2 { "table": "t2", "table_scan": { "rows": 10, - "cost": 2.021972656 + "read_cost": 0.01127965, + "read_and_compare_cost": 0.01159965 } } ] @@ -679,23 +732,30 @@ explain select * from v2 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "rows": 10, + "rows_after_filter": 10, + "rows_out": 10, + "cost": 0.01159965, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -703,15 +763,17 @@ explain select * from v2 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t2", "rows_for_plan": 10, - "cost_for_plan": 4.021972656 + "cost_for_plan": 0.01159965 } ] }, { - "best_join_order": ["t2"] + "best_join_order": ["t2"], + "rows": 10, + "cost": 0.01159965 }, { "attaching_conditions_to_tables": { @@ -719,10 +781,13 @@ explain select * from v2 { "attached_conditions_summary": [ { "table": "t2", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -794,7 +859,8 @@ explain select * from v1 { "table": "t1", "table_scan": { "rows": 10, - "cost": 2.021972656 + "read_cost": 0.01127965, + "read_and_compare_cost": 0.01159965 } } ] @@ -802,24 +868,31 @@ explain select * from v1 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "rows": 10, + "rows_after_filter": 10, + "rows_out": 10, + "cost": 0.01159965, + "index_only": false, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -827,11 +900,11 @@ explain select * from v1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 10, - "cost_for_plan": 4.021972656, - "cost_for_sorting": 10 + "cost_for_plan": 0.01159965, + "cost_for_sorting": 0.006368384 } ] }, @@ -841,7 +914,9 @@ explain select * from v1 { } }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 10, + "cost": 0.017968034 }, { "attaching_conditions_to_tables": { @@ -849,12 +924,15 @@ explain select * from v1 { "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } }, { + "make_join_readinfo": [] + }, + { "test_if_skip_sort_order": [] } ] @@ -876,7 +954,8 @@ explain select * from v1 { "table": "<derived2>", "table_scan": { "rows": 10, - "cost": 10 + "read_cost": 0.012414166, + "read_and_compare_cost": 0.012757506 } } ] @@ -884,23 +963,30 @@ explain select * from v1 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "<derived2>", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10, - "cost": 10, + "rows": 10, + "rows_after_filter": 10, + "rows_out": 10, + "cost": 0.012757506, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 10, + "rows_read": 10, + "rows_out": 10, + "cost": 0.012757506, "uses_join_buffering": false } } @@ -908,15 +994,17 @@ explain select * from v1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "<derived2>", "rows_for_plan": 10, - "cost_for_plan": 12 + "cost_for_plan": 0.012757506 } ] }, { - "best_join_order": ["<derived2>"] + "best_join_order": ["<derived2>"], + "rows": 10, + "cost": 0.012757506 }, { "attaching_conditions_to_tables": { @@ -924,10 +1012,13 @@ explain select * from v1 { "attached_conditions_summary": [ { "table": "<derived2>", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -1048,14 +1139,16 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "table": "t1", "table_scan": { "rows": 100, - "cost": 2.317382812 + "read_cost": 0.0224761, + "read_and_compare_cost": 0.0256761 } }, { "table": "t2", "table_scan": { "rows": 100, - "cost": 2.317382812 + "read_cost": 0.0224761, + "read_and_compare_cost": 0.0256761 } } ] @@ -1063,23 +1156,30 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, + "rows": 100, + "rows_after_filter": 100, + "rows_out": 100, + "cost": 0.0256761, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 100, - "cost": 2.317382812, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256761, "uses_join_buffering": false } } @@ -1087,18 +1187,25 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "best_access_path": { "table": "t2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, + "rows": 100, + "rows_after_filter": 100, + "rows_out": 100, + "cost": 0.0256761, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 100, - "cost": 2.317382812, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256761, "uses_join_buffering": false } } @@ -1106,17 +1213,20 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 100, - "cost_for_plan": 22.31738281, + "cost_for_plan": 0.0256761, "rest_of_plan": [ { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "get_costs_for_tables": [ { "best_access_path": { "table": "t2", + "plan_details": { + "record_count": 100 + }, "considered_access_paths": [ { "access_type": "ref", @@ -1124,20 +1234,25 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 200.0585794, + "cost": 0.1821659, "chosen": true }, { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, + "access_type": "scan_with_join_cache", + "rows": 100, + "rows_after_filter": 100, + "rows_out": 1, + "cost": 0.9604227, + "cost_without_join_buffer": 2.56761, + "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", - "records": 1, - "cost": 200.0585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.1821659, "uses_join_buffering": false } } @@ -1145,25 +1260,28 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ] }, { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "table": "t2", "rows_for_plan": 100, - "cost_for_plan": 242.3759623 + "cost_for_plan": 0.207842 } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t2", "rows_for_plan": 100, - "cost_for_plan": 22.31738281, + "cost_for_plan": 0.0256761, "rest_of_plan": [ { - "plan_prefix": ["t2"], + "plan_prefix": "t2", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 100 + }, "considered_access_paths": [ { "access_type": "ref", @@ -1171,20 +1289,25 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 200.0585794, + "cost": 0.1821659, "chosen": true }, { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, + "access_type": "scan_with_join_cache", + "rows": 100, + "rows_after_filter": 100, + "rows_out": 1, + "cost": 0.9604227, + "cost_without_join_buffer": 2.56761, + "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", - "records": 1, - "cost": 200.0585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.1821659, "uses_join_buffering": false } } @@ -1192,20 +1315,22 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ] }, { - "plan_prefix": ["t2"], + "plan_prefix": "t2", "table": "t1", "rows_for_plan": 100, - "cost_for_plan": 242.3759623, + "cost_for_plan": 0.207842, "pruned_by_cost": true, - "current_cost": 242.3759623, - "best_cost": 242.3759623 + "current_cost": 0.207842, + "best_cost": 0.207842 } ] } ] }, { - "best_join_order": ["t1", "t2"] + "best_join_order": ["t1", "t2"], + "rows": 100, + "cost": 0.207842 }, { "substitute_best_equal": { @@ -1219,14 +1344,17 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "attached_conditions_summary": [ { "table": "t1", - "attached": "t1.b is not null" + "attached_condition": "t1.b is not null" }, { "table": "t2", - "attached": "t1.a = t2.b + 2" + "attached_condition": "t1.a = t2.b + 2" } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -1244,10 +1372,11 @@ drop table t1,t2,t0; # group_by min max optimization # CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +insert into t1 select seq, mod(seq,4)+1 from seq_1_to_65536; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK +test.t1 analyze status Table is already up to date EXPLAIN SELECT DISTINCT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by @@ -1286,7 +1415,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "range_analysis": { "table_scan": { "rows": 65536, - "cost": 13255.2 + "cost": 10.29477568 }, "potential_range_indexes": [ { @@ -1302,9 +1431,8 @@ EXPLAIN SELECT DISTINCT a FROM t1 { ], "best_covering_index_scan": { "index": "a", - "cost": 13377.39141, - "chosen": false, - "cause": "cost" + "cost": 9.123706862, + "chosen": true }, "group_index_range": { "distinct_query": true, @@ -1313,7 +1441,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "index": "a", "covering": true, "rows": 5, - "cost": 6.25 + "cost": 0.004191135 } ] }, @@ -1325,7 +1453,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.25, + "cost": 0.004191135, "key_parts_used_for_access": ["a"], "ranges": [], "chosen": true @@ -1339,12 +1467,12 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.25, + "cost": 0.004191135, "key_parts_used_for_access": ["a"], "ranges": [] }, "rows_for_plan": 5, - "cost_for_plan": 6.25, + "cost_for_plan": 0.004191135, "chosen": true } } @@ -1354,23 +1482,29 @@ EXPLAIN SELECT DISTINCT a FROM t1 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "index_merge", - "resulting_rows": 5, - "cost": 6.25, + "rows": 5, + "rows_after_filter": 5, + "rows_out": 5, + "cost": 0.004191135, "chosen": true } ], "chosen_access_method": { "type": "index_merge", - "records": 5, - "cost": 6.25, + "rows_read": 5, + "rows_out": 5, + "cost": 0.004191135, "uses_join_buffering": false } } @@ -1378,15 +1512,17 @@ EXPLAIN SELECT DISTINCT a FROM t1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 5, - "cost_for_plan": 7.25 + "cost_for_plan": 0.004191135 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 5, + "cost": 0.004191135 }, { "attaching_conditions_to_tables": { @@ -1394,12 +1530,15 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } }, { + "make_join_readinfo": [] + }, + { "test_if_skip_sort_order": [] } ] @@ -1425,10 +1564,13 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 20 NULL 8 Using where; Using index for group-by +1 SIMPLE t1 range NULL a 20 NULL 7 Using where; Using index for group-by +set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 20 NULL 7 Using where; Using index select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { +set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "steps": [ { "join_preparation": { @@ -1484,7 +1626,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "range_analysis": { "table_scan": { "rows": 7, - "cost": 5.429052734 + "cost": 0.001130435 }, "potential_range_indexes": [ { @@ -1495,8 +1637,9 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "best_covering_index_scan": { "index": "a", - "cost": 2.409226263, - "chosen": true + "cost": 0.001758432, + "chosen": false, + "cause": "cost" }, "setup_range_conditions": [], "analyzing_range_alternatives": { @@ -1512,8 +1655,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "index": "a", "covering": true, "ranges": ["(2,3) <= (b,c) <= (2,3)"], - "rows": 8, - "cost": 2.2 + "rows": 7, + "cost": 0.004425189 } ] }, @@ -1524,54 +1667,61 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "min_aggregate": true, "max_aggregate": false, "distinct_aggregate": false, - "rows": 8, - "cost": 2.2, + "rows": 7, + "cost": 0.004425189, "key_parts_used_for_access": ["a", "b", "c"], "ranges": ["(2,3) <= (b,c) <= (2,3)"], - "chosen": true - }, - "chosen_range_access_summary": { - "range_access_plan": { - "type": "index_group", - "index": "a", - "min_max_arg": "d", - "min_aggregate": true, - "max_aggregate": false, - "distinct_aggregate": false, - "rows": 8, - "cost": 2.2, - "key_parts_used_for_access": ["a", "b", "c"], - "ranges": ["(2,3) <= (b,c) <= (2,3)"] - }, - "rows_for_plan": 8, - "cost_for_plan": 2.2, - "chosen": true + "chosen": false, + "cause": "cost" } } + }, + { + "selectivity_for_indexes": [], + "selectivity_for_columns": [ + { + "column_name": "b", + "ranges": ["2 <= b <= 2"], + "selectivity_from_histogram": 0.285714286 + }, + { + "column_name": "c", + "ranges": ["3 <= c <= 3"], + "selectivity_from_histogram": 0.285714286 + } + ], + "cond_selectivity": 0.081632653 } ] }, { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { - "access_type": "index_merge", - "resulting_rows": 8, - "cost": 2.2, + "access_type": "scan", + "rows": 7, + "rows_after_filter": 1, + "rows_out": 0.571428573, + "cost": 0.001758432, + "index_only": true, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { - "type": "index_merge", - "records": 8, - "cost": 2.2, + "type": "scan", + "rows_read": 1, + "rows_out": 0.571428573, + "cost": 0.001758432, "uses_join_buffering": false } } @@ -1579,16 +1729,21 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", - "rows_for_plan": 8, - "cost_for_plan": 3.8, - "cost_for_sorting": 8 + "rows_for_plan": 0.571428573, + "cost_for_plan": 0.001758432, + "pushdown_cond_selectivity": 0.571428573, + "filtered": 8.163265322, + "rows_out": 0.571428573, + "cost_for_sorting": 3.585611e-4 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 0.571428573, + "cost": 0.002116993 }, { "substitute_best_equal": { @@ -1602,13 +1757,39 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "attached_conditions_summary": [ { "table": "t1", - "attached": "t1.b = 2 and t1.c = 3" + "attached_condition": "t1.b = 2 and t1.c = 3" } ] } }, { - "test_if_skip_sort_order": [] + "make_join_readinfo": [] + }, + { + "test_if_skip_sort_order": [ + { + "reconsidering_access_paths_for_index_ordering": { + "clause": "GROUP BY", + "table": "t1", + "rows_estimation": 1, + "filesort_cost": 4.579083e-5, + "read_cost": 0.001804223, + "filesort_type": "priority_queue with addon fields", + "fanout": 1, + "possible_keys": [ + { + "index": "a", + "can_resolve_order": true, + "direction": 1, + "rows_to_examine": 7, + "range_scan": false, + "scan_cost": 0.001758432, + "chosen": true + } + ] + } + } + ] }, { "prepare_sum_aggregators": { @@ -1699,7 +1880,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "range_analysis": { "table_scan": { "rows": 16, - "cost": 7.23125 + "cost": 0.01253808 }, "potential_range_indexes": [ { @@ -1710,7 +1891,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.21171589, + "cost": 0.008002862, "chosen": true }, "setup_range_conditions": [], @@ -1728,7 +1909,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "covering": true, "ranges": ["(2001-01-04) <= (a)"], "rows": 9, - "cost": 2.35 + "cost": 0.005620843 } ] }, @@ -1740,7 +1921,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "max_aggregate": true, "distinct_aggregate": false, "rows": 9, - "cost": 2.35, + "cost": 0.005620843, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"], "chosen": true @@ -1754,12 +1935,12 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "max_aggregate": true, "distinct_aggregate": false, "rows": 9, - "cost": 2.35, + "cost": 0.005620843, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"] }, "rows_for_plan": 9, - "cost_for_plan": 2.35, + "cost_for_plan": 0.005620843, "chosen": true } } @@ -1769,24 +1950,30 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "index_merge", - "resulting_rows": 9, - "cost": 2.35, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.005620843, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", - "records": 9, - "cost": 2.35, + "rows_read": 9, + "rows_out": 9, + "cost": 0.005620843, "uses_join_buffering": false } } @@ -1794,16 +1981,18 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 9, - "cost_for_plan": 4.15, - "cost_for_sorting": 9 + "cost_for_plan": 0.005620843, + "cost_for_sorting": 0.005728198 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 9, + "cost": 0.011349041 }, { "substitute_best_equal": { @@ -1817,12 +2006,15 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "attached_conditions_summary": [ { "table": "t1", - "attached": "t1.a >= 20010104e0" + "attached_condition": "t1.a >= 20010104e0" } ] } }, { + "make_join_readinfo": [] + }, + { "test_if_skip_sort_order": [] }, { @@ -1909,7 +2101,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "range_analysis": { "table_scan": { "rows": 16, - "cost": 7.23125 + "cost": 0.01253808 }, "potential_range_indexes": [ { @@ -1920,7 +2112,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.21171589, + "cost": 0.008002862, "chosen": true }, "setup_range_conditions": [], @@ -1938,7 +2130,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "covering": true, "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "rows": 9, - "cost": 2.35 + "cost": 0.005620843 } ] }, @@ -1950,7 +2142,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "max_aggregate": false, "distinct_aggregate": false, "rows": 9, - "cost": 2.35, + "cost": 0.005620843, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "chosen": true @@ -1964,12 +2156,12 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "max_aggregate": false, "distinct_aggregate": false, "rows": 9, - "cost": 2.35, + "cost": 0.005620843, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"] }, "rows_for_plan": 9, - "cost_for_plan": 2.35, + "cost_for_plan": 0.005620843, "chosen": true } } @@ -1979,24 +2171,30 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "index_merge", - "resulting_rows": 9, - "cost": 2.35, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.005620843, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", - "records": 9, - "cost": 2.35, + "rows_read": 9, + "rows_out": 9, + "cost": 0.005620843, "uses_join_buffering": false } } @@ -2004,16 +2202,18 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 9, - "cost_for_plan": 4.15, - "cost_for_sorting": 9 + "cost_for_plan": 0.005620843, + "cost_for_sorting": 0.005728198 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 9, + "cost": 0.011349041 }, { "substitute_best_equal": { @@ -2027,12 +2227,15 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "attached_conditions_summary": [ { "table": "t1", - "attached": "t1.a = 20010104e0" + "attached_condition": "t1.a = 20010104e0" } ] } }, { + "make_join_readinfo": [] + }, + { "test_if_skip_sort_order": [] } ] @@ -2050,28 +2253,27 @@ drop table t1; # # Late ORDER BY optimization # -create table ten(a int); -insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table one_k(a int primary key); -insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table t1 ( pk int not null, a int, b int, c int, filler char(100), -KEY a_a(c), +KEY c(c), KEY a_c(a,c), KEY a_b(a,b) ); -insert into t1 -select a, a,a,a, 'filler-dataaa' from test.one_k; +insert into t1 select seq, seq,seq,seq, 'filler-dataaa' from seq_0_to_999; update t1 set a=1 where pk between 0 and 180; update t1 set b=2 where pk between 0 and 20; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +explain select * from t1 where a=1 and b=2 order by c limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a_c,a_b a_b 10 const,const 21 Using where; Using filesort +update t1 set b=2 where pk between 20 and 40; set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; id select_type table type possible_keys key key_len ref rows Extra @@ -2156,11 +2358,11 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "range_analysis": { "table_scan": { "rows": 1000, - "cost": 232.5644531 + "cost": 0.1731718 }, "potential_range_indexes": [ { - "index": "a_a", + "index": "c", "usable": false, "cause": "not applicable" }, @@ -2185,8 +2387,9 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 216.2943776, - "chosen": true + "cost": 0.223677504, + "chosen": false, + "cause": "cost" }, { "index": "a_b", @@ -2194,8 +2397,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "rowid_ordered": true, "using_mrr": false, "index_only": false, - "rows": 21, - "cost": 25.36242739, + "rows": 41, + "cost": 0.051929313, "chosen": true } ], @@ -2212,11 +2415,11 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "range_access_plan": { "type": "range_scan", "index": "a_b", - "rows": 21, + "rows": 41, "ranges": ["(1,2) <= (a,b) <= (1,2)"] }, - "rows_for_plan": 21, - "cost_for_plan": 25.36242739, + "rows_for_plan": 41, + "cost_for_plan": 0.051929313, "chosen": true } } @@ -2226,12 +2429,12 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "rowid_filters": [ { "key": "a_b", - "build_cost": 0.886777098, - "rows": 21 + "build_cost": 0.005839142, + "rows": 41 }, { "key": "a_c", - "build_cost": 10.52169992, + "build_cost": 0.024214742, "rows": 180 } ] @@ -2240,7 +2443,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "selectivity_for_indexes": [ { "index_name": "a_b", - "selectivity_from_index": 0.021 + "selectivity_from_index": 0.041 } ], "selectivity_for_columns": [ @@ -2255,34 +2458,36 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "selectivity_from_histogram": 0.021 } ], - "cond_selectivity": 0.021 + "cond_selectivity": 0.041 } ] }, { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "ref", "index": "a_c", "used_range_estimates": true, - "rowid_filter_skipped": "worst/max seeks clipping", "rows": 180, - "cost": 180.2743776, + "cost": 0.222922562, "chosen": true }, { "access_type": "ref", "index": "a_b", "used_range_estimates": true, - "rows": 21, - "cost": 21.14242739, + "rows": 41, + "cost": 0.051379171, "chosen": true }, { @@ -2293,8 +2498,9 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { ], "chosen_access_method": { "type": "ref", - "records": 21, - "cost": 21.14242739, + "rows_read": 41, + "rows_out": 41, + "cost": 0.051379171, "uses_join_buffering": false } } @@ -2302,15 +2508,17 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", - "rows_for_plan": 21, - "cost_for_plan": 25.34242739 + "rows_for_plan": 41, + "cost_for_plan": 0.051379171 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 41, + "cost": 0.051379171 }, { "substitute_best_equal": { @@ -2324,38 +2532,42 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } }, { + "make_join_readinfo": [] + }, + { "test_if_skip_sort_order": [ { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", - "fanout": 1, - "read_time": 21.14342739, "table": "t1", - "rows_estimation": 21, + "rows_estimation": 41, + "filesort_cost": 9.387121e-4, + "read_cost": 0.052317883, + "filesort_type": "priority_queue with addon fields", + "fanout": 1, "possible_keys": [ { - "index": "a_a", + "index": "c", "can_resolve_order": true, "direction": 1, - "updated_limit": 47, - "index_scan_time": 47, - "usable": false, - "cause": "cost" + "rows_to_examine": 24, + "range_scan": false, + "scan_cost": 0.030403398, + "chosen": true }, { "index": "a_c", "can_resolve_order": true, "direction": 1, - "updated_limit": 47, - "range_scan_time": 4.331020747, - "index_scan_time": 4.331020747, - "records": 180, + "rows_to_examine": 4.390243902, + "range_scan": true, + "scan_cost": 0.023415994, "chosen": true }, { @@ -2369,13 +2581,9 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { { "table": "t1", "range_analysis": { - "table_scan": { - "rows": 1000, - "cost": 1.79769e308 - }, "potential_range_indexes": [ { - "index": "a_a", + "index": "c", "usable": false, "cause": "not applicable" }, @@ -2400,7 +2608,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 216.2943776, + "cost": 0.223677504, + "cost_with_limit": 0.002574553, "chosen": true } ], @@ -2421,7 +2630,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1) <= (a) <= (1)"] }, "rows_for_plan": 180, - "cost_for_plan": 216.2943776, + "cost_for_plan": 0.223677504, "chosen": true } } @@ -2439,7 +2648,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { } ] } 0 0 -drop table t1,ten,one_k; +drop table t1; # # TABLE ELIMINATION # @@ -2530,7 +2739,8 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "table": "t1", "table_scan": { "rows": 4, - "cost": 2.006835938 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { @@ -2544,23 +2754,30 @@ select t1.a from t1 left join t2 on t1.a=t2.a { { "considered_execution_plans": [ { - "plan_prefix": ["t2"], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01066122, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 4, - "cost": 2.006835938, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -2568,15 +2785,17 @@ select t1.a from t1 left join t2 on t1.a=t2.a { ] }, { - "plan_prefix": ["t2"], + "plan_prefix": "", "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.806835937 + "cost_for_plan": 0.01066122 } ] }, { - "best_join_order": ["t2", "t1"] + "best_join_order": ["t2", "t1"], + "rows": 4, + "cost": 0.01066122 }, { "substitute_best_equal": { @@ -2585,19 +2804,23 @@ select t1.a from t1 left join t2 on t1.a=t2.a { } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { - "attached_conditions_computation": [], + "attached_conditions_computation": [ + { + "condition_on_constant_tables": "1", + "computing_condition": [] + } + ], "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -2676,14 +2899,16 @@ explain select * from t1 left join t2 on t2.a=t1.a { "table": "t1", "table_scan": { "rows": 4, - "cost": 2.006835938 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { "table": "t2", "table_scan": { "rows": 2, - "cost": 2.004394531 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -2691,23 +2916,30 @@ explain select * from t1 left join t2 on t2.a=t1.a { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01066122, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 4, - "cost": 2.006835938, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -2715,36 +2947,39 @@ explain select * from t1 left join t2 on t2.a=t1.a { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.806835937, + "cost_for_plan": 0.01066122, "rest_of_plan": [ { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "get_costs_for_tables": [ { "best_access_path": { "table": "t2", + "plan_details": { + "record_count": 4 + }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, - "cost": 4, + "cost": 0.007120904, "chosen": true }, { - "access_type": "scan", - "resulting_rows": 2, - "cost": 8.017578125, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", - "records": 1, - "cost": 4, + "rows_read": 1, + "rows_out": 1, + "cost": 0.007120904, "uses_join_buffering": false } } @@ -2752,17 +2987,19 @@ explain select * from t1 left join t2 on t2.a=t1.a { ] }, { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "table": "t2", "rows_for_plan": 4, - "cost_for_plan": 7.606835937 + "cost_for_plan": 0.017782124 } ] } ] }, { - "best_join_order": ["t1", "t2"] + "best_join_order": ["t1", "t2"], + "rows": 4, + "cost": 0.017782124 }, { "substitute_best_equal": { @@ -2778,23 +3015,27 @@ explain select * from t1 left join t2 on t2.a=t1.a { } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { - "attached_conditions_computation": [], + "attached_conditions_computation": [ + { + "condition_on_constant_tables": "1", + "computing_condition": [] + } + ], "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null }, { "table": "t2", - "attached": "trigcond(trigcond(t1.a is not null))" + "attached_condition": "trigcond(trigcond(t1.a is not null))" } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -2899,7 +3140,8 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "table": "t1", "table_scan": { "rows": 4, - "cost": 2.006835938 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { @@ -2919,23 +3161,30 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and { "considered_execution_plans": [ { - "plan_prefix": ["t3", "t2"], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01066122, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 4, - "cost": 2.006835938, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -2943,15 +3192,17 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and ] }, { - "plan_prefix": ["t3", "t2"], + "plan_prefix": "", "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.806835937 + "cost_for_plan": 0.01066122 } ] }, { - "best_join_order": ["t3", "t2", "t1"] + "best_join_order": ["t3", "t2", "t1"], + "rows": 4, + "cost": 0.01066122 }, { "substitute_best_equal": { @@ -2960,19 +3211,23 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { - "attached_conditions_computation": [], + "attached_conditions_computation": [ + { + "condition_on_constant_tables": "1", + "computing_condition": [] + } + ], "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -2989,33 +3244,27 @@ drop table t0, t1, t2, t3; # # IN subquery to sem-join is traced # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); -insert into t1 values (0,0),(1,1),(2,2); -create table t2 as select * from t1; -create table t11(a int, b int); -create table t10 (pk int, a int); -insert into t10 select a,a from t0; -create table t12 like t10; -insert into t12 select * from t10; -analyze table t1,t10; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK +insert into t1 select seq,seq from seq_0_to_3; +create table t2 (p int, a int); +insert into t2 select seq,seq from seq_1_to_10; set optimizer_trace='enabled=on'; -explain extended select * from t1 where a in (select pk from t10); +explain extended select * from t1 where a in (select p from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t10 ALL NULL NULL NULL NULL 10 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 10.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t10`) where 1 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a` +insert into t2 select seq,seq from seq_10_to_100; +explain extended select * from t1 where a in (select p from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 0.99 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a` select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -explain extended select * from t1 where a in (select pk from t10) { +explain extended select * from t1 where a in (select p from t2) { "steps": [ { "join_preparation": { @@ -3034,13 +3283,13 @@ explain extended select * from t1 where a in (select pk from t10) { } }, { - "expanded_query": "/* select#2 */ select t10.pk from t10" + "expanded_query": "/* select#2 */ select t2.p from t2" } ] } }, { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)" + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t2.p from t2)" } ] } @@ -3069,19 +3318,19 @@ explain extended select * from t1 where a in (select pk from t10) { { "condition_processing": { "condition": "WHERE", - "original_condition": "1 and t1.a = t10.pk", + "original_condition": "1 and t1.a = t2.p", "steps": [ { "transformation": "equality_propagation", - "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + "resulting_condition": "1 and multiple equal(t1.a, t2.p)" }, { "transformation": "constant_propagation", - "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + "resulting_condition": "1 and multiple equal(t1.a, t2.p)" }, { "transformation": "trivial_condition_removal", - "resulting_condition": "multiple equal(t1.a, t10.pk)" + "resulting_condition": "multiple equal(t1.a, t2.p)" } ] } @@ -3095,7 +3344,7 @@ explain extended select * from t1 where a in (select pk from t10) { "depends_on_map_bits": [] }, { - "table": "t10", + "table": "t2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] @@ -3110,15 +3359,17 @@ explain extended select * from t1 where a in (select pk from t10) { { "table": "t1", "table_scan": { - "rows": 3, - "cost": 2.006591797 + "rows": 4, + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { - "table": "t10", + "table": "t2", "table_scan": { - "rows": 10, - "cost": 2.021972656 + "rows": 101, + "read_cost": 0.022600505, + "read_and_compare_cost": 0.025832505 } } ] @@ -3134,23 +3385,30 @@ explain extended select * from t1 where a in (select pk from t10) { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { - "table": "t10", + "table": "t2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.025832505, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "rows_read": 101, + "rows_out": 101, + "cost": 0.025832505, "uses_join_buffering": false } } @@ -3158,10 +3416,10 @@ explain extended select * from t1 where a in (select pk from t10) { ] }, { - "plan_prefix": [], - "table": "t10", - "rows_for_plan": 10, - "cost_for_plan": 4.021972656 + "plan_prefix": "", + "table": "t2", + "rows_for_plan": 101, + "cost_for_plan": 0.025832505 } ] } @@ -3171,42 +3429,56 @@ explain extended select * from t1 where a in (select pk from t10) { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.006591797, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01066122, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.006591797, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } }, { "best_access_path": { - "table": "t10", + "table": "t2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.025832505, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "rows_read": 101, + "rows_out": 101, + "cost": 0.025832505, "uses_join_buffering": false } } @@ -3214,30 +3486,38 @@ explain extended select * from t1 where a in (select pk from t10) { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", - "rows_for_plan": 3, - "cost_for_plan": 2.606591797, + "rows_for_plan": 4, + "cost_for_plan": 0.01066122, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "get_costs_for_tables": [ { "best_access_path": { - "table": "t10", + "table": "t2", + "plan_details": { + "record_count": 4 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "access_type": "scan_with_join_cache", + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.063593833, + "cost_without_join_buffer": 0.10333002, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "rows_read": 101, + "rows_out": 101, + "cost": 0.063593833, "uses_join_buffering": true } } @@ -3245,38 +3525,47 @@ explain extended select * from t1 where a in (select pk from t10) { ] }, { - "plan_prefix": ["t1"], - "table": "t10", - "rows_for_plan": 30, - "cost_for_plan": 10.62856445, + "plan_prefix": "t1", + "table": "t2", + "rows_for_plan": 404, + "cost_for_plan": 0.074255053, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "read_time": 10.62856445 + "rows": 4, + "cost": 0.074255053 }, { "strategy": "SJ-Materialization", - "records": 3, - "read_time": 5.278564453 + "rows": 4, + "cost": 0.078768645 }, { "strategy": "DuplicateWeedout", - "records": 3, - "read_time": 27.12856445 + "prefix_row_count": 4, + "tmp_table_rows": 1, + "sj_inner_fanout": 101, + "rows": 4, + "dups_cost": 0.074255053, + "write_cost": 0.02564388, + "full_lookup_cost": 0.06503188, + "total_cost": 0.164930813 }, { - "chosen_strategy": "SJ-Materialization" + "chosen_strategy": "FirstMatch" } - ] + ], + "sj_rows_out": 1, + "sj_rows_for_plan": 4, + "sj_filtered": 0.99009901 } ] }, { - "plan_prefix": [], - "table": "t10", - "rows_for_plan": 10, - "cost_for_plan": 4.021972656, + "plan_prefix": "", + "table": "t2", + "rows_for_plan": 101, + "cost_for_plan": 0.025832505, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -3285,46 +3574,43 @@ explain extended select * from t1 where a in (select pk from t10) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "SJ-Materialization", + "semi_join_strategy": "FirstMatch", "join_order": [ { - "table": "t10" + "table": "t2" } ] } ] }, { - "best_join_order": ["t1", "<subquery2>"] + "best_join_order": ["t1", "t2"], + "rows": 4, + "cost": 0.074255053 }, { "substitute_best_equal": { "condition": "WHERE", - "resulting_condition": "1" + "resulting_condition": "t2.p = t1.a" } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "t1", - "attached": null - }, - { - "table": "t10", - "attached": null + "attached_condition": null }, { - "table": "<subquery2>", - "attached": null + "table": "t2", + "attached_condition": "t2.p = t1.a" } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -3337,12 +3623,10 @@ explain extended select * from t1 where a in (select pk from t10) { } ] } 0 0 -drop table t0,t1,t11,t10,t12,t2; +drop table t1,t2; # # Selectivities for columns and indexes. # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( pk int, a int, @@ -3350,7 +3634,7 @@ b int, key pk(pk), key pk_a(pk,a), key pk_a_b(pk,a,b)); -insert into t1 select a,a,a from t0; +insert into t1 select seq,seq,seq from seq_0_to_9; ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES (); Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -3464,7 +3748,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 6.031738281 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -3485,7 +3769,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "best_covering_index_scan": { "index": "pk_a_b", - "cost": 3.010739566, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -3498,7 +3782,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.345585794, + "cost": 0.002574553, "chosen": true }, { @@ -3508,7 +3792,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.345829876, + "cost": 0.002574553, "chosen": false, "cause": "cost" }, @@ -3519,7 +3803,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.346073957, + "cost": 0.001478954, "chosen": true } ], @@ -3527,10 +3811,10 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "intersecting_indexes": [ { "index": "pk", - "index_scan_cost": 1.000585794, - "cumulated_index_scan_cost": 1.000585794, - "disk_sweep_cost": 0.90078125, - "cumulative_total_cost": 1.901367044, + "index_scan_cost": 0.000806227, + "cumulated_index_scan_cost": 0.000806227, + "disk_sweep_cost": 0.001143284, + "cumulative_total_cost": 0.001949511, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, @@ -3568,7 +3852,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"] }, "rows_for_plan": 1, - "cost_for_plan": 0.346073957, + "cost_for_plan": 0.001478954, "chosen": true } } @@ -3578,17 +3862,17 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "rowid_filters": [ { "key": "pk", - "build_cost": 0.130585794, + "build_cost": 0.000899465, "rows": 1 }, { "key": "pk_a", - "build_cost": 0.130829876, + "build_cost": 0.000899465, "rows": 1 }, { "key": "pk_a_b", - "build_cost": 0.131073957, + "build_cost": 0.000899465, "rows": 1 } ] @@ -3619,18 +3903,21 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "ref", "index": "pk", "used_range_estimates": true, "rows": 1, - "cost": 1.125585794, + "cost": 0.002024411, "chosen": true }, { @@ -3638,7 +3925,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a", "used_range_estimates": true, "rows": 1, - "cost": 1.125829876, + "cost": 0.002024411, "chosen": false, "cause": "cost" }, @@ -3647,7 +3934,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a_b", "used_range_estimates": true, "rows": 1, - "cost": 0.126073957, + "cost": 0.000928812, "chosen": true }, { @@ -3658,8 +3945,9 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "chosen_access_method": { "type": "ref", - "records": 1, - "cost": 0.126073957, + "rows_read": 1, + "rows_out": 1, + "cost": 0.000928812, "uses_join_buffering": false } } @@ -3667,15 +3955,17 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 0.326073957 + "cost_for_plan": 0.000928812 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 1, + "cost": 0.000928812 }, { "substitute_best_equal": { @@ -3689,10 +3979,13 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -3707,7 +4000,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { } 0 0 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables= @save_use_stat_tables; -drop table t0,t1; +drop table t1; set optimizer_trace="enabled=off"; # # Tests added to show that sub-statements are not traced @@ -3728,8 +4021,6 @@ declare a int default 0; select count(*) from t2 into a; return a; end| -Warnings: -Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead set optimizer_trace='enabled=on'; select f1(a) from t1; f1(a) @@ -3771,7 +4062,8 @@ select f1(a) from t1 { "table": "t1", "table_scan": { "rows": 4, - "cost": 2.006835938 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } } ] @@ -3779,23 +4071,30 @@ select f1(a) from t1 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01066122, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 4, - "cost": 2.006835938, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -3803,15 +4102,17 @@ select f1(a) from t1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.806835937 + "cost_for_plan": 0.01066122 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 4, + "cost": 0.01066122 }, { "attaching_conditions_to_tables": { @@ -3819,10 +4120,13 @@ select f1(a) from t1 { "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -3875,7 +4179,8 @@ select f2(a) from t1 { "table": "t1", "table_scan": { "rows": 4, - "cost": 2.006835938 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } } ] @@ -3883,23 +4188,30 @@ select f2(a) from t1 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01066122, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 4, - "cost": 2.006835938, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -3907,15 +4219,17 @@ select f2(a) from t1 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.806835937 + "cost_for_plan": 0.01066122 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 4, + "cost": 0.01066122 }, { "attaching_conditions_to_tables": { @@ -3923,10 +4237,13 @@ select f2(a) from t1 { "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -3956,7 +4273,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2360 +2819 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3970,7 +4287,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2260 0 + 2719 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3978,7 +4295,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2360 0 +select * from t1 2819 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -3999,11 +4316,21 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete from t0 where t0.a<3 { "steps": [ { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "delete from t0 using dual where t0.a < 3" + } + ] + } + }, + { "table": "t0", "range_analysis": { "table_scan": { "rows": 10, - "cost": 6.021972656 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -4022,7 +4349,7 @@ explain delete from t0 where t0.a<3 { "using_mrr": false, "index_only": false, "rows": 3, - "cost": 3.746757383, + "cost": 0.005042291, "chosen": true } ], @@ -4030,7 +4357,7 @@ explain delete from t0 where t0.a<3 { }, "group_index_range": { "chosen": false, - "cause": "no join" + "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { @@ -4040,7 +4367,7 @@ explain delete from t0 where t0.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 3.746757383, + "cost_for_plan": 0.005042291, "chosen": true } } @@ -4143,7 +4470,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 6.021972656 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -4154,7 +4481,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 3.005857945, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -4167,7 +4494,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 0.746757383, + "cost": 0.001755494, "chosen": true } ], @@ -4188,12 +4515,22 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 0.746757383, + "cost_for_plan": 0.001755494, "chosen": true } } }, { + "table": "t0", + "rowid_filters": [ + { + "key": "a", + "build_cost": 0.001129926, + "rows": 3 + } + ] + }, + { "selectivity_for_indexes": [ { "index_name": "a", @@ -4208,7 +4545,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 6.021972656 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -4219,7 +4556,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 3.005857945, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -4232,7 +4569,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 0.746757383, + "cost": 0.001755494, "chosen": true } ], @@ -4253,12 +4590,22 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 0.746757383, + "cost_for_plan": 0.001755494, "chosen": true } } }, { + "table": "t1", + "rowid_filters": [ + { + "key": "a", + "build_cost": 0.001129926, + "rows": 3 + } + ] + }, + { "selectivity_for_indexes": [ { "index_name": "a", @@ -4273,23 +4620,30 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t0", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "range", - "resulting_rows": 3, - "cost": 0.746757383, + "range_index": "a", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.001755494, "chosen": true } ], "chosen_access_method": { "type": "range", - "records": 3, - "cost": 0.746757383, + "rows_read": 3, + "rows_out": 3, + "cost": 0.001755494, "uses_join_buffering": false } } @@ -4297,18 +4651,25 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "range", - "resulting_rows": 3, - "cost": 0.746757383, + "range_index": "a", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.001755494, "chosen": true } ], "chosen_access_method": { "type": "range", - "records": 3, - "cost": 0.746757383, + "rows_read": 3, + "rows_out": 3, + "cost": 0.001755494, "uses_join_buffering": false } } @@ -4316,17 +4677,20 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t0", "rows_for_plan": 3, - "cost_for_plan": 1.346757383, + "cost_for_plan": 0.001755494, "rest_of_plan": [ { - "plan_prefix": ["t0"], + "plan_prefix": "t0", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { "access_type": "ref", @@ -4334,7 +4698,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "reason": "not better than ref estimates", "rows": 1, - "cost": 3.001757383, + "cost": 0.002376836, "chosen": true }, { @@ -4345,8 +4709,9 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records": 1, - "cost": 3.001757383, + "rows_read": 1, + "rows_out": 1, + "cost": 0.002376836, "uses_join_buffering": false } } @@ -4354,25 +4719,28 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ] }, { - "plan_prefix": ["t0"], + "plan_prefix": "t0", "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 4.948514767 + "cost_for_plan": 0.00413233 } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 1.346757383, + "cost_for_plan": 0.001755494, "rest_of_plan": [ { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "get_costs_for_tables": [ { "best_access_path": { "table": "t0", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { "access_type": "ref", @@ -4380,9 +4748,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not better than ref estimates", - "rowid_filter_skipped": "worst/max seeks clipping", - "rows": 2, - "cost": 3.003514767, + "rows": 1.166666667, + "cost": 0.002392836, "chosen": true }, { @@ -4393,8 +4760,9 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records": 2, - "cost": 3.003514767, + "rows_read": 1.166666667, + "rows_out": 1.166666667, + "cost": 0.002392836, "uses_join_buffering": false } } @@ -4402,20 +4770,22 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ] }, { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "table": "t0", - "rows_for_plan": 6, - "cost_for_plan": 5.55027215, + "rows_for_plan": 3.5, + "cost_for_plan": 0.00414833, "pruned_by_cost": true, - "current_cost": 5.55027215, - "best_cost": 4.948514767 + "current_cost": 0.00414833, + "best_cost": 0.00413233 } ] } ] }, { - "best_join_order": ["t0", "t1"] + "best_join_order": ["t0", "t1"], + "rows": 3, + "cost": 0.00413233 }, { "substitute_best_equal": { @@ -4429,14 +4799,17 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "attached_conditions_summary": [ { "table": "t0", - "attached": "t0.a < 3 and t0.a is not null" + "attached_condition": "t0.a < 3 and t0.a is not null" }, { "table": "t1", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -4516,7 +4889,8 @@ explain select * from (select rand() from t1)q { "table": "t1", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -4524,23 +4898,30 @@ explain select * from (select rand() from t1)q { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -4548,15 +4929,17 @@ explain select * from (select rand() from t1)q { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953 + "cost_for_plan": 0.010504815 } ] }, { - "best_join_order": ["t1"] + "best_join_order": ["t1"], + "rows": 3, + "cost": 0.010504815 }, { "attaching_conditions_to_tables": { @@ -4564,10 +4947,13 @@ explain select * from (select rand() from t1)q { "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -4588,7 +4974,8 @@ explain select * from (select rand() from t1)q { "table": "<derived2>", "table_scan": { "rows": 3, - "cost": 3 + "read_cost": 0.01235805, + "read_and_compare_cost": 0.012461052 } } ] @@ -4596,23 +4983,30 @@ explain select * from (select rand() from t1)q { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "<derived2>", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 3, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.012461052, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 3, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012461052, "uses_join_buffering": false } } @@ -4620,15 +5014,17 @@ explain select * from (select rand() from t1)q { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "<derived2>", "rows_for_plan": 3, - "cost_for_plan": 3.6 + "cost_for_plan": 0.012461052 } ] }, { - "best_join_order": ["<derived2>"] + "best_join_order": ["<derived2>"], + "rows": 3, + "cost": 0.012461052 }, { "attaching_conditions_to_tables": { @@ -4636,10 +5032,13 @@ explain select * from (select rand() from t1)q { "attached_conditions_summary": [ { "table": "<derived2>", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -4780,21 +5179,24 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "table": "t1", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_2", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -4810,23 +5212,30 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -4834,18 +5243,25 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -4853,29 +5269,37 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "rest_of_plan": [ { - "plan_prefix": ["t_inner_1"], + "plan_prefix": "t_inner_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.011523207, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -4883,18 +5307,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, { - "plan_prefix": ["t_inner_1"], + "plan_prefix": "t_inner_1", "table": "t_inner_2", "rows_for_plan": 9, - "cost_for_plan": 6.410253906 + "cost_for_plan": 0.022028022 } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_2", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "pruned_by_heuristic": true } ] @@ -4905,23 +5329,30 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -4929,18 +5360,25 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -4948,18 +5386,25 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -4967,30 +5412,38 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.011523207, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -4998,18 +5451,26 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.011523207, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -5017,30 +5478,38 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "table": "t_inner_1", "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t1", "t_inner_1"], + "plan_prefix": "t1,t_inner_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.015203373, + "cost_without_join_buffer": 0.094543335, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015203373, "uses_join_buffering": true } } @@ -5048,56 +5517,65 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, { - "plan_prefix": ["t1", "t_inner_1"], + "plan_prefix": "t1,t_inner_1", "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 13.81538086, + "cost_for_plan": 0.037231395, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "read_time": 33.86665039 + "rows": 3, + "cost": 0.136562595 }, { "strategy": "SJ-Materialization", - "records": 3, - "read_time": 7.215380859 + "rows": 3, + "cost": 0.059588485 }, { "strategy": "DuplicateWeedout", - "records": 3, - "read_time": 18.31538086 + "prefix_row_count": 3, + "tmp_table_rows": 1, + "sj_inner_fanout": 9, + "rows": 3, + "dups_cost": 0.037231395, + "write_cost": 0.02548291, + "full_lookup_cost": 0.00434619, + "total_cost": 0.067060495 }, { "chosen_strategy": "SJ-Materialization" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 3, + "sj_filtered": 11.11111111 } ] }, { - "plan_prefix": ["t1"], + "plan_prefix": "t1", "table": "t_inner_2", "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_2", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5119,7 +5597,9 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] }, { - "best_join_order": ["t1", "<subquery2>"] + "best_join_order": ["t1", "<subquery2>"], + "rows": 3, + "cost": 0.059588485 }, { "substitute_best_equal": { @@ -5128,31 +5608,35 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { - "attached_conditions_computation": [], + "attached_conditions_computation": [ + { + "condition_on_constant_tables": "1", + "computing_condition": [] + } + ], "attached_conditions_summary": [ { "table": "t1", - "attached": null + "attached_condition": null }, { "table": "t_inner_1", - "attached": null + "attached_condition": null }, { "table": "t_inner_2", - "attached": null + "attached_condition": null }, { "table": "<subquery2>", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -5334,42 +5818,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "table": "t_outer_1", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, - "cost": 2.015380859 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, - "cost": 2.015380859 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, - "cost": 2.015380859 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -5392,23 +5882,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5416,18 +5913,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5435,18 +5939,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -5454,18 +5965,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -5473,18 +5991,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5492,18 +6017,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -5511,30 +6043,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_outer_1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.011523207, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -5542,18 +6082,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -5561,18 +6109,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -5580,18 +6136,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.011523207, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -5599,18 +6163,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -5618,30 +6190,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 10.02050781, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.050443503, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -5649,18 +6229,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.024600489, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -5668,18 +6256,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.024600489, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -5687,18 +6283,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.050443503, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -5706,30 +6310,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_1", "rows_for_plan": 81, - "cost_for_plan": 28.22563477, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -5737,18 +6349,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.067582275, + "cost_without_join_buffer": 0.850890015, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, "uses_join_buffering": true } } @@ -5756,18 +6376,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -5775,49 +6403,61 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "table": "t_inner_2", "rows_for_plan": 729, - "cost_for_plan": 176.0410156, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "read_time": 389.4047852 + "rows": 27, + "cost": 1.23517089 }, { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 289.4410156 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.222053862, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.368747182 }, { "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.034460781, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -5825,18 +6465,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.080024379, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -5844,41 +6492,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2", "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 307.6461426, + "cost_for_plan": 0.403207963, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -5886,79 +6531,80 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 455.4615234, + "cost_for_plan": 0.664765924, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "read_time": 668.825293 + "rows": 27, + "cost": 1.579280032 }, { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 568.8615234 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.664765924, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.811459244 }, { "chosen_strategy": "DuplicateWeedout" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 340.0563965, + "cost_for_plan": 0.448771561, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "table": "t_inner_4", "rows_for_plan": 243, - "cost_for_plan": 78.83076172, + "cost_for_plan": 0.116820804, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 243 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -5966,18 +6612,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 243 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -5985,41 +6639,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_4", "table": "t_inner_2", "rows_for_plan": 2187, - "cost_for_plan": 518.2461426, + "cost_for_plan": 0.745494255, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_4", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_4,t_inner_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 2187 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 6.764540577, + "cost_without_join_buffer": 25.02637682, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 6.764540577, "uses_join_buffering": true } } @@ -6027,113 +6678,119 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_4", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_4,t_inner_2", "table": "t_inner_3", "rows_for_plan": 19683, - "cost_for_plan": 4456.861523, + "cost_for_plan": 7.510034832, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "read_time": 9562.749707 + "rows": 27, + "cost": 28.96624341 }, { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 7413.361523 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 729, + "rows": 27, + "dups_cost": 7.510034832, + "write_cost": 0.02934619, + "full_lookup_cost": 3.16837251, + "total_cost": 10.70775353 }, { "chosen_strategy": "FirstMatch" } ], + "sj_rows_out": 0.012345679, + "sj_rows_for_plan": 27, + "sj_filtered": 0.137174211, "pruned_by_cost": true, - "current_cost": 9562.749707, - "best_cost": 568.8615234 + "current_cost": 28.96624341, + "best_cost": 0.811459244 } ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_4", "table": "t_inner_3", "rows_for_plan": 2187, - "cost_for_plan": 518.2461426, + "cost_for_plan": 0.745494255, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 176.0410156, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [], "pruned_by_heuristic": "min_read_time" } ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_2", "rows_for_plan": 243, - "cost_for_plan": 60.63588867, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 28.22563477, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 60.63588867, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_1", "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.024443331, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -6141,18 +6798,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.024443331, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -6160,18 +6825,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.015203373, + "cost_without_join_buffer": 0.094543335, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015203373, "uses_join_buffering": true } } @@ -6179,18 +6852,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.024443331, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -6198,30 +6879,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_outer_2", "rows_for_plan": 81, - "cost_for_plan": 24.62563477, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -6229,18 +6918,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.067582275, + "cost_without_join_buffer": 0.850890015, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, "uses_join_buffering": true } } @@ -6248,18 +6945,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -6267,44 +6972,56 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", "table": "t_inner_2", "rows_for_plan": 729, - "cost_for_plan": 172.4410156, + "cost_for_plan": 0.219286686, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 285.8410156 + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.219286686, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.365980006 }, { "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.034460781, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -6312,18 +7029,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.080024379, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -6331,41 +7056,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_2", "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 304.0461426, + "cost_for_plan": 0.400440787, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_2,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -6373,79 +7095,80 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_2,t_inner_4", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 451.8615234, + "cost_for_plan": 0.661998748, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "read_time": 665.225293 + "rows": 27, + "cost": 1.576512856 }, { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 565.2615234 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.661998748, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.808692068 }, { "chosen_strategy": "DuplicateWeedout" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_2", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 336.4563965, + "cost_for_plan": 0.446004385, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", "table": "t_inner_4", "rows_for_plan": 243, - "cost_for_plan": 75.23076172, + "cost_for_plan": 0.114053628, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 243 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -6453,18 +7176,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 243 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -6472,41 +7203,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4", "table": "t_inner_2", "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, + "cost_for_plan": 0.742727079, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_4", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 2187 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 6.764540577, + "cost_without_join_buffer": 25.02637682, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 6.764540577, "uses_join_buffering": true } } @@ -6514,96 +7242,111 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_4", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_2", "table": "t_inner_3", "rows_for_plan": 19683, - "cost_for_plan": 4453.261523, + "cost_for_plan": 7.507267656, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 7409.761523 + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 729, + "rows": 27, + "dups_cost": 7.507267656, + "write_cost": 0.02934619, + "full_lookup_cost": 3.16837251, + "total_cost": 10.70498636 }, { "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.012345679, + "sj_rows_for_plan": 27, + "sj_filtered": 0.137174211, "pruned_by_cost": true, - "current_cost": 7409.761523, - "best_cost": 565.2615234 + "current_cost": 10.70498636, + "best_cost": 0.808692068 } ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4", "table": "t_inner_3", "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, + "cost_for_plan": 0.742727079, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 172.4410156, + "cost_for_plan": 0.219286686, "semijoin_strategy_choice": [], "pruned_by_heuristic": "min_read_time" } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_inner_2", "rows_for_plan": 81, - "cost_for_plan": 24.62563477, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "read_time": 44.75893555 + "rows": 3, + "cost": 0.145008465 }, { "strategy": "DuplicateWeedout", - "records": 3, - "read_time": 37.22563477 + "prefix_row_count": 3, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 3, + "dups_cost": 0.046471353, + "write_cost": 0.02548291, + "full_lookup_cost": 0.01303857, + "total_cost": 0.084992833 }, { "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 3, + "sj_filtered": 3.703703704, "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.017419989, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -6611,18 +7354,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.012618795, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012618795, "uses_join_buffering": true } } @@ -6630,18 +7381,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.017419989, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -6649,35 +7408,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 44.64101563, + "cost_for_plan": 0.102412822, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.034460781, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -6685,18 +7447,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.080024379, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -6704,41 +7474,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2", "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 62.84614258, + "cost_for_plan": 0.136873603, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -6746,79 +7513,80 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 210.6615234, + "cost_for_plan": 0.398431564, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "read_time": 424.025293 + "rows": 27, + "cost": 1.312945672 }, { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 324.0615234 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.398431564, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.545124884 }, { "chosen_strategy": "DuplicateWeedout" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 95.25639648, + "cost_for_plan": 0.182437201, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 41.03076172, + "cost_for_plan": 0.097611628, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.034303623, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -6826,18 +7594,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.034303623, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -6845,41 +7621,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4", "table": "t_outer_2", "rows_for_plan": 81, - "cost_for_plan": 59.24614258, + "cost_for_plan": 0.131915251, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -6887,79 +7660,85 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 207.0615234, + "cost_for_plan": 0.393473212, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 320.4615234 + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.393473212, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.540166532 }, { "chosen_strategy": "DuplicateWeedout" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4", "table": "t_inner_3", "rows_for_plan": 81, - "cost_for_plan": 59.24614258, + "cost_for_plan": 0.131915251, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 44.64101563, + "cost_for_plan": 0.102412822, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_inner_4", "rows_for_plan": 27, - "cost_for_plan": 13.81538086, + "cost_for_plan": 0.037231395, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.065233941, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -6967,18 +7746,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.065233941, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -6986,18 +7773,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.065233941, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -7005,35 +7800,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", "table": "t_outer_2", "rows_for_plan": 243, - "cost_for_plan": 64.43076172, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_4", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4,t_outer_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 243 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -7041,18 +7839,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 243 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -7060,128 +7866,118 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_4", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4,t_outer_2", "table": "t_inner_2", "rows_for_plan": 2187, - "cost_for_plan": 503.8461426, + "cost_for_plan": 0.731138787, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 503.8461426, - "best_cost": 320.4615234 + "current_cost": 0.731138787, + "best_cost": 0.540166532 }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_4", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4,t_outer_2", "table": "t_inner_3", "rows_for_plan": 2187, - "cost_for_plan": 503.8461426, + "cost_for_plan": 0.731138787, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 503.8461426, - "best_cost": 320.4615234 + "current_cost": 0.731138787, + "best_cost": 0.540166532 } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", "table": "t_inner_2", "rows_for_plan": 243, - "cost_for_plan": 64.43076172, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 64.43076172, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_inner_3", "rows_for_plan": 81, - "cost_for_plan": 24.62563477, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 10.02050781, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 10.02050781, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_outer_2", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_2", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_4", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_3", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7205,7 +8001,9 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_4", "t_outer_2", "t_inner_3" - ] + ], + "rows": 27, + "cost": 0.540166532 }, { "substitute_best_equal": { @@ -7219,30 +8017,33 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached_conditions_summary": [ { "table": "t_outer_1", - "attached": null + "attached_condition": null }, { "table": "t_inner_1", - "attached": "t_inner_1.a = t_outer_1.a" + "attached_condition": "t_inner_1.a = t_outer_1.a" }, { "table": "t_inner_2", - "attached": null + "attached_condition": null }, { "table": "t_inner_4", - "attached": null + "attached_condition": null }, { "table": "t_outer_2", - "attached": null + "attached_condition": null }, { "table": "t_inner_3", - "attached": "t_inner_3.a = t_outer_2.a" + "attached_condition": "t_inner_3.a = t_outer_2.a" } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -7260,8 +8061,8 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) @@ -7425,42 +8226,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "table": "t_outer_1", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, - "cost": 2.015380859 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, - "cost": 2.015380859 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, - "cost": 2.015380859 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, - "cost": 2.005126953 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -7481,23 +8288,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -7505,18 +8319,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -7524,29 +8345,37 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "rest_of_plan": [ { - "plan_prefix": ["t_inner_1"], + "plan_prefix": "t_inner_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -7554,18 +8383,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_inner_1"], + "plan_prefix": "t_inner_1", "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 10.02050781 + "cost_for_plan": 0.02463804 } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_2", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "pruned_by_heuristic": true } ] @@ -7573,23 +8402,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -7597,18 +8433,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -7616,29 +8459,37 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_4", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "rest_of_plan": [ { - "plan_prefix": ["t_inner_4"], + "plan_prefix": "t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -7646,18 +8497,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_inner_4"], + "plan_prefix": "t_inner_4", "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 10.02050781 + "cost_for_plan": 0.02463804 } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_3", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "pruned_by_heuristic": true } ] @@ -7668,23 +8519,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -7692,18 +8550,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -7711,18 +8576,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -7730,18 +8602,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -7749,18 +8628,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -7768,18 +8654,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.011443245, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -7787,30 +8680,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_outer_1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.011523207, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -7818,18 +8719,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -7837,18 +8746,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -7856,18 +8773,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.011523207, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -7875,18 +8800,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.014133225, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -7894,30 +8827,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 10.02050781, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.050443503, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -7925,18 +8866,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_1", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.024600489, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -7944,18 +8893,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.024600489, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -7963,18 +8920,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.050443503, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -7982,30 +8947,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_1", "rows_for_plan": 81, - "cost_for_plan": 28.22563477, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -8013,18 +8986,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.067582275, + "cost_without_join_buffer": 0.850890015, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, "uses_join_buffering": true } } @@ -8032,18 +9013,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -8051,54 +9040,66 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "table": "t_inner_2", "rows_for_plan": 729, - "cost_for_plan": 176.0410156, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "read_time": 389.4047852 + "rows": 27, + "cost": 1.23517089 }, { "strategy": "SJ-Materialization", - "records": 27, - "read_time": 16.74101562 + "rows": 27, + "cost": 0.083958496 }, { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 289.4410156 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.222053862, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.368747182 }, { "chosen_strategy": "SJ-Materialization" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.034460781, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -8106,18 +9107,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.080024379, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -8125,41 +9134,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2", "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 34.94614258, + "cost_for_plan": 0.118419277, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -8167,135 +9173,133 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2,t_inner_4", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 182.7615234, + "cost_for_plan": 0.379977238, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "read_time": 396.125293 + "rows": 27, + "cost": 1.294491346 }, { "strategy": "SJ-Materialization", - "records": 27, - "read_time": 23.46152344 + "rows": 27, + "cost": 0.143278952 }, { "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 296.1615234 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.379977238, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.526670558 }, { "chosen_strategy": "SJ-Materialization" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1,t_inner_2", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 67.35639648, + "cost_for_plan": 0.163982875, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 67.35639648, - "best_cost": 23.46152344 + "current_cost": 0.163982875, + "best_cost": 0.143278952 } ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "table": "t_inner_4", "rows_for_plan": 243, - "cost_for_plan": 78.83076172, + "cost_for_plan": 0.116820804, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 78.83076172, - "best_cost": 23.46152344 + "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "plan_prefix": "t_outer_1,t_outer_2,t_inner_1", "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 176.0410156, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 176.0410156, - "best_cost": 23.46152344 + "current_cost": 0.222053862, + "best_cost": 0.143278952 } ] }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_2", "rows_for_plan": 243, - "cost_for_plan": 60.63588867, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 60.63588867, - "best_cost": 23.46152344 + "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 28.22563477, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 28.22563477, - "best_cost": 23.46152344 + "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], + "plan_prefix": "t_outer_1,t_outer_2", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 60.63588867, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 60.63588867, - "best_cost": 23.46152344 + "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_1", "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_2", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.024443331, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -8303,18 +9307,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.024443331, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -8322,18 +9334,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.015203373, + "cost_without_join_buffer": 0.094543335, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015203373, "uses_join_buffering": true } } @@ -8341,18 +9361,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.024443331, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -8360,59 +9388,358 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_outer_2", "rows_for_plan": 81, - "cost_for_plan": 24.62563477, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 24.62563477, - "best_cost": 23.46152344 + "rest_of_plan": [ + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_4", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.067582275, + "cost_without_join_buffer": 0.850890015, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172815333, + "cost_without_join_buffer": 0.926902845, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", + "table": "t_inner_2", + "rows_for_plan": 729, + "cost_for_plan": 0.219286686, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.219286686, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.365980006 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, + "pruned_by_cost": true, + "current_cost": 0.365980006, + "best_cost": 0.143278952 + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", + "table": "t_inner_4", + "rows_for_plan": 243, + "cost_for_plan": 0.114053628, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4", + "table": "t_inner_2", + "rows_for_plan": 2187, + "cost_for_plan": 0.742727079, + "semijoin_strategy_choice": [], + "pruned_by_cost": true, + "current_cost": 0.742727079, + "best_cost": 0.143278952 + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4", + "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 0.742727079, + "semijoin_strategy_choice": [ + { + "strategy": "SJ-Materialization", + "rows": 81, + "cost": 0.116338225 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 81, + "sj_filtered": 3.703703704, + "rest_of_plan": [ + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_3", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2,t_inner_4,t_inner_3", + "table": "t_inner_2", + "rows_for_plan": 729, + "cost_for_plan": 0.377896186, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 1.00428504, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 1.15097836 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, + "pruned_by_cost": true, + "current_cost": 1.15097836, + "best_cost": 0.143278952 + } + ] + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_outer_2", + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 0.219286686, + "semijoin_strategy_choice": [], + "pruned_by_cost": true, + "current_cost": 0.219286686, + "best_cost": 0.143278952 + } + ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_inner_2", "rows_for_plan": 81, - "cost_for_plan": 24.62563477, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "read_time": 44.75893555 + "rows": 3, + "cost": 0.145008465 }, { "strategy": "SJ-Materialization", - "records": 3, - "read_time": 8.125634766 + "rows": 3, + "cost": 0.065137975 }, { "strategy": "DuplicateWeedout", - "records": 3, - "read_time": 37.22563477 + "prefix_row_count": 3, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 3, + "dups_cost": 0.046471353, + "write_cost": 0.02548291, + "full_lookup_cost": 0.01303857, + "total_cost": 0.084992833 }, { "chosen_strategy": "SJ-Materialization" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 3, + "sj_filtered": 3.703703704, "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.017419989, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -8420,18 +9747,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.012618795, + "cost_without_join_buffer": 0.031514445, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012618795, "uses_join_buffering": true } } @@ -8439,18 +9774,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 3 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.017419989, + "cost_without_join_buffer": 0.034329735, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -8458,35 +9801,38 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 15.54101562, + "cost_for_plan": 0.082557964, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2", "get_costs_for_tables": [ { "best_access_path": { "table": "t_inner_4", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.034460781, + "cost_without_join_buffer": 0.283630005, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -8494,18 +9840,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.080024379, + "cost_without_join_buffer": 0.308967615, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -8513,67 +9867,126 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2", "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 33.74614258, + "cost_for_plan": 0.117018745, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 33.74614258, - "best_cost": 23.46152344 + "rest_of_plan": [ + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2,t_inner_4", + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 0.378576706, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "rows": 27, + "cost": 1.293090814 + }, + { + "strategy": "SJ-Materialization", + "rows": 27, + "cost": 0.14187842 + }, + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.378576706, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.525270026 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 + } + ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_outer_2", "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 66.15639648, + "cost_for_plan": 0.162582343, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 66.15639648, - "best_cost": 23.46152344 + "current_cost": 0.162582343, + "best_cost": 0.14187842 } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 11.93076172, + "cost_for_plan": 0.07775677, "semijoin_strategy_choice": [], "rest_of_plan": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4", "get_costs_for_tables": [ { "best_access_path": { "table": "t_outer_2", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.034303623, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -8581,18 +9994,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_access_path": { "table": "t_inner_3", + "plan_details": { + "record_count": 9 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.034303623, + "cost_without_join_buffer": 0.102989205, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 9, - "cost": 2.015380859, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -8600,130 +10021,366 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4", "table": "t_outer_2", "rows_for_plan": 81, - "cost_for_plan": 30.14614258, + "cost_for_plan": 0.112060393, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 30.14614258, - "best_cost": 23.46152344 + "rest_of_plan": [ + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "cost_without_join_buffer": 0.926902845, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4,t_outer_2", + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 0.373618354, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.373618354, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.520311674 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, + "pruned_by_cost": true, + "current_cost": 0.520311674, + "best_cost": 0.14187842 + } + ] }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2,t_inner_4", "table": "t_inner_3", "rows_for_plan": 81, - "cost_for_plan": 30.14614258, + "cost_for_plan": 0.112060393, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 30.14614258, - "best_cost": 23.46152344 + "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": "t_outer_1,t_inner_1,t_inner_2", "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 15.54101562, + "cost_for_plan": 0.082557964, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_inner_4", "rows_for_plan": 27, - "cost_for_plan": 13.81538086, + "cost_for_plan": 0.037231395, "semijoin_strategy_choice": [], - "pruned_by_heuristic": true + "rest_of_plan": [ + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_2", + "plan_details": { + "record_count": 27 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.065233941, + "cost_without_join_buffer": 0.308967615, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 27 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.065233941, + "cost_without_join_buffer": 0.308967615, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 27 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.065233941, + "cost_without_join_buffer": 0.308967615, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", + "table": "t_outer_2", + "rows_for_plan": 243, + "cost_for_plan": 0.102465336, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4,t_outer_2", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "cost_without_join_buffer": 2.780708535, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4,t_outer_2", + "table": "t_inner_2", + "rows_for_plan": 2187, + "cost_for_plan": 0.731138787, + "semijoin_strategy_choice": [], + "pruned_by_cost": true, + "current_cost": 0.731138787, + "best_cost": 0.14187842 + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4,t_outer_2", + "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 0.731138787, + "semijoin_strategy_choice": [], + "pruned_by_cost": true, + "current_cost": 0.731138787, + "best_cost": 0.14187842 + } + ] + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", + "table": "t_inner_2", + "rows_for_plan": 243, + "cost_for_plan": 0.102465336, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + }, + { + "plan_prefix": "t_outer_1,t_inner_1,t_inner_4", + "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 0.102465336, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1"], + "plan_prefix": "t_outer_1,t_inner_1", "table": "t_inner_3", "rows_for_plan": 81, - "cost_for_plan": 24.62563477, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 24.62563477, - "best_cost": 23.46152344 + "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 10.02050781, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": ["t_outer_1"], + "plan_prefix": "t_outer_1", "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 10.02050781, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_outer_2", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_1", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_2", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_4", "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t_inner_3", "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -8758,10 +10415,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_join_order": [ "t_outer_1", - "t_outer_2", "<subquery2>", + "t_outer_2", "<subquery3>" - ] + ], + "rows": 27, + "cost": 0.14187842 }, { "substitute_best_equal": { @@ -8770,47 +10429,51 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { - "attached_conditions_computation": [], + "attached_conditions_computation": [ + { + "condition_on_constant_tables": "1", + "computing_condition": [] + } + ], "attached_conditions_summary": [ { "table": "t_outer_1", - "attached": null - }, - { - "table": "t_outer_2", - "attached": null + "attached_condition": null }, { "table": "t_inner_1", - "attached": null + "attached_condition": null }, { "table": "t_inner_2", - "attached": null + "attached_condition": null }, { "table": "<subquery2>", - "attached": null + "attached_condition": null + }, + { + "table": "t_outer_2", + "attached_condition": null }, { "table": "t_inner_4", - "attached": null + "attached_condition": null }, { "table": "t_inner_3", - "attached": null + "attached_condition": null }, { "table": "<subquery3>", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -8865,7 +10528,7 @@ JS "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.345829876, + "cost": 0.001478954, "chosen": true } ], @@ -8894,7 +10557,7 @@ JS "using_mrr": false, "index_only": true, "rows": 107, - "cost": 21.63379668, + "cost": 0.016135574, "chosen": true } ], @@ -8926,7 +10589,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1203.877243, + "cost": 1.235690484, "chosen": true } ], @@ -8966,7 +10629,7 @@ JS "using_mrr": false, "index_only": false, "rows": 4, - "cost": 4.948710032, + "cost": 0.00627616, "chosen": true } ], @@ -9000,7 +10663,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.346171589, + "cost": 0.002574553, "chosen": true } ], @@ -9029,7 +10692,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.346171589, + "cost": 0.002574553, "chosen": true } ], @@ -9066,7 +10729,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.345927508, + "cost": 0.002574553, "chosen": true } ], @@ -9096,7 +10759,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.345878692, + "cost": 0.002574553, "chosen": true } ], @@ -9126,7 +10789,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.345927508, + "cost": 0.002574553, "chosen": true } ], @@ -9159,7 +10822,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.345878692, + "cost": 0.002574553, "chosen": true } ], @@ -9195,7 +10858,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.394255553, + "cost": 0.002574553, "chosen": true } ], @@ -9229,7 +10892,7 @@ JS "using_mrr": false, "index_only": false, "rows": 2, - "cost": 2.546855016, + "cost": 0.003808422, "chosen": true } ], @@ -9253,6 +10916,7 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": 1.235690484, "nested_loop": [ { "table": { @@ -9262,7 +10926,9 @@ EXPLAIN "key": "start_date", "key_length": "8", "used_key_parts": ["start_date", "end_date"], + "loops": 1, "rows": 1000, + "cost": 1.235690484, "filtered": 100, "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'" } @@ -9284,7 +10950,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1203.877243, + "cost": 1.235690484, "chosen": true } ], @@ -9334,28 +11000,35 @@ JS [ [ { - "plan_prefix": - [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "A", + "plan_details": + { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 5, - "cost": 3.017089844, + "rows": 10, + "rows_after_filter": 5, + "rows_out": 5, + "cost": 0.01159965, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 5, - "cost": 3.017089844, + "rows_read": 5, + "rows_out": 5, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -9364,20 +11037,28 @@ JS "best_access_path": { "table": "B", + "plan_details": + { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, + "rows": 1000, + "rows_after_filter": 800, + "rows_out": 800, + "cost": 0.1669214, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 800, - "cost": 44.19726562, + "rows_read": 800, + "rows_out": 800, + "cost": 0.1669214, "uses_join_buffering": false } } @@ -9385,36 +11066,42 @@ JS ] }, { - "plan_prefix": - [], + "plan_prefix": "", "table": "A", "rows_for_plan": 5, - "cost_for_plan": 4.017089844, + "cost_for_plan": 0.01159965, "rest_of_plan": [ { - "plan_prefix": - ["A"], + "plan_prefix": "A", "get_costs_for_tables": [ { "best_access_path": { "table": "B", + "plan_details": + { + "record_count": 5 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 800, - "cost": 220.9863281, + "rows": 1000, + "rows_after_filter": 800, + "rows_out": 800, + "cost": 0.8329686, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 800, - "cost": 220.9863281, + "rows_read": 800, + "rows_out": 800, + "cost": 0.8329686, "uses_join_buffering": false } } @@ -9422,20 +11109,18 @@ JS ] }, { - "plan_prefix": - ["A"], + "plan_prefix": "A", "table": "B", "rows_for_plan": 4000, - "cost_for_plan": 1025.003418 + "cost_for_plan": 0.84456825 } ] }, { - "plan_prefix": - [], + "plan_prefix": "", "table": "B", "rows_for_plan": 800, - "cost_for_plan": 204.1972656, + "cost_for_plan": 0.1669214, "pruned_by_heuristic": true } ] @@ -9451,28 +11136,35 @@ JS [ [ { - "plan_prefix": - [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "A", + "plan_details": + { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10, - "cost": 2.017089844, + "rows": 10, + "rows_after_filter": 10, + "rows_out": 10, + "cost": 0.01159965, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.017089844, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -9481,20 +11173,28 @@ JS "best_access_path": { "table": "B", + "plan_details": + { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, + "rows": 1000, + "rows_after_filter": 800, + "rows_out": 800, + "cost": 0.1669214, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 800, - "cost": 44.19726562, + "rows_read": 800, + "rows_out": 800, + "cost": 0.1669214, "uses_join_buffering": false } } @@ -9502,22 +11202,24 @@ JS ] }, { - "plan_prefix": - [], + "plan_prefix": "", "table": "A", "rows_for_plan": 10, - "cost_for_plan": 4.017089844, + "cost_for_plan": 0.01159965, "rest_of_plan": [ { - "plan_prefix": - ["A"], + "plan_prefix": "A", "get_costs_for_tables": [ { "best_access_path": { "table": "B", + "plan_details": + { + "record_count": 10 + }, "considered_access_paths": [ { @@ -9526,21 +11228,21 @@ JS "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 20.00585794, + "cost": 0.01901531, "chosen": true }, { - "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "ref", - "records": 1, - "cost": 20.00585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01901531, "uses_join_buffering": false } } @@ -9548,25 +11250,26 @@ JS ] }, { - "plan_prefix": - ["A"], + "plan_prefix": "A", "table": "B", "rows_for_plan": 10, - "cost_for_plan": 26.02294779, + "cost_for_plan": 0.03061496, + "pushdown_cond_selectivity": 0.8, + "filtered": 80, + "rows_out": 0.8, "selectivity": 0.8, "estimated_join_cardinality": 8 } ] }, { - "plan_prefix": - [], + "plan_prefix": "", "table": "B", "rows_for_plan": 800, - "cost_for_plan": 204.1972656, + "cost_for_plan": 0.1669214, "pruned_by_cost": true, - "current_cost": 204.1972656, - "best_cost": 26.02294779 + "current_cost": 0.1669214, + "best_cost": 0.03061496 } ] ] @@ -9595,7 +11298,7 @@ JS "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.357887479, + "cost": 0.002574553, "chosen": true } ], @@ -9655,7 +11358,7 @@ JS "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.345829876, + "cost": 0.001478954, "chosen": true } ] @@ -9678,28 +11381,35 @@ JS [ [ { - "plan_prefix": - [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": + { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "rows": 10, + "rows_after_filter": 10, + "rows_out": 10, + "cost": 0.01159965, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -9708,12 +11418,19 @@ JS "best_access_path": { "table": "t2", + "plan_details": + { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 100, - "cost": 2.219726562, + "rows": 100, + "rows_after_filter": 100, + "rows_out": 100, + "cost": 0.0256761, + "index_only": false, "chosen": true, "use_tmp_table": true } @@ -9721,8 +11438,9 @@ JS "chosen_access_method": { "type": "scan", - "records": 100, - "cost": 2.219726562, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256761, "uses_join_buffering": false } } @@ -9730,22 +11448,24 @@ JS ] }, { - "plan_prefix": - [], + "plan_prefix": "", "table": "t1", "rows_for_plan": 10, - "cost_for_plan": 4.021972656, + "cost_for_plan": 0.01159965, "rest_of_plan": [ { - "plan_prefix": - ["t1"], + "plan_prefix": "t1", "get_costs_for_tables": [ { "best_access_path": { "table": "t2", + "plan_details": + { + "record_count": 10 + }, "considered_access_paths": [ { @@ -9754,21 +11474,21 @@ JS "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 20.00585794, + "cost": 0.01840091, "chosen": true }, { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.219726562, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "ref", - "records": 1, - "cost": 20.00585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01840091, "uses_join_buffering": false } } @@ -9776,32 +11496,33 @@ JS ] }, { - "plan_prefix": - ["t1"], + "plan_prefix": "t1", "table": "t2", "rows_for_plan": 10, - "cost_for_plan": 26.0278306, - "cost_for_sorting": 10 + "cost_for_plan": 0.03000056, + "cost_for_sorting": 0.006368384 } ] }, { - "plan_prefix": - [], + "plan_prefix": "", "table": "t2", "rows_for_plan": 100, - "cost_for_plan": 22.21972656, + "cost_for_plan": 0.0256761, "rest_of_plan": [ { - "plan_prefix": - ["t2"], + "plan_prefix": "t2", "get_costs_for_tables": [ { "best_access_path": { "table": "t1", + "plan_details": + { + "record_count": 100 + }, "considered_access_paths": [ { @@ -9810,21 +11531,26 @@ JS "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 200.0585794, + "cost": 0.1821659, "chosen": true }, { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "access_type": "scan_with_join_cache", + "rows": 10, + "rows_after_filter": 10, + "rows_out": 1, + "cost": 0.11055225, + "cost_without_join_buffer": 1.159965, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "rows_read": 10, + "rows_out": 1, + "cost": 0.11055225, "uses_join_buffering": true } } @@ -9832,14 +11558,13 @@ JS ] }, { - "plan_prefix": - ["t2"], + "plan_prefix": "t2", "table": "t1", - "rows_for_plan": 1000, - "cost_for_plan": 224.2416992, + "rows_for_plan": 100, + "cost_for_plan": 0.13622835, "pruned_by_cost": true, - "current_cost": 224.2416992, - "best_cost": 36.0278306 + "current_cost": 0.13622835, + "best_cost": 0.036368944 } ] } @@ -9923,6 +11648,7 @@ drop table t1; # (on optimized builds) # CREATE TABLE t1( a INT, b INT, PRIMARY KEY( a ) ); +insert t1 values (2,3); SELECT sum(b), row_number() OVER (order by b) FROM t1 WHERE a = 101; sum(b) row_number() OVER (order by b) NULL 1 @@ -9938,8 +11664,8 @@ JS "rowid_ordered": true, "using_mrr": false, "index_only": false, - "rows": 0, - "cost": 0.145, + "rows": 1, + "cost": 0.002483968, "chosen": true } ] @@ -10087,6 +11813,16 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { }, { "join_preparation": { + "select_id": "fake", + "steps": [ + { + "expanded_query": "select c AS c from dual" + } + ] + } + }, + { + "join_preparation": { "select_id": 1, "steps": [ { @@ -10156,14 +11892,16 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { "table": "t", "table_scan": { "rows": 2, - "cost": 2.004394531 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } }, { "table": "<derived2>", "table_scan": { "rows": 2, - "cost": 2 + "read_cost": 0.012350033, + "read_and_compare_cost": 0.012418701 } } ] @@ -10171,23 +11909,30 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 2, - "cost": 2.004394531, + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.01034841, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 2, - "cost": 2.004394531, + "rows_read": 2, + "rows_out": 2, + "cost": 0.01034841, "uses_join_buffering": false } } @@ -10195,18 +11940,25 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { { "best_access_path": { "table": "<derived2>", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 2, - "cost": 2, + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.012418701, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 2, - "cost": 2, + "rows_read": 2, + "rows_out": 2, + "cost": 0.012418701, "uses_join_buffering": false } } @@ -10214,29 +11966,37 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "t", "rows_for_plan": 2, - "cost_for_plan": 2.404394531, + "cost_for_plan": 0.01034841, "rest_of_plan": [ { - "plan_prefix": ["t"], + "plan_prefix": "t", "get_costs_for_tables": [ { "best_access_path": { "table": "<derived2>", + "plan_details": { + "record_count": 2 + }, "considered_access_paths": [ { - "access_type": "scan", - "resulting_rows": 2, - "cost": 2, + "access_type": "scan_with_join_cache", + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.012911897, + "cost_without_join_buffer": 0.024837402, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 2, - "cost": 2, + "rows_read": 2, + "rows_out": 2, + "cost": 0.012911897, "uses_join_buffering": true } } @@ -10244,58 +12004,26 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { ] }, { - "plan_prefix": ["t"], + "plan_prefix": "t", "table": "<derived2>", "rows_for_plan": 4, - "cost_for_plan": 5.204394531 + "cost_for_plan": 0.023260307 } ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "<derived2>", "rows_for_plan": 2, - "cost_for_plan": 2.4, - "rest_of_plan": [ - { - "plan_prefix": ["<derived2>"], - "get_costs_for_tables": [ - { - "best_access_path": { - "table": "t", - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 2, - "cost": 2.004394531, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 2, - "cost": 2.004394531, - "uses_join_buffering": true - } - } - } - ] - }, - { - "plan_prefix": ["<derived2>"], - "table": "t", - "rows_for_plan": 4, - "cost_for_plan": 5.204394531, - "pruned_by_cost": true, - "current_cost": 5.204394531, - "best_cost": 5.204394531 - } - ] + "cost_for_plan": 0.012418701, + "pruned_by_heuristic": true } ] }, { - "best_join_order": ["t", "<derived2>"] + "best_join_order": ["t", "<derived2>"], + "rows": 4, + "cost": 0.023260307 }, { "substitute_best_equal": { @@ -10309,14 +12037,17 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { "attached_conditions_summary": [ { "table": "t", - "attached": null + "attached_condition": null }, { "table": "<derived2>", - "attached": "v.c < t.a" + "attached_condition": "v.c < t.a" } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -10338,16 +12069,6 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { } }, { - "join_preparation": { - "select_id": "fake", - "steps": [ - { - "expanded_query": "select c AS c from dual" - } - ] - } - }, - { "join_optimization": { "select_id": "fake", "steps": [ @@ -10367,7 +12088,8 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { "table": "union", "table_scan": { "rows": 2, - "cost": 10.1 + "read_cost": 0.010020701, + "read_and_compare_cost": 0.010084701 } } ] @@ -10375,23 +12097,30 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "union", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 2, - "cost": 10.1, + "rows": 2, + "rows_after_filter": 2, + "rows_out": 2, + "cost": 0.010084701, + "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 2, - "cost": 10.1, + "rows_read": 2, + "rows_out": 2, + "cost": 0.010084701, "uses_join_buffering": false } } @@ -10399,15 +12128,17 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "union", "rows_for_plan": 2, - "cost_for_plan": 10.5 + "cost_for_plan": 0.010084701 } ] }, { - "best_join_order": ["union"] + "best_join_order": ["union"], + "rows": 2, + "cost": 0.010084701 }, { "attaching_conditions_to_tables": { @@ -10415,10 +12146,13 @@ UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { "attached_conditions_summary": [ { "table": "union", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -10533,14 +12267,20 @@ JS "spl_pd_boundary": 2, "split_plan_search": [], - "lead_table": "t10", - "index": "grp_id", - "parts": 1, - "split_sel": 0.001, - "cost": 2535.968504, - "unsplit_cost": 253440.0075, - "records": 100, - "chosen": true + "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; @@ -10608,7 +12348,8 @@ select count(*) from seq_1_to_10000000 { "table": "seq_1_to_10000000", "table_scan": { "rows": 10000000, - "cost": 10000000 + "read_cost": 124.7880673, + "read_and_compare_cost": 444.7880673 } } ] @@ -10616,23 +12357,30 @@ select count(*) from seq_1_to_10000000 { { "considered_execution_plans": [ { - "plan_prefix": [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "seq_1_to_10000000", + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 10000000, - "cost": 10000000, + "rows": 10000000, + "rows_after_filter": 10000000, + "rows_out": 10000000, + "cost": 444.7880673, + "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10000000, - "cost": 10000000, + "rows_read": 10000000, + "rows_out": 10000000, + "cost": 444.7880673, "uses_join_buffering": false } } @@ -10640,15 +12388,17 @@ select count(*) from seq_1_to_10000000 { ] }, { - "plan_prefix": [], + "plan_prefix": "", "table": "seq_1_to_10000000", "rows_for_plan": 10000000, - "cost_for_plan": 12000000 + "cost_for_plan": 444.7880673 } ] }, { - "best_join_order": ["seq_1_to_10000000"] + "best_join_order": ["seq_1_to_10000000"], + "rows": 10000000, + "cost": 444.7880673 }, { "attaching_conditions_to_tables": { @@ -10656,10 +12406,13 @@ select count(*) from seq_1_to_10000000 { "attached_conditions_summary": [ { "table": "seq_1_to_10000000", - "attached": null + "attached_condition": null } ] } + }, + { + "make_join_readinfo": [] } ] } @@ -10687,7 +12440,7 @@ explain select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Impossible ON condition -1 SIMPLE t2 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t2 const PRIMARY NULL NULL NULL 0 Impossible ON condition 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) from information_schema.optimizer_trace; @@ -10712,7 +12465,7 @@ set in_predicate_conversion_threshold=3; explain select * from t0 where a in (1,2,3,4,5,6); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived3> ref key0 key0 4 test.t0.a 2 FirstMatch(t0) +1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t0.a 1 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) from information_schema.optimizer_trace; @@ -10826,8 +12579,6 @@ S { "access_type": "ref", "index": "PRIMARY", - "rows": 1.79769e308, - "cost": 1.79769e308, "chosen": false, "cause": "no predicate for first keypart" } @@ -10989,7 +12740,7 @@ on t1.a=t.a where t1.b < 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range idx_b idx_b 5 NULL 4 Using index condition; Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 1 select json_detailed(json_extract(trace, '$**.choose_best_splitting')) @@ -11019,14 +12770,17 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "considered_execution_plans": [ { - "plan_prefix": - [], + "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "t2", + "plan_details": + { + "record_count": 1 + }, "considered_access_paths": [ { @@ -11035,7 +12789,7 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "used_range_estimates": false, "reason": "not available", "rows": 1.8367, - "cost": 2.000585794, + "cost": 0.002051185, "chosen": true }, { @@ -11047,8 +12801,9 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "chosen_access_method": { "type": "ref", - "records": 1.8367, - "cost": 2.000585794, + "rows_read": 1.8367, + "rows_out": 1.8367, + "cost": 0.002051185, "uses_join_buffering": false } } @@ -11056,24 +12811,29 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) ] }, { - "plan_prefix": - [], + "plan_prefix": "", "table": "t2", "rows_for_plan": 1.8367, - "cost_for_plan": 2.367925794, - "cost_for_sorting": 1.8367 + "cost_for_plan": 0.002051185, + "cost_for_sorting": 0.001155201 } ] } ], - "lead_table": "t2", - "index": "idx_a", - "parts": 1, - "split_sel": 0.020407778, - "cost": 2.488945919, - "unsplit_cost": 25.72361682, - "records": 1, - "chosen": true + "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 + } } ] drop table t1,t2; @@ -11122,3 +12882,926 @@ left(trace, 100) set optimizer_trace='enabled=off'; # End of 10.6 tests +# +# Testing of records_out +# +set @save_optimizer_switch= @@optimizer_switch; +set @save_use_stat_tables= @@use_stat_tables; +set @save_histogram_size= @@histogram_size; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set optimizer_switch='rowid_filter=on'; +set use_stat_tables='preferably'; +set histogram_size=127; +create table t1 (a int, b int, c int, key(a),key(b)); +insert into t1 select seq, seq*2, seq/10 from seq_1_to_1000; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +explain select * from t1 where a<10 and b between 10 and 50 and c < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range|filter a,b a|b 5|5 NULL 9 (2%) Using index condition; Using where; Using rowid filter +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c from t1 where t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 0.1671618 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + }, + { + "index": "b", + "usable": true, + "key_parts": ["b"] + } + ], + "setup_range_conditions": [], + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["(NULL) < (a) < (10)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 9, + "cost": 0.012445505, + "chosen": true + }, + { + "index": "b", + "ranges": ["(10) <= (b) <= (50)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 21, + "cost": 0.027251933, + "chosen": false, + "cause": "cost" + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 9, + "ranges": ["(NULL) < (a) < (10)"] + }, + "rows_for_plan": 9, + "cost_for_plan": 0.012445505, + "chosen": true + } + } + }, + { + "table": "t1", + "rowid_filters": [ + { + "key": "a", + "build_cost": 0.001846537, + "rows": 9 + }, + { + "key": "b", + "build_cost": 0.003322634, + "rows": 21 + } + ] + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a", + "selectivity_from_index": 0.009 + }, + { + "index_name": "b", + "selectivity_from_index": 0.021 + } + ], + "selectivity_for_columns": [ + { + "column_name": "c", + "ranges": ["NULL < c < 10"], + "selectivity_from_histogram": 0.094 + } + ], + "cond_selectivity": 0.000017766 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": "", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "filter": { + "rowid_filter_index": "b", + "index_only_cost": 0.001605807, + "filter_startup_cost": 0.003322634, + "find_key_and_filter_lookup_cost": 6.695354e-4, + "filter_selectivity": 0.021, + "original_rows": 9, + "new_rows": 0.189, + "original_access_cost": 0.011607363, + "with_filter_access_cost": 0.002485375, + "original_found_rows_cost": 0.010001556, + "with_filter_found_rows_cost": 2.100327e-4, + "org_cost": 0.011895363, + "filter_cost": 0.005814057, + "filter_used": true + }, + "access_type": "range", + "range_index": "a", + "rows": 9, + "rows_after_filter": 0.189, + "rows_out": 0.017766, + "cost": 0.006364199, + "chosen": true + } + ], + "chosen_access_method": { + "type": "range", + "rows_read": 0.189, + "rows_out": 0.017766, + "cost": 0.006364199, + "uses_join_buffering": false, + "rowid_filter_index": "b" + } + } + } + ] + }, + { + "plan_prefix": "", + "table": "t1", + "rows_for_plan": 0.017766, + "cost_for_plan": 0.006364199, + "pushdown_cond_selectivity": 0.094, + "filtered": 0.1974, + "rows_out": 0.017766 + } + ] + }, + { + "best_join_order": ["t1"], + "rows": 0.017766, + "cost": 0.006364199 + }, + { + "table": "t1", + "range_analysis": { + "potential_range_indexes": [ + { + "index": "a", + "usable": false, + "cause": "not applicable" + }, + { + "index": "b", + "usable": true, + "key_parts": ["b"] + } + ], + "setup_range_conditions": [], + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "b", + "ranges": ["(10) <= (b) <= (50)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": true, + "rows": 21, + "cost": 0.004244354, + "chosen": true + } + ] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "b", + "rows": 21, + "ranges": ["(10) <= (b) <= (50)"] + }, + "rows_for_plan": 21, + "cost_for_plan": 0.004244354, + "chosen": true + } + } + }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + ] + } + }, + { + "make_join_readinfo": [ + { + "table": "t1", + "index_condition": "t1.a < 10", + "row_condition": "t1.b between 10 and 50 and t1.c < 10" + } + ] + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table t1; +create table three (a int); +insert into three values (1),(2),(3); +create table t1 (a int, b int, c int, key(a),key(b)); +insert into t1 select mod(seq,10), seq, seq from seq_1_to_10000; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +set optimizer_use_condition_selectivity=2; +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "cost": 2.662022424, + "nested_loop": [ + { + "table": { + "table_name": "three", + "access_type": "ALL", + "loops": 1, + "rows": 3, + "cost": 0.010504815, + "filtered": 100, + "attached_condition": "three.a is not null" + } + }, + { + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a", "b"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.three.a"], + "rowid_filter": { + "range": { + "key": "b", + "used_key_parts": ["b"] + }, + "rows": 4312, + "selectivity_pct": 43.12 + }, + "loops": 3, + "rows": 1000, + "cost": 2.651517609, + "filtered": 43.11999893, + "attached_condition": "t1.b < 5000 and t1.c < 1000" + } + } + ] + } +} +set optimizer_use_condition_selectivity=4; +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "cost": 1.712236739, + "nested_loop": [ + { + "table": { + "table_name": "three", + "access_type": "ALL", + "loops": 1, + "rows": 3, + "cost": 0.010504815, + "filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "possible_keys": ["a", "b"], + "loops": 3, + "rows": 10000, + "cost": 1.701731924, + "filtered": 3.230766058, + "attached_condition": "t1.b < 5000 and t1.c < 1000" + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "attached_condition": "t1.a = three.a" + } + } + ] + } +} +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select three.a AS a,t1.a AS a,t1.b AS b,t1.c AS c from three join t1 where t1.a = three.a and t1.b < 5000 and t1.c < 1000" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = three.a and t1.b < 5000 and t1.c < 1000", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "three", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "index": "a", + "field": "a", + "equals": "three.a", + "null_rejecting": true + } + ] + }, + { + "rows_estimation": [ + { + "table": "three", + "table_scan": { + "rows": 3, + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 + } + }, + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 10000, + "cost": 1.581538 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": false, + "cause": "not applicable" + }, + { + "index": "b", + "usable": true, + "key_parts": ["b"] + } + ], + "setup_range_conditions": [], + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "b", + "ranges": ["(NULL) < (b) < (5000)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 4312, + "cost": 5.325149412, + "chosen": false, + "cause": "cost" + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "group_index_range": { + "chosen": false, + "cause": "not single_table" + } + } + }, + { + "table": "t1", + "rowid_filters": [ + { + "key": "b", + "build_cost": 0.611957109, + "rows": 4312 + } + ] + }, + { + "selectivity_for_indexes": [ + { + "index_name": "b", + "selectivity_from_index": 0.4312 + } + ], + "selectivity_for_columns": [ + { + "column_name": "c", + "ranges": ["NULL < c < 1000"], + "selectivity_from_histogram": 0.0999 + } + ], + "cond_selectivity": 0.04307688 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": "", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "three", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.010504815, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, + "uses_join_buffering": false + } + } + }, + { + "best_access_path": { + "table": "t1", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 10000, + "rows_after_filter": 430.7688, + "rows_out": 430.7688, + "cost": 1.581538, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 430.7688, + "rows_out": 430.7688, + "cost": 1.581538, + "uses_join_buffering": false + } + } + } + ] + }, + { + "plan_prefix": "", + "table": "three", + "rows_for_plan": 3, + "cost_for_plan": 0.010504815, + "rest_of_plan": [ + { + "plan_prefix": "three", + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "plan_details": { + "record_count": 3 + }, + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "not available", + "filter": { + "rowid_filter_index": "b", + "index_only_cost": 0.092096742, + "filter_startup_cost": 0.611957109, + "find_key_and_filter_lookup_cost": 0.094772697, + "filter_selectivity": 0.4312, + "original_rows": 1000, + "new_rows": 431.2, + "original_access_cost": 1.203380742, + "with_filter_access_cost": 0.6660551, + "original_found_rows_cost": 1.111284, + "with_filter_found_rows_cost": 0.479185661, + "org_cost": 3.706142226, + "filter_cost": 2.651517609, + "filter_used": true + }, + "rows": 431.2, + "cost": 2.651517609, + "chosen": true + }, + { + "access_type": "scan_with_join_cache", + "rows": 10000, + "rows_after_filter": 430.7688, + "rows_out": 323.0766, + "cost": 1.701731924, + "cost_without_join_buffer": 4.7319164, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 430.7688, + "rows_out": 323.0766, + "cost": 1.701731924, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": "three", + "table": "t1", + "rows_for_plan": 969.2298, + "cost_for_plan": 1.712236739, + "pushdown_cond_selectivity": 0.75, + "filtered": 3.230766, + "rows_out": 323.0766 + } + ] + }, + { + "plan_prefix": "", + "table": "t1", + "rows_for_plan": 430.7688, + "cost_for_plan": 1.581538, + "pruned_by_heuristic": true + } + ] + }, + { + "best_join_order": ["three", "t1"], + "rows": 969.2298, + "cost": 1.712236739 + }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = three.a and t1.b < 5000 and t1.c < 1000" + } + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 10000, + "cost": 1.581538 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + }, + { + "index": "b", + "usable": true, + "key_parts": ["b"] + } + ], + "setup_range_conditions": [], + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a" + }, + { + "index": "b", + "ranges": ["(NULL) < (b) < (5000)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 4312, + "cost": 5.325149412, + "chosen": false, + "cause": "cost" + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "group_index_range": { + "chosen": false, + "cause": "not single_table" + } + } + } + ], + "attached_conditions_summary": [ + { + "table": "three", + "attached_condition": null + }, + { + "table": "t1", + "attached_condition": "t1.a = three.a and t1.b < 5000 and t1.c < 1000" + } + ] + } + }, + { + "make_join_readinfo": [] + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table three, t1; +# +# MDEV-21095: Index condition push down is not reflected in optimizer trace +# +create table t10 (a int, b int, c int, key(a,b)); +insert into t10 select seq, seq, seq from seq_1_to_10000; +explain format=json select * from t10 where a<3 and b!=5 and c<10; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "cost": 0.003808422, + "nested_loop": [ + { + "table": { + "table_name": "t10", + "access_type": "range", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "loops": 1, + "rows": 2, + "cost": 0.003808422, + "filtered": 100, + "index_condition": "t10.a < 3 and t10.b <> 5", + "attached_condition": "t10.c < 10" + } + } + ] + } +} +set optimizer_trace='enabled=on'; +select * from t10 where a<3 and b!=5 and c<10; +a b c +1 1 1 +2 2 2 +select json_detailed(json_extract(trace, '$**.attaching_conditions_to_tables')) as out1 +from information_schema.optimizer_trace; +out1 +[ + { + "attached_conditions_computation": + [], + "attached_conditions_summary": + [ + { + "table": "t10", + "attached_condition": "t10.a < 3 and t10.b <> 5 and t10.c < 10" + } + ] + } +] +drop table t10; +# +# MDEV-21092: EXISTS to IN is not reflected in the optimizer trace +# +set optimizer_trace='enabled=on'; +create table t1 (cn_c int, cn_n char(10), cn_a int ); +create table t2 (ci_p int, ci_c int ); +create table t3 (ci_p int, ci_c int ); +SELECT cn_n FROM t1 WHERE (EXISTS (select 1 from t2 where ci_p > 100000 and cn_c = ci_c) +OR (cn_n LIKE 'L%') ) +AND cn_a > 1000000; +cn_n +select +json_detailed( +json_extract(trace, '$.steps[*].join_optimization[0].steps[0].transformation') +) as out1 +from information_schema.optimizer_trace; +out1 +[ + { + "select_id": 2, + "from": "EXISTS (SELECT)", + "to": "IN (SELECT)", + "upper_not": false + } +] +drop table t1, t2, t3; +# +# MDEV-29997 Partition Pruning not included in optimizer tracing +# +create table t2 (a int, b int) partition by hash(a) partitions 10; +create table t3 (a int, b int) partition by hash(a) partitions 10; +INSERT INTO t2 SELECT seq, seq from seq_1_to_10; +INSERT INTO t3 SELECT seq, seq from seq_1_to_10; +set optimizer_trace='enabled=on'; +explain partitions select * from t2,t3 where t2.a in (2,3,4) and t3.a in (4,5); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t3 p4,p5 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 p2,p3,p4 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +select json_detailed(json_extract(trace, '$**.prune_partitions')) as out1 +from information_schema.optimizer_trace; +out1 +[ + { + "table": "t2", + "used_partitions": "p2,p3,p4" + }, + { + "table": "t3", + "used_partitions": "p4,p5" + } +] +drop table t2,t3; +create table t1 ( +a int +) partition by range (a) +( partition p0 values less than(10), +partition p1 values less than (20), +partition p2 values less than (25) +); +insert into t1 values (5),(15),(22); +explain select * from t1 where a = 28; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +select json_detailed(json_extract(trace, '$**.prune_partitions')) as out1 +from information_schema.optimizer_trace; +out1 +[ + { + "table": "t1", + "used_partitions": "" + } +] +drop table t1; +set @@optimizer_switch= @save_optimizer_switch; +set @@use_stat_tables= @save_use_stat_tables; +set @@histogram_size= @save_histogram_size; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; |