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 * FROM `t1` tbl0 INNER JOIN `t1` tbl1 |
ON tbl1.`c1` = CAST(tbl0.`c2` AS CHAR(30)) |
LEFT JOIN `t1` tbl2 ON tbl2.`c3` = 'str' |
INNER JOIN `t2` tbl5; |
(where tX, cX and 'str' are obfuscated names of tables and columns and a constant from the dataset).
On current 10.6, the query takes below 1 sec
|
10.6 |
Empty set (0.907 sec)
|
On the preview branch for MDEV-28852 (preview-10.10-optimizer 6f979f88c), with the patch from MDEV-28929, it takes over 13 min:
Empty set (13 min 45.263 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 | SIMPLE | tbl0 | ALL | NULL | NULL | NULL | NULL | 1314 | 100.00 | | |
| 1 | SIMPLE | tbl1 | ALL | NULL | NULL | NULL | NULL | 1314 | 100.00 | Using where; Using join buffer (flat, BNL join) | |
| 1 | SIMPLE | tbl2 | ref | a_ref_9 | a_ref_9 | 99 | const | 1 | 100.00 | Using where | |
| 1 | SIMPLE | tbl5 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | Using join buffer (flat, 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 | SIMPLE | tbl5 | ALL | NULL | NULL | NULL | NULL | 619 | 100.00 | | |
| 1 | SIMPLE | tbl0 | ALL | NULL | NULL | NULL | NULL | 1314 | 100.00 | Using join buffer (flat, BNL join) | |
| 1 | SIMPLE | tbl1 | ALL | NULL | NULL | NULL | NULL | 1314 | 100.00 | Using where; Using join buffer (incremental, BNL join) | |
| 1 | SIMPLE | tbl2 | ref | a_ref_9 | a_ref_9 | 99 | const | 1 | 100.00 | Using where | |
+------+-------------+-------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+ |
Attachments
Issue Links
- is caused by
-
MDEV-28852 Improve optimization of joins with many tables, including eq_ref tables
-
- Closed
-