[MDEV-29072] Slower query with MDEV-28852 comparing to 10.6 or 10.10 (5 sec vs 0.03 sec) Created: 2022-07-09  Updated: 2022-07-11  Resolved: 2022-07-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-28852 Improve optimization of joins with ma... Closed

 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) |
 
+------+--------------------+--------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------------+



 Comments   
Comment by Sergei Petrunia [ 2022-07-11 ]

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) |
 
+------+--------------------+--------+------+---------------+------+---------+------+------+----------------------------------------------------------------------------+

Comment by Sergei Petrunia [ 2022-07-11 ]

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.

Comment by Sergei Petrunia [ 2022-07-11 ]

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
 
                        },

Comment by Sergei Petrunia [ 2022-07-11 ]

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
 
                        }

Comment by Sergei Petrunia [ 2022-07-11 ]

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.

Generated at Thu Feb 08 10:05:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.