Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
N/A
-
None
Description
The test uses all default server startup options, loads original data set from MDEV-28073 (xpo_full_testcase.sql), runs ANALYZE PERSISTENT FOR ALL on all tables, and executes the query:
SELECT alias1.* |
FROM `t1` AS alias1 |
WHERE alias1.`c1` NOT IN ( |
SELECT alias2.`c2` |
FROM `t1` AS alias2, `t2` AS alias3 |
WHERE alias3.`c3` IN ( |
SELECT alias4.`c2` |
FROM `t1` AS alias4 JOIN `t1` AS alias5 |
ON (alias5.`c4` = alias4.`c5` ) |
WHERE alias5.`c4` = alias1.`c5` ) AND alias2.`c5` != 7 |
) AND alias1.`c6` IN (219, 167); |
(where tX and cX are obfuscated names of tables and columns from the dataset).
On current 10.6 / 10.10, the query takes below 0.1 sec
|
10.6 |
Empty set (0.036 sec)
|
On the preview branch for MDEV-28852 (preview-10.10-optimizer 6f979f88c), with and without the patch from MDEV-28929, it takes over 5 sec:
|
preview-10.10-optimizer 6f979f88c + patch from MDEV-28929 |
Empty set (5.361 sec)
|
Note that it's about the actual execution, not about plan selection; on both versions EXPLAIN is nearly instantaneous. The plans are correspondingly
|
10.6 |
+------+--------------------+--------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------------+--------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------------+ |
| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where | |
| 2 | DEPENDENT SUBQUERY | alias3 | ALL | NULL | NULL | NULL | NULL | 86 | 100.00 | | |
| 2 | DEPENDENT SUBQUERY | alias4 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where; Using join buffer (flat, BNL join) | |
| 2 | DEPENDENT SUBQUERY | alias5 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where; FirstMatch(alias3); Using join buffer (incremental, BNL join) | |
| 2 | DEPENDENT SUBQUERY | alias2 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where; Using join buffer (incremental, BNL join) | |
+------+--------------------+--------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------------+ |
|
preview-10.10-optimizer 6f979f88c5 + patch from MDEV-28929 |
+------+--------------------+--------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------------+--------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------------+ |
| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where | |
| 2 | DEPENDENT SUBQUERY | alias3 | ALL | NULL | NULL | NULL | NULL | 86 | 100.00 | | |
| 2 | DEPENDENT SUBQUERY | alias2 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where; Using join buffer (flat, BNL join) | |
| 2 | DEPENDENT SUBQUERY | alias4 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where; Using join buffer (incremental, BNL join) | |
| 2 | DEPENDENT SUBQUERY | alias5 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using where; FirstMatch(alias2); Using join buffer (incremental, BNL join) | |
+------+--------------------+--------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------------+ |
Attachments
Issue Links
- is caused by
-
MDEV-28852 Improve optimization of joins with many tables, including eq_ref tables
-
- Closed
-
Activity
ANALYZE FORMAT=JSON on 10.6 shows that the table alias4 has this attached condition that filters out everything:
"attached_condition": "alias3.u_exception = alias4.sys_mod_count",
|
"r_filtered": 0
|
Because of this, tables alias5 and alias2 are not accessed at all.
The optimizer is not aware about the selectivity of this condition.
For 10.10, the condition that never matches is also attached to table alias4:
"attached_condition": "alias3.u_exception = alias4.sys_mod_count",
|
"r_filtered": 0
|
The problem is that the join order in the subquery is alias3, alias2, alias4. Which means the join condition attached to table alias4 is checked 619 * 86 * 619= 32,951,846 times, and so the query runs slower.
Why does 10.10 prefer alias3-alias2 to alias3-alias4?
Because of pruning: "alias4" is not better than alias2:
{
|
"plan_prefix": ["alias3"],
|
"table": "alias2",
|
"rows_for_plan": 53234,
|
"cost_for_plan": 10686,
|
"semijoin_strategy_choice": [],
|
...
{
|
"plan_prefix": ["alias3"],
|
"table": "alias4",
|
"rows_for_plan": 53234,
|
"cost_for_plan": 10686,
|
"semijoin_strategy_choice": [],
|
"pruned_by_heuristic": true
|
},
|
in 10.6, it's other way around:
"plan_prefix": ["alias3"],
|
"table": "alias4",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "scan",
|
"resulting_rows": 619,
|
"cost": 19,
|
"chosen": true
|
}
|
],
|
"chosen_access_method": {
|
"type": "scan",
|
"records": 619,
|
"cost": 19,
|
"uses_join_buffering": true
|
}
|
},
|
},
|
"rows_for_plan": 53234,
|
"cost_for_plan": 10686,
|
"rest_of_plan": [
|
{
|
"plan_prefix": ["alias3", "alias4"],
|
...
|
}
|
....
{
|
"plan_prefix": ["alias3"],
|
"table": "alias2",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "scan",
|
"resulting_rows": 619,
|
"cost": 19,
|
"chosen": true
|
}
|
],
|
"chosen_access_method": {
|
"type": "scan",
|
"records": 619,
|
"cost": 19,
|
"uses_join_buffering": true
|
}
|
},
|
"rows_for_plan": 53234,
|
"cost_for_plan": 10686,
|
"semijoin_strategy_choice": [],
|
"pruned_by_heuristic": true
|
}
|
Closing as Not A Bug.
The optimizer is not capable of predicting that an condition "column_=column_y" where the columns are not supported by indexes will have no matches.
EXPLAINs show different join orders but query plans do not show a drastic difference
(I've marked the grandchild subquery's tables with "SQ")
10.6:
+------+--------------------+--------+------+---------------+------+---------+------+------+----------------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+--------------------+--------+------+---------------+------+---------+------+------+----------------------------------------------------------------------------+| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 619 | Using where || 2 | DEPENDENT SUBQUERY | alias3 | ALL | NULL | NULL | NULL | NULL | 86 | ||SQ 2 | DEPENDENT SUBQUERY | alias4 | ALL | NULL | NULL | NULL | NULL | 619 | Using where; Using join buffer (flat, BNL join) ||SQ 2 | DEPENDENT SUBQUERY | alias5 | ALL | NULL | NULL | NULL | NULL | 619 | Using where; FirstMatch(alias3); Using join buffer (incremental, BNL join) || 2 | DEPENDENT SUBQUERY | alias2 | ALL | NULL | NULL | NULL | NULL | 619 | Using where; Using join buffer (incremental, BNL join) |+------+--------------------+--------+------+---------------+------+---------+------+------+----------------------------------------------------------------------------+10.10:
+------+--------------------+--------+------+---------------+------+---------+------+------+----------------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+--------------------+--------+------+---------------+------+---------+------+------+----------------------------------------------------------------------------+| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 619 | Using where || 2 | DEPENDENT SUBQUERY | alias3 | ALL | NULL | NULL | NULL | NULL | 86 | || 2 | DEPENDENT SUBQUERY | alias2 | ALL | NULL | NULL | NULL | NULL | 619 | Using where; Using join buffer (flat, BNL join) || SQ 2 | DEPENDENT SUBQUERY | alias4 | ALL | NULL | NULL | NULL | NULL | 619 | Using where; Using join buffer (incremental, BNL join) || SQ 2 | DEPENDENT SUBQUERY | alias5 | ALL | NULL | NULL | NULL | NULL | 619 | Using where; FirstMatch(alias2); Using join buffer (incremental, BNL join) |+------+--------------------+--------+------+---------------+------+---------+------+------+----------------------------------------------------------------------------+