[MDEV-30017] MariaDB optimizer significantly slower with CTE than competitive product Created: 2022-11-15  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.6.11, 10.9.4
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Oli Sennhauser Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 1
Labels: CTE, optimizer, subquery, union
Environment:

Linux, Ubuntu 20.04, n.a.

Issue Links:
Duplicate
duplicates MDEV-26591 Is MariaDB really caching or reusing ... Open
PartOf
is part of MDEV-28906 MySQL 8.0 desired compatibility Open
Relates
relates to MDEV-30312 Improvement: make derived_with_keys n... Open

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

 Comments   
Comment by Daniel Black [ 2022-11-17 ]

Thanks oli,

Test data welcome to upload on https://mariadb.com/kb/en/meta/mariadb-ftp-server/

Comment by Oli Sennhauser [ 2022-11-17 ]

File uploaded...

Comment by Daniel Black [ 2022-11-17 ]

https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html

Consider the following query:

SELECT *
 
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
 
         ON t1.f1=derived_t2.f1;

The optimizer constructs an index over column f1 from derived_t2 if doing so would enable use of ref access for the lowest cost execution plan

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