[MDEV-31206] Window functions are extremely slow comparing to MySQL - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Optimizer - Window functions
Labels:
None

Description

SET max_recursive_iterations = 100000;

drop table if exists t;

create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte;

select max(a) over( ) from t;

The output from the client, connected to the server running with all defaults.
It's reproducible with MTR just as well, but I wanted to rule out all differences in server configuration between MTR and regular server as well as between MariaDB's MTR and MySQL's MTR.

11.0.1 release
MariaDB [test]> select max(a) over( ) from t;
...
+----------------+
30000 rows in set (31.360 sec)

Test case for MySQL differs in the name of the variable only:

SET cte_max_recursion_depth = 100000;

drop table if exists t;

create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte;

select max(a) over( ) from t;

8.0.28 release
mysql> select max(a) over( ) from t;
...
+----------------+
30000 rows in set (0.04 sec)

So, it's 30 sec vs 0.04 sec on a release bintar.

Execution plans:

MySQL
mysql> explain format=json select max(a) over( ) from t \G
************************* 1. row *************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3059.35"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"frame_buffer": {
"using_temporary_table": true,
"optimized_frame_evaluation": true
},
"functions": [
"max"
]
}
],
"table": {
"table_name": "t",
"access_type": "ALL",
"rows_examined_per_scan": 30351,
"rows_produced_per_join": 30351,
"filtered": "100.00",
"cost_info": {
"read_cost": "24.25",
"eval_cost": "3035.10",
"prefix_cost": "3059.35",
"data_read_per_join": "474K"
},
"used_columns": [
"a"
]
}
}
}
}
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------+
\| Level \| Code \| Message \|
+-------+------+----------------------------------------------------------------------------------------+
\| Note \| 1003 \| /* select#1 */ select max(`test`.`t`.`a`) OVER () AS `max(a) over( )` from `test`.`t` \|
+-------+------+----------------------------------------------------------------------------------------+

MariaDB
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost": 4.9214646,
"window_functions_computation": {
"sorts": [
{
"filesort": {
"sort_key": "a"
}
}
],
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t",
"access_type": "ALL",
"loops": 1,
"rows": 29623,
"cost": 4.9214646,
"filtered": 100
}
}
]
}
}
}
}
1 row in set (0.000 sec)

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2023-05-06 19:20

Elena Stepanova made changes - 2023-05-06 21:38

Field	Original Value	New Value
Description	{code:sql} SET max_recursive_iterations = 100000; drop table if exists t; create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte; select max(a) over( ) from t; {code} The output from the client, connected to the server running with all defaults. It's reproducible with MTR just as well, but I wanted to rule out all differences in server configuration between MTR and regular server as well as between MariaDB's MTR and MySQL's MTR. {code:sql\|title=11.0.1 release} MariaDB [test]> select max(a) over( ) from t; ... +----------------+ 30000 rows in set (31.360 sec) {code} Test case for MySQL differs in the name of the variable only: {code:sql} SET cte_max_recursion_depth = 100000; drop table if exists t; create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte; select max(a) over( ) from t; {code} {code:sql\|title=8.0.28 release} mysql> select max(a) over( ) from t; ... +----------------+ 30000 rows in set (0.04 sec) {code} So, it's 30 sec vs 0.04 sec on a release bintar. Execution plans: {code:sql\|title=MySQL} mysql> explain format=json select max(a) over( ) from t \G ************************* 1. row ************************* EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3059.35" }, "windowing": { "windows": [ { "name": "<unnamed window>", "frame_buffer": { "using_temporary_table": true, "optimized_frame_evaluation": true }, "functions": [ "max" ] } ], "table": { "table_name": "t", "access_type": "ALL", "rows_examined_per_scan": 30351, "rows_produced_per_join": 30351, "filtered": "100.00", "cost_info": { "read_cost": "24.25", "eval_cost": "3035.10", "prefix_cost": "3059.35", "data_read_per_join": "474K" }, "used_columns": [ "a" ] } } } } 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------+ \| Level \| Code \| Message \| +-------+------+----------------------------------------------------------------------------------------+ \| Note \| 1003 \| /* select#1 */ select max(`test`.`t`.`a`) OVER () AS `max(a) over( )` from `test`.`t` \| +-------+------+----------------------------------------------------------------------------------------+ {code} {code:sql\|title=MariaDB} EXPLAIN: { "query_block": { "select_id": 1, "cost": 4.9214646, "window_functions_computation": { "sorts": [ { "filesort": { "sort_key": "a" } } ], "temporary_table": { "nested_loop": [ { "table": { "table_name": "t", "access_type": "ALL", "loops": 1, "rows": 29623, "cost": 4.9214646, "filtered": 100 } } ] } } } } 1 row in set (0.000 sec) {code}	{code:sql} SET max_recursive_iterations = 100000; drop table if exists t; create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte; select max(a) over( ) from t; {code} The output from the client, connected to the server running with all defaults. It's reproducible with MTR just as well, but I wanted to rule out all differences in server configuration between MTR and regular server as well as between MariaDB's MTR and MySQL's MTR. {code:sql\|title=11.0.1 release} MariaDB [test]> select max(a) over( ) from t; ... +----------------+ 30000 rows in set (31.360 sec) {code} Test case for MySQL differs in the name of the variable only: {code:sql} SET cte_max_recursion_depth = 100000; drop table if exists t; create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte; select max(a) over( ) from t; {code} {code:sql\|title=8.0.28 release} mysql> select max(a) over( ) from t; ... +----------------+ 30000 rows in set (0.04 sec) {code} So, it's 30 sec vs 0.04 sec on a release bintar. Execution plans: {code:sql\|title=MySQL} mysql> explain format=json select max(a) over( ) from t \G ************************* 1. row ************************* EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3059.35" }, "windowing": { "windows": [ { "name": "<unnamed window>", "frame_buffer": { "using_temporary_table": true, "optimized_frame_evaluation": true }, "functions": [ "max" ] } ], "table": { "table_name": "t", "access_type": "ALL", "rows_examined_per_scan": 30351, "rows_produced_per_join": 30351, "filtered": "100.00", "cost_info": { "read_cost": "24.25", "eval_cost": "3035.10", "prefix_cost": "3059.35", "data_read_per_join": "474K" }, "used_columns": [ "a" ] } } } } 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------+ \| Level \| Code \| Message \| +-------+------+----------------------------------------------------------------------------------------+ \| Note \| 1003 \| /* select#1 */ select max(`test`.`t`.`a`) OVER () AS `max(a) over( )` from `test`.`t` \| +-------+------+----------------------------------------------------------------------------------------+ {code} {code:sql\|title=MariaDB} EXPLAIN: { "query_block": { "select_id": 1, "cost": 4.9214646, "window_functions_computation": { "sorts": [ { "filesort": { "sort_key": "a" } } ], "temporary_table": { "nested_loop": [ { "table": { "table_name": "t", "access_type": "ALL", "loops": 1, "rows": 29623, "cost": 4.9214646, "filtered": 100 } } ] } } } } 1 row in set (0.000 sec) {code}

Julien Fritsch made changes - 2023-11-28 10:24

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2023-11-28 10:28

Fix Version/s

10.10 [ 27530 ]

Julien Fritsch made changes - 2024-05-03 14:40

Fix Version/s

11.0 [ 28320 ]

Julien Fritsch made changes - 2024-09-10 15:04

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Sergei Petrunia

Reporter:: Elena Stepanova

Votes:: 1 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-05-06 19:20

Updated:: 2024-09-10 15:04

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration