diff options
| author | Monty <monty@mariadb.org> | 2022-05-31 17:36:32 +0300 |
|---|---|---|
| committer | Oleg Smirnov <olernov@gmail.com> | 2022-07-26 22:27:28 +0700 |
| commit | b3c74bdc1f09b2b8babd7f2bd9d52df2749ddcc3 (patch) | |
| tree | 9e3364bb4ef289d8ffd951499a321f7d126119a9 /mysql-test/main/opt_trace.result | |
| parent | 213772f99a109d3ea58fb40c5c48422d458366fa (diff) | |
| download | mariadb-git-b3c74bdc1f09b2b8babd7f2bd9d52df2749ddcc3.tar.gz | |
Improve pruning in greedy_search by sorting tables during search
MDEV-28073 Slow query performance in MariaDB when using many tables
The faster we can find a good query plan, the more options we have for
finding and pruning (ignoring) bad plans.
This patch adds sorting of plans to best_extension_by_limited_search().
The plans, from best_access_path() are sorted according to the numbers
of found rows. This allows us to faster find 'good tables' and we are
thus able to eliminate 'bad plans' faster.
One side effect of this patch is that if two tables have equal cost,
the table that which was used earlier in the query is preferred.
This allows users to improve plans by reordering eq_ref tables in the
order they would like them to be uses.
Result changes caused by the patch:
- Traces are different as now we print the cost for using tables before
we start considering them in the plan.
- Table order are changed for some plans. In most cases this is because
the plans are equal and tables are in this case sorted according to
their usage in the original query.
- A few plans was changed as the optimizer was able to find a better
plan (that was pruned by the original code).
Other things:
- Added a new statistic variable: "optimizer_join_prefixes_check_calls",
which counts number of calls to best_extension_by_limited_search().
This can be used to check the prune efficiency in greedy_search().
- Added variable "JOIN_TAB::embedded_dependent" to be able to handle
XX IN (SELECT..) in the greedy_optimizer. The idea is that we
should prune a table if any of the tables in embedded_dependent is
not yet read.
- When using many tables in a query, there will be some additional
memory usage as we need to pre-allocate table of
table_count*table_count*sizeof(POSITION) objects (POSITION is 312
bytes for now) to hold the pre-calculated best_access_path()
information. This memory usage is offset by the expected
performance improvement when using many tables in a query.
- Removed the code from an earlier patch to keep the table order in
join->best_ref in the original order. This is not needed anymore as we
are now sorting the tables for each best_extension_by_limited_search()
call.
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 3242 |
1 files changed, 2154 insertions, 1088 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 6f0d13441ff..9aa85c6bd08 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -143,7 +143,11 @@ select * from v1 { "cost": 2.204394531, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531 } @@ -293,7 +297,11 @@ select * from (select * from t1 where t1.a=1)q { "cost": 2.204394531, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531 } @@ -449,7 +457,11 @@ select * from v2 { "cost": 2.204394531, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 1, "cost_for_plan": 2.404394531, "cost_for_sorting": 1 @@ -520,7 +532,11 @@ select * from v2 { "cost": 2, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "<derived2>", "rows_for_plan": 2, "cost_for_plan": 2.4 } @@ -656,7 +672,11 @@ explain select * from v2 { "cost": 2.021972656, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t2", "rows_for_plan": 10, "cost_for_plan": 4.021972656 } @@ -772,7 +792,11 @@ explain select * from v1 { "cost": 2.021972656, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 10, "cost_for_plan": 4.021972656, "cost_for_sorting": 10 @@ -837,7 +861,11 @@ explain select * from v1 { "cost": 10, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "<derived2>", "rows_for_plan": 10, "cost_for_plan": 12 } @@ -1009,6 +1037,27 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "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": [ @@ -1039,7 +1088,11 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "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 @@ -1049,22 +1102,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "plan_prefix": [], "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 - } - }, "rows_for_plan": 100, "cost_for_plan": 22.31738281, "rest_of_plan": [ @@ -1095,7 +1132,11 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "cost": 200.0585794, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t2"], + "table": "t1", "rows_for_plan": 100, "cost_for_plan": 242.3759623, "pruned_by_cost": true @@ -1271,7 +1312,11 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "cost": 6.25, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 5, "cost_for_plan": 7.25 } @@ -1461,7 +1506,11 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "cost": 2.2, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 8, "cost_for_plan": 3.8, "cost_for_sorting": 8 @@ -1659,7 +1708,11 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "cost": 2.35, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9 @@ -1846,7 +1899,11 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "cost": 2.35, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 9, "cost_for_plan": 4.15, "cost_for_sorting": 9 @@ -2132,7 +2189,11 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "cost": 21.14242739, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 21, "cost_for_plan": 25.34242739 } @@ -2386,7 +2447,11 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t2"], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -2525,7 +2590,11 @@ explain select * from t1 left join t2 on t2.a=t1.a { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937, "rest_of_plan": [ @@ -2554,7 +2623,11 @@ explain select * from t1 left join t2 on t2.a=t1.a { "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 @@ -2738,7 +2811,11 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t3", "t2"], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -2945,7 +3022,11 @@ explain extended select * from t1 where a in (select pk from t10) { "cost": 2.021972656, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t10", "rows_for_plan": 10, "cost_for_plan": 4.021972656 } @@ -2975,6 +3056,27 @@ explain extended select * from t1 where a in (select pk from t10) { "uses_join_buffering": false } }, + "table": "t10", + "best_access_path": { + "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 + } + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.606591797, "semijoin_strategy_choice": [], @@ -2997,7 +3099,11 @@ explain extended select * from t1 where a in (select pk from t10) { "cost": 2.021972656, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t1"], + "table": "t10", "rows_for_plan": 30, "cost_for_plan": 10.62856445, "semijoin_strategy_choice": [ @@ -3026,22 +3132,6 @@ explain extended select * from t1 where a in (select pk from t10) { { "plan_prefix": [], "table": "t10", - "best_access_path": { - "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 - } - }, "rows_for_plan": 10, "cost_for_plan": 4.021972656, "semijoin_strategy_choice": [], @@ -3427,7 +3517,11 @@ explain select * from t1 where pk = 2 and a=5 and b=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 @@ -3556,7 +3650,11 @@ select f1(a) from t1 { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -3652,7 +3750,11 @@ select f2(a) from t1 { "cost": 2.006835938, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 4, "cost_for_plan": 2.806835937 } @@ -3700,7 +3802,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2092 +2190 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3714,7 +3816,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 1992 0 + 2090 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3722,7 +3824,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2092 0 +select * from t1 2190 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -4035,6 +4137,27 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "uses_join_buffering": false } }, + "table": "t1", + "best_access_path": { + "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 + } + } + }, + { + "plan_prefix": [], + "table": "t0", "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ @@ -4064,7 +4187,11 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "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 @@ -4074,22 +4201,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "plan_prefix": [], "table": "t1", - "best_access_path": { - "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 - } - }, "rows_for_plan": 3, "cost_for_plan": 1.346757383, "rest_of_plan": [ @@ -4120,7 +4231,11 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "cost": 3.003514767, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": ["t1"], + "table": "t0", "rows_for_plan": 6, "cost_for_plan": 5.55027215, "pruned_by_cost": true @@ -4264,7 +4379,11 @@ explain select * from (select rand() from t1)q { "cost": 2.005126953, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "t1", "rows_for_plan": 3, "cost_for_plan": 2.605126953 } @@ -4328,7 +4447,11 @@ explain select * from (select rand() from t1)q { "cost": 3, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "<derived2>", "rows_for_plan": 3, "cost_for_plan": 3.6 } @@ -4535,6 +4658,27 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "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": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ @@ -4556,7 +4700,11 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "cost": 2.005126953, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t_inner_1"], + "table": "t_inner_2", "rows_for_plan": 9, "cost_for_plan": 6.410253906 } @@ -4565,22 +4713,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "plan_prefix": [], "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 - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "pruned_by_heuristic": true @@ -4611,6 +4743,44 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "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": [], @@ -4634,6 +4804,27 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "uses_join_buffering": true } }, + "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": true + } + } + }, + { + "plan_prefix": ["t1"], + "table": "t_inner_1", "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -4656,7 +4847,11 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "cost": 2.005126953, "uses_join_buffering": true } - }, + } + }, + { + "plan_prefix": ["t1", "t_inner_1"], + "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 13.81538086, "semijoin_strategy_choice": [ @@ -4685,22 +4880,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "plan_prefix": ["t1"], "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": true - } - }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -4711,22 +4890,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "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 - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -4735,22 +4898,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "plan_prefix": [], "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 - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -5065,6 +5212,95 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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 + } + ], + "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": [], @@ -5088,12 +5324,84 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "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"], + "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_inner_1"], + "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ @@ -5111,27 +5419,68 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [ - { - "strategy": "FirstMatch", + "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, - "read_time": 44.75893555 - }, - { - "strategy": "DuplicateWeedout", + "cost": 2.005126953, + "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, - "read_time": 37.22563477 - }, - { - "chosen_strategy": "DuplicateWeedout" + "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_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_inner_1", "t_inner_2"], - "table": "t_outer_2", + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5148,16 +5497,68 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 27, - "cost_for_plan": 44.64101563, - "semijoin_strategy_choice": [], + "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_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", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_4", "best_access_path": { @@ -5176,16 +5577,42 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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_outer_2", + "t_inner_1", + "t_inner_2" + ], + "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 62.84614258, + "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_outer_2", "t_inner_4" ], "table": "t_inner_3", @@ -5204,19 +5631,29 @@ 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 } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 210.6615234, + "cost_for_plan": 455.4615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, - "read_time": 424.025293 + "read_time": 668.825293 }, { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 324.0615234 + "read_time": 568.8615234 }, { "chosen_strategy": "DuplicateWeedout" @@ -5228,65 +5665,33 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", - "t_outer_2" + "t_inner_2" ], "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 - } - }, "rows_for_plan": 243, - "cost_for_plan": 95.25639648, + "cost_for_plan": 340.0563965, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } ] }, { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], "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 - } - }, - "rows_for_plan": 9, - "cost_for_plan": 41.03076172, + "rows_for_plan": 243, + "cost_for_plan": 78.83076172, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", "t_inner_4" ], - "table": "t_outer_2", + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5303,17 +5708,43 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 81, - "cost_for_plan": 59.24614258, + "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_outer_2", + "t_inner_1", + "t_inner_4" + ], + "table": "t_inner_2", + "rows_for_plan": 2187, + "cost_for_plan": 518.2461426, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", "t_inner_4", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_3", "best_access_path": { @@ -5331,30 +5762,85 @@ 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 } - }, - "rows_for_plan": 729, - "cost_for_plan": 207.0615234, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_4", + "t_inner_2" + ], + "table": "t_inner_3", + "rows_for_plan": 19683, + "cost_for_plan": 4456.861523, "semijoin_strategy_choice": [ { + "strategy": "FirstMatch", + "records": 27, + "read_time": 9562.749707 + }, + { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 320.4615234 + "read_time": 7413.361523 }, { - "chosen_strategy": "DuplicateWeedout" + "chosen_strategy": "FirstMatch" } - ] + ], + "pruned_by_cost": true } ] }, { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", "t_inner_4" ], "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 518.2461426, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "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 + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5370,42 +5856,90 @@ 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 } - }, - "rows_for_plan": 81, - "cost_for_plan": 59.24614258, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_3" + ], + "table": "t_inner_2", + "rows_for_plan": 6561, + "cost_for_plan": 1490.256396, "semijoin_strategy_choice": [], - "pruned_by_heuristic": true + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_3" + ], + "table": "t_inner_4", + "rows_for_plan": 2187, + "cost_for_plan": 615.4461426, + "semijoin_strategy_choice": [], + "pruned_by_cost": true } ] - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], - "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 - } - }, - "rows_for_plan": 27, - "cost_for_plan": 44.64101563, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true } ] }, { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_2", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_4", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_1", + "rows_for_plan": 9, + "cost_for_plan": 6.410253906, + "semijoin_strategy_choice": [], + "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": [ @@ -5423,6 +5957,44 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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": [], @@ -5446,6 +6018,44 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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", "t_outer_2"], + "table": "t_inner_2", "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [ @@ -5483,6 +6093,32 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_4", "rows_for_plan": 81, "cost_for_plan": 304.0461426, "semijoin_strategy_choice": [], @@ -5511,7 +6147,17 @@ 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 } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 729, "cost_for_plan": 451.8615234, "semijoin_strategy_choice": [ @@ -5528,8 +6174,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "chosen_strategy": "DuplicateWeedout" } - ], - "pruned_by_cost": true + ] } ] }, @@ -5541,48 +6186,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 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 - } - ], - "chosen_access_method": { - "type": "scan", - "records": 9, - "cost": 2.015380859, - "uses_join_buffering": true - } - }, "rows_for_plan": 243, "cost_for_plan": 336.4563965, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "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 - } - }, "rows_for_plan": 243, "cost_for_plan": 75.23076172, "semijoin_strategy_choice": [], @@ -5611,18 +6224,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - }, - { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_outer_2", - "t_inner_4" - ], "table": "t_inner_3", "best_access_path": { "considered_access_paths": [ @@ -5639,33 +6240,89 @@ 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 } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_2", "rows_for_plan": 2187, "cost_for_plan": 514.6461426, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "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 + } + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.015380859, + "uses_join_buffering": true + } + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4", + "t_inner_2" + ], + "table": "t_inner_3", + "rows_for_plan": 19683, + "cost_for_plan": 4453.261523, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 7409.761523 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 514.6461426, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "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 - } - }, "rows_for_plan": 729, "cost_for_plan": 172.4410156, "semijoin_strategy_choice": [], @@ -5677,6 +6334,23 @@ 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 + } + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.005126953, + "uses_join_buffering": true + } + }, "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ @@ -5693,7 +6367,16 @@ 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 } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_2", "rows_for_plan": 6561, "cost_for_plan": 1486.656396, "semijoin_strategy_choice": [], @@ -5707,22 +6390,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_3" ], "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 - } - }, "rows_for_plan": 2187, "cost_for_plan": 611.8461426, "semijoin_strategy_choice": [], @@ -5734,30 +6401,28 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "plan_prefix": ["t_outer_1", "t_inner_1"], - "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", + "table": "t_inner_2", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true + "read_time": 44.75893555 + }, + { + "strategy": "DuplicateWeedout", + "records": 3, + "read_time": 37.22563477 + }, + { + "chosen_strategy": "DuplicateWeedout" } - }, - "rows_for_plan": 27, - "cost_for_plan": 13.81538086, - "semijoin_strategy_choice": [], + ], "rest_of_plan": [ { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], - "table": "t_inner_2", + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", "best_access_path": { "considered_access_paths": [ { @@ -5774,18 +6439,73 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 243, - "cost_for_plan": 64.43076172, + "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", "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_4", - "t_inner_2" + "t_inner_2", + "t_outer_2" ], - "table": "t_outer_2", + "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": [ { @@ -5801,20 +6521,105 @@ 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 } - }, - "rows_for_plan": 2187, - "cost_for_plan": 503.8461426, + } + }, + { + "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": [], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "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", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 210.6615234, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 27, + "read_time": 424.025293 + }, + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 324.0615234 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ] + } + ] }, { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_4", - "t_inner_2" + "t_inner_2", + "t_outer_2" ], "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 95.25639648, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_4", + "rows_for_plan": 9, + "cost_for_plan": 41.03076172, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", "best_access_path": { "considered_access_paths": [ { @@ -5831,14 +6636,119 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 2187, - "cost_for_plan": 503.8461426, + "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", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", + "rows_for_plan": 81, + "cost_for_plan": 59.24614258, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "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 + } + ], + "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_inner_4", + "t_outer_2" + ], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 207.0615234, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 320.4615234 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ] + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 81, + "cost_for_plan": 59.24614258, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] }, { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_3", + "rows_for_plan": 27, + "cost_for_plan": 44.64101563, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_4", + "rows_for_plan": 27, + "cost_for_plan": 13.81538086, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_outer_2", "best_access_path": { @@ -5857,14 +6767,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 243, - "cost_for_plan": 64.43076172, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], - "table": "t_inner_3", + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5881,39 +6784,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 243, - "cost_for_plan": 64.43076172, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - } - ] - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1"], - "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 - } - }, - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [], - "rest_of_plan": [ - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], - "table": "t_inner_2", + "table": "t_inner_3", "best_access_path": { "considered_access_paths": [ { @@ -5929,19 +6800,23 @@ 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 } - }, - "rows_for_plan": 729, - "cost_for_plan": 172.4410156, + } + }, + { + "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_3", - "t_inner_2" + "t_inner_4", + "t_outer_2" ], - "table": "t_outer_2", + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -5958,115 +6833,34 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 6561, - "cost_for_plan": 1486.656396, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - }, - { - "plan_prefix": [ - "t_outer_1", - "t_inner_1", - "t_inner_3", - "t_inner_2" - ], - "table": "t_inner_4", + "table": "t_inner_3", "best_access_path": { "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 } - }, - "rows_for_plan": 2187, - "cost_for_plan": 611.8461426, - "semijoin_strategy_choice": [], - "pruned_by_cost": true - } - ] - }, - { - "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], - "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 - } - }, - "rows_for_plan": 729, - "cost_for_plan": 172.4410156, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1", "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 - } - ], - "chosen_access_method": { - "type": "scan", - "records": 3, - "cost": 2.005126953, - "uses_join_buffering": true - } - }, - "rows_for_plan": 243, - "cost_for_plan": 75.23076172, - "semijoin_strategy_choice": [], - "rest_of_plan": [ + }, { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_3", - "t_inner_4" + "t_inner_4", + "t_outer_2" ], "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 - } - }, "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, + "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -6074,80 +6868,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": [ "t_outer_1", "t_inner_1", - "t_inner_3", - "t_inner_4" + "t_inner_4", + "t_outer_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_3", "rows_for_plan": 2187, - "cost_for_plan": 514.6461426, + "cost_for_plan": 503.8461426, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_inner_2", + "rows_for_plan": 243, + "cost_for_plan": 64.43076172, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 64.43076172, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_3", + "rows_for_plan": 81, + "cost_for_plan": 24.62563477, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true } ] }, { "plan_prefix": ["t_outer_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 - } - }, - "rows_for_plan": 27, - "cost_for_plan": 10.02050781, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1"], - "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 - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -6156,22 +6918,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "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 - } - }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -6180,22 +6926,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "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 - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -6205,71 +6935,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "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 - } - }, - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "table": "t_outer_2", + "rows_for_plan": 9, + "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "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 - } - }, - "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "table": "t_inner_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "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_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -6278,22 +6960,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "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 - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -6302,22 +6968,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "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": false - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -6398,8 +7048,8 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) @@ -6637,6 +7287,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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 + } + } + }, + { + "plan_prefix": [], + "table": "t_inner_1", "rows_for_plan": 3, "cost_for_plan": 2.605126953, "rest_of_plan": [ @@ -6658,7 +7329,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 } - }, + } + }, + { + "plan_prefix": ["t_inner_1"], + "table": "t_inner_2", "rows_for_plan": 27, "cost_for_plan": 10.02050781 } @@ -6667,22 +7342,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "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 - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true @@ -6710,6 +7369,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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 + } + ], + "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": [ @@ -6731,7 +7411,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 } - }, + } + }, + { + "plan_prefix": ["t_inner_4"], + "table": "t_inner_3", "rows_for_plan": 27, "cost_for_plan": 10.02050781 } @@ -6740,22 +7424,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "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": false - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "pruned_by_heuristic": true @@ -6786,6 +7454,95 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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 + } + ], + "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": [], @@ -6809,12 +7566,84 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 9, - "cost_for_plan": 6.410253906, + "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"], + "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_inner_1"], + "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ @@ -6832,32 +7661,68 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 81, - "cost_for_plan": 24.62563477, - "semijoin_strategy_choice": [ - { - "strategy": "FirstMatch", - "records": 3, - "read_time": 44.75893555 - }, - { - "strategy": "SJ-Materialization", + "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, - "read_time": 8.125634766 - }, - { - "strategy": "DuplicateWeedout", + "cost": 2.005126953, + "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, - "read_time": 37.22563477 - }, - { - "chosen_strategy": "SJ-Materialization" + "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_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_inner_1", "t_inner_2"], - "table": "t_outer_2", + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_2", "best_access_path": { "considered_access_paths": [ { @@ -6874,16 +7739,73 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 27, - "cost_for_plan": 15.54101562, - "semijoin_strategy_choice": [], + "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_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", - "t_outer_2" + "t_inner_2" ], "table": "t_inner_4", "best_access_path": { @@ -6902,16 +7824,42 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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_outer_2", + "t_inner_1", + "t_inner_2" + ], + "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 33.74614258, + "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_outer_2", "t_inner_4" ], "table": "t_inner_3", @@ -6930,24 +7878,34 @@ 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 } - }, + } + }, + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 181.5615234, + "cost_for_plan": 182.7615234, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, - "read_time": 394.925293 + "read_time": 396.125293 }, { "strategy": "SJ-Materialization", "records": 27, - "read_time": 22.26152344 + "read_time": 23.46152344 }, { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 294.9615234 + "read_time": 296.1615234 }, { "chosen_strategy": "SJ-Materialization" @@ -6959,36 +7917,193 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [ "t_outer_1", + "t_outer_2", "t_inner_1", - "t_inner_2", - "t_outer_2" + "t_inner_2" ], "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 - } - }, "rows_for_plan": 243, - "cost_for_plan": 66.15639648, + "cost_for_plan": 67.35639648, "semijoin_strategy_choice": [], "pruned_by_cost": true } ] }, { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_4", + "rows_for_plan": 243, + "cost_for_plan": 78.83076172, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2", "t_inner_1"], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 176.0410156, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_2", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_4", + "rows_for_plan": 81, + "cost_for_plan": 28.22563477, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_outer_2"], + "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 60.63588867, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_1", + "rows_for_plan": 9, + "cost_for_plan": 6.410253906, + "semijoin_strategy_choice": [], + "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 + }, + { + "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", + "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": [ @@ -7006,8 +8121,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "uses_join_buffering": true } }, - "rows_for_plan": 9, - "cost_for_plan": 11.93076172, + "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", "t_inner_2"], + "table": "t_outer_2", + "rows_for_plan": 27, + "cost_for_plan": 15.54101562, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7015,9 +8151,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_1", "t_inner_1", "t_inner_2", - "t_inner_4" + "t_outer_2" ], - "table": "t_outer_2", + "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": [ { @@ -7033,9 +8186,18 @@ 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 } - }, + } + }, + { + "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": 30.14614258, + "cost_for_plan": 33.74614258, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -7044,9 +8206,31 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_1", "t_inner_1", "t_inner_2", - "t_inner_4" + "t_outer_2" ], "table": "t_inner_3", + "rows_for_plan": 243, + "cost_for_plan": 66.15639648, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_4", + "rows_for_plan": 9, + "cost_for_plan": 11.93076172, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", "best_access_path": { "considered_access_paths": [ { @@ -7063,6 +8247,45 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "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", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", + "rows_for_plan": 81, + "cost_for_plan": 30.14614258, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", "rows_for_plan": 81, "cost_for_plan": 30.14614258, "semijoin_strategy_choice": [], @@ -7073,22 +8296,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], "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 - } - }, "rows_for_plan": 27, "cost_for_plan": 15.54101562, "semijoin_strategy_choice": [], @@ -7098,47 +8305,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "plan_prefix": ["t_outer_1", "t_inner_1"], - "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 - } - }, - "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_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 - } - }, "rows_for_plan": 27, "cost_for_plan": 13.81538086, "semijoin_strategy_choice": [], @@ -7147,22 +8314,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1", "t_inner_1"], "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 - } - }, "rows_for_plan": 81, "cost_for_plan": 24.62563477, "semijoin_strategy_choice": [], @@ -7173,46 +8324,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_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 - } - }, - "rows_for_plan": 27, - "cost_for_plan": 10.02050781, - "semijoin_strategy_choice": [], - "pruned_by_heuristic": true - }, - { - "plan_prefix": ["t_outer_1"], - "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 - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -7221,22 +8332,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "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 - } - }, "rows_for_plan": 9, "cost_for_plan": 6.410253906, "semijoin_strategy_choice": [], @@ -7245,22 +8340,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": ["t_outer_1"], "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 - } - }, "rows_for_plan": 27, "cost_for_plan": 10.02050781, "semijoin_strategy_choice": [], @@ -7270,71 +8349,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { }, { "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 - } - }, - "rows_for_plan": 3, - "cost_for_plan": 2.605126953, + "table": "t_outer_2", + "rows_for_plan": 9, + "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "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 - } - }, - "rows_for_plan": 9, - "cost_for_plan": 3.815380859, + "table": "t_inner_1", + "rows_for_plan": 3, + "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, { "plan_prefix": [], - "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_2", "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -7343,22 +8374,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "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 - } - }, "rows_for_plan": 3, "cost_for_plan": 2.605126953, "semijoin_strategy_choice": [], @@ -7367,22 +8382,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "plan_prefix": [], "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": false - } - }, "rows_for_plan": 9, "cost_for_plan": 3.815380859, "semijoin_strategy_choice": [], @@ -7419,8 +8418,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_join_order": [ "t_outer_1", - "<subquery2>", "t_outer_2", + "<subquery2>", "<subquery3>" ] }, @@ -7443,19 +8442,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_inner_1", + "table": "t_outer_2", "attached": null }, { - "table": "t_inner_2", + "table": "t_inner_1", "attached": null }, { - "table": "<subquery2>", + "table": "t_inner_2", "attached": null }, { - "table": "t_outer_2", + "table": "<subquery2>", "attached": null }, { @@ -8086,6 +9085,34 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "uses_join_buffering": false } }, + "table": "B", + "best_access_path": + { + "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 + } + } + }, + + { + "plan_prefix": + [ + ], + "table": "A", "rows_for_plan": 5, "cost_for_plan": 4.017089844, "rest_of_plan": @@ -8116,7 +9143,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 220.9863281, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + "A" + ], + "table": "B", "rows_for_plan": 4000, "cost_for_plan": 1025.003418 } @@ -8128,26 +9163,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ ], "table": "B", - "best_access_path": - { - "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 - } - }, "rows_for_plan": 800, "cost_for_plan": 204.1972656, "pruned_by_heuristic": true @@ -8191,6 +9206,34 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "uses_join_buffering": false } }, + "table": "B", + "best_access_path": + { + "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 + } + } + }, + + { + "plan_prefix": + [ + ], + "table": "A", "rows_for_plan": 10, "cost_for_plan": 4.017089844, "rest_of_plan": @@ -8231,7 +9274,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20.00585794, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + "A" + ], + "table": "B", "rows_for_plan": 10, "cost_for_plan": 26.02294779, "selectivity": 0.8, @@ -8245,26 +9296,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ ], "table": "B", - "best_access_path": - { - "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 - } - }, "rows_for_plan": 800, "cost_for_plan": 204.1972656, "pruned_by_cost": true @@ -8415,6 +9446,35 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "uses_join_buffering": false } }, + "table": "t2", + "best_access_path": + { + "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 + } + } + }, + + { + "plan_prefix": + [ + ], + "table": "t1", "rows_for_plan": 10, "cost_for_plan": 4.021972656, "rest_of_plan": @@ -8455,7 +9515,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20.00585794, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + "t1" + ], + "table": "t2", "rows_for_plan": 10, "cost_for_plan": 26.0278306, "cost_for_sorting": 10, @@ -8469,27 +9537,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ ], "table": "t2", - "best_access_path": - { - "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 - } - }, "rows_for_plan": 100, "cost_for_plan": 22.21972656, "rest_of_plan": @@ -8530,7 +9577,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 2.021972656, "uses_join_buffering": true } - }, + } + }, + + { + "plan_prefix": + [ + "t2" + ], + "table": "t1", "rows_for_plan": 1000, "cost_for_plan": 224.2416992, "pruned_by_cost": true @@ -8777,7 +9832,11 @@ select count(*) from seq_1_to_10000000 { "cost": 10000000, "uses_join_buffering": false } - }, + } + }, + { + "plan_prefix": [], + "table": "seq_1_to_10000000", "rows_for_plan": 10000000, "cost_for_plan": 12000000 } @@ -8948,7 +10007,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[0].rest_of_plan[0] +$.steps[1].join_optimization.steps[4].considered_execution_plans[1].rest_of_plan[0] select json_detailed(json_extract( @trace, @@ -9180,7 +10239,14 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "cost": 2.000585794, "uses_join_buffering": false } - }, + } + }, + + { + "plan_prefix": + [ + ], + "table": "t2", "rows_for_plan": 1.8367, "cost_for_plan": 2.367925794, "cost_for_sorting": 1.8367 @@ -9258,5 +10324,5 @@ left(trace, 100) "select_id": 1, "steps": [ -# End of 10.6 tests set optimizer_trace='enabled=off'; +# End of 10.6 tests |