diff options
| author | Monty <monty@mariadb.org> | 2022-06-02 19:47:23 +0300 |
|---|---|---|
| committer | Oleg Smirnov <olernov@gmail.com> | 2022-07-26 22:27:29 +0700 |
| commit | 515b9ad05a6de9dac3871ef2769dde1b5834c6e3 (patch) | |
| tree | 5f5b257759585ee21769497df862ab57cff55c4c /mysql-test/main/opt_trace.result | |
| parent | 6e7376eb59006f557383f3cc6499aba8f0e4cfd8 (diff) | |
| download | mariadb-git-515b9ad05a6de9dac3871ef2769dde1b5834c6e3.tar.gz | |
Added EQ_REF chaining to the greedy_optimizer
MDEV-28073 Slow query performance in MariaDB when using many table
The idea is to prefer and chain EQ_REF tables (tables that uses an
unique key to find a row) when searching for the best table combination.
This significantly reduces row combinations that has to be examined.
This is optimization is enabled when setting optimizer_prune_level=2
(which is now default).
Implementation:
- optimizer_prune_level has a new level, 2, which enables EQ_REF
optimization in addition to the pruning done by level 1.
Level 2 is now default.
- Added JOIN::eq_ref_tables that contains bits of tables that could use
potentially use EQ_REF access in the query. This is calculated
in sort_and_filter_keyuse()
Under optimizer_prune_level=2:
- When the greedy_optimizer notices that the preceding table was an
EQ_REF table, it tries to add an EQ_REF table next. If an EQ_REF
table exists, only this one will be considered at this level.
We also collect all EQ_REF tables chained by the next levels and these
are ignored on the starting level as we have already examined these.
If no EQ_REF table exists, we continue as normal.
This optimization speeds up the greedy_optimizer combination test with
~25%
Other things:
- I ported the changes in MySQL 5.7 to greedy_optimizer.test to MariaDB
to be able to ensure we can handle all cases that MySQL can do.
- I have run all tests with --mysqld=--optimizer_prune_level=1 to verify that
there where no test changes.
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 4586 |
1 files changed, 2528 insertions, 2058 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 9aa85c6bd08..e71b1c1dbc1 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -127,23 +127,27 @@ select * from v1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 1, - "cost": 2.204394531, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 1, + "cost": 2.204394531, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 1, + "cost": 2.204394531, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 1, - "cost": 2.204394531, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -281,23 +285,27 @@ select * from (select * from t1 where t1.a=1)q { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 1, - "cost": 2.204394531, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 1, + "cost": 2.204394531, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 1, + "cost": 2.204394531, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 1, - "cost": 2.204394531, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -440,24 +448,28 @@ select * from v2 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 1, - "cost": 2.204394531, - "chosen": true, - "use_tmp_table": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 1, + "cost": 2.204394531, + "chosen": true, + "use_tmp_table": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 1, + "cost": 2.204394531, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 1, - "cost": 2.204394531, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -516,23 +528,27 @@ select * from v2 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "<derived2>", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 2, - "cost": 2, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "<derived2>", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 2, - "cost": 2, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -656,23 +672,27 @@ explain select * from v2 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.021972656, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.021972656, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 10, - "cost": 2.021972656, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -775,24 +795,28 @@ explain select * from v1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, - "chosen": true, - "use_tmp_table": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.021972656, + "chosen": true, + "use_tmp_table": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.021972656, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 10, - "cost": 2.021972656, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -845,23 +869,27 @@ explain select * from v1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "<derived2>", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 10, - "cost": 10, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "<derived2>", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 10, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 10, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 10, - "cost": 10, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -1020,82 +1048,91 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 100, - "cost": 2.317382812, - "uses_join_buffering": false - } - }, - "table": "t2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 100, - "cost": 2.317382812, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t1", - "rows_for_plan": 100, - "cost_for_plan": 22.31738281, - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": ["t1"], - "table": "t2", "best_access_path": { + "table": "t1", "considered_access_paths": [ { - "access_type": "ref", - "index": "a", - "used_range_estimates": false, - "reason": "not available", - "rows": 1, - "cost": 200.0585794, + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.317382812, "chosen": true - }, + } + ], + "chosen_access_method": { + "type": "scan", + "records": 100, + "cost": 2.317382812, + "uses_join_buffering": false + } + } + }, + { + "best_access_path": { + "table": "t2", + "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 100, "cost": 2.317382812, - "chosen": false + "chosen": true } ], "chosen_access_method": { - "type": "ref", - "records": 1, - "cost": 200.0585794, + "type": "scan", + "records": 100, + "cost": 2.317382812, "uses_join_buffering": false } } + } + ] + }, + { + "plan_prefix": [], + "table": "t1", + "rows_for_plan": 100, + "cost_for_plan": 22.31738281, + "rest_of_plan": [ + { + "plan_prefix": ["t1"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t2", + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1, + "cost": 200.0585794, + "chosen": true + }, + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.317382812, + "chosen": false + } + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 200.0585794, + "uses_join_buffering": false + } + } + } + ] }, { "plan_prefix": ["t1"], "table": "t2", "rows_for_plan": 100, - "cost_for_plan": 242.3759623, - "pruned_by_hanging_leaf": true + "cost_for_plan": 242.3759623 } ] }, @@ -1107,32 +1144,36 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "rest_of_plan": [ { "plan_prefix": ["t2"], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "ref", - "index": "a", - "used_range_estimates": false, - "reason": "not available", - "rows": 1, - "cost": 200.0585794, - "chosen": true - }, - { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, - "chosen": false + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1, + "cost": 200.0585794, + "chosen": true + }, + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.317382812, + "chosen": false + } + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 200.0585794, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "ref", - "records": 1, - "cost": 200.0585794, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": ["t2"], @@ -1296,23 +1337,27 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "index_merge", - "resulting_rows": 5, - "cost": 6.25, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "index_merge", + "resulting_rows": 5, + "cost": 6.25, + "chosen": true + } + ], + "chosen_access_method": { + "type": "index_merge", + "records": 5, + "cost": 6.25, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "index_merge", - "records": 5, - "cost": 6.25, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -1489,24 +1534,28 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "index_merge", - "resulting_rows": 8, - "cost": 2.2, - "chosen": true, - "use_tmp_table": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "index_merge", + "resulting_rows": 8, + "cost": 2.2, + "chosen": true, + "use_tmp_table": true + } + ], + "chosen_access_method": { + "type": "index_merge", + "records": 8, + "cost": 2.2, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "index_merge", - "records": 8, - "cost": 2.2, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -1691,24 +1740,28 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "index_merge", - "resulting_rows": 9, - "cost": 2.35, - "chosen": true, - "use_tmp_table": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "index_merge", + "resulting_rows": 9, + "cost": 2.35, + "chosen": true, + "use_tmp_table": true + } + ], + "chosen_access_method": { + "type": "index_merge", + "records": 9, + "cost": 2.35, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "index_merge", - "records": 9, - "cost": 2.35, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -1882,24 +1935,28 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "index_merge", - "resulting_rows": 9, - "cost": 2.35, - "chosen": true, - "use_tmp_table": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "index_merge", + "resulting_rows": 9, + "cost": 2.35, + "chosen": true, + "use_tmp_table": true + } + ], + "chosen_access_method": { + "type": "index_merge", + "records": 9, + "cost": 2.35, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "index_merge", - "records": 9, - "cost": 2.35, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -2158,38 +2215,42 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "ref", - "index": "a_c", - "used_range_estimates": true, - "rows": 180, - "cost": 180.2743776, - "chosen": true - }, - { - "access_type": "ref", - "index": "a_b", - "used_range_estimates": true, - "rows": 21, - "cost": 21.14242739, - "chosen": true - }, - { - "type": "scan", - "chosen": false, - "cause": "cost" + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a_c", + "used_range_estimates": true, + "rows": 180, + "cost": 180.2743776, + "chosen": true + }, + { + "access_type": "ref", + "index": "a_b", + "used_range_estimates": true, + "rows": 21, + "cost": 21.14242739, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": { + "type": "ref", + "records": 21, + "cost": 21.14242739, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "ref", - "records": 21, - "cost": 21.14242739, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -2431,23 +2492,27 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "considered_execution_plans": [ { "plan_prefix": ["t2"], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.006835938, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.006835938, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 4, - "cost": 2.006835938, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": ["t2"], @@ -2574,23 +2639,27 @@ explain select * from t1 left join t2 on t2.a=t1.a { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.006835938, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.006835938, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 4, - "cost": 2.006835938, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -2600,37 +2669,40 @@ explain select * from t1 left join t2 on t2.a=t1.a { "rest_of_plan": [ { "plan_prefix": ["t1"], - "table": "t2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "eq_ref", - "index": "PRIMARY", - "rows": 1, - "cost": 4, - "chosen": true - }, - { - "access_type": "scan", - "resulting_rows": 2, - "cost": 8.017578125, - "chosen": false + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t2", + "considered_access_paths": [ + { + "access_type": "eq_ref", + "index": "PRIMARY", + "rows": 1, + "cost": 4, + "chosen": true + }, + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 8.017578125, + "chosen": false + } + ], + "chosen_access_method": { + "type": "eq_ref", + "records": 1, + "cost": 4, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "eq_ref", - "records": 1, - "cost": 4, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": ["t1"], "table": "t2", "rows_for_plan": 4, - "cost_for_plan": 7.606835937, - "pruned_by_hanging_leaf": true + "cost_for_plan": 7.606835937 } ] } @@ -2795,23 +2867,27 @@ 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"], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.006835938, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.006835938, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 4, - "cost": 2.006835938, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": ["t3", "t2"], @@ -3006,23 +3082,27 @@ explain extended select * from t1 where a in (select pk from t10) { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t10", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t10", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.021972656, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.021972656, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 10, - "cost": 2.021972656, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -3039,52 +3119,29 @@ explain extended select * from t1 where a in (select pk from t10) { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.006591797, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.006591797, - "uses_join_buffering": false - } - }, - "table": "t10", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.006591797, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.006591797, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 10, - "cost": 2.021972656, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t1", - "rows_for_plan": 3, - "cost_for_plan": 2.606591797, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { - "plan_prefix": ["t1"], - "table": "t10", "best_access_path": { + "table": "t10", "considered_access_paths": [ { "access_type": "scan", @@ -3097,9 +3154,42 @@ explain extended select * from t1 where a in (select pk from t10) { "type": "scan", "records": 10, "cost": 2.021972656, - "uses_join_buffering": true + "uses_join_buffering": false } } + } + ] + }, + { + "plan_prefix": [], + "table": "t1", + "rows_for_plan": 3, + "cost_for_plan": 2.606591797, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": ["t1"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t10", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.021972656, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.021972656, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": ["t1"], @@ -3477,54 +3567,57 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "ref", - "index": "pk", - "used_range_estimates": true, - "rows": 1, - "cost": 1.125585794, - "chosen": true - }, - { - "access_type": "ref", - "index": "pk_a", - "used_range_estimates": true, - "rows": 1, - "cost": 1.125829876, - "chosen": false, - "cause": "cost" - }, - { - "access_type": "ref", - "index": "pk_a_b", - "used_range_estimates": true, - "rows": 1, - "cost": 0.126073957, - "chosen": true - }, - { - "type": "scan", - "chosen": false, - "cause": "cost" + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "ref", + "index": "pk", + "used_range_estimates": true, + "rows": 1, + "cost": 1.125585794, + "chosen": true + }, + { + "access_type": "ref", + "index": "pk_a", + "used_range_estimates": true, + "rows": 1, + "cost": 1.125829876, + "chosen": false, + "cause": "cost" + }, + { + "access_type": "ref", + "index": "pk_a_b", + "used_range_estimates": true, + "rows": 1, + "cost": 0.126073957, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 0.126073957, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "ref", - "records": 1, - "cost": 0.126073957, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 0.326073957, - "pruned_by_hanging_leaf": true + "cost_for_plan": 0.326073957 } ] }, @@ -3634,23 +3727,27 @@ select f1(a) from t1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.006835938, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.006835938, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 4, - "cost": 2.006835938, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -3734,23 +3831,27 @@ select f2(a) from t1 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 4, - "cost": 2.006835938, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 4, + "cost": 2.006835938, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.006835938, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 4, - "cost": 2.006835938, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -3802,7 +3903,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2190 +2360 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3816,7 +3917,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2090 0 + 2260 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3824,7 +3925,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2190 0 +select * from t1 2360 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -4120,40 +4221,46 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t0", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "range", - "resulting_rows": 3, - "cost": 0.746757383, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t0", + "considered_access_paths": [ + { + "access_type": "range", + "resulting_rows": 3, + "cost": 0.746757383, + "chosen": true + } + ], + "chosen_access_method": { + "type": "range", + "records": 3, + "cost": 0.746757383, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "range", - "records": 3, - "cost": 0.746757383, - "uses_join_buffering": false - } - }, - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "range", - "resulting_rows": 3, - "cost": 0.746757383, - "chosen": true + }, + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "range", + "resulting_rows": 3, + "cost": 0.746757383, + "chosen": true + } + ], + "chosen_access_method": { + "type": "range", + "records": 3, + "cost": 0.746757383, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "range", - "records": 3, - "cost": 0.746757383, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -4163,38 +4270,41 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "rest_of_plan": [ { "plan_prefix": ["t0"], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "ref", - "index": "a", - "used_range_estimates": false, - "reason": "not better than ref estimates", - "rows": 1, - "cost": 3.001757383, - "chosen": true - }, - { - "type": "scan", - "chosen": false, - "cause": "cost" + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "not better than ref estimates", + "rows": 1, + "cost": 3.001757383, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 3.001757383, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "ref", - "records": 1, - "cost": 3.001757383, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": ["t0"], "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 4.948514767, - "pruned_by_hanging_leaf": true + "cost_for_plan": 4.948514767 } ] }, @@ -4206,32 +4316,36 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "rest_of_plan": [ { "plan_prefix": ["t1"], - "table": "t0", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "ref", - "index": "a", - "rec_per_key_stats_missing": true, - "used_range_estimates": false, - "reason": "not better than ref estimates", - "rows": 2, - "cost": 3.003514767, - "chosen": true - }, - { - "type": "scan", - "chosen": false, - "cause": "cost" + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t0", + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "rec_per_key_stats_missing": true, + "used_range_estimates": false, + "reason": "not better than ref estimates", + "rows": 2, + "cost": 3.003514767, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": { + "type": "ref", + "records": 2, + "cost": 3.003514767, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "ref", - "records": 2, - "cost": 3.003514767, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": ["t1"], @@ -4363,23 +4477,27 @@ explain select * from (select rand() from t1)q { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -4431,23 +4549,27 @@ explain select * from (select rand() from t1)q { "considered_execution_plans": [ { "plan_prefix": [], - "table": "<derived2>", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 3, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "<derived2>", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 3, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 3, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 3, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -4641,51 +4763,29 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "considered_execution_plans": [ { "plan_prefix": [], - "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t_inner_1", - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, - "rest_of_plan": [ + }, { - "plan_prefix": ["t_inner_1"], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -4698,9 +4798,41 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "type": "scan", "records": 3, "cost": 2.005126953, - "uses_join_buffering": true + "uses_join_buffering": false } } + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, + "rest_of_plan": [ + { + "plan_prefix": ["t_inner_1"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": ["t_inner_1"], @@ -4726,69 +4858,10 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "considered_execution_plans": [ { "plan_prefix": [], - "table": "t1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t1", - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": ["t1"], - "table": "t_inner_1", "best_access_path": { + "table": "t1", "considered_access_paths": [ { "access_type": "scan", @@ -4801,11 +4874,13 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "type": "scan", "records": 3, "cost": 2.005126953, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_inner_2", + } + }, + { "best_access_path": { + "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", @@ -4818,21 +4893,44 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "type": "scan", "records": 3, "cost": 2.005126953, - "uses_join_buffering": true + "uses_join_buffering": false } } }, { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } + } + } + ] + }, + { + "plan_prefix": [], + "table": "t1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { "plan_prefix": ["t1"], - "table": "t_inner_1", - "rows_for_plan": 9, - "cost_for_plan": 6.410253906, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": ["t1", "t_inner_1"], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", @@ -4850,6 +4948,58 @@ 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", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": ["t1"], + "table": "t_inner_1", + "rows_for_plan": 9, + "cost_for_plan": 6.410253906, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": ["t1", "t_inner_1"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + } + ] + }, + { "plan_prefix": ["t1", "t_inner_1"], "table": "t_inner_2", "rows_for_plan": 27, @@ -5195,120 +5345,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t_outer_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t_outer_1", - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { - "plan_prefix": ["t_outer_1"], - "table": "t_inner_1", "best_access_path": { + "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", @@ -5321,11 +5380,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 3, "cost": 2.005126953, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_inner_2", + } + }, + { "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -5338,11 +5399,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_outer_2", + } + }, + { "best_access_path": { + "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", @@ -5355,11 +5418,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -5372,11 +5437,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 3, "cost": 2.005126953, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -5389,21 +5456,44 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } } - }, + } + ] + }, + { + "plan_prefix": [], + "table": "t_outer_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], - "table": "t_outer_2", - "rows_for_plan": 27, - "cost_for_plan": 10.02050781, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -5418,26 +5508,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_1", + } + }, + { "best_access_path": { + "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "resulting_rows": 9, + "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "records": 9, + "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -5452,9 +5546,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -5470,18 +5566,22 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } - }, + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 10.02050781, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2"], - "table": "t_inner_1", - "rows_for_plan": 81, - "cost_for_plan": 28.22563477, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -5496,9 +5596,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", @@ -5513,9 +5615,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { + "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -5531,37 +5654,41 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } - }, + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_1", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], - "table": "t_inner_2", - "rows_for_plan": 729, - "cost_for_plan": 176.0410156, - "semijoin_strategy_choice": [ + "get_costs_for_tables": [ { - "strategy": "FirstMatch", - "records": 27, - "read_time": 389.4047852 - }, - { - "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 289.4410156 + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } }, { - "chosen_strategy": "DuplicateWeedout" - } - ], - "rest_of_plan": [ - { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], - "table": "t_inner_4", "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -5576,9 +5703,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -5594,29 +5723,60 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 389.4047852 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 289.4410156 }, { + "chosen_strategy": "DuplicateWeedout" + } + ], + "rest_of_plan": [ + { "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, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], - "table": "t_inner_3", "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -5632,6 +5792,50 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "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, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "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", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": [ @@ -5691,40 +5895,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_1", "t_inner_4" ], - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + }, + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -5746,23 +5956,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_4", "t_inner_2" ], - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -5823,40 +6037,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_1", "t_inner_3" ], - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + }, + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -5923,86 +6143,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - } - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_outer_2", - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], - "table": "t_inner_2", "best_access_path": { + "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", @@ -6017,9 +6180,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -6034,9 +6199,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -6052,32 +6219,41 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } - }, + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_outer_2", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], - "table": "t_inner_2", - "rows_for_plan": 729, - "cost_for_plan": 172.4410156, - "semijoin_strategy_choice": [ + "get_costs_for_tables": [ { - "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 285.8410156 + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } }, { - "chosen_strategy": "DuplicateWeedout" - } - ], - "rest_of_plan": [ - { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_2" - ], - "table": "t_inner_4", "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -6092,9 +6268,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -6110,29 +6288,55 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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": 172.4410156, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 285.8410156 }, { + "chosen_strategy": "DuplicateWeedout" + } + ], + "rest_of_plan": [ + { "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, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_2", - "t_inner_4" - ], - "table": "t_inner_3", "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -6148,6 +6352,50 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "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, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "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", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": [ @@ -6207,40 +6455,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_2", "t_inner_4" ], - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + }, + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -6262,23 +6516,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_4", "t_inner_2" ], - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -6334,40 +6592,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_2", "t_inner_3" ], - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + }, + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -6422,74 +6686,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - } - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "table": "t_outer_2", - "rows_for_plan": 27, - "cost_for_plan": 44.64101563, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], - "table": "t_inner_4", "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -6504,9 +6723,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -6522,7 +6743,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } - }, + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 44.64101563, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", @@ -6530,21 +6760,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_2", "t_outer_2" ], - "table": "t_inner_4", - "rows_for_plan": 81, - "cost_for_plan": 62.84614258, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "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", "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -6560,6 +6798,50 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "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, + "semijoin_strategy_choice": [], + "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", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": [ @@ -6619,40 +6901,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_2", "t_inner_4" ], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + }, + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -6674,23 +6962,27 @@ 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" ], - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -6750,74 +7042,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "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": 64.43076172, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_4", - "t_outer_2" - ], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -6832,9 +7079,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -6850,6 +7099,63 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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": 64.43076172, + "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", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": [ @@ -7270,51 +7576,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t_inner_1", - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, - "rest_of_plan": [ + }, { - "plan_prefix": ["t_inner_1"], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -7327,9 +7611,41 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } } + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, + "rest_of_plan": [ + { + "plan_prefix": ["t_inner_1"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": ["t_inner_1"], @@ -7352,51 +7668,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t_inner_4", - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, - "rest_of_plan": [ + }, { - "plan_prefix": ["t_inner_4"], - "table": "t_inner_3", "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -7409,9 +7703,41 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } } + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_4", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, + "rest_of_plan": [ + { + "plan_prefix": ["t_inner_4"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": ["t_inner_4"], @@ -7437,120 +7763,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t_outer_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_1", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - }, - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": false - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": false - } - } - }, - { - "plan_prefix": [], - "table": "t_outer_1", - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { - "plan_prefix": ["t_outer_1"], - "table": "t_inner_1", "best_access_path": { + "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", @@ -7563,11 +7798,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 3, "cost": 2.005126953, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_inner_2", + } + }, + { "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -7580,11 +7817,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_outer_2", + } + }, + { "best_access_path": { + "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", @@ -7597,11 +7836,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -7614,11 +7855,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 3, "cost": 2.005126953, - "uses_join_buffering": true + "uses_join_buffering": false } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -7631,21 +7874,44 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "type": "scan", "records": 9, "cost": 2.015380859, - "uses_join_buffering": true + "uses_join_buffering": false } } - }, + } + ] + }, + { + "plan_prefix": [], + "table": "t_outer_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": ["t_outer_1"], - "table": "t_outer_2", - "rows_for_plan": 27, - "cost_for_plan": 10.02050781, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_1", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, { - "plan_prefix": ["t_outer_1", "t_outer_2"], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -7660,26 +7926,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_1", + } + }, + { "best_access_path": { + "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, + "resulting_rows": 9, + "cost": 2.015380859, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 3, - "cost": 2.005126953, + "records": 9, + "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -7694,9 +7964,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -7712,18 +7984,22 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } - }, + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 10.02050781, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2"], - "table": "t_inner_1", - "rows_for_plan": 81, - "cost_for_plan": 28.22563477, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], - "table": "t_inner_2", "best_access_path": { + "table": "t_inner_2", "considered_access_paths": [ { "access_type": "scan", @@ -7738,9 +8014,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_1", "considered_access_paths": [ { "access_type": "scan", @@ -7755,9 +8033,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -7773,42 +8072,41 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } - }, + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_1", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], + "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], - "table": "t_inner_2", - "rows_for_plan": 729, - "cost_for_plan": 176.0410156, - "semijoin_strategy_choice": [ + "get_costs_for_tables": [ { - "strategy": "FirstMatch", - "records": 27, - "read_time": 389.4047852 - }, - { - "strategy": "SJ-Materialization", - "records": 27, - "read_time": 16.74101562 - }, - { - "strategy": "DuplicateWeedout", - "records": 27, - "read_time": 289.4410156 + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } }, { - "chosen_strategy": "SJ-Materialization" - } - ], - "rest_of_plan": [ - { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2" - ], - "table": "t_inner_4", "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -7823,9 +8121,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -7841,29 +8141,65 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 389.4047852 + }, + { + "strategy": "SJ-Materialization", + "records": 27, + "read_time": 16.74101562 }, { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 289.4410156 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "rest_of_plan": [ + { "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, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ { - "plan_prefix": [ - "t_outer_1", - "t_outer_2", - "t_inner_1", - "t_inner_2", - "t_inner_4" - ], - "table": "t_inner_3", "best_access_path": { + "table": "t_inner_4", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -7879,6 +8215,50 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "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, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "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", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": [ @@ -7982,113 +8362,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_inner_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_4", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 2.005126953, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - } - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_outer_2", - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1"], - "table": "t_inner_2", - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [ - { - "strategy": "FirstMatch", - "records": 3, - "read_time": 44.75893555 - }, - { - "strategy": "SJ-Materialization", - "records": 3, - "read_time": 8.125634766 - }, + "get_costs_for_tables": [ { - "strategy": "DuplicateWeedout", - "records": 3, - "read_time": 37.22563477 + "best_access_path": { + "table": "t_inner_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } }, { - "chosen_strategy": "SJ-Materialization" - } - ], - "rest_of_plan": [ - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "table": "t_outer_2", "best_access_path": { + "table": "t_outer_2", "considered_access_paths": [ { "access_type": "scan", @@ -8103,9 +8399,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.015380859, "uses_join_buffering": true } - }, - "table": "t_inner_4", + } + }, + { "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -8120,9 +8418,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -8138,23 +8438,68 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_outer_2", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_2", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 3, + "read_time": 44.75893555 }, { + "strategy": "SJ-Materialization", + "records": 3, + "read_time": 8.125634766 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.22563477 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "rest_of_plan": [ + { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "table": "t_outer_2", - "rows_for_plan": 27, - "cost_for_plan": 15.54101562, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_2", - "t_outer_2" - ], - "table": "t_inner_4", "best_access_path": { + "table": "t_inner_4", "considered_access_paths": [ { "access_type": "scan", @@ -8169,9 +8514,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "cost": 2.005126953, "uses_join_buffering": true } - }, - "table": "t_inner_3", + } + }, + { "best_access_path": { + "table": "t_inner_3", "considered_access_paths": [ { "access_type": "scan", @@ -8187,6 +8534,63 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } } + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 15.54101562, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "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", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.005126953, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + } + ] }, { "plan_prefix": [ @@ -8230,40 +8634,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_2", "t_inner_4" ], - "table": "t_outer_2", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_outer_2", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, - "table": "t_inner_3", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 2.015380859, - "chosen": true + }, + { + "best_access_path": { + "table": "t_inner_3", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.015380859, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true } - } + ] }, { "plan_prefix": [ @@ -9064,48 +9474,57 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "plan_prefix": [ ], - "table": "A", - "best_access_path": - { - "considered_access_paths": - [ - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "access_type": "scan", - "resulting_rows": 5, - "cost": 3.017089844, - "chosen": true + "table": "A", + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 5, + "cost": 3.017089844, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 5, + "cost": 3.017089844, + "uses_join_buffering": false + } } - ], - "chosen_access_method": + }, + { - "type": "scan", - "records": 5, - "cost": 3.017089844, - "uses_join_buffering": false - } - }, - "table": "B", - "best_access_path": - { - "considered_access_paths": - [ - + "best_access_path": { - "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, - "chosen": true + "table": "B", + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 800, + "cost": 44.19726562, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 800, + "cost": 44.19726562, + "uses_join_buffering": false + } } - ], - "chosen_access_method": - { - "type": "scan", - "records": 800, - "cost": 44.19726562, - "uses_join_buffering": false } - } + ] }, { @@ -9123,27 +9542,33 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ "A" ], - "table": "B", - "best_access_path": - { - "considered_access_paths": - [ - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "access_type": "scan", - "resulting_rows": 800, - "cost": 220.9863281, - "chosen": true + "table": "B", + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 800, + "cost": 220.9863281, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 800, + "cost": 220.9863281, + "uses_join_buffering": false + } } - ], - "chosen_access_method": - { - "type": "scan", - "records": 800, - "cost": 220.9863281, - "uses_join_buffering": false } - } + ] }, { @@ -9185,48 +9610,57 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "plan_prefix": [ ], - "table": "A", - "best_access_path": - { - "considered_access_paths": - [ - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.017089844, - "chosen": true + "table": "A", + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.017089844, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 10, + "cost": 2.017089844, + "uses_join_buffering": false + } } - ], - "chosen_access_method": + }, + { - "type": "scan", - "records": 10, - "cost": 2.017089844, - "uses_join_buffering": false - } - }, - "table": "B", - "best_access_path": - { - "considered_access_paths": - [ - + "best_access_path": { - "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, - "chosen": true + "table": "B", + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 800, + "cost": 44.19726562, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 800, + "cost": 44.19726562, + "uses_join_buffering": false + } } - ], - "chosen_access_method": - { - "type": "scan", - "records": 800, - "cost": 44.19726562, - "uses_join_buffering": false } - } + ] }, { @@ -9244,37 +9678,43 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ "A" ], - "table": "B", - "best_access_path": - { - "considered_access_paths": - [ - - { - "access_type": "ref", - "index": "b", - "used_range_estimates": false, - "reason": "not available", - "rows": 1, - "cost": 20.00585794, - "chosen": true - }, - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, - "chosen": false + "table": "B", + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "b", + "used_range_estimates": false, + "reason": "not available", + "rows": 1, + "cost": 20.00585794, + "chosen": true + }, + + { + "access_type": "scan", + "resulting_rows": 800, + "cost": 44.19726562, + "chosen": false + } + ], + "chosen_access_method": + { + "type": "ref", + "records": 1, + "cost": 20.00585794, + "uses_join_buffering": false + } } - ], - "chosen_access_method": - { - "type": "ref", - "records": 1, - "cost": 20.00585794, - "uses_join_buffering": false } - } + ] }, { @@ -9425,49 +9865,58 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "plan_prefix": [ ], - "table": "t1", - "best_access_path": - { - "considered_access_paths": - [ - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, - "chosen": true + "table": "t1", + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.021972656, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 10, + "cost": 2.021972656, + "uses_join_buffering": false + } } - ], - "chosen_access_method": + }, + { - "type": "scan", - "records": 10, - "cost": 2.021972656, - "uses_join_buffering": false - } - }, - "table": "t2", - "best_access_path": - { - "considered_access_paths": - [ - + "best_access_path": { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.219726562, - "chosen": true, - "use_tmp_table": true + "table": "t2", + "considered_access_paths": + [ + + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.219726562, + "chosen": true, + "use_tmp_table": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 100, + "cost": 2.219726562, + "uses_join_buffering": false + } } - ], - "chosen_access_method": - { - "type": "scan", - "records": 100, - "cost": 2.219726562, - "uses_join_buffering": false } - } + ] }, { @@ -9485,37 +9934,43 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ "t1" ], - "table": "t2", - "best_access_path": - { - "considered_access_paths": - [ - - { - "access_type": "ref", - "index": "a", - "used_range_estimates": false, - "reason": "not available", - "rows": 1, - "cost": 20.00585794, - "chosen": true - }, - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "access_type": "scan", - "resulting_rows": 100, - "cost": 2.219726562, - "chosen": false + "table": "t2", + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1, + "cost": 20.00585794, + "chosen": true + }, + + { + "access_type": "scan", + "resulting_rows": 100, + "cost": 2.219726562, + "chosen": false + } + ], + "chosen_access_method": + { + "type": "ref", + "records": 1, + "cost": 20.00585794, + "uses_join_buffering": false + } } - ], - "chosen_access_method": - { - "type": "ref", - "records": 1, - "cost": 20.00585794, - "uses_join_buffering": false } - } + ] }, { @@ -9526,8 +9981,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "table": "t2", "rows_for_plan": 10, "cost_for_plan": 26.0278306, - "cost_for_sorting": 10, - "pruned_by_hanging_leaf": true + "cost_for_sorting": 10 } ] }, @@ -9547,37 +10001,43 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ "t2" ], - "table": "t1", - "best_access_path": - { - "considered_access_paths": - [ - - { - "access_type": "ref", - "index": "a", - "used_range_estimates": false, - "reason": "not available", - "rows": 1, - "cost": 200.0585794, - "chosen": true - }, - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, - "chosen": true + "table": "t1", + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1, + "cost": 200.0585794, + "chosen": true + }, + + { + "access_type": "scan", + "resulting_rows": 10, + "cost": 2.021972656, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "records": 10, + "cost": 2.021972656, + "uses_join_buffering": true + } } - ], - "chosen_access_method": - { - "type": "scan", - "records": 10, - "cost": 2.021972656, - "uses_join_buffering": true } - } + ] }, { @@ -9816,23 +10276,27 @@ select count(*) from seq_1_to_10000000 { "considered_execution_plans": [ { "plan_prefix": [], - "table": "seq_1_to_10000000", - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 10000000, - "cost": 10000000, - "chosen": true + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "seq_1_to_10000000", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 10000000, + "cost": 10000000, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 10000000, + "cost": 10000000, + "uses_join_buffering": false + } } - ], - "chosen_access_method": { - "type": "scan", - "records": 10000000, - "cost": 10000000, - "uses_join_buffering": false } - } + ] }, { "plan_prefix": [], @@ -10007,7 +10471,7 @@ set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')) set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); select @sub_path; @sub_path -$.steps[1].join_optimization.steps[4].considered_execution_plans[1].rest_of_plan[0] +$.steps[1].join_optimization.steps[4].considered_execution_plans[1].rest_of_plan[0].get_costs_for_tables[0] select json_detailed(json_extract( @trace, @@ -10210,36 +10674,42 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "plan_prefix": [ ], - "table": "t2", - "best_access_path": - { - "considered_access_paths": - [ - - { - "access_type": "ref", - "index": "idx_a", - "used_range_estimates": false, - "reason": "not available", - "rows": 1.8367, - "cost": 2.000585794, - "chosen": true - }, - + "get_costs_for_tables": + [ + + { + "best_access_path": { - "type": "scan", - "chosen": false, - "cause": "cost" + "table": "t2", + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "idx_a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1.8367, + "cost": 2.000585794, + "chosen": true + }, + + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": + { + "type": "ref", + "records": 1.8367, + "cost": 2.000585794, + "uses_join_buffering": false + } } - ], - "chosen_access_method": - { - "type": "ref", - "records": 1.8367, - "cost": 2.000585794, - "uses_join_buffering": false } - } + ] }, { |