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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue is caused by |
Thanks to SHOW ANALYZE, I don't need to wait till the query finishes on 10.10.
The zero-selectivity condition is checked at a later stage:
MariaDB [test]> show analyze format=json for 5\G
|
*************************** 1. row ***************************
|
SHOW ANALYZE: {
|
"r_query_time_in_progress_ms": 386689,
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "tbl5",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 619,
|
"r_rows": 613,
|
"r_table_time_ms": 27.99626565,
|
"r_other_time_ms": 0.773400926,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
},
|
{
|
"block-nl-join": {
|
"table": {
|
"table_name": "tbl0",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1314,
|
"r_rows": 234,
|
"r_table_time_ms": 34.85750579,
|
"r_other_time_ms": 123.3221248,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"r_filtered": 100
|
}
|
},
|
{
|
"block-nl-join": {
|
"table": {
|
"table_name": "tbl1",
|
"access_type": "ALL",
|
"r_loops": 260,
|
"rows": 1314,
|
"r_rows": 1310.303846,
|
"r_table_time_ms": 50559.88911,
|
"r_other_time_ms": 336830.8912,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "incremental",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"attached_condition": "tbl1.a_str_1 = cast(tbl0.a_dte_3 as char(30) charset utf8mb3)",
|
"r_filtered": 0
|
}
|
},
|
tbl5 (613 rows) times tbl0 , times tbl1.
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Resolution | Not a Bug [ 6 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
ANALYZE in 10.6
ANALYZE: {"query_block": {"select_id": 1,"r_loops": 1,"r_total_time_ms": 2881.971711,"table": {"table_name": "tbl0","access_type": "ALL","r_loops": 1,"rows": 1314,"r_rows": 1314,"r_table_time_ms": 216.52326,"r_other_time_ms": 2.414378573,"filtered": 100,"r_filtered": 100},"block-nl-join": {"table": {"table_name": "tbl1","access_type": "ALL","r_loops": 3,"rows": 1314,"r_rows": 1314,"r_table_time_ms": 652.4840785,"r_other_time_ms": 2010.371111,"filtered": 100,"r_filtered": 100},"buffer_type": "flat","buffer_size": "256Kb","join_type": "BNL","attached_condition": "tbl1.a_str_1 = cast(tbl0.a_dte_3 as char(30) charset utf8mb3)","r_filtered": 0},...Note tbl1 has r_filtered=0.
The filtering is done early.
We read tbl0, read tbl1, then join them which gives 1314*1314 record combinations, which gives ~2.8 sec total run time.