doris
doris copied to clipboard
Published
20 hours ago •
apache
apache
[opt](nereids) modify cbo statistics in filter and scan
Proposed changes
Issue Number: (https://github.com/apache/doris/issues/34946)
The entire discussion process can be referred to this issue：https://github.com/apache/doris/issues/34946
Further comments
If this is a relatively large or complex change, kick off the discussion at [email protected] by explaining why you chose the solution you did and what alternatives you considered, etc...
Thank you for your contribution to Apache Doris. Don't know what should be done next? See How to process your PR
Since 2024-03-18, the Document has been moved to doris-website. See Doris Document.
hi keith, do you notice that in JoinEstimation, if child.rowCount is 0, estimator regard the input row count as 1. please refer to: https://github.com/apache/doris/blob/f53cf052d84335c09b2680259b1366343fc90c9a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java#L198
hi keith, do you notice that in JoinEstimation, if child.rowCount is 0, estimator regard the input row count as 1. please refer to:
https://github.com/apache/doris/blob/f53cf052d84335c09b2680259b1366343fc90c9a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java#L198
hi minghong, in JoinEstimation, outputRowCount of joinNode is calculated as 1, this will cause the result of Join to be considered as only one row of data in the plan, and be distributed using broadcast https://github.com/apache/doris/blob/f53cf052d84335c09b2680259b1366343fc90c9a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java#L144
PR approved by at least one committer and no changes requested.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
TPC-H: Total hot run time: 40090 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit ab8040853070bd0068fd314d75df8fe23e58b292, data reload: false
------ Round 1 ----------------------------------
q1 17620 4319 4260 4260
q2 2027 196 196 196
q3 10501 1165 1141 1141
q4 10626 858 786 786
q5 7534 2689 2771 2689
q6 220 145 138 138
q7 960 613 596 596
q8 9407 2080 2104 2080
q9 9246 6614 6597 6597
q10 9329 3772 3722 3722
q11 455 246 233 233
q12 450 227 229 227
q13 17828 2959 3007 2959
q14 253 215 233 215
q15 508 473 468 468
q16 535 382 377 377
q17 965 684 635 635
q18 7979 7357 7452 7357
q19 4592 1530 1551 1530
q20 646 308 305 305
q21 4912 3320 3248 3248
q22 378 331 332 331
Total cold run time: 116971 ms
Total hot run time: 40090 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4285 4207 4230 4207
q2 369 269 262 262
q3 2975 2734 2769 2734
q4 1844 1565 1616 1565
q5 5227 5260 5250 5250
q6 210 123 125 123
q7 2131 1741 1730 1730
q8 3149 3308 3298 3298
q9 8342 8292 8301 8292
q10 3827 3660 3642 3642
q11 583 506 468 468
q12 771 610 598 598
q13 17236 2974 2961 2961
q14 287 270 256 256
q15 519 472 480 472
q16 470 406 428 406
q17 1764 1488 1443 1443
q18 7703 7566 7400 7400
q19 1632 1565 1627 1565
q20 1954 1791 1785 1785
q21 4795 4668 4750 4668
q22 608 552 539 539
Total cold run time: 70681 ms
Total hot run time: 53664 ms
TPC-DS: Total hot run time: 169083 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit ab8040853070bd0068fd314d75df8fe23e58b292, data reload: false
query1 938 375 376 375
query2 6452 2353 2563 2353
query3 6652 205 212 205
query4 21112 17373 17372 17372
query5 4127 439 440 439
query6 249 163 185 163
query7 4587 302 292 292
query8 329 297 300 297
query9 8670 2421 2404 2404
query10 438 271 262 262
query11 10580 9997 10031 9997
query12 135 86 83 83
query13 1640 374 369 369
query14 8474 6911 6932 6911
query15 228 188 179 179
query16 7803 258 256 256
query17 1742 527 517 517
query18 1954 286 263 263
query19 196 156 159 156
query20 95 88 87 87
query21 192 130 126 126
query22 4107 4033 3831 3831
query23 33650 32974 32983 32974
query24 12141 2865 2779 2779
query25 659 355 363 355
query26 1800 154 157 154
query27 3012 319 331 319
query28 7688 2099 2086 2086
query29 1115 617 601 601
query30 278 146 159 146
query31 952 748 746 746
query32 89 54 59 54
query33 766 273 269 269
query34 1010 488 474 474
query35 746 604 606 604
query36 1063 915 909 909
query37 236 64 68 64
query38 2900 2782 2767 2767
query39 849 797 790 790
query40 276 125 124 124
query41 55 50 53 50
query42 102 99 100 99
query43 614 570 547 547
query44 1208 747 748 747
query45 187 170 165 165
query46 1064 715 710 710
query47 1815 1775 1774 1774
query48 360 302 298 298
query49 1221 416 398 398
query50 770 396 391 391
query51 6721 6619 6614 6614
query52 114 91 102 91
query53 358 295 288 288
query54 992 457 433 433
query55 76 73 74 73
query56 304 258 261 258
query57 1146 1042 1005 1005
query58 287 221 216 216
query59 3543 3081 3053 3053
query60 271 265 259 259
query61 89 95 91 91
query62 647 438 455 438
query63 318 296 293 293
query64 9826 2239 1701 1701
query65 3272 3107 3126 3107
query66 1375 317 328 317
query67 15089 14946 14713 14713
query68 4615 537 528 528
query69 445 281 280 280
query70 1127 1092 1159 1092
query71 408 281 280 280
query72 7912 2712 2560 2560
query73 711 318 320 318
query74 6036 5536 5573 5536
query75 3434 2614 2615 2614
query76 2800 915 978 915
query77 443 268 272 268
query78 10276 9806 9852 9806
query79 1034 538 526 526
query80 794 445 451 445
query81 502 223 218 218
query82 376 92 93 92
query83 270 171 168 168
query84 232 85 86 85
query85 1231 289 268 268
query86 377 288 299 288
query87 3333 3126 3120 3120
query88 3044 2381 2358 2358
query89 468 403 392 392
query90 1947 195 191 191
query91 128 99 99 99
query92 60 51 52 51
query93 1022 511 496 496
query94 1224 189 186 186
query95 415 308 307 307
query96 571 266 271 266
query97 3175 3027 2987 2987
query98 248 223 214 214
query99 1100 848 857 848
Total cold run time: 271512 ms
Total hot run time: 169083 ms
ClickBench: Total hot run time: 30.92 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit ab8040853070bd0068fd314d75df8fe23e58b292, data reload: false
query1 0.04 0.03 0.03
query2 0.08 0.04 0.04
query3 0.24 0.05 0.04
query4 1.67 0.07 0.07
query5 0.49 0.50 0.49
query6 1.12 0.72 0.73
query7 0.02 0.01 0.02
query8 0.05 0.04 0.04
query9 0.52 0.49 0.50
query10 0.54 0.55 0.53
query11 0.16 0.11 0.11
query12 0.14 0.11 0.11
query13 0.59 0.59 0.58
query14 0.79 0.79 0.78
query15 0.83 0.81 0.80
query16 0.36 0.35 0.34
query17 0.93 1.01 1.02
query18 0.22 0.24 0.22
query19 1.73 1.67 1.65
query20 0.01 0.01 0.01
query21 15.51 0.68 0.66
query22 4.34 6.39 2.48
query23 18.29 1.44 1.19
query24 1.70 0.30 0.21
query25 0.15 0.09 0.07
query26 0.27 0.17 0.17
query27 0.08 0.08 0.07
query28 13.37 1.00 0.99
query29 12.72 3.30 3.31
query30 0.24 0.08 0.05
query31 2.85 0.38 0.36
query32 3.34 0.47 0.47
query33 2.89 2.89 2.89
query34 17.30 4.42 4.44
query35 4.57 4.48 4.55
query36 0.65 0.49 0.46
query37 0.17 0.15 0.15
query38 0.15 0.14 0.14
query39 0.04 0.03 0.04
query40 0.16 0.14 0.15
query41 0.09 0.05 0.05
query42 0.05 0.05 0.04
query43 0.04 0.03 0.04
Total cold run time: 109.5 s
Total hot run time: 30.92 s
TPC-H: Total hot run time: 40843 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 09574d52f7a4208bcc794e87f379992e5e87910e, data reload: false
------ Round 1 ----------------------------------
q1 17620 4550 4302 4302
q2 2028 197 189 189
q3 10451 1114 1116 1114
q4 10158 747 772 747
q5 7604 2739 2690 2690
q6 222 135 137 135
q7 969 621 612 612
q8 9567 2118 2118 2118
q9 8943 6542 6496 6496
q10 8929 3768 3690 3690
q11 455 255 250 250
q12 423 243 229 229
q13 18030 2984 2996 2984
q14 260 220 231 220
q15 527 466 478 466
q16 501 385 399 385
q17 969 669 747 669
q18 8154 7531 7475 7475
q19 7758 1499 1498 1498
q20 719 331 318 318
q21 5002 3920 3915 3915
q22 388 364 341 341
Total cold run time: 119677 ms
Total hot run time: 40843 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4411 4237 4222 4222
q2 375 260 267 260
q3 3008 2746 2728 2728
q4 1953 1624 1610 1610
q5 5253 5272 5482 5272
q6 220 128 127 127
q7 2109 1760 1694 1694
q8 3227 3350 3334 3334
q9 8381 8394 8363 8363
q10 3872 3573 3655 3573
q11 588 486 486 486
q12 774 630 590 590
q13 16288 2983 3000 2983
q14 319 267 261 261
q15 505 470 474 470
q16 473 408 405 405
q17 1781 1504 1491 1491
q18 7593 7419 7324 7324
q19 1844 1483 1638 1483
q20 1959 1780 1791 1780
q21 4780 4826 4738 4738
q22 621 526 542 526
Total cold run time: 70334 ms
Total hot run time: 53720 ms
TPC-DS: Total hot run time: 174481 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 09574d52f7a4208bcc794e87f379992e5e87910e, data reload: false
query1 938 378 375 375
query2 6459 2738 2496 2496
query3 6652 211 216 211
query4 19936 17405 17311 17311
query5 4166 478 486 478
query6 251 163 158 158
query7 4586 305 298 298
query8 330 302 285 285
query9 8762 2452 2420 2420
query10 448 309 277 277
query11 10418 10001 10065 10001
query12 138 89 94 89
query13 1629 361 366 361
query14 9491 8035 8002 8002
query15 237 197 188 188
query16 7708 282 295 282
query17 1806 554 553 553
query18 1958 308 276 276
query19 194 155 167 155
query20 100 89 88 88
query21 215 135 138 135
query22 4281 4071 3981 3981
query23 33701 32989 33162 32989
query24 12122 2824 2798 2798
query25 660 390 368 368
query26 1782 159 166 159
query27 3022 331 327 327
query28 7765 2077 2072 2072
query29 1077 616 613 613
query30 279 154 154 154
query31 976 757 736 736
query32 90 54 53 53
query33 768 297 292 292
query34 987 497 484 484
query35 773 624 645 624
query36 1143 931 918 918
query37 287 69 75 69
query38 2894 2772 2749 2749
query39 902 810 805 805
query40 286 128 132 128
query41 55 52 58 52
query42 128 100 99 99
query43 603 567 577 567
query44 1171 729 744 729
query45 202 174 163 163
query46 1086 737 743 737
query47 1827 1775 1756 1756
query48 378 301 312 301
query49 1203 424 414 414
query50 793 394 404 394
query51 6916 6776 6727 6727
query52 101 105 92 92
query53 379 299 290 290
query54 1039 453 449 449
query55 82 72 72 72
query56 303 272 268 268
query57 1170 1044 1045 1044
query58 269 269 247 247
query59 3548 3461 3559 3461
query60 303 271 279 271
query61 92 93 93 93
query62 652 463 430 430
query63 335 303 295 295
query64 9924 2203 1734 1734
query65 3229 3141 3114 3114
query66 1384 337 326 326
query67 15298 14950 14679 14679
query68 4570 544 545 544
query69 446 317 324 317
query70 1193 1128 1129 1128
query71 404 294 286 286
query72 7327 5712 5371 5371
query73 765 332 333 332
query74 5913 5500 5560 5500
query75 3463 2681 2703 2681
query76 2667 918 909 909
query77 462 313 306 306
query78 10200 9815 9822 9815
query79 2330 522 551 522
query80 1080 477 472 472
query81 585 230 222 222
query82 720 105 105 105
query83 245 179 191 179
query84 242 87 89 87
query85 1965 267 275 267
query86 507 311 299 299
query87 3245 3130 3044 3044
query88 4233 2455 2447 2447
query89 500 410 403 403
query90 1814 191 194 191
query91 141 113 108 108
query92 68 52 54 52
query93 2163 525 507 507
query94 1231 205 199 199
query95 421 324 407 324
query96 610 291 272 272
query97 3193 2995 2992 2992
query98 246 227 214 214
query99 1198 824 833 824
Total cold run time: 276834 ms
Total hot run time: 174481 ms
ClickBench: Total hot run time: 30.83 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 09574d52f7a4208bcc794e87f379992e5e87910e, data reload: false
query1 0.03 0.03 0.03
query2 0.08 0.04 0.04
query3 0.22 0.05 0.05
query4 1.67 0.08 0.07
query5 0.50 0.47 0.47
query6 1.13 0.72 0.72
query7 0.02 0.01 0.02
query8 0.05 0.04 0.05
query9 0.54 0.49 0.48
query10 0.54 0.55 0.54
query11 0.16 0.11 0.11
query12 0.15 0.11 0.12
query13 0.59 0.58 0.60
query14 0.77 0.77 0.79
query15 0.84 0.82 0.82
query16 0.37 0.36 0.36
query17 1.04 0.96 1.00
query18 0.20 0.26 0.23
query19 1.87 1.76 1.66
query20 0.02 0.01 0.01
query21 15.49 0.70 0.66
query22 4.29 6.88 2.29
query23 18.25 1.36 1.23
query24 2.06 0.22 0.21
query25 0.15 0.08 0.08
query26 0.27 0.18 0.17
query27 0.08 0.08 0.08
query28 13.29 1.04 0.99
query29 13.22 3.32 3.30
query30 0.25 0.06 0.08
query31 2.84 0.39 0.39
query32 3.28 0.48 0.47
query33 2.85 2.93 2.87
query34 17.18 4.44 4.44
query35 4.52 4.59 4.48
query36 0.66 0.47 0.46
query37 0.18 0.16 0.15
query38 0.15 0.14 0.15
query39 0.04 0.04 0.04
query40 0.16 0.13 0.14
query41 0.09 0.05 0.05
query42 0.06 0.04 0.04
query43 0.04 0.04 0.04
Total cold run time: 110.19 s
Total hot run time: 30.83 s
TPC-H: Total hot run time: 39833 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 908b64b5e3498867cae7a7c6c463d456ab4414ef, data reload: false
------ Round 1 ----------------------------------
q1 17621 4280 4307 4280
q2 2026 191 205 191
q3 10420 1164 1120 1120
q4 10196 731 755 731
q5 7484 2681 2607 2607
q6 222 135 133 133
q7 953 591 596 591
q8 9232 2072 2048 2048
q9 8794 6512 6464 6464
q10 8882 3727 3718 3718
q11 449 234 231 231
q12 511 233 237 233
q13 17758 2950 2955 2950
q14 273 217 224 217
q15 507 464 473 464
q16 508 375 374 374
q17 964 747 678 678
q18 7957 7455 7386 7386
q19 5143 1462 1538 1462
q20 656 318 315 315
q21 5116 3356 3311 3311
q22 391 329 330 329
Total cold run time: 116063 ms
Total hot run time: 39833 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4379 4278 4283 4278
q2 371 275 278 275
q3 2926 2735 2972 2735
q4 1974 1705 1628 1628
q5 5577 5552 5537 5537
q6 239 130 131 130
q7 2197 1839 1910 1839
q8 3303 3403 3394 3394
q9 8703 8712 8834 8712
q10 4141 3879 3828 3828
q11 587 462 474 462
q12 775 623 640 623
q13 17027 3175 3138 3138
q14 308 263 274 263
q15 533 473 487 473
q16 496 435 433 433
q17 1782 1496 1498 1496
q18 8093 7864 7798 7798
q19 1829 1666 1495 1495
q20 2110 1882 1897 1882
q21 10306 4875 4929 4875
q22 620 532 527 527
Total cold run time: 78276 ms
Total hot run time: 55821 ms
TPC-DS: Total hot run time: 172027 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 908b64b5e3498867cae7a7c6c463d456ab4414ef, data reload: false
query1 924 378 376 376
query2 6502 2453 2202 2202
query3 6626 203 207 203
query4 19440 17227 17412 17227
query5 3597 463 448 448
query6 235 161 159 159
query7 4590 299 288 288
query8 304 293 283 283
query9 8442 2391 2347 2347
query10 554 300 276 276
query11 10455 10060 9915 9915
query12 117 82 84 82
query13 1640 355 363 355
query14 8728 7744 6809 6809
query15 265 192 191 191
query16 7648 275 266 266
query17 1396 548 528 528
query18 1873 282 307 282
query19 203 165 176 165
query20 94 88 86 86
query21 221 139 125 125
query22 4617 4279 4441 4279
query23 33913 33545 33484 33484
query24 10741 2801 2745 2745
query25 584 363 372 363
query26 706 153 151 151
query27 2239 322 314 314
query28 5898 2051 2090 2051
query29 888 592 594 592
query30 232 152 152 152
query31 911 740 745 740
query32 93 50 51 50
query33 637 270 269 269
query34 852 467 465 465
query35 718 616 613 613
query36 1104 914 917 914
query37 128 66 67 66
query38 2851 2724 2683 2683
query39 834 804 807 804
query40 205 120 120 120
query41 56 49 54 49
query42 115 100 93 93
query43 569 524 540 524
query44 1068 712 711 711
query45 196 164 163 163
query46 1067 697 734 697
query47 1881 1767 1802 1767
query48 376 303 300 300
query49 862 415 411 411
query50 777 387 391 387
query51 6793 6644 6615 6615
query52 101 92 88 88
query53 349 291 286 286
query54 848 447 442 442
query55 75 69 74 69
query56 269 246 265 246
query57 1114 1045 1028 1028
query58 246 236 244 236
query59 3293 3035 3161 3035
query60 283 260 259 259
query61 89 91 88 88
query62 583 446 435 435
query63 325 286 279 279
query64 8498 2291 1739 1739
query65 3170 3103 3079 3079
query66 736 315 321 315
query67 15519 15085 14848 14848
query68 4500 531 534 531
query69 485 399 318 318
query70 1168 1130 1070 1070
query71 353 263 269 263
query72 7163 5201 5620 5201
query73 742 322 320 320
query74 5980 5500 5571 5500
query75 3337 2619 2631 2619
query76 2185 986 851 851
query77 433 296 302 296
query78 10327 9850 9816 9816
query79 2805 519 513 513
query80 2325 454 441 441
query81 572 214 223 214
query82 919 105 100 100
query83 300 165 165 165
query84 263 91 82 82
query85 1281 279 268 268
query86 456 310 333 310
query87 3254 3080 3078 3078
query88 3877 2332 2339 2332
query89 478 383 382 382
query90 1652 196 190 190
query91 137 107 109 107
query92 58 50 50 50
query93 1844 491 491 491
query94 1147 191 192 191
query95 415 314 319 314
query96 593 267 264 264
query97 3198 3051 3070 3051
query98 222 201 196 196
query99 1245 846 856 846
Total cold run time: 264977 ms
Total hot run time: 172027 ms
ClickBench: Total hot run time: 30.21 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 908b64b5e3498867cae7a7c6c463d456ab4414ef, data reload: false
query1 0.04 0.03 0.04
query2 0.07 0.04 0.04
query3 0.23 0.05 0.04
query4 1.68 0.07 0.07
query5 0.53 0.48 0.47
query6 1.13 0.72 0.72
query7 0.02 0.01 0.01
query8 0.05 0.05 0.04
query9 0.54 0.49 0.51
query10 0.54 0.58 0.55
query11 0.15 0.11 0.11
query12 0.15 0.12 0.12
query13 0.60 0.59 0.60
query14 0.78 0.79 0.78
query15 0.84 0.81 0.81
query16 0.35 0.36 0.37
query17 0.98 0.99 0.97
query18 0.21 0.27 0.24
query19 1.76 1.73 1.72
query20 0.01 0.01 0.01
query21 15.43 0.67 0.66
query22 4.40 7.62 1.45
query23 18.27 1.51 1.34
query24 2.15 0.22 0.22
query25 0.15 0.08 0.08
query26 0.27 0.17 0.17
query27 0.09 0.08 0.09
query28 13.21 1.03 1.00
query29 12.65 3.30 3.28
query30 0.26 0.06 0.06
query31 2.86 0.38 0.38
query32 3.28 0.48 0.46
query33 2.85 2.95 3.00
query34 17.08 4.41 4.42
query35 4.52 4.46 4.54
query36 0.67 0.46 0.46
query37 0.17 0.15 0.15
query38 0.14 0.14 0.14
query39 0.04 0.03 0.04
query40 0.18 0.14 0.14
query41 0.09 0.05 0.04
query42 0.05 0.05 0.05
query43 0.04 0.03 0.04
Total cold run time: 109.51 s
Total hot run time: 30.21 s
We're closing this PR because it hasn't been updated in a while. This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable. If you'd like to revive this PR, please reopen it and feel free a maintainer to remove the Stale tag!