Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29072

Slower query with MDEV-28852 comparing to 10.6 or 10.10 (5 sec vs 0.03 sec)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Not a Bug
    • Affects Version/s: N/A
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      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

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.