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

MariaDB optimizer significantly slower with CTE than competitive product

    XMLWordPrintable

    Details

      Description

      Customer came up with a query which is linearly slower with the amount of UNIONs attached to a CTE. This should not because CTE should only be evaluated once. He further showed that implementing it with a TEMPORARY TABLE did not show the effect.

      Query + Benchmark + Query Execution Plan:

      WITH t AS (
      SELECT SUM(id) AS c, data AS pn
        FROM test
       WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59'
         AND data LIKE 'ab%'
       GROUP BY data
       ORDER BY data
      -- MariaDB 10.9: 0.37s
      -- Competing product: 0.70s
      -- MariaDB 10.6: 0.66s
      )
      SELECT 'AAA' AS label, ((SELECT c FROM t WHERE pn = 'ab06d9ca0e5eb27e5bb2e5c0f345b782')+(SELECT c FROM t WHERE pn = 'ab0f8e861348995d1cbf9ec21d4bf52d'))/((SELECT c FROM t WHERE pn = 'ab134910f6808802cf44b587fb825ab5')+(SELECT c FROM t WHERE pn = 'ab1d6682feb41a97054e8c634ecd5552'))*100 AS value
      -- MariaDB 10.9: 0.79s
      -- Competing product: 0.70s
      -- MariaDB 10.6: 2.04s
      UNION
      SELECT 'BBB' AS label, (SELECT c FROM t WHERE pn = 'ab2516b576d7463f106b5671bdac6d32')/((SELECT c FROM t WHERE pn = 'ab28e05f4fa736f4738a2ff6cf494f04')+(SELECT c FROM t WHERE pn = 'ab31ab6a7d52fee49990d63dee58371b'))*100 AS value
      -- MariaDB 10.9: 1.38s
      -- Competing product: 0.70s
      -- MariaDB 10.6: 3.54s
      UNION
      SELECT 'CCC' AS label, (SELECT c FROM t WHERE pn = 'ab390b211d7bb3cc19fc1f808fb1bc3a')/(SELECT c FROM t WHERE pn = 'ab45af9e4ca10d3a618d40ec3f230349')*100 AS value
      -- MariaDB 10.9: 1.76s
      -- Competing product: 0.70s
      -- MariaDB 10.6: 4.58s
      UNION
      SELECT 'DDD' AS label, ((SELECT c FROM t WHERE pn = 'ab531756624d8287d38eb5fe5809bdb4')+(SELECT c FROM t WHERE pn = 'ab5aaa230cc64c15a61f908aea1c3f9c'))/((SELECT c FROM t WHERE pn = 'ab60f7712cd176b6ac26040d69be0e11')+(SELECT c FROM t WHERE pn = 'ab674e464997f4916e7ed23942c53d23'))*100 AS value
      UNION
      SELECT 'EEE' AS label, (SELECT c FROM t WHERE pn = 'ab6cc3781441a08d67eb8039521c63bb')/((SELECT c FROM t WHERE pn = 'ab7612bca85db4f6c4638a658fbee646')+(SELECT c FROM t WHERE pn = 'ab7811882f520c2051c6761edf4c306f'))*100 AS value
      UNION
      SELECT 'FFF' AS label, (SELECT c FROM t WHERE pn = 'ab7f0bd3758fbdac0ff6abb24db166ad')/(SELECT c FROM t WHERE pn = 'ab87d7d598ad47ca5db3bf9e6cbdee48')*100 AS value
      -- MariaDB 10.9: 3.51s
      -- Competing product: 0.71s
      -- MariaDB 10.6: 9.15s
      ;
       
      -- MariaDB 10.6 and 10.9 (same QEP)
      +------+--------------+------------------------+------+---------------+------+---------+------+---------+----------------+
      | id   | select_type  | table                  | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
      +------+--------------+------------------------+------+---------------+------+---------+------+---------+----------------+
      |    1 | PRIMARY      | NULL                   | NULL | NULL          | NULL | NULL    | NULL | NULL    | No tables used |
      |    6 | SUBQUERY     | <derived28>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   28 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |    5 | SUBQUERY     | <derived27>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   27 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |    4 | SUBQUERY     | <derived26>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   26 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |    3 | SUBQUERY     | <derived2>             | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |    2 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |    7 | UNION        | NULL                   | NULL | NULL          | NULL | NULL    | NULL | NULL    | No tables used |
      |   10 | SUBQUERY     | <derived31>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   31 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |    9 | SUBQUERY     | <derived30>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   30 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |    8 | SUBQUERY     | <derived29>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   29 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   11 | UNION        | NULL                   | NULL | NULL          | NULL | NULL    | NULL | NULL    | No tables used |
      |   13 | SUBQUERY     | <derived33>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   33 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   12 | SUBQUERY     | <derived32>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   32 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   14 | UNION        | NULL                   | NULL | NULL          | NULL | NULL    | NULL | NULL    | No tables used |
      |   18 | SUBQUERY     | <derived37>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   37 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   17 | SUBQUERY     | <derived36>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   36 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   16 | SUBQUERY     | <derived35>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   35 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   15 | SUBQUERY     | <derived34>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   34 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   19 | UNION        | NULL                   | NULL | NULL          | NULL | NULL    | NULL | NULL    | No tables used |
      |   22 | SUBQUERY     | <derived40>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   40 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   21 | SUBQUERY     | <derived39>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   39 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   20 | SUBQUERY     | <derived38>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   38 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   23 | UNION        | NULL                   | NULL | NULL          | NULL | NULL    | NULL | NULL    | No tables used |
      |   25 | SUBQUERY     | <derived42>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   42 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   24 | SUBQUERY     | <derived41>            | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      |   41 | DERIVED      | test                   | ALL  | NULL          | NULL | NULL    | NULL | 1044500 | Using where    |
      | NULL | UNION RESULT | <union1,7,11,14,19,23> | ALL  | NULL          | NULL | NULL    | NULL | NULL    |                |
      +------+--------------+------------------------+------+---------------+------+---------+------+---------+----------------+
      

      – Competitive product

      +----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+
      | id | select_type  | table                   | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra                                        |
      +----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+
      |  1 | PRIMARY      | NULL                    | NULL       | NULL | NULL          | NULL        | NULL    | NULL  |   NULL |     NULL | No tables used                               |
      |  8 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      |  6 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      |  4 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      |  2 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      |  3 | DERIVED      | test                    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 949500 |     1.23 | Using where; Using temporary; Using filesort |
      | 10 | UNION        | NULL                    | NULL       | NULL | NULL          | NULL        | NULL    | NULL  |   NULL |     NULL | No tables used                               |
      | 15 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 13 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 11 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 17 | UNION        | NULL                    | NULL       | NULL | NULL          | NULL        | NULL    | NULL  |   NULL |     NULL | No tables used                               |
      | 20 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 18 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 22 | UNION        | NULL                    | NULL       | NULL | NULL          | NULL        | NULL    | NULL  |   NULL |     NULL | No tables used                               |
      | 29 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 27 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 25 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 23 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 31 | UNION        | NULL                    | NULL       | NULL | NULL          | NULL        | NULL    | NULL  |   NULL |     NULL | No tables used                               |
      | 36 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 34 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 32 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 38 | UNION        | NULL                    | NULL       | NULL | NULL          | NULL        | NULL    | NULL  |   NULL |     NULL | No tables used                               |
      | 41 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | 39 | SUBQUERY     | <derived3>              | NULL       | ref  | <auto_key0>   | <auto_key0> | 131     | const |     10 |   100.00 | NULL                                         |
      | NULL | UNION RESULT | <union1,10,17,22,31,38> | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |   NULL |     NULL | Using temporary                              |
      +----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+
      

      Testdata can be attached but exceeds size (25.83 MB).

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            oli Oli Sennhauser
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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