diff options
| author | Monty <monty@mariadb.org> | 2022-10-04 16:16:06 +0300 |
|---|---|---|
| committer | Sergei Petrunia <sergey@mariadb.com> | 2023-02-03 00:00:35 +0300 |
| commit | 727491b72adcf3c10b252a61579f70c7ec2246be (patch) | |
| tree | b5ac3878d973ac0a01bd86d0c8a4d7d7723ecc03 /mysql-test/main/opt_trace.result | |
| parent | eb68023c8e3f014495cd694e1b8d5dafc1e37a8e (diff) | |
| download | mariadb-git-727491b72adcf3c10b252a61579f70c7ec2246be.tar.gz | |
Added test cases for preceding test
This includes all test changes from
"Changing all cost calculation to be given in milliseconds"
and forwards.
Some of the things that caused changes in the result files:
- As part of fixing tests, I added 'echo' to some comments to be able to
easier find out where things where wrong.
- MATERIALIZED has now a higher cost compared to X than before. Because
of this some MATERIALIZED types have changed to DEPENDEND SUBQUERY.
- Some test cases that required MATERIALIZED to repeat a bug was
changed by adding more rows to force MATERIALIZED to happen.
- 'Filtered' in SHOW EXPLAIN has in many case changed from 100.00 to
something smaller. This is because now filtered also takes into
account the smallest possible ref access and filters, even if they
where not used. Another reason for 'Filtered' being smaller is that
we now also take into account implicit filtering done for subqueries
using FIRSTMATCH.
(main.subselect_no_exists_to_in)
This is caluculated in best_access_path() and stored in records_out.
- Table orders has changed because more accurate costs.
- 'index' and 'ALL' for small tables has changed to use 'range' or
'ref' because of optimizer_scan_setup_cost.
- index can be changed to 'range' as 'range' optimizer assumes we don't
have to read the blocks from disk that range optimizer has already read.
This can be confusing in the case where there is no obvious where clause
but instead there is a hidden 'key_column > NULL' added by the optimizer.
(main.subselect_no_exists_to_in)
- Scan on primary clustered key does not report 'Using Index' anymore
(It's a table scan, not an index scan).
- For derived tables, the number of rows is now 100 instead of 2,
which can be seen in EXPLAIN.
- More tests have "Using index for group by" as the cost of this
optimization is now more correct (lower).
- A primary key could be preferred for a normal key, even if it would
access more rows, as it's faster to do 1 lokoup and 3 'index_next' on a
clustered primary key than one lookup trough a secondary.
(main.stat_tables_innodb)
Notes:
- There was a 4.7% more calls to best_extension_by_limited_search() in
the main.greedy_optimizer test. However examining the test results
it looked that the plans where slightly better (eq_ref where more
chained together) so I assume this is ok.
- I have verified a few test cases where there was notable/unexpected
changes in the plan and in all cases the new optimizer plans where
faster. (main.greedy_optimizer and some others)
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 3791 |
1 files changed, 2166 insertions, 1625 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index ea8d6139920..35cae556d92 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -118,8 +118,8 @@ select * from v1 { "table": "t1", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -139,18 +139,18 @@ select * from v1 { { "access_type": "scan", "rows": 2, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 1.502197266, + "rows_out": 1, + "cost": 0.01034841, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 1, - "cost": 1.502197266, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01034841, "uses_join_buffering": false } } @@ -161,14 +161,14 @@ select * from v1 { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 1.502197266 + "cost_for_plan": 0.01034841 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 1.502197266 + "cost": 0.01034841 }, { "substitute_best_equal": { @@ -286,8 +286,8 @@ select * from (select * from t1 where t1.a=1)q { "table": "t1", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -307,18 +307,18 @@ select * from (select * from t1 where t1.a=1)q { { "access_type": "scan", "rows": 2, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 1.502197266, + "rows_out": 1, + "cost": 0.01034841, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 1, - "cost": 1.502197266, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01034841, "uses_join_buffering": false } } @@ -329,14 +329,14 @@ select * from (select * from t1 where t1.a=1)q { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 1.502197266 + "cost_for_plan": 0.01034841 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 1.502197266 + "cost": 0.01034841 }, { "substitute_best_equal": { @@ -459,8 +459,8 @@ select * from v2 { "table": "t1", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -480,9 +480,9 @@ select * from v2 { { "access_type": "scan", "rows": 2, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 1.502197266, + "rows_out": 1, + "cost": 0.01034841, "index_only": false, "chosen": true, "use_tmp_table": true @@ -490,9 +490,9 @@ select * from v2 { ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 1, - "cost": 1.502197266, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01034841, "uses_join_buffering": false } } @@ -503,15 +503,15 @@ select * from v2 { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 1.502197266, - "cost_for_sorting": 1 + "cost_for_plan": 0.01034841, + "cost_for_sorting": 6.301866e-4 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 2.502197266 + "cost": 0.010978597 }, { "substitute_best_equal": { @@ -549,8 +549,8 @@ select * from v2 { "table": "<derived2>", "table_scan": { "rows": 2, - "read_cost": 2, - "read_and_compare_cost": 2.5 + "read_cost": 0.012350033, + "read_and_compare_cost": 0.012418701 } } ] @@ -570,18 +570,18 @@ select * from v2 { { "access_type": "scan", "rows": 2, - "rows_after_scan": 2, "rows_after_filter": 2, - "cost": 2.5, + "rows_out": 2, + "cost": 0.012418701, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 2, - "records_out": 2, - "cost": 2.5, + "rows_read": 2, + "rows_out": 2, + "cost": 0.012418701, "uses_join_buffering": false } } @@ -592,14 +592,14 @@ select * from v2 { "plan_prefix": [], "table": "<derived2>", "rows_for_plan": 2, - "cost_for_plan": 2.5 + "cost_for_plan": 0.012418701 } ] }, { "best_join_order": ["<derived2>"], "rows": 2, - "cost": 2.5 + "cost": 0.012418701 }, { "attaching_conditions_to_tables": { @@ -703,8 +703,8 @@ explain select * from v2 { "table": "t2", "table_scan": { "rows": 10, - "read_cost": 1.010986328, - "read_and_compare_cost": 3.510986328 + "read_cost": 0.01127965, + "read_and_compare_cost": 0.01159965 } } ] @@ -724,18 +724,18 @@ explain select * from v2 { { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.510986328, + "rows_out": 10, + "cost": 0.01159965, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -746,14 +746,14 @@ explain select * from v2 { "plan_prefix": [], "table": "t2", "rows_for_plan": 10, - "cost_for_plan": 3.510986328 + "cost_for_plan": 0.01159965 } ] }, { "best_join_order": ["t2"], "rows": 10, - "cost": 3.510986328 + "cost": 0.01159965 }, { "attaching_conditions_to_tables": { @@ -836,8 +836,8 @@ explain select * from v1 { "table": "t1", "table_scan": { "rows": 10, - "read_cost": 1.010986328, - "read_and_compare_cost": 3.510986328 + "read_cost": 0.01127965, + "read_and_compare_cost": 0.01159965 } } ] @@ -857,9 +857,9 @@ explain select * from v1 { { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.510986328, + "rows_out": 10, + "cost": 0.01159965, "index_only": false, "chosen": true, "use_tmp_table": true @@ -867,9 +867,9 @@ explain select * from v1 { ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -880,15 +880,15 @@ explain select * from v1 { "plan_prefix": [], "table": "t1", "rows_for_plan": 10, - "cost_for_plan": 3.510986328, - "cost_for_sorting": 10 + "cost_for_plan": 0.01159965, + "cost_for_sorting": 0.006368384 } ] }, { "best_join_order": ["t1"], "rows": 10, - "cost": 13.51098633 + "cost": 0.017968034 }, { "attaching_conditions_to_tables": { @@ -920,8 +920,8 @@ explain select * from v1 { "table": "<derived2>", "table_scan": { "rows": 10, - "read_cost": 10, - "read_and_compare_cost": 12.5 + "read_cost": 0.012414166, + "read_and_compare_cost": 0.012757506 } } ] @@ -941,18 +941,18 @@ explain select * from v1 { { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 12.5, + "rows_out": 10, + "cost": 0.012757506, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 12.5, + "rows_read": 10, + "rows_out": 10, + "cost": 0.012757506, "uses_join_buffering": false } } @@ -963,14 +963,14 @@ explain select * from v1 { "plan_prefix": [], "table": "<derived2>", "rows_for_plan": 10, - "cost_for_plan": 12.5 + "cost_for_plan": 0.012757506 } ] }, { "best_join_order": ["<derived2>"], "rows": 10, - "cost": 12.5 + "cost": 0.012757506 }, { "attaching_conditions_to_tables": { @@ -1102,16 +1102,16 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "table": "t1", "table_scan": { "rows": 100, - "read_cost": 1.158691406, - "read_and_compare_cost": 26.15869141 + "read_cost": 0.0224761, + "read_and_compare_cost": 0.0256761 } }, { "table": "t2", "table_scan": { "rows": 100, - "read_cost": 1.158691406, - "read_and_compare_cost": 26.15869141 + "read_cost": 0.0224761, + "read_and_compare_cost": 0.0256761 } } ] @@ -1131,18 +1131,18 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "access_type": "scan", "rows": 100, - "rows_after_scan": 100, "rows_after_filter": 100, - "cost": 26.15869141, + "rows_out": 100, + "cost": 0.0256761, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 100, - "records_out": 100, - "cost": 26.15869141, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256761, "uses_join_buffering": false } } @@ -1157,18 +1157,18 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "access_type": "scan", "rows": 100, - "rows_after_scan": 100, "rows_after_filter": 100, - "cost": 26.15869141, + "rows_out": 100, + "cost": 0.0256761, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 100, - "records_out": 100, - "cost": 26.15869141, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256761, "uses_join_buffering": false } } @@ -1179,7 +1179,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "plan_prefix": [], "table": "t1", "rows_for_plan": 100, - "cost_for_plan": 26.15869141, + "cost_for_plan": 0.0256761, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -1197,24 +1197,24 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 125.0585794, + "cost": 0.1731074, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 100, - "rows_after_scan": 75, - "rows_after_filter": 75, - "cost": 1807.408691, + "rows_after_filter": 100, + "rows_out": 1, + "cost": 0.9604227, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 125.0585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.1731074, "uses_join_buffering": false } } @@ -1225,7 +1225,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "plan_prefix": ["t1"], "table": "t2", "rows_for_plan": 100, - "cost_for_plan": 151.2172709 + "cost_for_plan": 0.1987835 } ] }, @@ -1233,7 +1233,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "plan_prefix": [], "table": "t2", "rows_for_plan": 100, - "cost_for_plan": 26.15869141, + "cost_for_plan": 0.0256761, "rest_of_plan": [ { "plan_prefix": ["t2"], @@ -1251,24 +1251,24 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 125.0585794, + "cost": 0.1731074, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 100, - "rows_after_scan": 75, - "rows_after_filter": 75, - "cost": 1807.408691, + "rows_after_filter": 100, + "rows_out": 1, + "cost": 0.9604227, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 125.0585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.1731074, "uses_join_buffering": false } } @@ -1279,10 +1279,10 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "plan_prefix": ["t2"], "table": "t1", "rows_for_plan": 100, - "cost_for_plan": 151.2172709, + "cost_for_plan": 0.1987835, "pruned_by_cost": true, - "current_cost": 151.2172709, - "best_cost": 151.2173709 + "current_cost": 0.1987835, + "best_cost": 0.1987835 } ] } @@ -1291,7 +1291,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "best_join_order": ["t1", "t2"], "rows": 100, - "cost": 151.2172709 + "cost": 0.1987835 }, { "substitute_best_equal": { @@ -1330,10 +1330,11 @@ drop table t1,t2,t0; # group_by min max optimization # CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +insert into t1 select seq, mod(seq,4)+1 from seq_1_to_65536; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK +test.t1 analyze status Table is already up to date EXPLAIN SELECT DISTINCT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by @@ -1372,7 +1373,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "range_analysis": { "table_scan": { "rows": 65536, - "cost": 16457 + "cost": 10.29477568 }, "potential_range_indexes": [ { @@ -1388,7 +1389,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { ], "best_covering_index_scan": { "index": "a", - "cost": 14898.29141, + "cost": 9.123706862, "chosen": true }, "group_index_range": { @@ -1398,7 +1399,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "index": "a", "covering": true, "rows": 5, - "cost": 6.5 + "cost": 0.004191135 } ] }, @@ -1410,7 +1411,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.5, + "cost": 0.004191135, "key_parts_used_for_access": ["a"], "ranges": [], "chosen": true @@ -1424,12 +1425,12 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.5, + "cost": 0.004191135, "key_parts_used_for_access": ["a"], "ranges": [] }, "rows_for_plan": 5, - "cost_for_plan": 6.5, + "cost_for_plan": 0.004191135, "chosen": true } } @@ -1451,17 +1452,17 @@ EXPLAIN SELECT DISTINCT a FROM t1 { { "access_type": "index_merge", "rows": 5, - "rows_after_scan": 5, "rows_after_filter": 5, - "cost": 6.5, + "rows_out": 5, + "cost": 0.004191135, "chosen": true } ], "chosen_access_method": { "type": "index_merge", - "records_read": 5, - "records_out": 5, - "cost": 6.5, + "rows_read": 5, + "rows_out": 5, + "cost": 0.004191135, "uses_join_buffering": false } } @@ -1472,14 +1473,14 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "plan_prefix": [], "table": "t1", "rows_for_plan": 5, - "cost_for_plan": 6.5 + "cost_for_plan": 0.004191135 } ] }, { "best_join_order": ["t1"], "rows": 5, - "cost": 6.5 + "cost": 0.004191135 }, { "attaching_conditions_to_tables": { @@ -1515,10 +1516,13 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 20 NULL 7 Using where; Using index for group-by +set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; +id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 20 NULL 7 Using where; Using index select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { +set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "steps": [ { "join_preparation": { @@ -1574,7 +1578,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "range_analysis": { "table_scan": { "rows": 7, - "cost": 2.764526367 + "cost": 0.001130435 }, "potential_range_indexes": [ { @@ -1585,8 +1589,9 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "best_covering_index_scan": { "index": "a", - "cost": 2.084226263, - "chosen": true + "cost": 0.001758432, + "chosen": false, + "cause": "cost" }, "setup_range_conditions": [], "analyzing_range_alternatives": { @@ -1603,7 +1608,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "covering": true, "ranges": ["(2,3) <= (b,c) <= (2,3)"], "rows": 7, - "cost": 2.75 + "cost": 0.004425189 } ] }, @@ -1615,7 +1620,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "max_aggregate": false, "distinct_aggregate": false, "rows": 7, - "cost": 2.75, + "cost": 0.004425189, "key_parts_used_for_access": ["a", "b", "c"], "ranges": ["(2,3) <= (b,c) <= (2,3)"], "chosen": false, @@ -1656,9 +1661,9 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { { "access_type": "scan", "rows": 7, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 2.084226263, + "rows_out": 0.571428573, + "cost": 0.001758432, "index_only": true, "chosen": true, "use_tmp_table": true @@ -1666,9 +1671,9 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 0.571428573, - "cost": 2.084226263, + "rows_read": 1, + "rows_out": 0.571428573, + "cost": 0.001758432, "uses_join_buffering": false } } @@ -1678,16 +1683,19 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { { "plan_prefix": [], "table": "t1", - "rows_for_plan": 1, - "cost_for_plan": 2.084226263, - "cost_for_sorting": 1 + "rows_for_plan": 0.571428573, + "cost_for_plan": 0.001758432, + "pushdown_cond_selectivity": 0.571428573, + "filtered": 8.163265322, + "rows_out": 0.571428573, + "cost_for_sorting": 3.585611e-4 } ] }, { "best_join_order": ["t1"], - "rows": 1, - "cost": 3.084226263 + "rows": 0.571428573, + "cost": 0.002116993 }, { "substitute_best_equal": { @@ -1711,8 +1719,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "clause": "GROUP BY", "table": "t1", "rows_estimation": 1, - "read_cost": 2.147624763, - "filesort_cost": 0.0633985, + "filesort_cost": 4.579083e-5, + "read_cost": 0.001804223, "filesort_type": "priority_queue with addon fields", "fanout": 1, "possible_keys": [ @@ -1722,7 +1730,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "direction": 1, "rows_to_examine": 7, "range_scan": false, - "scan_cost": 2.084226263, + "scan_cost": 0.001667847, "chosen": true } ] @@ -1811,7 +1819,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "range_analysis": { "table_scan": { "rows": 16, - "cost": 5.015625 + "cost": 0.01253808 }, "potential_range_indexes": [ { @@ -1822,7 +1830,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.11171589, + "cost": 0.008002862, "chosen": true }, "setup_range_conditions": [], @@ -1840,7 +1848,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "covering": true, "ranges": ["(2001-01-04) <= (a)"], "rows": 9, - "cost": 3.25 + "cost": 0.005620843 } ] }, @@ -1852,7 +1860,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "max_aggregate": true, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"], "chosen": true @@ -1866,12 +1874,12 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "max_aggregate": true, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"] }, "rows_for_plan": 9, - "cost_for_plan": 3.25, + "cost_for_plan": 0.005620843, "chosen": true } } @@ -1893,18 +1901,18 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { { "access_type": "index_merge", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25, + "rows_out": 9, + "cost": 0.005620843, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", - "records_read": 9, - "records_out": 9, - "cost": 3.25, + "rows_read": 9, + "rows_out": 9, + "cost": 0.005620843, "uses_join_buffering": false } } @@ -1915,15 +1923,15 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "plan_prefix": [], "table": "t1", "rows_for_plan": 9, - "cost_for_plan": 3.25, - "cost_for_sorting": 9 + "cost_for_plan": 0.005620843, + "cost_for_sorting": 0.005728198 } ] }, { "best_join_order": ["t1"], "rows": 9, - "cost": 12.25 + "cost": 0.011349041 }, { "substitute_best_equal": { @@ -2014,7 +2022,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "range_analysis": { "table_scan": { "rows": 16, - "cost": 5.015625 + "cost": 0.01253808 }, "potential_range_indexes": [ { @@ -2025,7 +2033,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.11171589, + "cost": 0.008002862, "chosen": true }, "setup_range_conditions": [], @@ -2043,7 +2051,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "covering": true, "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "rows": 9, - "cost": 3.25 + "cost": 0.005620843 } ] }, @@ -2055,7 +2063,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "max_aggregate": false, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "chosen": true @@ -2069,12 +2077,12 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "max_aggregate": false, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"] }, "rows_for_plan": 9, - "cost_for_plan": 3.25, + "cost_for_plan": 0.005620843, "chosen": true } } @@ -2096,18 +2104,18 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { { "access_type": "index_merge", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25, + "rows_out": 9, + "cost": 0.005620843, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", - "records_read": 9, - "records_out": 9, - "cost": 3.25, + "rows_read": 9, + "rows_out": 9, + "cost": 0.005620843, "uses_join_buffering": false } } @@ -2118,15 +2126,15 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "plan_prefix": [], "table": "t1", "rows_for_plan": 9, - "cost_for_plan": 3.25, - "cost_for_sorting": 9 + "cost_for_plan": 0.005620843, + "cost_for_sorting": 0.005728198 } ] }, { "best_join_order": ["t1"], "rows": 9, - "cost": 12.25 + "cost": 0.011349041 }, { "substitute_best_equal": { @@ -2160,10 +2168,6 @@ drop table t1; # # Late ORDER BY optimization # -create table ten(a int); -insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table one_k(a int primary key); -insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table t1 ( pk int not null, a int, @@ -2174,19 +2178,18 @@ KEY c(c), KEY a_c(a,c), KEY a_b(a,b) ); -insert into t1 -select a, a,a,a, 'filler-dataaa' from test.one_k; +insert into t1 select seq, seq,seq,seq, 'filler-dataaa' from seq_0_to_999; update t1 set a=1 where pk between 0 and 180; update t1 set b=2 where pk between 0 and 20; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK -set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a_c,a_b a_b 10 const,const 21 Using where; Using filesort update t1 set b=2 where pk between 20 and 40; +set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a_c,a_b a_c 5 NULL 180 Using where @@ -2270,7 +2273,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "range_analysis": { "table_scan": { "rows": 1000, - "cost": 265.2822266 + "cost": 0.1731718 }, "potential_range_indexes": [ { @@ -2299,8 +2302,9 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 135.6693776, - "chosen": true + "cost": 0.223346519, + "chosen": false, + "cause": "cost" }, { "index": "a_b", @@ -2309,7 +2313,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 41, - "cost": 31.3040249, + "cost": 0.051838728, "chosen": true } ], @@ -2330,7 +2334,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1,2) <= (a,b) <= (1,2)"] }, "rows_for_plan": 41, - "cost_for_plan": 31.3040249, + "cost_for_plan": 0.051838728, "chosen": true } } @@ -2340,12 +2344,12 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "rowid_filters": [ { "key": "a_b", - "build_cost": 1.752281351, + "build_cost": 6.9153e-4, "rows": 41 }, { "key": "a_c", - "build_cost": 6.264109827, + "build_cost": 0.0040552, "rows": 180 } ] @@ -2390,7 +2394,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_c", "used_range_estimates": true, "rows": 180, - "cost": 135.6493776, + "cost": 0.222796377, "chosen": true }, { @@ -2398,7 +2402,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_b", "used_range_estimates": true, "rows": 41, - "cost": 31.2840249, + "cost": 0.051288586, "chosen": true }, { @@ -2409,9 +2413,9 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { ], "chosen_access_method": { "type": "ref", - "records_read": 41, - "records_out": 41, - "cost": 31.2840249, + "rows_read": 41, + "rows_out": 41, + "cost": 0.051288586, "uses_join_buffering": false } } @@ -2422,14 +2426,14 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "plan_prefix": [], "table": "t1", "rows_for_plan": 41, - "cost_for_plan": 31.2840249 + "cost_for_plan": 0.051288586 } ] }, { "best_join_order": ["t1"], "rows": 41, - "cost": 31.2840249 + "cost": 0.051288586 }, { "substitute_best_equal": { @@ -2453,8 +2457,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "clause": "ORDER BY", "table": "t1", "rows_estimation": 41, - "read_cost": 32.58369415, - "filesort_cost": 1.299669251, + "filesort_cost": 9.387121e-4, + "read_cost": 0.052227298, "filesort_type": "priority_queue with addon fields", "fanout": 1, "possible_keys": [ @@ -2464,7 +2468,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "direction": 1, "rows_to_examine": 24, "range_scan": false, - "scan_cost": 18.51405907, + "scan_cost": 0.030312813, "chosen": true }, { @@ -2473,7 +2477,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "direction": 1, "rows_to_examine": 4.390243902, "range_scan": true, - "scan_cost": 11.5484164, + "scan_cost": 0.023380552, "chosen": true }, { @@ -2487,10 +2491,6 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { { "table": "t1", "range_analysis": { - "table_scan": { - "rows": 1000, - "cost": 1.79769e308 - }, "potential_range_indexes": [ { "index": "c", @@ -2518,7 +2518,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 135.6693776, + "cost": 0.223346519, + "cost_with_limit": 0.002483968, "chosen": true } ], @@ -2539,7 +2540,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1) <= (a) <= (1)"] }, "rows_for_plan": 180, - "cost_for_plan": 135.6693776, + "cost_for_plan": 0.223346519, "chosen": true } } @@ -2555,7 +2556,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { } ] } 0 0 -drop table t1,ten,one_k; +drop table t1; # # TABLE ELIMINATION # @@ -2646,8 +2647,8 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { @@ -2673,18 +2674,18 @@ select t1.a from t1 left join t2 on t1.a=t2.a { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01066122, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -2695,14 +2696,14 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "plan_prefix": ["t2"], "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01066122 } ] }, { "best_join_order": ["t2", "t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01066122 }, { "substitute_best_equal": { @@ -2802,16 +2803,16 @@ explain select * from t1 left join t2 on t2.a=t1.a { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { "table": "t2", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01028441, + "read_and_compare_cost": 0.01034841 } } ] @@ -2831,18 +2832,18 @@ explain select * from t1 left join t2 on t2.a=t1.a { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01066122, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -2853,7 +2854,7 @@ explain select * from t1 left join t2 on t2.a=t1.a { "plan_prefix": [], "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.003417969, + "cost_for_plan": 0.01066122, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -2869,24 +2870,20 @@ explain select * from t1 left join t2 on t2.a=t1.a { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, - "cost": 5.002147913, + "cost": 0.007120904, "chosen": true }, { - "access_type": "scan", - "rows": 2, - "rows_after_scan": 1.5, - "rows_after_filter": 1.5, - "cost": 6.008789062, - "index_only": false, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", - "records_read": 1, - "records_out": 1, - "cost": 5.002147913, + "rows_read": 1, + "rows_out": 1, + "cost": 0.007120904, "uses_join_buffering": false } } @@ -2897,7 +2894,7 @@ explain select * from t1 left join t2 on t2.a=t1.a { "plan_prefix": ["t1"], "table": "t2", "rows_for_plan": 4, - "cost_for_plan": 7.005565882 + "cost_for_plan": 0.017782124 } ] } @@ -2906,7 +2903,7 @@ explain select * from t1 left join t2 on t2.a=t1.a { { "best_join_order": ["t1", "t2"], "rows": 4, - "cost": 7.005565882 + "cost": 0.017782124 }, { "substitute_best_equal": { @@ -3043,8 +3040,8 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { @@ -3076,18 +3073,18 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01066122, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -3098,14 +3095,14 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "plan_prefix": ["t3", "t2"], "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01066122 } ] }, { "best_join_order": ["t3", "t2", "t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01066122 }, { "substitute_best_equal": { @@ -3143,33 +3140,27 @@ drop table t0, t1, t2, t3; # # IN subquery to sem-join is traced # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); -insert into t1 values (0,0),(1,1),(2,2); -create table t2 as select * from t1; -create table t11(a int, b int); -create table t10 (pk int, a int); -insert into t10 select a,a from t0; -create table t12 like t10; -insert into t12 select * from t10; -analyze table t1,t10; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK +insert into t1 select seq,seq from seq_0_to_3; +create table t2 (p int, a int); +insert into t2 select seq,seq from seq_1_to_10; set optimizer_trace='enabled=on'; -explain extended select * from t1 where a in (select pk from t10); +explain extended select * from t1 where a in (select p from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a` +insert into t2 select seq,seq from seq_10_to_100; +explain extended select * from t1 where a in (select p from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t10 ALL NULL NULL NULL NULL 10 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t10`) where 1 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a` select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -explain extended select * from t1 where a in (select pk from t10) { +explain extended select * from t1 where a in (select p from t2) { "steps": [ { "join_preparation": { @@ -3188,13 +3179,13 @@ explain extended select * from t1 where a in (select pk from t10) { } }, { - "expanded_query": "/* select#2 */ select t10.pk from t10" + "expanded_query": "/* select#2 */ select t2.p from t2" } ] } }, { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)" + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t2.p from t2)" } ] } @@ -3223,19 +3214,19 @@ explain extended select * from t1 where a in (select pk from t10) { { "condition_processing": { "condition": "WHERE", - "original_condition": "1 and t1.a = t10.pk", + "original_condition": "1 and t1.a = t2.p", "steps": [ { "transformation": "equality_propagation", - "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + "resulting_condition": "1 and multiple equal(t1.a, t2.p)" }, { "transformation": "constant_propagation", - "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + "resulting_condition": "1 and multiple equal(t1.a, t2.p)" }, { "transformation": "trivial_condition_removal", - "resulting_condition": "multiple equal(t1.a, t10.pk)" + "resulting_condition": "multiple equal(t1.a, t2.p)" } ] } @@ -3249,7 +3240,7 @@ explain extended select * from t1 where a in (select pk from t10) { "depends_on_map_bits": [] }, { - "table": "t10", + "table": "t2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] @@ -3264,17 +3255,17 @@ explain extended select * from t1 where a in (select pk from t10) { { "table": "t1", "table_scan": { - "rows": 3, - "read_cost": 1.003295898, - "read_and_compare_cost": 1.753295898 + "rows": 4, + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } }, { - "table": "t10", + "table": "t2", "table_scan": { - "rows": 10, - "read_cost": 1.010986328, - "read_and_compare_cost": 3.510986328 + "rows": 101, + "read_cost": 0.022600505, + "read_and_compare_cost": 0.025832505 } } ] @@ -3294,26 +3285,26 @@ explain extended select * from t1 where a in (select pk from t10) { "get_costs_for_tables": [ { "best_access_path": { - "table": "t10", + "table": "t2", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", - "rows": 10, - "rows_after_scan": 10, - "rows_after_filter": 10, - "cost": 3.510986328, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.025832505, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 101, + "rows_out": 101, + "cost": 0.025832505, "uses_join_buffering": false } } @@ -3322,9 +3313,9 @@ explain extended select * from t1 where a in (select pk from t10) { }, { "plan_prefix": [], - "table": "t10", - "rows_for_plan": 10, - "cost_for_plan": 3.510986328 + "table": "t2", + "rows_for_plan": 101, + "cost_for_plan": 0.025832505 } ] } @@ -3345,45 +3336,45 @@ explain extended select * from t1 where a in (select pk from t10) { "considered_access_paths": [ { "access_type": "scan", - "rows": 3, - "rows_after_scan": 3, - "rows_after_filter": 3, - "cost": 1.753295898, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01066122, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.753295898, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } }, { "best_access_path": { - "table": "t10", + "table": "t2", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", - "rows": 10, - "rows_after_scan": 10, - "rows_after_filter": 10, - "cost": 3.510986328, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.025832505, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 101, + "rows_out": 101, + "cost": 0.025832505, "uses_join_buffering": false } } @@ -3393,8 +3384,8 @@ explain extended select * from t1 where a in (select pk from t10) { { "plan_prefix": [], "table": "t1", - "rows_for_plan": 3, - "cost_for_plan": 1.753295898, + "rows_for_plan": 4, + "cost_for_plan": 0.01066122, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -3402,26 +3393,26 @@ explain extended select * from t1 where a in (select pk from t10) { "get_costs_for_tables": [ { "best_access_path": { - "table": "t10", + "table": "t2", "plan_details": { - "record_count": 3 + "record_count": 4 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", - "rows": 10, - "rows_after_scan": 10, - "rows_after_filter": 10, - "cost": 10.63598633, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.063593833, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 10.63598633, + "rows_read": 101, + "rows_out": 101, + "cost": 0.063593833, "uses_join_buffering": true } } @@ -3430,40 +3421,46 @@ explain extended select * from t1 where a in (select pk from t10) { }, { "plan_prefix": ["t1"], - "table": "t10", - "rows_for_plan": 30, - "cost_for_plan": 12.38928223, + "table": "t2", + "rows_for_plan": 404, + "cost_for_plan": 0.074255053, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 12.38928223 + "rows": 4, + "cost": 0.074255053 }, { "strategy": "SJ-Materialization", - "records": 3, - "cost": 8.664282227 + "rows": 4, + "cost": 0.078768645 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 12.38928223, - "write_cost": 2.5, - "full_lookup_cost": 15, - "total_cost": 29.88928223 + "prefix_row_count": 4, + "tmp_table_rows": 1, + "sj_inner_fanout": 101, + "rows": 4, + "dups_cost": 0.074255053, + "write_cost": 0.02564388, + "full_lookup_cost": 0.06503188, + "total_cost": 0.164930813 }, { - "chosen_strategy": "SJ-Materialization" + "chosen_strategy": "FirstMatch" } - ] + ], + "sj_rows_out": 1, + "sj_rows_for_plan": 4, + "sj_filtered": 0.99009901 } ] }, { "plan_prefix": [], - "table": "t10", - "rows_for_plan": 10, - "cost_for_plan": 3.510986328, + "table": "t2", + "rows_for_plan": 101, + "cost_for_plan": 0.025832505, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -3472,31 +3469,51 @@ explain extended select * from t1 where a in (select pk from t10) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "SJ-Materialization", + "semi_join_strategy": "FirstMatch", "join_order": [ { - "table": "t10" + "table": "t2", + "best_access_path": { + "table": "t2", + "plan_details": { + "record_count": 4 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.10333002, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 101, + "rows_out": 101, + "cost": 0.10333002, + "uses_join_buffering": false + } + } } ] } ] }, { - "best_join_order": ["t1", "<subquery2>"], - "rows": 3, - "cost": 8.664282227 + "best_join_order": ["t1", "t2"], + "rows": 4, + "cost": 0.074255053 }, { "substitute_best_equal": { "condition": "WHERE", - "resulting_condition": "1" + "resulting_condition": "t2.p = t1.a" } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ @@ -3505,12 +3522,8 @@ explain extended select * from t1 where a in (select pk from t10) { "attached": null }, { - "table": "t10", - "attached": null - }, - { - "table": "<subquery2>", - "attached": null + "table": "t2", + "attached": "t2.p = t1.a" } ] } @@ -3526,12 +3539,10 @@ explain extended select * from t1 where a in (select pk from t10) { } ] } 0 0 -drop table t0,t1,t11,t10,t12,t2; +drop table t1,t2; # # Selectivities for columns and indexes. # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( pk int, a int, @@ -3539,7 +3550,7 @@ b int, key pk(pk), key pk_a(pk,a), key pk_a_b(pk,a,b)); -insert into t1 select a,a,a from t0; +insert into t1 select seq,seq,seq from seq_0_to_9; ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES (); Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -3653,7 +3664,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.515869141 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -3674,7 +3685,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "best_covering_index_scan": { "index": "pk_a_b", - "cost": 2.760739566, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -3687,7 +3698,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270585794, + "cost": 0.002483968, "chosen": true }, { @@ -3697,7 +3708,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270829876, + "cost": 0.002483968, "chosen": false, "cause": "cost" }, @@ -3708,7 +3719,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.746073957, + "cost": 0.001388369, "chosen": true } ], @@ -3716,10 +3727,10 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "intersecting_indexes": [ { "index": "pk", - "index_scan_cost": 0.525585794, - "cumulated_index_scan_cost": 0.525585794, - "disk_sweep_cost": 0.75, - "cumulative_total_cost": 1.275585794, + "index_scan_cost": 0.000806227, + "cumulated_index_scan_cost": 0.000806227, + "disk_sweep_cost": 0.001143284, + "cumulative_total_cost": 0.001949511, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, @@ -3757,7 +3768,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"] }, "rows_for_plan": 1, - "cost_for_plan": 0.746073957, + "cost_for_plan": 0.001388369, "chosen": true } } @@ -3767,17 +3778,17 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "rowid_filters": [ { "key": "pk", - "build_cost": 0.526585794, + "build_cost": 0.000002653, "rows": 1 }, { "key": "pk_a", - "build_cost": 0.526829876, + "build_cost": 0.000002653, "rows": 1 }, { "key": "pk_a_b", - "build_cost": 0.527073957, + "build_cost": 0.000002653, "rows": 1 } ] @@ -3822,7 +3833,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk", "used_range_estimates": true, "rows": 1, - "cost": 1.250585794, + "cost": 0.001933826, "chosen": true }, { @@ -3830,7 +3841,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a", "used_range_estimates": true, "rows": 1, - "cost": 1.250829876, + "cost": 0.001933826, "chosen": false, "cause": "cost" }, @@ -3839,7 +3850,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a_b", "used_range_estimates": true, "rows": 1, - "cost": 0.726073957, + "cost": 0.000838227, "chosen": true }, { @@ -3850,9 +3861,9 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 0.726073957, + "rows_read": 1, + "rows_out": 1, + "cost": 0.000838227, "uses_join_buffering": false } } @@ -3863,14 +3874,14 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "plan_prefix": [], "table": "t1", "rows_for_plan": 1, - "cost_for_plan": 0.726073957 + "cost_for_plan": 0.000838227 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 0.726073957 + "cost": 0.000838227 }, { "substitute_best_equal": { @@ -3902,7 +3913,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { } 0 0 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables= @save_use_stat_tables; -drop table t0,t1; +drop table t1; set optimizer_trace="enabled=off"; # # Tests added to show that sub-statements are not traced @@ -3966,8 +3977,8 @@ select f1(a) from t1 { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } } ] @@ -3987,18 +3998,18 @@ select f1(a) from t1 { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01066122, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -4009,14 +4020,14 @@ select f1(a) from t1 { "plan_prefix": [], "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01066122 } ] }, { "best_join_order": ["t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01066122 }, { "attaching_conditions_to_tables": { @@ -4080,8 +4091,8 @@ select f2(a) from t1 { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01053322, + "read_and_compare_cost": 0.01066122 } } ] @@ -4101,18 +4112,18 @@ select f2(a) from t1 { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01066122, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01066122, "uses_join_buffering": false } } @@ -4123,14 +4134,14 @@ select f2(a) from t1 { "plan_prefix": [], "table": "t1", "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01066122 } ] }, { "best_join_order": ["t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01066122 }, { "attaching_conditions_to_tables": { @@ -4171,7 +4182,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2766 +2747 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -4185,7 +4196,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2666 0 + 2647 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -4193,7 +4204,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2766 0 +select * from t1 2747 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -4218,7 +4229,7 @@ explain delete from t0 where t0.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.510986328 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -4237,7 +4248,7 @@ explain delete from t0 where t0.a<3 { "using_mrr": false, "index_only": false, "rows": 3, - "cost": 2.771757383, + "cost": 0.004951706, "chosen": true } ], @@ -4255,7 +4266,7 @@ explain delete from t0 where t0.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 2.771757383, + "cost_for_plan": 0.004951706, "chosen": true } } @@ -4358,7 +4369,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.510986328 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -4369,7 +4380,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 2.755857945, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -4382,7 +4393,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 1.196757383, + "cost": 0.001664909, "chosen": true } ], @@ -4403,7 +4414,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "chosen": true } } @@ -4423,7 +4434,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.510986328 + "cost": 0.01159965 }, "potential_range_indexes": [ { @@ -4434,7 +4445,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 2.755857945, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -4447,7 +4458,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 1.196757383, + "cost": 0.001664909, "chosen": true } ], @@ -4468,7 +4479,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "chosen": true } } @@ -4499,18 +4510,19 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "considered_access_paths": [ { "access_type": "range", + "range_index": "a", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.196757383, + "rows_out": 3, + "cost": 0.001664909, "chosen": true } ], "chosen_access_method": { "type": "range", - "records_read": 3, - "records_out": 3, - "cost": 1.196757383, + "rows_read": 3, + "rows_out": 3, + "cost": 0.001664909, "uses_join_buffering": false } } @@ -4524,18 +4536,19 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "considered_access_paths": [ { "access_type": "range", + "range_index": "a", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.196757383, + "rows_out": 3, + "cost": 0.001664909, "chosen": true } ], "chosen_access_method": { "type": "range", - "records_read": 3, - "records_out": 3, - "cost": 1.196757383, + "rows_read": 3, + "rows_out": 3, + "cost": 0.001664909, "uses_join_buffering": false } } @@ -4546,7 +4559,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "plan_prefix": [], "table": "t0", "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "rest_of_plan": [ { "plan_prefix": ["t0"], @@ -4564,7 +4577,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "reason": "not better than ref estimates", "rows": 1, - "cost": 2.176757383, + "cost": 0.002105081, "chosen": true }, { @@ -4575,9 +4588,9 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 2.176757383, + "rows_read": 1, + "rows_out": 1, + "cost": 0.002105081, "uses_join_buffering": false } } @@ -4588,7 +4601,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "plan_prefix": ["t0"], "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 3.373514767 + "cost_for_plan": 0.00376999 } ] }, @@ -4596,7 +4609,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "plan_prefix": [], "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -4615,7 +4628,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "reason": "not better than ref estimates", "rows": 2, - "cost": 2.853514767, + "cost": 0.002519891, "chosen": true }, { @@ -4626,9 +4639,9 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records_read": 2, - "records_out": 1.166666667, - "cost": 2.853514767, + "rows_read": 2, + "rows_out": 2, + "cost": 0.002519891, "uses_join_buffering": false } } @@ -4639,10 +4652,10 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "plan_prefix": ["t1"], "table": "t0", "rows_for_plan": 6, - "cost_for_plan": 4.05027215, + "cost_for_plan": 0.0041848, "pruned_by_cost": true, - "current_cost": 4.05027215, - "best_cost": 3.373614767 + "current_cost": 0.0041848, + "best_cost": 0.00376999 } ] } @@ -4651,7 +4664,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "best_join_order": ["t0", "t1"], "rows": 3, - "cost": 3.373514767 + "cost": 0.00376999 }, { "substitute_best_equal": { @@ -4760,8 +4773,8 @@ explain select * from (select rand() from t1)q { "table": "t1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -4781,18 +4794,18 @@ explain select * from (select rand() from t1)q { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -4803,14 +4816,14 @@ explain select * from (select rand() from t1)q { "plan_prefix": [], "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 1.752563477 + "cost_for_plan": 0.010504815 } ] }, { "best_join_order": ["t1"], "rows": 3, - "cost": 1.752563477 + "cost": 0.010504815 }, { "attaching_conditions_to_tables": { @@ -4842,8 +4855,8 @@ explain select * from (select rand() from t1)q { "table": "<derived2>", "table_scan": { "rows": 3, - "read_cost": 3, - "read_and_compare_cost": 3.75 + "read_cost": 0.01235805, + "read_and_compare_cost": 0.012461052 } } ] @@ -4863,18 +4876,18 @@ explain select * from (select rand() from t1)q { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.75, + "rows_out": 3, + "cost": 0.012461052, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.75, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012461052, "uses_join_buffering": false } } @@ -4885,14 +4898,14 @@ explain select * from (select rand() from t1)q { "plan_prefix": [], "table": "<derived2>", "rows_for_plan": 3, - "cost_for_plan": 3.75 + "cost_for_plan": 0.012461052 } ] }, { "best_join_order": ["<derived2>"], "rows": 3, - "cost": 3.75 + "cost": 0.012461052 }, { "attaching_conditions_to_tables": { @@ -5044,24 +5057,24 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "table": "t1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_2", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -5089,18 +5102,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5115,18 +5128,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5137,7 +5150,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], @@ -5152,18 +5165,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011523207, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -5174,7 +5187,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "plan_prefix": ["t_inner_1"], "table": "t_inner_2", "rows_for_plan": 9, - "cost_for_plan": 5.642626953 + "cost_for_plan": 0.022028022 } ] }, @@ -5182,7 +5195,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "pruned_by_heuristic": true } ] @@ -5205,18 +5218,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5231,18 +5244,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5257,18 +5270,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5279,7 +5292,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "plan_prefix": [], "table": "t1", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5295,18 +5308,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011523207, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -5321,18 +5334,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011523207, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -5343,7 +5356,7 @@ 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_1", "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5359,18 +5372,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 9.177563477, + "rows_out": 3, + "cost": 0.015203373, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 9.177563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015203373, "uses_join_buffering": true } } @@ -5381,30 +5394,36 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "plan_prefix": ["t1", "t_inner_1"], "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 14.82019043, + "cost_for_plan": 0.037231395, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 22.7833252 + "rows": 3, + "cost": 0.136562595 }, { "strategy": "SJ-Materialization", - "records": 3, - "cost": 10.64519043 + "rows": 3, + "cost": 0.059588485 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 14.82019043, - "write_cost": 1.45, - "full_lookup_cost": 4.05, - "total_cost": 20.32019043 + "prefix_row_count": 3, + "tmp_table_rows": 1, + "sj_inner_fanout": 9, + "rows": 3, + "dups_cost": 0.037231395, + "write_cost": 0.02548291, + "full_lookup_cost": 0.00434619, + "total_cost": 0.067060495 }, { "chosen_strategy": "SJ-Materialization" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 3, + "sj_filtered": 11.11111111 } ] }, @@ -5412,7 +5431,7 @@ 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", "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5422,7 +5441,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -5430,7 +5449,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5454,7 +5473,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_join_order": ["t1", "<subquery2>"], "rows": 3, - "cost": 10.64519043 + "cost": 0.059588485 }, { "substitute_best_equal": { @@ -5506,11 +5525,11 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 FirstMatch(t_outer_1); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Start temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; FirstMatch(t_outer_2); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; End temporary; Using join buffer (incremental, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and @@ -5669,48 +5688,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "table": "t_outer_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -5745,18 +5764,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5771,18 +5790,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5797,18 +5816,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -5823,18 +5842,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -5849,18 +5868,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -5875,18 +5894,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -5897,7 +5916,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": [], "table": "t_outer_1", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5913,18 +5932,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011523207, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -5939,18 +5958,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -5965,18 +5984,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -5991,18 +6010,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011523207, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -6017,18 +6036,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -6039,7 +6058,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"], "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6055,18 +6074,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 70.08269043, + "rows_out": 9, + "cost": 0.050443503, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 70.08269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -6081,18 +6100,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 24.02756348, + "rows_out": 3, + "cost": 0.024600489, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 24.02756348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -6107,18 +6126,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 24.02756348, + "rows_out": 3, + "cost": 0.024600489, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 24.02756348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -6133,18 +6152,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 70.08269043, + "rows_out": 9, + "cost": 0.050443503, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 70.08269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -6155,7 +6174,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_outer_2"], "table": "t_inner_1", "rows_for_plan": 81, - "cost_for_plan": 35.45031738, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6171,18 +6190,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -6197,18 +6216,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 77.69006348, + "rows_out": 3, + "cost": 0.067582275, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 77.69006348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, "uses_join_buffering": true } } @@ -6223,18 +6242,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -6245,25 +6264,31 @@ 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"], "table": "t_inner_2", "rows_for_plan": 729, - "cost_for_plan": 266.5205078, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 322.6148926 + "rows": 27, + "cost": 1.23517089 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 266.5205078, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 380.9205078 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.222053862, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.368747182 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "rest_of_plan": [ { "plan_prefix": [ @@ -6283,18 +6308,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 30.10256348, + "rows_out": 3, + "cost": 0.034460781, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 30.10256348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -6309,18 +6334,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 88.30769043, + "rows_out": 9, + "cost": 0.080024379, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 88.30769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -6336,7 +6361,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 352.7174561, + "cost_for_plan": 0.403207963, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6358,18 +6383,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261557961, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -6386,25 +6411,31 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 638.4626465, + "cost_for_plan": 0.664765924, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 633.8070313 + "rows": 27, + "cost": 1.579280032 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 638.4626465, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 752.8626465 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.664765924, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.811459244 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, @@ -6417,7 +6448,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 410.922583, + "cost_for_plan": 0.448771561, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -6427,7 +6458,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_outer_2", "t_inner_1"], "table": "t_inner_4", "rows_for_plan": 243, - "cost_for_plan": 113.1403809, + "cost_for_plan": 0.116820804, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6448,18 +6479,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628673451, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -6474,18 +6505,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628673451, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -6501,11 +6532,86 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_2", "rows_for_plan": 2187, - "cost_for_plan": 881.8480713, + "cost_for_plan": 0.745494255, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 881.8480713, - "best_cost": 633.8071312 + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_outer_2", + "t_inner_1", + "t_inner_4", + "t_inner_2" + ], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 2187 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 6.764540577, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 6.764540577, + "uses_join_buffering": true + } + } + } + ] + }, + { + "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": 7.510034832, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "rows": 27, + "cost": 28.96624341 + }, + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 729, + "rows": 27, + "dups_cost": 7.510034832, + "write_cost": 0.02934619, + "full_lookup_cost": 3.16837251, + "total_cost": 10.70775353 + }, + { + "chosen_strategy": "FirstMatch" + } + ], + "sj_rows_out": 0.012345679, + "sj_rows_for_plan": 27, + "sj_filtered": 0.137174211, + "pruned_by_cost": true, + "current_cost": 28.96624341, + "best_cost": 0.811459244 + } + ] }, { "plan_prefix": [ @@ -6516,11 +6622,9 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 2187, - "cost_for_plan": 881.8480713, + "cost_for_plan": 0.745494255, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 881.8480713, - "best_cost": 633.8071312 + "pruned_by_heuristic": true } ] }, @@ -6528,7 +6632,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_outer_2", "t_inner_1"], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 266.5205078, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [], "pruned_by_heuristic": "min_read_time" } @@ -6538,7 +6642,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_outer_2"], "table": "t_inner_2", "rows_for_plan": 243, - "cost_for_plan": 81.50544434, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6546,7 +6650,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_outer_2"], "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 35.45031738, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6554,7 +6658,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_outer_2"], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 81.50544434, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -6564,7 +6668,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"], "table": "t_inner_1", "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6580,18 +6684,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024443331, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -6606,18 +6710,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024443331, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -6632,18 +6736,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 9.177563477, + "rows_out": 3, + "cost": 0.015203373, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 9.177563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015203373, "uses_join_buffering": true } } @@ -6658,18 +6762,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024443331, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -6680,7 +6784,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", "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6696,18 +6800,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -6722,18 +6826,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 77.69006348, + "rows_out": 3, + "cost": 0.067582275, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 77.69006348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, "uses_join_buffering": true } } @@ -6748,18 +6852,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -6770,20 +6874,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_2", "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219286686, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 262.2455078, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 376.6455078 + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.219286686, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.365980006 }, { "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "rest_of_plan": [ { "plan_prefix": [ @@ -6803,18 +6913,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 30.10256348, + "rows_out": 3, + "cost": 0.034460781, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 30.10256348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -6829,18 +6939,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 88.30769043, + "rows_out": 9, + "cost": 0.080024379, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 88.30769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -6856,7 +6966,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 406.7480713, + "cost_for_plan": 0.400440787, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6878,18 +6988,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261557961, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -6906,28 +7016,31 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 692.4932617, + "cost_for_plan": 0.661998748, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 687.8376465 + "rows": 27, + "cost": 1.576512856 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 692.4932617, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 806.8932617 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.661998748, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.808692068 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } ], - "pruned_by_cost": true, - "current_cost": 687.8376465, - "best_cost": 633.8071312 + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, @@ -6940,7 +7053,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 464.9531982, + "cost_for_plan": 0.446004385, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -6950,7 +7063,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", "t_outer_2"], "table": "t_inner_4", "rows_for_plan": 243, - "cost_for_plan": 108.8653809, + "cost_for_plan": 0.114053628, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6971,18 +7084,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628673451, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -6997,18 +7110,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628673451, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -7024,11 +7137,81 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_2", "rows_for_plan": 2187, - "cost_for_plan": 877.5730713, + "cost_for_plan": 0.742727079, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 877.5730713, - "best_cost": 633.8071312 + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4", + "t_inner_2" + ], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 2187 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 6.764540577, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 6.764540577, + "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": 7.507267656, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 729, + "rows": 27, + "dups_cost": 7.507267656, + "write_cost": 0.02934619, + "full_lookup_cost": 3.16837251, + "total_cost": 10.70498636 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "sj_rows_out": 0.012345679, + "sj_rows_for_plan": 27, + "sj_filtered": 0.137174211, + "pruned_by_cost": true, + "current_cost": 10.70498636, + "best_cost": 0.808692068 + } + ] }, { "plan_prefix": [ @@ -7039,11 +7222,9 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 2187, - "cost_for_plan": 877.5730713, + "cost_for_plan": 0.742727079, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 877.5730713, - "best_cost": 633.8071312 + "pruned_by_heuristic": true } ] }, @@ -7051,7 +7232,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", "t_outer_2"], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219286686, "semijoin_strategy_choice": [], "pruned_by_heuristic": "min_read_time" } @@ -7061,25 +7242,31 @@ 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_2", "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 36.32946777 + "rows": 3, + "cost": 0.145008465 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 31.17531738, - "write_cost": 1.45, - "full_lookup_cost": 12.15, - "total_cost": 44.77531738 + "prefix_row_count": 3, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 3, + "dups_cost": 0.046471353, + "write_cost": 0.02548291, + "full_lookup_cost": 0.01303857, + "total_cost": 0.084992833 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 3, + "sj_filtered": 3.703703704, "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -7094,18 +7281,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017419989, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -7120,18 +7307,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 4.565063477, + "rows_out": 3, + "cost": 0.012618795, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 4.565063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012618795, "uses_join_buffering": true } } @@ -7146,18 +7333,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017419989, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -7168,7 +7355,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", "t_inner_2"], "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 48.0246582, + "cost_for_plan": 0.102412822, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7189,18 +7376,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 30.10256348, + "rows_out": 3, + "cost": 0.034460781, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 30.10256348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -7215,18 +7402,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 88.30769043, + "rows_out": 9, + "cost": 0.080024379, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 88.30769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -7242,7 +7429,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 78.12722168, + "cost_for_plan": 0.136873603, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7264,18 +7451,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261557961, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -7292,25 +7479,31 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 363.8724121, + "cost_for_plan": 0.398431564, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 359.2167969 + "rows": 27, + "cost": 1.312945672 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 363.8724121, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 478.2724121 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.398431564, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.545124884 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, @@ -7323,7 +7516,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 136.3323486, + "cost_for_plan": 0.182437201, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7333,7 +7526,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", "t_inner_2"], "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 40.89453125, + "cost_for_plan": 0.097611628, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7354,18 +7547,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034303623, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -7380,18 +7573,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034303623, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -7407,7 +7600,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_outer_2", "rows_for_plan": 81, - "cost_for_plan": 72.50222168, + "cost_for_plan": 0.131915251, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7429,18 +7622,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261557961, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -7457,23 +7650,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 358.2474121, + "cost_for_plan": 0.393473212, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 358.2474121, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 472.6474121 + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.393473212, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.540166532 }, { "chosen_strategy": "DuplicateWeedout" } ], - "pruned_by_cost": true, - "current_cost": 472.6474121, - "best_cost": 359.2168969 + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, @@ -7486,7 +7682,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 81, - "cost_for_plan": 72.50222168, + "cost_for_plan": 0.131915251, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7496,7 +7692,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", "t_inner_2"], "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 48.0246582, + "cost_for_plan": 0.102412822, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7506,7 +7702,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_inner_4", "rows_for_plan": 27, - "cost_for_plan": 14.82019043, + "cost_for_plan": 0.037231395, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7522,18 +7718,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065233941, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -7548,18 +7744,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065233941, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -7574,18 +7770,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065233941, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -7596,7 +7792,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", "t_inner_4"], "table": "t_outer_2", "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7617,18 +7813,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628673451, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -7643,18 +7839,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628673451, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, "uses_join_buffering": true } } @@ -7670,11 +7866,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_2", "rows_for_plan": 2187, - "cost_for_plan": 862.7230713, + "cost_for_plan": 0.731138787, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 862.7230713, - "best_cost": 359.2168969 + "current_cost": 0.731138787, + "best_cost": 0.540166532 }, { "plan_prefix": [ @@ -7685,11 +7881,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 2187, - "cost_for_plan": 862.7230713, + "cost_for_plan": 0.731138787, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 862.7230713, - "best_cost": 359.2168969 + "current_cost": 0.731138787, + "best_cost": 0.540166532 } ] }, @@ -7697,7 +7893,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", "t_inner_4"], "table": "t_inner_2", "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -7705,7 +7901,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", "t_inner_4"], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7715,7 +7911,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_inner_3", "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7725,7 +7921,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"], "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -7733,7 +7929,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"], "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -7741,7 +7937,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"], "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7751,7 +7947,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": [], "table": "t_outer_2", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -7759,7 +7955,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -7767,7 +7963,7 @@ 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", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -7775,7 +7971,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -7783,7 +7979,7 @@ 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", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7792,122 +7988,10 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "FirstMatch", - "join_order": [ - { - "table": "t_inner_4", - "best_access_path": { - "table": "t_inner_4", - "plan_details": { - "record_count": 27 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 3, - "rows_after_scan": 3, - "rows_after_filter": 3, - "cost": 47.31921387, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 47.31921387, - "uses_join_buffering": false - } - } - }, - { - "table": "t_inner_3", - "best_access_path": { - "table": "t_inner_3", - "plan_details": { - "record_count": 81 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 9, - "rows_after_scan": 9, - "rows_after_filter": 9, - "cost": 263.8729248, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 263.8729248, - "uses_join_buffering": false - } - } - } - ] + "semi_join_strategy": "DuplicateWeedout" }, { - "semi_join_strategy": "FirstMatch", - "join_order": [ - { - "table": "t_inner_1", - "best_access_path": { - "table": "t_inner_1", - "plan_details": { - "record_count": 3 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 3, - "rows_after_scan": 3, - "rows_after_filter": 3, - "cost": 5.25769043, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 5.25769043, - "uses_join_buffering": false - } - } - }, - { - "table": "t_inner_2", - "best_access_path": { - "table": "t_inner_2", - "plan_details": { - "record_count": 9 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 9, - "rows_after_scan": 9, - "rows_after_filter": 9, - "cost": 29.31921387, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 29.31921387, - "uses_join_buffering": false - } - } - } - ] + "semi_join_strategy": "DuplicateWeedout" } ] }, @@ -7916,12 +8000,12 @@ 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_outer_2", "t_inner_4", + "t_outer_2", "t_inner_3" ], "rows": 27, - "cost": 359.2167969 + "cost": 0.540166532 }, { "substitute_best_equal": { @@ -7946,11 +8030,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_outer_2", + "table": "t_inner_4", "attached": null }, { - "table": "t_inner_4", + "table": "t_outer_2", "attached": null }, { @@ -7976,8 +8060,8 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) @@ -8141,48 +8225,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "table": "t_outer_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011155245, + "read_and_compare_cost": 0.011443245 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } } ] @@ -8215,18 +8299,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -8241,18 +8325,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -8263,7 +8347,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], @@ -8278,18 +8362,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -8300,7 +8384,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": ["t_inner_1"], "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 11.42275391 + "cost_for_plan": 0.02463804 } ] }, @@ -8308,7 +8392,7 @@ 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", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "pruned_by_heuristic": true } ] @@ -8328,18 +8412,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -8354,18 +8438,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -8376,7 +8460,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "rest_of_plan": [ { "plan_prefix": ["t_inner_4"], @@ -8391,18 +8475,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -8413,7 +8497,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": ["t_inner_4"], "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 11.42275391 + "cost_for_plan": 0.02463804 } ] }, @@ -8421,7 +8505,7 @@ 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", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "pruned_by_heuristic": true } ] @@ -8444,18 +8528,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -8470,18 +8554,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -8496,18 +8580,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -8522,18 +8606,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -8548,18 +8632,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -8574,18 +8658,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011443245, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011443245, "uses_join_buffering": false } } @@ -8596,7 +8680,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": [], "table": "t_outer_1", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -8612,18 +8696,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011523207, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -8638,18 +8722,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -8664,18 +8748,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -8690,18 +8774,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011523207, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011523207, "uses_join_buffering": true } } @@ -8716,18 +8800,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014133225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014133225, "uses_join_buffering": true } } @@ -8738,7 +8822,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"], "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -8754,18 +8838,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 70.08269043, + "rows_out": 9, + "cost": 0.050443503, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 70.08269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -8780,18 +8864,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 24.02756348, + "rows_out": 3, + "cost": 0.024600489, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 24.02756348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -8806,18 +8890,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 24.02756348, + "rows_out": 3, + "cost": 0.024600489, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 24.02756348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.024600489, "uses_join_buffering": true } } @@ -8832,18 +8916,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 70.08269043, + "rows_out": 9, + "cost": 0.050443503, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 70.08269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.050443503, "uses_join_buffering": true } } @@ -8854,7 +8938,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_outer_2"], "table": "t_inner_1", "rows_for_plan": 81, - "cost_for_plan": 35.45031738, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -8870,18 +8954,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -8896,18 +8980,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 77.69006348, + "rows_out": 3, + "cost": 0.067582275, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 77.69006348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, "uses_join_buffering": true } } @@ -8922,18 +9006,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -8944,30 +9028,36 @@ 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"], "table": "t_inner_2", "rows_for_plan": 729, - "cost_for_plan": 266.5205078, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 322.6148926 + "rows": 27, + "cost": 1.23517089 }, { "strategy": "SJ-Materialization", - "records": 27, - "cost": 35.99550781 + "rows": 27, + "cost": 0.083958496 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 266.5205078, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 380.9205078 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.222053862, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.368747182 }, { "chosen_strategy": "SJ-Materialization" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "rest_of_plan": [ { "plan_prefix": [ @@ -8987,18 +9077,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 30.10256348, + "rows_out": 3, + "cost": 0.034460781, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 30.10256348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -9013,18 +9103,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 88.30769043, + "rows_out": 9, + "cost": 0.080024379, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 88.30769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -9040,7 +9130,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 66.09807129, + "cost_for_plan": 0.118419277, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -9062,18 +9152,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261557961, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -9090,30 +9180,36 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 351.8432617, + "cost_for_plan": 0.379977238, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 347.1876465 + "rows": 27, + "cost": 1.294491346 }, { "strategy": "SJ-Materialization", - "records": 27, - "cost": 60.56826172 + "rows": 27, + "cost": 0.143278952 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 351.8432617, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 466.2432617 + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.379977238, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.526670558 }, { "chosen_strategy": "SJ-Materialization" } - ] + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 } ] }, @@ -9126,11 +9222,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 124.3031982, + "cost_for_plan": 0.163982875, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 124.3031982, - "best_cost": 60.56836172 + "current_cost": 0.163982875, + "best_cost": 0.143278952 } ] }, @@ -9138,21 +9234,19 @@ 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"], "table": "t_inner_4", "rows_for_plan": 243, - "cost_for_plan": 113.1403809, + "cost_for_plan": 0.116820804, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 113.1403809, - "best_cost": 60.56836172 + "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": 266.5205078, + "cost_for_plan": 0.222053862, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 266.5205078, - "best_cost": 60.56836172 + "current_cost": 0.222053862, + "best_cost": 0.143278952 } ] }, @@ -9160,17 +9254,15 @@ 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"], "table": "t_inner_2", "rows_for_plan": 243, - "cost_for_plan": 81.50544434, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 81.50544434, - "best_cost": 60.56836172 + "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1", "t_outer_2"], "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 35.45031738, + "cost_for_plan": 0.049238529, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -9178,11 +9270,9 @@ 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"], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 81.50544434, + "cost_for_plan": 0.075081543, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 81.50544434, - "best_cost": 60.56836172 + "pruned_by_heuristic": true } ] }, @@ -9190,7 +9280,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"], "table": "t_inner_1", "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -9206,18 +9296,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024443331, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -9232,18 +9322,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024443331, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -9258,18 +9348,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 9.177563477, + "rows_out": 3, + "cost": 0.015203373, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 9.177563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015203373, "uses_join_buffering": true } } @@ -9284,18 +9374,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024443331, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024443331, "uses_join_buffering": true } } @@ -9306,7 +9396,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", "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -9322,18 +9412,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -9348,18 +9438,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 77.69006348, + "rows_out": 3, + "cost": 0.067582275, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 77.69006348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067582275, "uses_join_buffering": true } } @@ -9374,18 +9464,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172815333, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172815333, "uses_join_buffering": true } } @@ -9396,43 +9486,222 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_2", "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219286686, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 262.2455078, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 376.6455078 + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.219286686, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.365980006 }, { "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "pruned_by_cost": true, - "current_cost": 376.6455078, - "best_cost": 60.56836172 + "current_cost": 0.365980006, + "best_cost": 0.143278952 }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_4", "rows_for_plan": 243, - "cost_for_plan": 108.8653809, + "cost_for_plan": 0.114053628, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 108.8653809, - "best_cost": 60.56836172 + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_2", + "rows_for_plan": 2187, + "cost_for_plan": 0.742727079, + "semijoin_strategy_choice": [], + "pruned_by_cost": true, + "current_cost": 0.742727079, + "best_cost": 0.143278952 + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 0.742727079, + "semijoin_strategy_choice": [ + { + "strategy": "SJ-Materialization", + "rows": 81, + "cost": 0.116338225 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 81, + "sj_filtered": 3.703703704, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4", + "t_inner_3" + ], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4", + "t_inner_3" + ], + "table": "t_inner_2", + "rows_for_plan": 729, + "cost_for_plan": 0.377896186, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 1.00428504, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 1.15097836 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, + "pruned_by_cost": true, + "current_cost": 1.15097836, + "best_cost": 0.143278952 + } + ] + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219286686, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 262.2455078, - "best_cost": 60.56836172 + "current_cost": 0.219286686, + "best_cost": 0.143278952 } ] }, @@ -9440,30 +9709,36 @@ 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_2", "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 36.32946777 + "rows": 3, + "cost": 0.145008465 }, { "strategy": "SJ-Materialization", - "records": 3, - "cost": 19.12531738 + "rows": 3, + "cost": 0.065137975 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 31.17531738, - "write_cost": 1.45, - "full_lookup_cost": 12.15, - "total_cost": 44.77531738 + "prefix_row_count": 3, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 3, + "dups_cost": 0.046471353, + "write_cost": 0.02548291, + "full_lookup_cost": 0.01303857, + "total_cost": 0.084992833 }, { "chosen_strategy": "SJ-Materialization" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 3, + "sj_filtered": 3.703703704, "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -9478,18 +9753,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017419989, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -9504,18 +9779,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 4.565063477, + "rows_out": 3, + "cost": 0.012618795, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 4.565063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012618795, "uses_join_buffering": true } } @@ -9530,18 +9805,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017419989, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017419989, "uses_join_buffering": true } } @@ -9552,7 +9827,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", "t_inner_2"], "table": "t_outer_2", "rows_for_plan": 27, - "cost_for_plan": 30.82050781, + "cost_for_plan": 0.082557964, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -9573,18 +9848,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 30.10256348, + "rows_out": 3, + "cost": 0.034460781, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 30.10256348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034460781, "uses_join_buffering": true } } @@ -9599,18 +9874,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 88.30769043, + "rows_out": 9, + "cost": 0.080024379, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 88.30769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.080024379, "uses_join_buffering": true } } @@ -9626,11 +9901,88 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_4", "rows_for_plan": 81, - "cost_for_plan": 60.92307129, + "cost_for_plan": 0.117018745, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 60.92307129, - "best_cost": 60.56836172 + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261557961, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "rows_for_plan": 729, + "cost_for_plan": 0.378576706, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "rows": 27, + "cost": 1.293090814 + }, + { + "strategy": "SJ-Materialization", + "rows": 27, + "cost": 0.14187842 + }, + { + "strategy": "DuplicateWeedout", + "prefix_row_count": 27, + "tmp_table_rows": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.378576706, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.525270026 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704 + } + ] }, { "plan_prefix": [ @@ -9641,11 +9993,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 243, - "cost_for_plan": 119.1281982, + "cost_for_plan": 0.162582343, "semijoin_strategy_choice": [], "pruned_by_cost": true, - "current_cost": 119.1281982, - "best_cost": 60.56836172 + "current_cost": 0.162582343, + "best_cost": 0.14187842 } ] }, @@ -9653,7 +10005,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", "t_inner_2"], "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 23.69038086, + "cost_for_plan": 0.07775677, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -9674,18 +10026,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034303623, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -9700,18 +10052,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034303623, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034303623, "uses_join_buffering": true } } @@ -9727,7 +10079,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_outer_2", "rows_for_plan": 81, - "cost_for_plan": 55.29807129, + "cost_for_plan": 0.112060393, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -9749,18 +10101,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261557961, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261557961, "uses_join_buffering": true } } @@ -9777,23 +10129,29 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 729, - "cost_for_plan": 341.0432617, + "cost_for_plan": 0.373618354, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 341.0432617, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 455.4432617 + "prefix_row_count": 3, + "tmp_table_rows": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.373618354, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.520311674 }, { "chosen_strategy": "DuplicateWeedout" } ], + "sj_rows_out": 0.333333333, + "sj_rows_for_plan": 27, + "sj_filtered": 3.703703704, "pruned_by_cost": true, - "current_cost": 455.4432617, - "best_cost": 60.56836172 + "current_cost": 0.520311674, + "best_cost": 0.14187842 } ] }, @@ -9806,7 +10164,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "table": "t_inner_3", "rows_for_plan": 81, - "cost_for_plan": 55.29807129, + "cost_for_plan": 0.112060393, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -9816,7 +10174,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", "t_inner_2"], "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 30.82050781, + "cost_for_plan": 0.082557964, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -9826,7 +10184,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_inner_4", "rows_for_plan": 27, - "cost_for_plan": 14.82019043, + "cost_for_plan": 0.037231395, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -9842,18 +10200,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065233941, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -9868,18 +10226,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065233941, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -9894,18 +10252,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065233941, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065233941, "uses_join_buffering": true } } @@ -9916,31 +10274,118 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_outer_2", "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 94.01538086, - "best_cost": 60.56836172 + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_4", + "t_outer_2" + ], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t_inner_2", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + }, + { + "best_access_path": { + "table": "t_inner_3", + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628673451, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628673451, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_4", + "t_outer_2" + ], + "table": "t_inner_2", + "rows_for_plan": 2187, + "cost_for_plan": 0.731138787, + "semijoin_strategy_choice": [], + "pruned_by_cost": true, + "current_cost": 0.731138787, + "best_cost": 0.14187842 + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_4", + "t_outer_2" + ], + "table": "t_inner_3", + "rows_for_plan": 2187, + "cost_for_plan": 0.731138787, + "semijoin_strategy_choice": [], + "pruned_by_cost": true, + "current_cost": 0.731138787, + "best_cost": 0.14187842 + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], "table": "t_inner_2", "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 94.01538086, - "best_cost": 60.56836172 + "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": 94.01538086, + "cost_for_plan": 0.102465336, "semijoin_strategy_choice": [], - "pruned_by_cost": true, - "current_cost": 94.01538086, - "best_cost": 60.56836172 + "pruned_by_heuristic": true } ] }, @@ -9948,7 +10393,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_inner_3", "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046471353, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -9958,7 +10403,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"], "table": "t_inner_2", "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -9966,7 +10411,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"], "table": "t_inner_4", "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.022028022, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -9974,7 +10419,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"], "table": "t_inner_3", "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02463804, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -9984,7 +10429,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "plan_prefix": [], "table": "t_outer_2", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -9992,7 +10437,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -10000,7 +10445,7 @@ 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", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -10008,7 +10453,7 @@ 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", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -10016,7 +10461,7 @@ 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", "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011443245, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -10051,12 +10496,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "best_join_order": [ "t_outer_1", - "t_outer_2", "<subquery2>", + "t_outer_2", "<subquery3>" ], "rows": 27, - "cost": 60.56826172 + "cost": 0.14187842 }, { "substitute_best_equal": { @@ -10077,10 +10522,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_outer_2", - "attached": null - }, - { "table": "t_inner_1", "attached": null }, @@ -10093,6 +10534,10 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { + "table": "t_outer_2", + "attached": null + }, + { "table": "t_inner_4", "attached": null }, @@ -10160,7 +10605,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.745829876, + "cost": 0.001388369, "chosen": true } ], @@ -10189,7 +10634,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 107, - "cost": 24.68379668, + "cost": 0.016044989, "chosen": true } ], @@ -10221,7 +10666,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 756.2522431, + "cost": 1.235599899, "chosen": true } ], @@ -10261,7 +10706,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 4, - "cost": 3.523710032, + "cost": 0.006185575, "chosen": true } ], @@ -10295,7 +10740,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.271171589, + "cost": 0.002483968, "chosen": true } ], @@ -10324,7 +10769,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.271171589, + "cost": 0.002483968, "chosen": true } ], @@ -10361,7 +10806,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270927508, + "cost": 0.002483968, "chosen": true } ], @@ -10391,7 +10836,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270878692, + "cost": 0.002483968, "chosen": true } ], @@ -10421,7 +10866,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270927508, + "cost": 0.002483968, "chosen": true } ], @@ -10454,7 +10899,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270878692, + "cost": 0.002483968, "chosen": true } ], @@ -10490,7 +10935,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.319255553, + "cost": 0.002483968, "chosen": true } ], @@ -10509,7 +10954,7 @@ INSERT INTO t1 VALUES (2, 'ab\n'); set optimizer_trace=1; EXPLAIN SELECT * FROM t1 WHERE b='ab\n'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL i_b NULL NULL NULL 2 Using where +1 SIMPLE t1 ref i_b i_b 13 const 2 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ @@ -10524,9 +10969,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2, - "cost": 2.021855016, - "chosen": false, - "cause": "cost" + "cost": 0.003717837, + "chosen": true } ], "analyzing_roworder_intersect": @@ -10580,7 +11024,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 756.2522431, + "cost": 1.235599899, "chosen": true } ], @@ -10647,9 +11091,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 10, - "rows_after_scan": 5, "rows_after_filter": 5, - "cost": 3.508544922, + "rows_out": 5, + "cost": 0.01159965, "index_only": false, "chosen": true } @@ -10657,9 +11101,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 5, - "records_out": 5, - "cost": 3.508544922, + "rows_read": 5, + "rows_out": 5, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -10677,9 +11121,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 1000, - "rows_after_scan": 800, "rows_after_filter": 800, - "cost": 252.0986328, + "rows_out": 800, + "cost": 0.1669214, "index_only": false, "chosen": true } @@ -10687,9 +11131,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 800, - "records_out": 800, - "cost": 252.0986328, + "rows_read": 800, + "rows_out": 800, + "cost": 0.1669214, "uses_join_buffering": false } } @@ -10701,7 +11145,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [], "table": "A", "rows_for_plan": 5, - "cost_for_plan": 3.508544922, + "cost_for_plan": 0.01159965, "rest_of_plan": [ { @@ -10722,9 +11166,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 1000, - "rows_after_scan": 800, "rows_after_filter": 800, - "cost": 1260.493164, + "rows_out": 800, + "cost": 0.834607, "index_only": false, "chosen": true } @@ -10732,9 +11176,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 800, - "records_out": 800, - "cost": 1260.493164, + "rows_read": 800, + "rows_out": 800, + "cost": 0.834607, "uses_join_buffering": false } } @@ -10746,7 +11190,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ["A"], "table": "B", "rows_for_plan": 4000, - "cost_for_plan": 1264.001709 + "cost_for_plan": 0.84620665 } ] }, @@ -10755,7 +11199,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [], "table": "B", "rows_for_plan": 800, - "cost_for_plan": 252.0986328, + "cost_for_plan": 0.1669214, "pruned_by_heuristic": true } ] @@ -10788,9 +11232,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.508544922, + "rows_out": 10, + "cost": 0.01159965, "index_only": false, "chosen": true } @@ -10798,9 +11242,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.508544922, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -10818,9 +11262,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 1000, - "rows_after_scan": 800, "rows_after_filter": 800, - "cost": 252.0986328, + "rows_out": 800, + "cost": 0.1669214, "index_only": false, "chosen": true } @@ -10828,9 +11272,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 800, - "records_out": 800, - "cost": 252.0986328, + "rows_read": 800, + "rows_out": 800, + "cost": 0.1669214, "uses_join_buffering": false } } @@ -10842,7 +11286,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [], "table": "A", "rows_for_plan": 10, - "cost_for_plan": 3.508544922, + "cost_for_plan": 0.01159965, "rest_of_plan": [ { @@ -10866,25 +11310,21 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 12.50585794, + "cost": 0.01810946, "chosen": true }, { - "access_type": "scan_with_join_cache", - "rows": 1000, - "rows_after_scan": 600, - "rows_after_filter": 600, - "cost": 1677.098633, - "index_only": false, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 12.50585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01810946, "uses_join_buffering": false } } @@ -10896,7 +11336,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ["A"], "table": "B", "rows_for_plan": 10, - "cost_for_plan": 16.01440287, + "cost_for_plan": 0.02970911, + "pushdown_cond_selectivity": 0.8, + "filtered": 80, + "rows_out": 0.8, "selectivity": 0.8, "estimated_join_cardinality": 8 } @@ -10907,10 +11350,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [], "table": "B", "rows_for_plan": 800, - "cost_for_plan": 252.0986328, + "cost_for_plan": 0.1669214, "pruned_by_cost": true, - "current_cost": 252.0986328, - "best_cost": 16.01450287 + "current_cost": 0.1669214, + "best_cost": 0.02970911 } ] ] @@ -10939,7 +11382,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.282887479, + "cost": 0.002483968, "chosen": true } ], @@ -10964,9 +11407,9 @@ insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); explain select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where -1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Using where select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) [ @@ -10999,7 +11442,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.745829876, + "cost": 0.001388369, "chosen": true } ] @@ -11039,9 +11482,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.510986328, + "rows_out": 10, + "cost": 0.01159965, "index_only": false, "chosen": true } @@ -11049,9 +11492,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01159965, "uses_join_buffering": false } } @@ -11069,9 +11512,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 100, - "rows_after_scan": 100, "rows_after_filter": 100, - "cost": 26.10986328, + "rows_out": 100, + "cost": 0.0256761, "index_only": false, "chosen": true, "use_tmp_table": true @@ -11080,9 +11523,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 100, - "records_out": 100, - "cost": 26.10986328, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256761, "uses_join_buffering": false } } @@ -11094,7 +11537,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [], "table": "t1", "rows_for_plan": 10, - "cost_for_plan": 3.510986328, + "cost_for_plan": 0.01159965, "rest_of_plan": [ { @@ -11118,25 +11561,21 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "used_range_estimates": false, "reason": "not available", "rows": 1, - "cost": 12.50585794, + "cost": 0.01749506, "chosen": true }, { - "access_type": "scan_with_join_cache", - "rows": 100, - "rows_after_scan": 75, - "rows_after_filter": 75, - "cost": 204.2348633, - "index_only": false, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 12.50585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01749506, "uses_join_buffering": false } } @@ -11148,8 +11587,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ["t1"], "table": "t2", "rows_for_plan": 10, - "cost_for_plan": 16.01684427, - "cost_for_sorting": 10 + "cost_for_plan": 0.02909471, + "cost_for_sorting": 0.006368384 } ] }, @@ -11158,10 +11597,75 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [], "table": "t2", "rows_for_plan": 100, - "cost_for_plan": 26.10986328, - "pruned_by_cost": true, - "current_cost": 26.10986328, - "best_cost": 26.01694427 + "cost_for_plan": 0.0256761, + "rest_of_plan": + [ + + { + "plan_prefix": + [ + "t2" + ], + "get_costs_for_tables": + [ + + { + "best_access_path": + { + "table": "t1", + "plan_details": + { + "record_count": 100 + }, + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "not available", + "rows": 1, + "cost": 0.1731074, + "chosen": true + }, + + { + "access_type": "scan_with_join_cache", + "rows": 10, + "rows_after_filter": 10, + "rows_out": 1, + "cost": 0.11055225, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "rows_read": 10, + "rows_out": 1, + "cost": 0.11055225, + "uses_join_buffering": true + } + } + } + ] + }, + + { + "plan_prefix": + [ + "t2" + ], + "table": "t1", + "rows_for_plan": 100, + "cost_for_plan": 0.13622835, + "pruned_by_cost": true, + "current_cost": 0.13622835, + "best_cost": 0.035463094 + } + ] } ] ] @@ -11179,7 +11683,7 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 100 1.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 0.25 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; @@ -11259,7 +11763,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) "using_mrr": false, "index_only": false, "rows": 0, - "cost": 0.52, + "cost": 0.001340684, "chosen": true } ] @@ -11361,8 +11865,8 @@ select count(*) from seq_1_to_10000000 { "table": "seq_1_to_10000000", "table_scan": { "rows": 10000000, - "read_cost": 5000000, - "read_and_compare_cost": 7250000 + "read_cost": 124.7880673, + "read_and_compare_cost": 444.7880673 } } ] @@ -11382,18 +11886,18 @@ select count(*) from seq_1_to_10000000 { { "access_type": "scan", "rows": 10000000, - "rows_after_scan": 10000000, "rows_after_filter": 10000000, - "cost": 7250000, + "rows_out": 10000000, + "cost": 444.7880673, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10000000, - "records_out": 10000000, - "cost": 7250000, + "rows_read": 10000000, + "rows_out": 10000000, + "cost": 444.7880673, "uses_join_buffering": false } } @@ -11404,14 +11908,14 @@ select count(*) from seq_1_to_10000000 { "plan_prefix": [], "table": "seq_1_to_10000000", "rows_for_plan": 10000000, - "cost_for_plan": 7250000 + "cost_for_plan": 444.7880673 } ] }, { "best_join_order": ["seq_1_to_10000000"], "rows": 10000000, - "cost": 7250000 + "cost": 444.7880673 }, { "attaching_conditions_to_tables": { @@ -11450,7 +11954,7 @@ explain select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Impossible ON condition -1 SIMPLE t2 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t2 const PRIMARY NULL NULL NULL 0 Impossible ON condition 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) from information_schema.optimizer_trace; @@ -11771,7 +12275,7 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "used_range_estimates": false, "reason": "not available", "rows": 1.8367, - "cost": 1.417925794, + "cost": 0.0019606, "chosen": true }, { @@ -11783,9 +12287,9 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "chosen_access_method": { "type": "ref", - "records_read": 1.8367, - "records_out": 1.8367, - "cost": 1.417925794, + "rows_read": 1.8367, + "rows_out": 1.8367, + "cost": 0.0019606, "uses_join_buffering": false } } @@ -11797,19 +12301,25 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) [], "table": "t2", "rows_for_plan": 1.8367, - "cost_for_plan": 1.417925794, - "cost_for_sorting": 1.8367 + "cost_for_plan": 0.0019606, + "cost_for_sorting": 0.001155201 } ] }, { - "best_splitting": + "split_materialized": { "table": "t2", "key": "idx_a", - "record_count": 4, - "cost": 4.157170953, - "unsplit_cost": 60.1794762 + "org_cost": 0.0019606, + "postjoin_cost": 0.001135418, + "one_splitting_cost": 0.003096018, + "unsplit_postjoin_cost": 0.036032575, + "unsplit_cost": 0.060625425, + "rows": 1.8367, + "outer_rows": 4, + "total_splitting_cost": 0.012384072, + "chosen": true } } ] @@ -11819,13 +12329,7 @@ json_detailed(json_extract(trace, '$**.lateral_derived')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.lateral_derived')) -[ - { - "startup_cost": 16.62868381, - "splitting_cost": 4.157170953, - "records": 1 - } -] +NULL drop table t1,t2; # # Test table functions. @@ -11881,7 +12385,6 @@ set @save_histogram_size= @@histogram_size; set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; set optimizer_switch='rowid_filter=on'; set use_stat_tables='preferably'; -set optimizer_use_condition_selectivity=4; set histogram_size=127; create table t1 (a int, b int, c int, key(a),key(b)); insert into t1 select seq, seq*2, seq/10 from seq_1_to_1000; @@ -11949,7 +12452,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "range_analysis": { "table_scan": { "rows": 1000, - "cost": 252.5869141 + "cost": 0.1671618 }, "potential_range_indexes": [ { @@ -11973,7 +12476,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "using_mrr": false, "index_only": false, "rows": 9, - "cost": 7.27527215, + "cost": 0.01235492, "chosen": true }, { @@ -11983,7 +12486,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "using_mrr": false, "index_only": false, "rows": 21, - "cost": 16.28230168, + "cost": 0.027161348, "chosen": false, "cause": "cost" } @@ -12005,7 +12508,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "ranges": ["(NULL) < (a) < (10)"] }, "rows_for_plan": 9, - "cost_for_plan": 7.27527215, + "cost_for_plan": 0.01235492, "chosen": true } } @@ -12015,12 +12518,12 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "rowid_filters": [ { "key": "a", - "build_cost": 0.759047172, + "build_cost": 9.95653e-5, "rows": 9 }, { "key": "b", - "build_cost": 1.122236655, + "build_cost": 3.004222e-4, "rows": 21 } ] @@ -12061,33 +12564,37 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "considered_access_paths": [ { "filter": { - "rowid_filter_key": "b", - "index_only_cost": 0.50527215, - "filter_startup_cost": 1.122236655, - "find_key_and_filter_lookup_cost": 0.386507019, + "rowid_filter_index": "b", + "index_only_cost": 0.001515222, + "filter_startup_cost": 3.004222e-4, + "find_key_and_filter_lookup_cost": 6.445451e-4, "filter_selectivity": 0.021, - "orginal_rows": 9, + "original_rows": 9, "new_rows": 0.189, - "original_found_rows_cost": 5.45527215, - "new_found_rows_cost": 1.381168455, - "cost": 2.54120511, + "original_access_cost": 0.011516778, + "with_filter_access_cost": 0.0023698, + "original_found_rows_cost": 0.010001556, + "with_filter_found_rows_cost": 2.100327e-4, + "org_cost": 0.011804778, + "filter_cost": 0.00267627, "filter_used": true }, "access_type": "range", + "range_index": "a", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 0.189, - "cost": 2.54120511, + "rows_out": 0.017766, + "cost": 0.00267627, "chosen": true } ], "chosen_access_method": { "type": "range", - "records_read": 9, - "records_out": 0.017766, - "cost": 2.54120511, + "rows_read": 0.189, + "rows_out": 0.017766, + "cost": 0.00267627, "uses_join_buffering": false, - "rowid_filter_key": "b" + "rowid_filter_index": "b" } } } @@ -12096,23 +12603,22 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { { "plan_prefix": [], "table": "t1", - "rows_for_plan": 9, - "cost_for_plan": 2.54120511 + "rows_for_plan": 0.017766, + "cost_for_plan": 0.00267627, + "pushdown_cond_selectivity": 0.094, + "filtered": 0.1974, + "rows_out": 0.017766 } ] }, { "best_join_order": ["t1"], - "rows": 9, - "cost": 2.54120511 + "rows": 0.017766, + "cost": 0.00267627 }, { "table": "t1", "range_analysis": { - "table_scan": { - "rows": 1000, - "cost": 1.79769e308 - }, "potential_range_indexes": [ { "index": "a", @@ -12135,7 +12641,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "using_mrr": false, "index_only": true, "rows": 21, - "cost": 5.257301684, + "cost": 0.004153769, "chosen": true } ] @@ -12148,7 +12654,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "ranges": ["(10) <= (b) <= (50)"] }, "rows_for_plan": 21, - "cost_for_plan": 5.257301684, + "cost_for_plan": 0.004153769, "chosen": true } } @@ -12190,6 +12696,7 @@ analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date +set optimizer_use_condition_selectivity=2; explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; EXPLAIN { @@ -12223,13 +12730,47 @@ EXPLAIN "selectivity_pct": 43.12 }, "rows": 1000, - "filtered": 4.307688236, + "filtered": 43.11999893, "attached_condition": "t1.b < 5000 and t1.c < 1000" } } ] } } +set optimizer_use_condition_selectivity=4; +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "three", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "possible_keys": ["a", "b"], + "rows": 10000, + "filtered": 4.307688236, + "attached_condition": "t1.b < 5000 and t1.c < 1000" + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "attached_condition": "t1.a = three.a" + } + } + ] + } +} QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000 { "steps": [ @@ -12300,8 +12841,8 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "table": "three", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010408815, + "read_and_compare_cost": 0.010504815 } }, { @@ -12309,7 +12850,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "range_analysis": { "table_scan": { "rows": 10000, - "cost": 2516.869141 + "cost": 1.581538 }, "potential_range_indexes": [ { @@ -12333,7 +12874,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "using_mrr": false, "index_only": false, "rows": 4312, - "cost": 3260.045946, + "cost": 5.325058827, "chosen": false, "cause": "cost" } @@ -12354,7 +12895,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "rowid_filters": [ { "key": "b", - "build_cost": 174.2257513, + "build_cost": 0.149564727, "rows": 4312 } ] @@ -12392,18 +12933,18 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010504815, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010504815, "uses_join_buffering": false } } @@ -12418,18 +12959,18 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and { "access_type": "scan", "rows": 10000, - "rows_after_scan": 430.7688, "rows_after_filter": 430.7688, - "cost": 2516.869141, + "rows_out": 430.7688, + "cost": 1.581538, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 430.7688, - "records_out": 430.7688, - "cost": 2516.869141, + "rows_read": 430.7688, + "rows_out": 430.7688, + "cost": 1.581538, "uses_join_buffering": false } } @@ -12440,7 +12981,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "plan_prefix": [], "table": "three", "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010504815, "rest_of_plan": [ { "plan_prefix": ["three"], @@ -12458,39 +12999,41 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "used_range_estimates": false, "reason": "not available", "filter": { - "rowid_filter_key": "b", - "index_only_cost": 2.585794484, - "filter_startup_cost": 174.2257513, - "find_key_and_filter_lookup_cost": 96.19157113, + "rowid_filter_index": "b", + "index_only_cost": 0.092006157, + "filter_startup_cost": 0.149564727, + "find_key_and_filter_lookup_cost": 0.085742374, "filter_selectivity": 0.4312, - "orginal_rows": 1000, + "original_rows": 1000, "new_rows": 431.2, - "original_found_rows_cost": 552.5857945, - "new_found_rows_cost": 335.9373656, - "cost": 1440.757848, + "original_access_cost": 1.203290157, + "with_filter_access_cost": 0.656934192, + "original_found_rows_cost": 1.111284, + "with_filter_found_rows_cost": 0.479185661, + "org_cost": 3.705870471, + "filter_cost": 2.161762502, "filter_used": true }, "rows": 431.2, - "cost": 1440.757848, + "cost": 2.161762502, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 10000, - "rows_after_scan": 323.0766, - "rows_after_filter": 323.0766, - "cost": 2747.061218, + "rows_after_filter": 430.7688, + "rows_out": 430.7688, + "cost": 1.701731924, "index_only": false, - "chosen": false + "chosen": true } ], "chosen_access_method": { - "type": "ref", - "records_read": 1000, - "records_out": 323.0766, - "cost": 1440.757848, - "uses_join_buffering": false, - "rowid_filter_key": "b" + "type": "scan", + "rows_read": 430.7688, + "rows_out": 430.7688, + "cost": 1.701731924, + "uses_join_buffering": true } } } @@ -12499,10 +13042,8 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and { "plan_prefix": ["three"], "table": "t1", - "rows_for_plan": 3000, - "cost_for_plan": 1442.510412, - "selectivity": 0.04307688, - "estimated_join_cardinality": 129.23064 + "rows_for_plan": 1292.3064, + "cost_for_plan": 1.712236739 } ] }, @@ -12510,64 +13051,15 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "plan_prefix": [], "table": "t1", "rows_for_plan": 430.7688, - "cost_for_plan": 2516.869141, - "pruned_by_cost": true, - "current_cost": 2516.869141, - "best_cost": 1442.510512 + "cost_for_plan": 1.581538, + "pruned_by_heuristic": true } ] }, { "best_join_order": ["three", "t1"], - "rows": 129.23064, - "cost": 1442.510412 - }, - { - "table": "t1", - "range_analysis": { - "table_scan": { - "rows": 10000, - "cost": 1.79769e308 - }, - "potential_range_indexes": [ - { - "index": "a", - "usable": false, - "cause": "not applicable" - }, - { - "index": "b", - "usable": true, - "key_parts": ["b"] - } - ], - "setup_range_conditions": [], - "analyzing_range_alternatives": { - "range_scan_alternatives": [ - { - "index": "b", - "ranges": ["(NULL) < (b) < (5000)"], - "rowid_ordered": false, - "using_mrr": false, - "index_only": true, - "rows": 4312, - "cost": 996.2459458, - "chosen": true - } - ] - }, - "chosen_range_access_summary": { - "range_access_plan": { - "type": "range_scan", - "index": "b", - "rows": 4312, - "ranges": ["(NULL) < (b) < (5000)"] - }, - "rows_for_plan": 4312, - "cost_for_plan": 996.2459458, - "chosen": true - } - } + "rows": 1292.3064, + "cost": 1.712236739 }, { "substitute_best_equal": { @@ -12577,15 +13069,64 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and }, { "attaching_conditions_to_tables": { - "attached_conditions_computation": [], + "attached_conditions_computation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 10000, + "cost": 1.581538 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + }, + { + "index": "b", + "usable": true, + "key_parts": ["b"] + } + ], + "setup_range_conditions": [], + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a" + }, + { + "index": "b", + "ranges": ["(NULL) < (b) < (5000)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 4312, + "cost": 5.325058827, + "chosen": false, + "cause": "cost" + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "group_index_range": { + "chosen": false, + "cause": "not single_table" + } + } + } + ], "attached_conditions_summary": [ { "table": "three", - "attached": "three.a is not null" + "attached": null }, { "table": "t1", - "attached": "t1.b < 5000 and t1.c < 1000" + "attached": "t1.a = three.a and t1.b < 5000 and t1.c < 1000" } ] } |