[MDEV-32560] Slow query with multiple Joins and subquery (30000 times slower than on version 10.4.) Created: 2023-10-24  Updated: 2024-01-18

Status: In Progress
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5, 10.6, 10.9, 10.10, 10.11, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5
Fix Version/s: 10.5, 10.6, 10.11

Type: Bug Priority: Critical
Reporter: Ján Forgáč Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: not-11.0+, performance
Environment:

tested on both Linux and Windows

Attachments: Text File bad-commit-eb483c5181ab4-analyze-format-json.txt     Text File bad-commit-eb483c5181ab4-explain-extended.txt     Text File good-commit-b3ab3105fdb3-analyze-format-json.txt     Text File good-commit-b3ab3105fdb3-explain-extended.txt     Zip Archive test.zip    

 Description   

This query 

SELECT d0_.id AS id_0, d0_.root_id AS sclr_1, d1_.internal_amount AS internal_amount_2 
FROM d_product d0_ 
LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id 
LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id 
LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id 
LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id
 
JOIN (
 
	SELECT d0_.root_id AS root_id, d0_.id AS id_0, d0_.root_id AS sclr_1, MAX(d1_.internal_amount) AS internal_amount_2 
	FROM d_product d0_ 
	LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id 
	LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id 
	LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id 
	LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id 
	WHERE d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0 
	GROUP BY d0_.root_id
 
      ) subq ON subq.root_id = d0_.root_id 
WHERE subq.internal_amount_2 = d1_.internal_amount AND d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN
 
