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

Slower query #2 with MDEV-28852 comparing to 10.6 or 10.10 (820 sec vs 0.9 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 * 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

            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.