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)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Not a Bug
    • N/A
    • N/A
    • Optimizer
    • 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

          Task - A task that needs to be done. MDEV-28852 Improve optimization of joins with many tables, including eq_ref tables

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Sergei Petrunia made transition -
            Open Closed
            		1d 17h 5m 1

            People

              psergei Sergei Petrunia
              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.