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
-