[MDEV-29094] Many-table join optimization: re-run Join Order Benchmark Created: 2022-07-13  Updated: 2022-07-13  Resolved: 2022-07-13

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Tests
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-28852 Improve optimization of joins with ma... Closed

 Description   

It is not clear whether the benchmark is releavant, but re-running it is cheap.

 Comments   
Comment by Sergei Petrunia [ 2022-07-13 ]

I've ran it now I don't observe any significant difference with 10.6:

query_name mariadb-10.6 mariadb-10.10 Speedup%
10a 427 429 100.47
10b 147 148 100.68
10c 36471 35907 98.45
11a 92 90 97.83
11b 41 41 100.00
11c 578 565 97.75
11d 194 193 99.48
12a 2336 2288 97.95
12b 363 348 95.87
12c 2846 2780 97.68
13a 17278 16739 96.88
13b 5429 5273 97.13
13c 1058 1012 95.65
13d 19178 18696 97.49
14a 228 224 98.25
14b 67 68 101.49
14c 699 698 99.86
15a 3643 3600 98.82
15b 41 41 100.00
15c 3649 3609 98.90
15d 3333 3297 98.92
16a 112 113 100.89
16b 14808 14778 99.80
16c 1283 1280 99.77
16d 1015 1009 99.41
17a 7770 7686 98.92
17b 3619 3587 99.12
17c 3496 3465 99.11
17d 19753 19542 98.93
17e 7792 7689 98.68
17f 19793 19551 98.78
18a 4202 4127 98.22
18b 1932 1905 98.60
18c 8999 8875 98.62
19a 12775 12578 98.46
19b 3933 3840 97.64
19c 11809 11527 97.61
19d 19532 19106 97.82
1a 11 10 90.91
1b 0 0 #DIV/0!
1c 1 1 100.00
1d 0 0 #DIV/0!
20a 1488 1444 97.04
20b 638 622 97.49
20c 537 521 97.02
21a 99 97 97.98
21b 72 71 98.61
21c 126 123 97.62
22a 944 928 98.31
22b 357 349 97.76
22c 5597 5499 98.25
22d 10852 10602 97.70
23a 4044 3951 97.70
23b 122 121 99.18
23c 4048 3957 97.75
24a 1028 1020 99.22
24b 44 44 100.00
25a 4997 4975 99.56
25b 177 177 100.00
25c 11597 11399 98.29
26a 245 232 94.69
26b 53 53 100.00
26c 549 540 98.36
27a 53 52 98.11
27b 45 44 97.78
27c 87 87 100.00
28a 1551 1529 98.58
28b 823 815 99.03
28c 2211 2174 98.33
29a 28 27 96.43
29b 23 23 100.00
29c 204 201 98.53
2a 432 424 98.15
2b 431 424 98.38
2c 431 422 97.91
2d 438 432 98.63
30a 1055 1037 98.29
30b 230 227 98.70
30c 4310 4248 98.56
31a 2041 1997 97.84
31b 241 236 97.93
31c 2270 2229 98.19
32a 16 16 100.00
32b 149 147 98.66
33a 11 10 90.91
33b 5 5 100.00
33c 119 116 97.48
3a 190 188 98.95
3b 92 90 97.83
3c 430 422 98.14
4a 2342 2246 95.90
4b 501 480 95.81
4c 5064 4904 96.84
5a 1360 1344 98.82
5b 1358 1342 98.82
5c 1392 1374 98.71
6a 20 21 105.00
6b 110 110 100.00
6c 16 16 100.00
6d 2675 2656 99.29
6e 19 20 105.26
6f 2673 2664 99.66
7a 1239 1231 99.35
7b 144 141 97.92
7c 13272 13077 98.53
8a 2725 2686 98.57
8b 204 201 98.53
8c 22641 22232 98.19
8d 2789 2731 97.92
9a 8016 7783 97.09
9b 7905 7679 97.14
9c 8999 8760 97.34
9d 11064 10776 97.40
Comment by Sergei Petrunia [ 2022-07-13 ]

Re-run with v4 patch for join pruning:
The numbers are query time in milliseconds:

query_name m-10.6 m-10.10 m-10.10-patch-v4
10a 427 429 437
10b 147 148 145
10c 36471 35907 36263
11a 92 90 91
11b 41 41 41
11c 578 565 574
11d 194 193 198
12a 2336 2288 2304
12b 363 348 352
12c 2846 2780 2810
13a 17278 16739 17058
13b 5429 5273 5319
13c 1058 1012 1051
13d 19178 18696 18967
14a 228 224 232
14b 67 68 68
14c 699 698 707
15a 3643 3600 3649
15b 41 41 41
15c 3649 3609 3652
15d 3333 3297 3307
16a 112 113 113
16b 14808 14778 14781
16c 1283 1280 1278
16d 1015 1009 1011
17a 7770 7686 7720
17b 3619 3587 3599
17c 3496 3465 3462
17d 19753 19542 19690
17e 7792 7689 7740
17f 19793 19551 19648
18a 4202 4127 4163
18b 1932 1905 1926
18c 8999 8875 8991
19a 12775 12578 12826
19b 3933 3840 3939
19c 11809 11527 11796
19d 19532 19106 19281
1a 11 10 11
1b 0 0 0
1c 1 1 1
1d 0 0 0
20a 1488 1444 1454
20b 638 622 624
20c 537 521 531
21a 99 97 98
21b 72 71 72
21c 126 123 125
22a 944 928 756
22b 357 349 164
22c 5597 5499 5308
22d 10852 10602 10727
23a 4044 3951 4047
23b 122 121 123
23c 4048 3957 4049
24a 1028 1020 1029
24b 44 44 44
25a 4997 4975 5007
25b 177 177 173
25c 11597 11399 11577
26a 245 232 236
26b 53 53 53
26c 549 540 539
27a 53 52 70
27b 45 44 43
27c 87 87 86
28a 1551 1529 1677
28b 823 815 872
28c 2211 2174 2413
29a 28 27 28
29b 23 23 24
29c 204 201 202
2a 432 424 432
2b 431 424 428
2c 431 422 428
2d 438 432 433
30a 1055 1037 1055
30b 230 227 229
30c 4310 4248 4297
31a 2041 1997 2023
31b 241 236 238
31c 2270 2229 2262
32a 16 16 16
32b 149 147 148
33a 11 10 10
33b 5 5 5
33c 119 116 174
3a 190 188 186
3b 92 90 90
3c 430 422 427
4a 2342 2246 2280
4b 501 480 483
4c 5064 4904 4961
5a 1360 1344 1342
5b 1358 1342 1344
5c 1392 1374 1379
6a 20 21 20
6b 110 110 109
6c 16 16 16
6d 2675 2656 2668
6e 19 20 19
6f 2673 2664 2664
7a 1239 1231 1231
7b 144 141 143
7c 13272 13077 13173
8a 2725 2686 2711
8b 204 201 201
8c 22641 22232 22531
8d 2789 2731 2763
9a 8016 7783 8015
9b 7905 7679 7865
9c 8999 8760 8819
9d 11064 10776 10852
Generated at Thu Feb 08 10:05:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.