(0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0 
GROUP BY d0_.root_id 
ORDER BY d0_.purchasable_variant DESC, d1_.internal_amount_2 DESC, d0_.id ASC

on version 10.3.31 and 10.4.31 runs cca 2ms
with same dataset on 10.5+ it runs 60000ms and more

I provide test data, where the problem is obvious, if you import this data and run the query above.

 Comments   
Comment by Ján Forgáč [ 2023-10-24 ]

This:

optimizer_use_condition_selectivity = 1

solved the problem, as is mentioned here: https://www.reddit.com/r/mariadb/comments/104527q/comment/j362k5p/

But default value is 4: Use selectivity of all range predicates estimated with histogram

Comment by Alice Sherepa [ 2023-10-24 ]

Thank you for the report! I repeated as described on 10.5-10.11, while 11.0+ performed as expected.
10.4.31

MariaDB [test]> SELECT d0_.id AS id_0, d0_.root_id AS sclr_1, d1_.internal_amount AS internal_amount_2 
    -> FROM d_product d0_ 
    -> LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id 
    -> LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id 
    -> LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id 
    -> LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id
 
    -> JOIN (
 
    -> SELECT d0_.root_id AS root_id, d0_.id AS id_0, d0_.root_id AS sclr_1, MAX(d1_.internal_amount) AS internal_amount_2 
    -> FROM d_product d0_ 
    -> LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id 
    -> LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id 
    -> LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id 
    -> LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id 
    -> WHERE d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0 
    -> GROUP BY d0_.root_id
 
    ->       ) subq ON subq.root_id = d0_.root_id 
    -> WHERE subq.internal_amount_2 = d1_.internal_amount AND d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN
 
    -> (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0 
    -> GROUP BY d0_.root_id 
    -> ORDER BY d0_.purchasable_variant DESC, d1_.internal_amount_2 DESC, d0_.id ASC;
 
+------------------+------------------+-------------------+
 
| id_0             | sclr_1           | internal_amount_2 |
 
+------------------+------------------+-------------------+
 
| �[s��S��D�k��u         | �[s��S��D�k��u         |                 7 |
 
| �[{���~���k��u          | �[{���~���k��u          |                 7 |
 
| �[|��g�ڬk��u         | �[|��g�ڬk��u         |                 7 |
 
| �[{_I���k��u       | �[{_I���k��u       |                 3 |
 
| �[{���k��u          | �[{���k��u          |                 3 |
 
| �[{���^�k��u        | �[{���^�k��u        |                 3 |
 
| �[{�H�n��k��u        | �[{�H�n��k��u        |                 3 |
 
| �[|8sƺ`�k��u      | �[|8sƺ`�k��u      |                 3 |
 
| �[|1 ��q�k��u       | �[|1 ��q�k��u       |                 3 |
 
| �[|C�Q����k��u        | �[|C�Q����k��u        |                 3 |
 
| �[|্@���k��u         | �[|্@���k��u         |                 3 |
 
| �[} �橃�k��u       | �[} �橃�k��u       |                 3 |
 
| �[�N����k��u         | �[�N����k��u         |                 3 |
 
+------------------+------------------+-------------------+
 
13 rows in set (0,006 sec)
 
 
 
MariaDB [test]> explain extended SELECT d0_.id AS id_0, d0_.root_id AS sclr_1, d1_.internal_amount AS internal_amount_2  FROM d_product d0_  LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id  LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id  LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id  LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id JOIN ( SELECT d0_.root_id AS root_id, d0_.id AS id_0, d0_.root_id AS sclr_1, MAX(d1_.internal_amount) AS internal_amount_2  FROM d_product d0_  LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id  LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id  LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id  LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id  WHERE d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0  GROUP BY d0_.root_id       ) subq ON subq.root_id = d0_.root_id  WHERE subq.internal_amount_2 = d1_.internal_amount AND d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0  GROUP BY d0_.root_id  ORDER BY d0_.purchasable_variant DESC, d1_.internal_amount_2 DESC, d0_.id ASC;
 
+------+-----------------+------------+------------+--------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------------------+--------+----------+----------------------------------------------+
 
| id   | select_type     | table      | type       | possible_keys                                                                                    | key                                       | key_len | ref               | rows   | filtered | Extra                                        |
 
+------+-----------------+------------+------------+--------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------------------+--------+----------+----------------------------------------------+
 
|    1 | PRIMARY         | d2_        | const      | PRIMARY                                                                                          | PRIMARY                                   | 16      | const             | 1      |   100.00 | Using index; Using temporary; Using filesort |
 
|    1 | PRIMARY         | s3_        | const      | PRIMARY                                                                                          | PRIMARY                                   | 4       | const             | 1      |   100.00 | Using index                                  |
 
|    1 | PRIMARY         | d0_        | ref        | PRIMARY,UNIQ_producer_code,IDX_25A6FEBF89B658FE,IDX_25A6FEBF79066886,IDX_25A6FEBFDCD6110,visible | UNIQ_producer_code                        | 17      | const             | 13     |   100.00 | Using index condition; Using where           |
 
|    1 | PRIMARY         | d1_        | eq_ref     | PRIMARY,IDX_internalAmount                                                                       | PRIMARY                                   | 16      | test.d0_.stock_id | 1      |     1.27 | Using where                                  |
 
|    1 | PRIMARY         | d4_        | eq_ref     | PRIMARY,IDX_9E5EF6FD4584665A,IDX_9E5EF6FD9DAFEFE6                                                | PRIMARY                                   | 20      | test.d0_.id,const | 1      |   100.00 | Using index                                  |
 
|    1 | PRIMARY         | <derived2> | ref        | key0                                                                                             | key0                                      | 17      | test.d0_.root_id  | 2      |   100.00 | Using where                                  |
 
|    2 | LATERAL DERIVED | d2_        | const      | PRIMARY                                                                                          | PRIMARY                                   | 16      | const             | 1      |   100.00 | Using index                                  |
 
|    2 | LATERAL DERIVED | s3_        | const      | PRIMARY                                                                                          | PRIMARY                                   | 4       | const             | 1      |   100.00 | Using index                                  |
 
|    2 | LATERAL DERIVED | d0_        | ref|filter | PRIMARY,UNIQ_producer_code,IDX_25A6FEBF89B658FE,IDX_25A6FEBF79066886,IDX_25A6FEBFDCD6110,visible | IDX_25A6FEBF79066886|IDX_25A6FEBF89B658FE | 17|17   | test.d0_.root_id  | 4 (0%) |    50.00 | Using where; Using rowid filter              |
 
|    2 | LATERAL DERIVED | d1_        | eq_ref     | PRIMARY,IDX_internalAmount                                                                       | PRIMARY                                   | 16      | test.d0_.stock_id | 1      |     1.27 | Using where                                  |
 
|    2 | LATERAL DERIVED | d4_        | eq_ref     | PRIMARY,IDX_9E5EF6FD4584665A,IDX_9E5EF6FD9DAFEFE6                                                | PRIMARY                                   | 20      | test.d0_.id,const | 1      |   100.00 | Using index                                  |
 
+------+-----------------+------------+------------+--------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------------------+--------+----------+----------------------------------------------+
 
11 rows in set, 1 warning (0,003 sec)

10.5.19

MariaDB [test]> SELECT d0_.id AS id_0, d0_.root_id AS sclr_1, d1_.internal_amount AS internal_amount_2 
    -> FROM d_product d0_ 
    -> LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id 
    -> LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id 
    -> LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id 
    -> LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id
 
    -> JOIN (
 
    -> SELECT d0_.root_id AS root_id, d0_.id AS id_0, d0_.root_id AS sclr_1, MAX(d1_.internal_amount) AS internal_amount_2 
    -> FROM d_product d0_ 
    -> LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id 
    -> LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id 
    -> LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id 
    -> LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id 
    -> WHERE d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0 
    -> GROUP BY d0_.root_id
 
    ->       ) subq ON subq.root_id = d0_.root_id 
    -> WHERE subq.internal_amount_2 = d1_.internal_amount AND d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN
 
    -> (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0 
    -> GROUP BY d0_.root_id 
    -> ORDER BY d0_.purchasable_variant DESC, d1_.internal_amount_2 DESC, d0_.id ASC;
 
^CCtrl-C -- query killed. Continuing normally.
 
ERROR 1317 (70100): Query execution was interrupted
 
MariaDB [test]> explain extended SELECT d0_.id AS id_0, d0_.root_id AS sclr_1, d1_.internal_amount AS internal_amount_2  FROM d_product d0_  LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id  LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id  LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id  LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id JOIN ( SELECT d0_.root_id AS root_id, d0_.id AS id_0, d0_.root_id AS sclr_1, MAX(d1_.internal_amount) AS internal_amount_2  FROM d_product d0_  LEFT JOIN d_product_stock d1_ ON d0_.stock_id = d1_.id  LEFT JOIN d_producer d2_ ON d0_.producer_id = d2_.id  LEFT JOIN d_product_categories_all d4_ ON d0_.id = d4_.product_id  LEFT JOIN structure_node s3_ ON s3_.id = d4_.structure_node_id  WHERE d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0  GROUP BY d0_.root_id       ) subq ON subq.root_id = d0_.root_id  WHERE subq.internal_amount_2 = d1_.internal_amount AND d0_.visible = 1 AND s3_.id = 7 AND d2_.id IN (0x11ede8e0ac4cb26e90e7ac1f6bf1f375) AND d1_.internal_amount > 0  GROUP BY d0_.root_id  ORDER BY d0_.purchasable_variant DESC, d1_.internal_amount_2 DESC, d0_.id ASC;
 
+------+-------------+------------+--------+--------------------------------------------------------------------------------------------------+----------------------+---------+------------------------+-------+----------+----------------------------------------------+
 
| id   | select_type | table      | type   | possible_keys                                                                                    | key                  | key_len | ref                    | rows  | filtered | Extra                                        |
 
+------+-------------+------------+--------+--------------------------------------------------------------------------------------------------+----------------------+---------+------------------------+-------+----------+----------------------------------------------+
 
|    1 | PRIMARY     | d2_        | const  | PRIMARY                                                                                          | PRIMARY              | 16      | const                  | 1     |   100.00 | Using index; Using temporary; Using filesort |
 
|    1 | PRIMARY     | s3_        | const  | PRIMARY                                                                                          | PRIMARY              | 4       | const                  | 1     |   100.00 | Using index                                  |
 
|    1 | PRIMARY     | <derived2> | ALL    | NULL                                                                                             | NULL                 | NULL    | NULL                   | 13    |     0.00 | Using where                                  |
 
|    1 | PRIMARY     | d4_        | ref    | PRIMARY,IDX_9E5EF6FD4584665A,IDX_9E5EF6FD9DAFEFE6                                                | IDX_9E5EF6FD9DAFEFE6 | 4       | const                  | 71936 |   100.00 | Using index                                  |
 
|    1 | PRIMARY     | d1_        | ref    | PRIMARY,IDX_internalAmount                                                                       | IDX_internalAmount   | 4       | subq.internal_amount_2 | 552   |   100.00 |                                              |
 
|    1 | PRIMARY     | d0_        | ref    | PRIMARY,UNIQ_producer_code,IDX_25A6FEBF89B658FE,IDX_25A6FEBF79066886,IDX_25A6FEBFDCD6110,visible | UNIQ_producer_code   | 17      | const                  | 13    |   100.00 | Using index condition; Using where           |
 
|    2 | DERIVED     | d2_        | const  | PRIMARY                                                                                          | PRIMARY              | 16      | const                  | 1     |   100.00 | Using index; Using temporary; Using filesort |
 
|    2 | DERIVED     | s3_        | const  | PRIMARY                                                                                          | PRIMARY              | 4       | const                  | 1     |   100.00 | Using index                                  |
 
|    2 | DERIVED     | d0_        | ref    | PRIMARY,UNIQ_producer_code,IDX_25A6FEBF89B658FE,IDX_25A6FEBF79066886,IDX_25A6FEBFDCD6110,visible | IDX_25A6FEBF89B658FE | 17      | const                  | 13    |   100.00 | Using index condition; Using where           |
 
|    2 | DERIVED     | d1_        | eq_ref | PRIMARY,IDX_internalAmount                                                                       | PRIMARY              | 16      | test.d0_.stock_id      | 1     |     1.30 | Using where                                  |
 
|    2 | DERIVED     | d4_        | eq_ref | PRIMARY,IDX_9E5EF6FD4584665A,IDX_9E5EF6FD9DAFEFE6                                                | PRIMARY              | 20      | test.d0_.id,const      | 1     |   100.00 | Using index                                  |
 
+------+-------------+------------+--------+--------------------------------------------------------------------------------------------------+----------------------+---------+------------------------+-------+----------+----------------------------------------------+
 
11 rows in set, 1 warning (0,001 sec)
 
 
 
Note (Code 1003): /* select#1 */ select `test`.`d0_`.`id` AS `id_0`,`test`.`d0_`.`root_id` AS `sclr_1`,`test`.`d1_`.`internal_amount` AS `internal_amount_2` from `test`.`d_product` `d0_` join `test`.`d_product_stock` `d1_` join `test`.`d_producer` `d2_` join `test`.`d_product_categories_all` `d4_` join `test`.`structure_node` `s3_` join (/* select#2 */ select `test`.`d0_`.`root_id` AS `root_id`,`test`.`d0_`.`id` AS `id_0`,`test`.`d0_`.`root_id` AS `sclr_1`,max(`test`.`d1_`.`internal_amount`) AS `internal_amount_2` from `test`.`d_product` `d0_` join `test`.`d_product_stock` `d1_` join `test`.`d_producer` `d2_` join `test`.`d_product_categories_all` `d4_` join `test`.`structure_node` `s3_` where `test`.`d0_`.`visible` = 1 and `test`.`d4_`.`structure_node_id` = 7 and `test`.`d0_`.`producer_id` = 0x90e7ac1f6bf1f375 and `test`.`d4_`.`product_id` = `test`.`d0_`.`id` and `test`.`d1_`.`id` = `test`.`d0_`.`stock_id` and `test`.`d1_`.`internal_amount` > 0 group by `test`.`d0_`.`root_id`) `subq` where `test`.`d1_`.`internal_amount` = `subq`.`internal_amount_2` and `test`.`d0_`.`visible` = 1 and `test`.`d4_`.`structure_node_id` = 7 and `test`.`d0_`.`producer_id` = 0x90e7ac1f6bf1f375 and `test`.`d0_`.`root_id` = `subq`.`root_id` and `test`.`d0_`.`id` = `test`.`d4_`.`product_id` and `test`.`d0_`.`stock_id` = `test`.`d1_`.`id` and `subq`.`internal_amount_2` > 0 group by `test`.`d0_`.`root_id` order by `test`.`d0_`.`purchasable_variant` desc,`test`.`d1_`.`internal_amount_2` desc,`test`.`d0_`.`id`

Comment by Dave Gosselin [ 2024-01-16 ]

Times below are for relative comparison only; their absolute values are not important.
Discoveries made today:

  • Reported query completes in about 30 minutes
  • SET optimizer_use_condition_selectivity = 1; alleviates performance problem in 10.5; any other value 2-5 does not.
  • Removing the WHERE clause from the SELECT subquery in the reported problem query restores expected performance and returns the correct result in about half the time as 10.4 (100ms compared to 210ms)
  • Changing the storage engine from innodb to myisam when loading the data from the attached SQL file results in the reported query running in about 120ms, unaltered, rather than 30 minutes.
  • SHOW PROFILE output has mariadb spending all of its time, comparatively, in 'Creating sort index' (999.999999)
  • Linux perf tools show us spending most of our time in innodb code. This appears to conflict with SHOW PROFILE, so more investigation is needed.
  • Sort index stage begins at JOIN_TAB::sort_table, majority implementation found in create_sort_index

Attempted to 'git bisect' to where problem was introduced, but encountered many problems building 10.5.x releases before 10.5.14, due to: (1) mysqld crashing with signal 6 when initializing the database, (2) upstream PCRE2 support unavailable so relied instead on distro which may or may not be compatible, (3) OpenSSL version too old on database initialization, and (4) "*** stack smashing detected ***" error.

Comment by Dave Gosselin [ 2024-01-18 ]

I overcame the four issues described in my last comment related to git bisect.
In 10.5, the performance regression was introduced at git sha eb483c5181ab430877c135c16224284cfc517b3d. The git sha immediately preceding it, b3ab3105fdb34dae6c2d4270751bc0694c3d9df8 works as expected and is consistent with the performance in 10.4
As noted in the ticket, version 10.11 is affected as well. I tested 11.0 and was surprised to find that the performance regression no longer existed in 11.0. Again, using git bisect, I discovered in 11.0 that git sha 956980971f98e1eb451e71856fe823470a5d1d32 alleviates the performance regression. The commit immediately preceding it, 6fa7451759e0832cc13a0e47a8300292b96bdb75 exhibits the regression.
This means that there are two commits of interest to look at: when the regression was introduced in 10.5 (eb483c5181ab430877c135c16224284cfc517b3d) and later when it was alleviated in 11.0 (956980971f98e1eb451e71856fe823470a5d1d32). The changes in 11.0 don't seem to be written in a way to be backported to 10.5, and I suspect that the regression was alleviated incidentally due to a lot of other work in this area. Perhaps we can fix the regression in 10.5 on its own?
I've attached four files which contain ANALYZE FORMAT=JSON and EXPLAIN EXTENDED for the complaint query for mariadb built at each of these four commits. I don't have MDEVs that I can reference because none of the commit messages reference any. During the SQL Processor call today, the team suggested that I provide this analysis and reassign to psergei for disposition (maybe to further reassign to Monty?).
bad-commit-eb483c5181ab4-analyze-format-json.txt bad-commit-eb483c5181ab4-explain-extended.txt good-commit-b3ab3105fdb3-analyze-format-json.txt good-commit-b3ab3105fdb3-explain-extended.txt

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