You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "DarvenDuan (via GitHub)" <gi...@apache.org> on 2023/12/22 10:10:21 UTC
[PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
DarvenDuan opened a new pull request, #28886:
URL: https://github.com/apache/doris/pull/28886
If the `case when` contains a constant zero/null in SUM agg function, we can use preaggregation to speed up queries and it will not effect the correctness of result.
Issue Number: close #xxx
If we use `case when` or `if` in `SUM`, Planner will get the follows steps:
1、rewrite expression `if` to `case when`.
2、check the Index contains all key columns in `when` expr.
3、check the expr in `then` is constant zero/null.
4、check the expr in `else` is constant zero/null.
If conditions 2, 3, and 3 are all met, we open preagg.
## Further comments
If this is a relatively large or complex change, kick off the discussion at [dev@doris.apache.org](mailto:dev@doris.apache.org) by explaining why you chose the solution you did and what alternatives you considered, etc...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904261865
<details>
<summary>TPC-DS: <b>Total hot run time: 177347 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit ec8ac677e721785d374690ee9a9a87b47b44cc0d, data reload: false
query1 926 335 333 333
query2 6550 1791 2087 1791
query3 6696 197 198 197
query4 30438 22232 22352 22232
query5 4446 371 362 362
query6 237 154 152 152
query7 4608 265 267 265
query8 218 190 194 190
query9 8372 2551 2560 2551
query10 424 245 246 245
query11 16757 15605 15655 15605
query12 126 67 67 67
query13 1668 373 377 373
query14 10642 7030 6936 6936
query15 212 181 186 181
query16 5789 251 254 251
query17 945 487 478 478
query18 1799 255 251 251
query19 183 132 143 132
query20 67 66 64 64
query21 202 123 120 120
query22 5158 5013 5083 5013
query23 31751 31158 30796 30796
query24 11624 2827 2834 2827
query25 569 314 312 312
query26 1645 144 143 143
query27 3220 289 290 289
query28 7298 1846 1828 1828
query29 1687 616 623 616
query30 293 138 138 138
query31 954 748 746 746
query32 89 52 52 52
query33 709 210 214 210
query34 1120 464 459 459
query35 864 722 761 722
query36 1379 1264 1240 1240
query37 93 61 59 59
query38 3352 3227 3202 3202
query39 1324 1290 1265 1265
query40 344 88 83 83
query41 38 35 34 34
query42 94 82 83 82
query43 509 488 478 478
query44 1111 692 698 692
query45 194 180 174 174
query46 1072 658 661 658
query47 1724 1546 1584 1546
query48 407 322 308 308
query49 1229 282 292 282
query50 682 316 317 316
query51 5302 5281 5190 5190
query52 79 83 76 76
query53 325 260 250 250
query54 239 184 188 184
query55 79 71 78 71
query56 182 169 165 165
query57 1028 952 942 942
query58 194 157 160 157
query59 2919 2809 2757 2757
query60 212 189 193 189
query61 80 82 80 80
query62 642 379 354 354
query63 287 259 257 257
query64 6004 1748 1749 1748
query65 3358 3259 3249 3249
query66 1269 309 311 309
query67 15407 15751 15179 15179
query68 12001 529 508 508
query69 599 305 299 299
query70 1718 1531 1486 1486
query71 527 221 219 219
query72 4852 2820 2821 2820
query73 2423 314 317 314
query74 7419 6377 6416 6377
query75 4919 2292 2325 2292
query76 6717 1013 1055 1013
query77 661 229 239 229
query78 9118 9045 8693 8693
query79 1017 507 492 492
query80 559 316 318 316
query81 449 217 199 199
query82 201 79 78 78
query83 139 123 120 120
query84 278 71 69 69
query85 1054 330 343 330
query86 408 427 387 387
query87 3501 3300 3336 3300
query88 2698 2195 2197 2195
query89 432 361 348 348
query90 2047 189 190 189
query91 156 121 121 121
query92 57 45 44 44
query93 1362 456 450 450
query94 1178 170 159 159
query95 521 464 459 459
query96 617 329 315 315
query97 4268 4195 4165 4165
query98 208 198 188 188
query99 1039 670 715 670
Total cold run time: 292587 ms
Total hot run time: 177347 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904104162
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Load test result on commit dc843dfac5d3453a191062c29063f08093e542d1 with default session variables
Stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
Stream load orc: 59 seconds loaded 1101869774 Bytes, about 17 MB/s
Stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
Insert into select: 14.8 seconds inserted 10000000 Rows, about 675K ops/s
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1884329230
> > > I don't understand why we need to support this case, it seems meaningless?
> >
> >
> > If we create a rollup or a materialized view for data preaggregation, and then use `SUM(case ... when ... then)` or `SUM(if(... , ... , ...))`, Doris can't hit rollup or materialized view we created before.
> > simple case:
> > create table: CREATE TABLE test_tbl ( key1 INT NOT NULL, key2 INT NULL, key3 INT NULL, value BIGINT sum NULL ) ENGINE=OLAP AGGREGATE KEY(key1, key2, key3) COMMENT 'OLAP' DISTRIBUTED BY HASH(key1) BUCKETS 10 PROPERTIES ( "replication_num" = "1" );
> > create rollup: `alter table test_tbl add rollup test_rollup(key1,key2,value);`
> > I hope this query can hit rollup **test_rollup** `select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl group by key1;`
>
> Thanks for your effort. But this pr is not correct, because the sql above SHOULD NOT hit mv. The sql bellow is correct one which need to hit mv(but not supported now). `select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl where key2 > 0 group by key1;` This sql is equivalent to `select key1,sum(value) as value from test_tbl where key2 > 0 group by key1;`
>
> As the mv selection logical is being refactoring in nereids. We can considering such optimization after the refactoring work done. ( This refactoring is scheduled, but not in near future)
Thanks for your replay, I have a few questions I'd like to ask you, can I add your WeChat? My WeChat: DUAN1998525
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902083799
<details>
<summary>TPC-DS: <b>Total hot run time: 176502 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 7c2e3100ebb87c0c93f0dd3136772e9cc99e9d4e, data reload: false
query1 938 338 328 328
query2 6567 1940 1874 1874
query3 6692 207 197 197
query4 33871 22250 22162 22162
query5 6880 602 529 529
query6 248 154 154 154
query7 4607 264 262 262
query8 221 176 183 176
query9 8397 2527 2518 2518
query10 430 243 242 242
query11 17114 15598 15359 15359
query12 124 72 70 70
query13 1689 393 387 387
query14 10403 7072 6974 6974
query15 220 178 180 178
query16 4611 234 233 233
query17 993 486 473 473
query18 1787 265 270 265
query19 174 138 134 134
query20 81 78 70 70
query21 187 137 133 133
query22 4871 4712 4703 4703
query23 31539 30893 30634 30634
query24 11847 2846 2823 2823
query25 575 317 307 307
query26 1421 142 142 142
query27 3236 267 276 267
query28 7115 1821 1820 1820
query29 1218 633 635 633
query30 276 142 137 137
query31 955 734 750 734
query32 76 52 50 50
query33 701 212 206 206
query34 1143 482 469 469
query35 855 782 764 764
query36 1339 1239 1235 1235
query37 93 58 58 58
query38 3370 3236 3175 3175
query39 1317 1280 1287 1280
query40 208 87 88 87
query41 37 35 35 35
query42 90 80 87 80
query43 516 481 469 469
query44 1119 680 685 680
query45 201 181 214 181
query46 1062 656 642 642
query47 1577 1538 1592 1538
query48 393 314 327 314
query49 1134 284 284 284
query50 686 316 309 309
query51 5282 5229 5212 5212
query52 92 76 75 75
query53 316 261 257 257
query54 450 293 287 287
query55 88 72 74 72
query56 190 178 176 176
query57 971 912 906 906
query58 179 174 171 171
query59 2810 2723 2700 2700
query60 207 184 192 184
query61 81 82 88 82
query62 641 363 383 363
query63 275 263 262 262
query64 4995 1749 1743 1743
query65 3326 3265 3246 3246
query66 1223 312 312 312
query67 15555 14950 15259 14950
query68 12631 538 505 505
query69 611 305 301 301
query70 1719 1503 1508 1503
query71 555 214 207 207
query72 5172 2828 2856 2828
query73 2724 317 313 313
query74 6929 6386 6333 6333
query75 5165 2349 2354 2349
query76 6662 999 979 979
query77 655 230 232 230
query78 9291 9045 8574 8574
query79 989 495 521 495
query80 573 338 325 325
query81 472 202 199 199
query82 204 77 82 77
query83 148 115 126 115
query84 275 69 70 69
query85 1103 335 337 335
query86 395 411 422 411
query87 3484 3364 3336 3336
query88 2921 2213 2201 2201
query89 432 357 332 332
query90 2004 189 184 184
query91 155 130 122 122
query92 53 46 45 45
query93 1430 454 430 430
query94 1209 165 158 158
query95 501 458 459 458
query96 610 323 317 317
query97 4300 4121 4135 4121
query98 202 205 188 188
query99 1086 712 690 690
Total cold run time: 296545 ms
Total hot run time: 176502 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902526642
<details>
<summary>TPC-DS: <b>Total hot run time: 177866 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 50919cacf7645e286623238fa5acb691dde7ae56, data reload: false
query1 934 327 326 326
query2 6572 1902 1843 1843
query3 6702 200 198 198
query4 34316 22143 22248 22143
query5 6895 591 517 517
query6 248 169 152 152
query7 4601 258 258 258
query8 224 171 178 171
query9 8416 2521 2522 2521
query10 408 215 237 215
query11 16472 15553 15742 15553
query12 119 68 66 66
query13 1691 384 379 379
query14 10510 7093 7076 7076
query15 224 183 185 183
query16 4793 237 237 237
query17 982 482 466 466
query18 1783 272 255 255
query19 182 137 133 133
query20 71 70 68 68
query21 192 123 122 122
query22 5435 5373 5374 5373
query23 32612 30998 30891 30891
query24 12797 2806 2825 2806
query25 560 314 309 309
query26 1805 140 140 140
query27 3274 267 264 264
query28 7230 1818 1807 1807
query29 2130 622 625 622
query30 287 137 141 137
query31 936 736 736 736
query32 78 52 48 48
query33 703 215 208 208
query34 1290 453 471 453
query35 917 782 771 771
query36 1371 1255 1258 1255
query37 169 60 61 60
query38 3312 3267 3267 3267
query39 1318 1278 1275 1275
query40 337 84 82 82
query41 38 37 35 35
query42 85 82 87 82
query43 508 469 473 469
query44 1070 680 679 679
query45 195 176 172 172
query46 1065 648 660 648
query47 1609 1542 1549 1542
query48 390 307 307 307
query49 1218 302 279 279
query50 670 306 313 306
query51 5363 5304 5185 5185
query52 87 74 73 73
query53 317 253 262 253
query54 458 282 297 282
query55 81 77 79 77
query56 178 165 163 163
query57 974 921 911 911
query58 189 161 161 161
query59 2798 2672 2665 2665
query60 206 195 179 179
query61 83 83 82 82
query62 614 365 359 359
query63 283 249 260 249
query64 6129 1759 1759 1759
query65 3337 3266 3258 3258
query66 1432 316 308 308
query67 15831 15434 15463 15434
query68 11241 537 503 503
query69 615 315 296 296
query70 1691 1533 1541 1533
query71 566 217 203 203
query72 4962 2861 2873 2861
query73 2121 315 317 315
query74 7107 6376 6302 6302
query75 5122 2342 2339 2339
query76 6816 973 1002 973
query77 892 237 227 227
query78 9382 8761 8579 8579
query79 990 514 488 488
query80 637 319 307 307
query81 443 211 206 206
query82 198 77 75 75
query83 141 122 115 115
query84 275 70 73 70
query85 1074 353 318 318
query86 408 393 379 379
query87 3476 3314 3295 3295
query88 3277 2210 2207 2207
query89 424 366 362 362
query90 2145 183 184 183
query91 166 130 127 127
query92 51 43 41 41
query93 1762 445 414 414
query94 1278 163 158 158
query95 501 456 434 434
query96 631 314 316 314
query97 4292 4087 4166 4087
query98 212 196 181 181
query99 1035 671 705 671
Total cold run time: 302035 ms
Total hot run time: 177866 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904195791
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904087213
<details>
<summary>TPC-DS: <b>Total hot run time: 176612 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit dc843dfac5d3453a191062c29063f08093e542d1, data reload: false
query1 927 344 334 334
query2 6568 2001 1951 1951
query3 6696 208 204 204
query4 32322 22259 22246 22246
query5 4474 449 434 434
query6 244 154 154 154
query7 4602 266 259 259
query8 221 174 176 174
query9 8451 2543 2523 2523
query10 431 240 220 220
query11 16618 15666 15580 15580
query12 120 69 64 64
query13 1668 378 375 375
query14 10581 6922 6821 6821
query15 223 190 189 189
query16 5787 257 248 248
query17 950 455 465 455
query18 1795 257 244 244
query19 191 130 136 130
query20 69 73 72 72
query21 185 129 126 126
query22 5146 4858 4949 4858
query23 31514 30698 30976 30698
query24 11183 2782 2804 2782
query25 543 311 293 293
query26 882 140 142 140
query27 2889 285 283 283
query28 6079 1878 1855 1855
query29 943 620 607 607
query30 278 137 133 133
query31 945 755 745 745
query32 70 49 46 46
query33 595 209 205 205
query34 862 456 459 456
query35 885 772 753 753
query36 1370 1230 1206 1206
query37 94 60 58 58
query38 3346 3191 3298 3191
query39 1334 1290 1266 1266
query40 196 90 84 84
query41 37 35 33 33
query42 89 77 83 77
query43 524 471 476 471
query44 1052 692 683 683
query45 195 175 175 175
query46 1051 642 643 642
query47 1677 1605 1583 1583
query48 399 306 311 306
query49 1088 284 286 284
query50 691 316 304 304
query51 5288 5240 5223 5223
query52 93 75 71 71
query53 320 268 260 260
query54 246 189 185 185
query55 79 77 73 73
query56 178 158 163 158
query57 1005 932 948 932
query58 184 153 153 153
query59 2889 2725 2747 2725
query60 213 182 182 182
query61 80 79 83 79
query62 605 381 371 371
query63 270 253 253 253
query64 4218 1771 1746 1746
query65 3372 3271 3250 3250
query66 1216 318 309 309
query67 16105 15044 15138 15044
query68 12215 541 501 501
query69 600 302 292 292
query70 1602 1517 1509 1509
query71 508 218 210 210
query72 5068 2807 2822 2807
query73 2806 315 318 315
query74 7043 6309 6379 6309
query75 5013 2404 2306 2306
query76 6486 1040 1024 1024
query77 653 232 233 232
query78 9523 8849 8633 8633
query79 1017 502 490 490
query80 602 310 310 310
query81 467 198 205 198
query82 200 84 75 75
query83 136 118 117 117
query84 274 68 66 66
query85 1090 327 333 327
query86 386 390 383 383
query87 3568 3368 3262 3262
query88 2841 2184 2172 2172
query89 453 364 341 341
query90 1983 184 183 183
query91 153 120 132 120
query92 52 46 42 42
query93 1742 433 420 420
query94 1341 155 156 155
query95 500 470 445 445
query96 618 313 319 313
query97 4233 4143 4180 4143
query98 204 194 193 193
query99 1012 702 685 685
Total cold run time: 289830 ms
Total hot run time: 176612 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868223653
run fe_ut
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868843677
run pipeline_x
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868915628
run pipelinex_p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902084805
<details>
<summary>ClickBench: <b>Total hot run time: 30.28 s</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 7c2e3100ebb87c0c93f0dd3136772e9cc99e9d4e, data reload: false
query1 0.04 0.03 0.03
query2 0.05 0.02 0.02
query3 0.23 0.04 0.05
query4 1.70 0.06 0.06
query5 0.54 0.52 0.52
query6 1.29 0.64 0.62
query7 0.02 0.01 0.02
query8 0.04 0.02 0.02
query9 0.55 0.52 0.51
query10 0.58 0.56 0.56
query11 0.11 0.09 0.10
query12 0.11 0.09 0.09
query13 0.60 0.60 0.61
query14 0.79 0.81 0.80
query15 0.80 0.77 0.77
query16 0.37 0.37 0.40
query17 1.03 1.03 1.03
query18 0.22 0.26 0.26
query19 1.90 1.79 1.81
query20 0.02 0.01 0.01
query21 15.38 0.57 0.57
query22 2.84 2.27 1.55
query23 17.29 0.77 0.70
query24 2.34 0.81 1.36
query25 0.47 0.15 0.08
query26 0.60 0.14 0.14
query27 0.05 0.05 0.05
query28 11.71 0.77 0.75
query29 12.55 3.09 3.28
query30 0.50 0.47 0.49
query31 2.79 0.35 0.33
query32 3.38 0.48 0.49
query33 3.20 3.24 3.22
query34 15.94 4.27 4.24
query35 4.38 4.27 4.24
query36 1.10 1.08 1.09
query37 0.07 0.05 0.05
query38 0.04 0.03 0.03
query39 0.02 0.02 0.02
query40 0.17 0.13 0.14
query41 0.07 0.01 0.02
query42 0.02 0.02 0.02
query43 0.02 0.02 0.02
Total cold run time: 105.92 s
Total hot run time: 30.28 s
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902524855
<details>
<summary>TPC-H: <b>Total hot run time: 38757 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 50919cacf7645e286623238fa5acb691dde7ae56, data reload: false
------ Round 1 ----------------------------------
q1 17644 5519 5271 5271
q2 2046 147 132 132
q3 10768 1157 1144 1144
q4 10353 773 817 773
q5 7761 3149 3150 3149
q6 200 119 122 119
q7 852 488 486 486
q8 9237 1949 1951 1949
q9 7230 6325 6295 6295
q10 8218 3094 3081 3081
q11 427 225 224 224
q12 350 190 191 190
q13 17991 3365 3345 3345
q14 248 216 211 211
q15 542 509 496 496
q16 420 368 382 368
q17 935 506 531 506
q18 7516 6919 6686 6686
q19 1542 1343 1419 1343
q20 566 290 283 283
q21 2707 2427 2397 2397
q22 377 309 311 309
Total cold run time: 107930 ms
Total hot run time: 38757 ms
----- Round 2, with runtime_filter_mode=off -----
q1 5627 5182 5047 5047
q2 341 219 214 214
q3 3321 3188 3282 3188
q4 2066 2056 2066 2056
q5 5842 5929 5972 5929
q6 195 114 116 114
q7 2336 1941 1890 1890
q8 3216 3382 3417 3382
q9 8971 8863 8816 8816
q10 3927 3751 3755 3751
q11 564 456 460 456
q12 821 612 602 602
q13 16931 3145 3139 3139
q14 300 251 264 251
q15 551 504 503 503
q16 510 471 483 471
q17 1884 1846 1859 1846
q18 9625 14569 9767 9767
q19 20007 1509 1523 1509
q20 4560 1944 1923 1923
q21 14808 5494 5372 5372
q22 1094 549 562 549
Total cold run time: 107497 ms
Total hot run time: 60775 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868189261
run p1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "BiteTheDDDDt (via GitHub)" <gi...@apache.org>.
BiteTheDDDDt commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868982009
I don't understand why we need to support this case, it seems meaningless?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "BiteTheDDDDt (via GitHub)" <gi...@apache.org>.
BiteTheDDDDt commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1869235518
> > I don't understand why we need to support this case, it seems meaningless?
>
> If we create a rollup or a materialized view for data preaggregation, and then use `SUM(case ... when ... then)` or `SUM(if(... , ... , ...))`, Doris can't hit rollup or materialized view we created before.
>
> simple case:
>
> create table: CREATE TABLE test_tbl ( key1 INT NOT NULL, key2 INT NULL, key3 INT NULL, value BIGINT sum NULL ) ENGINE=OLAP AGGREGATE KEY(key1, key2, key3) COMMENT 'OLAP' DISTRIBUTED BY HASH(key1) BUCKETS 10 PROPERTIES ( "replication_num" = "1" );
>
> create rollup: `alter table test_tbl add rollup test_rollup(key1,key2,value);`
>
> I hope this query can hit rollup **test_rollup** `select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl group by key1;`
Can this kind of rewriting be used as a general rule instead of targeting materialized views?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "caiconghui (via GitHub)" <gi...@apache.org>.
caiconghui merged PR #28886:
URL: https://github.com/apache/doris/pull/28886
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902085311
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Load test result on commit 7c2e3100ebb87c0c93f0dd3136772e9cc99e9d4e with default session variables
Stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
Stream load orc: 59 seconds loaded 1101869774 Bytes, about 17 MB/s
Stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
Insert into select: 14.9 seconds inserted 10000000 Rows, about 671K ops/s
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868673308
run p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868421796
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1905215784
PR approved by anyone and no changes requested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904238145
<details>
<summary>TPC-H: <b>Total hot run time: 38884 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit ec8ac677e721785d374690ee9a9a87b47b44cc0d, data reload: false
------ Round 1 ----------------------------------
q1 17604 5263 5388 5263
q2 2051 148 137 137
q3 10698 1200 1181 1181
q4 10607 801 831 801
q5 8244 3245 3189 3189
q6 204 123 126 123
q7 877 517 509 509
q8 9526 1960 1961 1960
q9 7598 6561 6329 6329
q10 8239 3029 3000 3000
q11 417 202 209 202
q12 356 191 190 190
q13 17996 3370 3348 3348
q14 252 220 208 208
q15 546 507 502 502
q16 436 368 374 368
q17 933 550 506 506
q18 7632 6811 6777 6777
q19 1580 1339 1305 1305
q20 598 306 302 302
q21 2764 2390 2387 2387
q22 373 297 303 297
Total cold run time: 109531 ms
Total hot run time: 38884 ms
----- Round 2, with runtime_filter_mode=off -----
q1 5400 5391 5246 5246
q2 326 211 210 210
q3 3319 3214 3247 3214
q4 2115 2042 1987 1987
q5 5902 5776 5851 5776
q6 194 119 117 117
q7 2287 1931 1905 1905
q8 3201 3380 3384 3380
q9 8968 9232 8981 8981
q10 3922 3815 3802 3802
q11 579 465 455 455
q12 805 645 650 645
q13 16908 3150 3167 3150
q14 283 267 258 258
q15 557 510 507 507
q16 516 481 482 481
q17 1871 1838 1813 1813
q18 9547 13372 9572 9572
q19 28033 1589 1542 1542
q20 4574 1950 1921 1921
q21 15740 5377 5400 5377
q22 1561 572 557 557
Total cold run time: 116608 ms
Total hot run time: 60896 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1867504722
run p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868223278
run be-ut
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868843100
run pipline_x
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1867607869
run compile
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868678942
run p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902516071
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902527537
<details>
<summary>ClickBench: <b>Total hot run time: 30.76 s</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 50919cacf7645e286623238fa5acb691dde7ae56, data reload: false
query1 0.03 0.02 0.02
query2 0.06 0.03 0.02
query3 0.22 0.07 0.06
query4 1.68 0.08 0.11
query5 0.53 0.53 0.52
query6 1.29 0.63 0.63
query7 0.02 0.01 0.01
query8 0.03 0.02 0.02
query9 0.56 0.51 0.50
query10 0.55 0.58 0.56
query11 0.12 0.08 0.08
query12 0.11 0.10 0.09
query13 0.61 0.62 0.61
query14 0.79 0.83 0.81
query15 0.79 0.78 0.77
query16 0.37 0.37 0.39
query17 1.01 1.00 1.00
query18 0.25 0.23 0.27
query19 1.85 1.78 1.75
query20 0.01 0.01 0.02
query21 15.40 0.60 0.57
query22 2.88 2.70 1.49
query23 17.67 0.80 0.89
query24 2.65 1.29 1.00
query25 0.37 0.21 0.14
query26 0.56 0.14 0.15
query27 0.05 0.06 0.05
query28 11.28 0.80 0.77
query29 12.56 3.20 3.27
query30 0.51 0.48 0.47
query31 2.78 0.34 0.34
query32 3.42 0.48 0.48
query33 3.26 3.25 3.27
query34 15.83 4.34 4.28
query35 4.35 4.35 4.30
query36 1.09 1.07 1.09
query37 0.07 0.05 0.05
query38 0.03 0.03 0.03
query39 0.02 0.02 0.02
query40 0.16 0.14 0.13
query41 0.07 0.02 0.01
query42 0.02 0.01 0.01
query43 0.02 0.02 0.02
Total cold run time: 105.93 s
Total hot run time: 30.76 s
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902554666
run p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "starocean999 (via GitHub)" <gi...@apache.org>.
starocean999 commented on code in PR #28886:
URL: https://github.com/apache/doris/pull/28886#discussion_r1461459417
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java:
##########
@@ -260,6 +260,10 @@ public boolean isConstant() {
}
}
+ public boolean isConstantZero() {
Review Comment:
using name isLiteralZero is better
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1905215759
PR approved by at least one committer and no changes requested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868223263
run fe-ut
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868425227
TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Tpch sf100 test result on commit 332594c71f4bd275c7129fe91a4ec520690c16ed, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4694 4434 4458 4434
q2 372 140 159 140
q3 1475 1235 1240 1235
q4 1105 936 902 902
q5 3171 3160 3182 3160
q6 248 132 137 132
q7 1022 491 495 491
q8 2215 2228 2203 2203
q9 6740 6676 6680 6676
q10 3244 3276 3269 3269
q11 313 186 192 186
q12 352 206 208 206
q13 4568 3786 3779 3779
q14 239 209 217 209
q15 569 526 525 525
q16 450 392 380 380
q17 1026 658 558 558
q18 7106 6820 6759 6759
q19 1518 1417 1454 1417
q20 500 328 302 302
q21 3118 2659 2650 2650
q22 346 278 285 278
Total cold run time: 44391 ms
Total hot run time: 39891 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4346 4338 4334 4334
q2 275 168 170 168
q3 3515 3520 3501 3501
q4 2402 2368 2373 2368
q5 5719 5717 5728 5717
q6 243 125 124 124
q7 2356 1860 1889 1860
q8 3526 3539 3533 3533
q9 9084 8987 9010 8987
q10 3958 4003 4025 4003
q11 486 364 365 364
q12 774 599 615 599
q13 4296 3566 3544 3544
q14 291 271 256 256
q15 563 526 524 524
q16 490 454 478 454
q17 1906 1873 1871 1871
q18 8561 8114 8150 8114
q19 1785 1770 1732 1732
q20 2259 1949 1943 1943
q21 6503 6166 6142 6142
q22 484 427 411 411
Total cold run time: 63822 ms
Total hot run time: 60549 ms
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868847570
run pipeline_x p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1867716666
run compile
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868770003
run compile
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902074884
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "starocean999 (via GitHub)" <gi...@apache.org>.
starocean999 commented on code in PR #28886:
URL: https://github.com/apache/doris/pull/28886#discussion_r1461459936
##########
fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMvIndexTest.java:
##########
@@ -1251,4 +1251,88 @@ private void testMvWithTwoTable(String sql, String firstTableIndexName, String s
Assertions.assertEquals(secondTableIndexName, scan1.getSelectedIndexName());
});
}
+
+ @Test
+ public void testSubExpressionsInAggregation() throws Exception {
+ createTable("CREATE TABLE db1.`test_pre_agg_tbl` (\n"
Review Comment:
pls add test for float/double, decimalv2/v3 too
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902964378
run arm
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904097942
<details>
<summary>ClickBench: <b>Total hot run time: 30.92 s</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit dc843dfac5d3453a191062c29063f08093e542d1, data reload: false
query1 0.03 0.02 0.02
query2 0.06 0.02 0.02
query3 0.23 0.05 0.06
query4 1.66 0.10 0.10
query5 0.53 0.52 0.52
query6 1.28 0.64 0.64
query7 0.02 0.01 0.01
query8 0.03 0.02 0.02
query9 0.55 0.50 0.49
query10 0.54 0.56 0.56
query11 0.11 0.09 0.09
query12 0.12 0.09 0.08
query13 0.62 0.61 0.60
query14 0.80 0.80 0.80
query15 0.80 0.81 0.78
query16 0.39 0.38 0.38
query17 0.96 1.01 1.03
query18 0.24 0.25 0.27
query19 1.88 1.80 1.80
query20 0.01 0.01 0.01
query21 15.41 0.58 0.55
query22 2.31 2.39 2.81
query23 17.38 0.88 0.68
query24 2.72 0.92 0.57
query25 0.34 0.22 0.07
query26 0.49 0.14 0.14
query27 0.06 0.06 0.05
query28 12.06 0.80 0.75
query29 12.50 3.21 3.14
query30 0.53 0.52 0.47
query31 2.78 0.34 0.35
query32 3.36 0.50 0.49
query33 3.24 3.28 3.25
query34 16.19 4.22 4.23
query35 4.30 4.32 4.26
query36 1.12 1.08 1.07
query37 0.07 0.04 0.04
query38 0.04 0.03 0.03
query39 0.02 0.02 0.01
query40 0.17 0.13 0.13
query41 0.07 0.01 0.02
query42 0.03 0.02 0.01
query43 0.02 0.02 0.02
Total cold run time: 106.07 s
Total hot run time: 30.92 s
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "starocean999 (via GitHub)" <gi...@apache.org>.
starocean999 commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1870895767
> > I don't understand why we need to support this case, it seems meaningless?
>
> If we create a rollup or a materialized view for data preaggregation, and then use `SUM(case ... when ... then)` or `SUM(if(... , ... , ...))`, Doris can't hit rollup or materialized view we created before.
>
> simple case:
>
> create table: CREATE TABLE test_tbl ( key1 INT NOT NULL, key2 INT NULL, key3 INT NULL, value BIGINT sum NULL ) ENGINE=OLAP AGGREGATE KEY(key1, key2, key3) COMMENT 'OLAP' DISTRIBUTED BY HASH(key1) BUCKETS 10 PROPERTIES ( "replication_num" = "1" );
>
> create rollup: `alter table test_tbl add rollup test_rollup(key1,key2,value);`
>
> I hope this query can hit rollup **test_rollup** `select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl group by key1;`
Thanks for your effort. But this pr is not correct, because the sql above SHOULD NOT hit mv. The sql bellow is correct one which need to hit mv(but not supported now).
`select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl where key2 > 0 group by key1;`
This sql is equivalent to
`select key1,sum(value) as value from test_tbl where key2 > 0 group by key1;`
As the mv selection logical is being refactoring in nereids. We can considering such optimization after the refactoring work done. ( This refactoring is scheduled, but not in near future)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868953771
run feut
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868178612
run p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904065247
<details>
<summary>TPC-H: <b>Total hot run time: 38775 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit dc843dfac5d3453a191062c29063f08093e542d1, data reload: false
------ Round 1 ----------------------------------
q1 17653 5453 5276 5276
q2 2039 144 137 137
q3 10629 1186 1170 1170
q4 10228 785 800 785
q5 7758 3191 3104 3104
q6 203 121 125 121
q7 884 489 473 473
q8 9249 1929 1952 1929
q9 7260 6401 6364 6364
q10 8257 3027 3163 3027
q11 428 213 211 211
q12 357 192 196 192
q13 18015 3366 3394 3366
q14 237 216 215 215
q15 550 523 498 498
q16 482 385 372 372
q17 945 555 495 495
q18 7429 7007 6788 6788
q19 1593 1253 1362 1253
q20 582 309 296 296
q21 2799 2400 2453 2400
q22 376 327 303 303
Total cold run time: 107953 ms
Total hot run time: 38775 ms
----- Round 2, with runtime_filter_mode=off -----
q1 5503 5432 5377 5377
q2 328 214 213 213
q3 3363 3259 3281 3259
q4 2099 2064 2035 2035
q5 6063 5886 5983 5886
q6 208 118 123 118
q7 2308 1857 1902 1857
q8 3227 3387 3409 3387
q9 8985 8963 8888 8888
q10 3962 3879 3823 3823
q11 550 459 467 459
q12 815 617 638 617
q13 16931 3208 3159 3159
q14 286 248 273 248
q15 550 497 507 497
q16 508 479 477 477
q17 2097 1861 1873 1861
q18 9531 17570 9529 9529
q19 24004 1647 1535 1535
q20 4565 1925 1917 1917
q21 14504 5418 5338 5338
q22 993 551 598 551
Total cold run time: 111380 ms
Total hot run time: 61031 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902081581
<details>
<summary>TPC-H: <b>Total hot run time: 38703 ms</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 7c2e3100ebb87c0c93f0dd3136772e9cc99e9d4e, data reload: false
------ Round 1 ----------------------------------
q1 17673 5335 5387 5335
q2 2031 142 137 137
q3 10641 1162 1147 1147
q4 10222 773 793 773
q5 7771 3111 3155 3111
q6 199 118 116 116
q7 859 488 472 472
q8 9234 1891 1939 1891
q9 7234 6367 6339 6339
q10 8213 3035 3017 3017
q11 407 212 212 212
q12 360 182 182 182
q13 17990 3369 3319 3319
q14 254 224 216 216
q15 538 511 500 500
q16 426 383 386 383
q17 939 505 477 477
q18 7532 6898 6755 6755
q19 1571 1369 1332 1332
q20 572 291 287 287
q21 2773 2403 2434 2403
q22 364 327 299 299
Total cold run time: 107803 ms
Total hot run time: 38703 ms
----- Round 2, with runtime_filter_mode=off -----
q1 5455 5390 5336 5336
q2 324 216 211 211
q3 3360 3240 3168 3168
q4 2119 2075 1992 1992
q5 5857 5891 5851 5851
q6 194 115 112 112
q7 2312 1877 1811 1811
q8 3222 3367 3386 3367
q9 9061 8758 8896 8758
q10 3884 3824 3827 3824
q11 550 447 462 447
q12 794 617 608 608
q13 16921 3156 3149 3149
q14 290 258 276 258
q15 550 503 501 501
q16 500 448 460 448
q17 1867 1805 1829 1805
q18 9302 18776 9655 9655
q19 24055 1559 1557 1557
q20 4613 1922 1922 1922
q21 14431 5396 5332 5332
q22 941 548 577 548
Total cold run time: 110602 ms
Total hot run time: 60660 ms
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902098686
run feut
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904280308
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Load test result on commit ec8ac677e721785d374690ee9a9a87b47b44cc0d with default session variables
Stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
Stream load orc: 58 seconds loaded 1101869774 Bytes, about 18 MB/s
Stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
Insert into select: 14.1 seconds inserted 10000000 Rows, about 709K ops/s
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1902527995
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Load test result on commit 50919cacf7645e286623238fa5acb691dde7ae56 with default session variables
Stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
Stream load orc: 58 seconds loaded 1101869774 Bytes, about 18 MB/s
Stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
Insert into select: 14.7 seconds inserted 10000000 Rows, about 680K ops/s
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1903980716
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Optimize preagg for aggregate function [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1904273786
<details>
<summary>ClickBench: <b>Total hot run time: 30.77 s</b></summary>
```
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit ec8ac677e721785d374690ee9a9a87b47b44cc0d, data reload: false
query1 0.03 0.02 0.02
query2 0.06 0.02 0.03
query3 0.23 0.05 0.05
query4 1.68 0.06 0.07
query5 0.52 0.53 0.52
query6 1.31 0.65 0.63
query7 0.02 0.01 0.02
query8 0.04 0.02 0.02
query9 0.55 0.50 0.50
query10 0.57 0.57 0.56
query11 0.12 0.08 0.09
query12 0.12 0.09 0.08
query13 0.61 0.62 0.60
query14 0.78 0.81 0.80
query15 0.80 0.77 0.78
query16 0.40 0.37 0.38
query17 0.98 1.03 1.05
query18 0.24 0.28 0.24
query19 1.88 1.80 1.83
query20 0.01 0.01 0.01
query21 15.41 0.58 0.58
query22 2.58 2.33 2.08
query23 17.07 0.79 0.85
query24 2.77 0.57 1.38
query25 0.34 0.18 0.13
query26 0.58 0.14 0.14
query27 0.07 0.05 0.05
query28 11.30 0.77 0.75
query29 12.55 3.12 3.24
query30 0.53 0.49 0.48
query31 2.77 0.34 0.35
query32 3.36 0.49 0.49
query33 3.25 3.21 3.26
query34 15.98 4.31 4.29
query35 4.33 4.29 4.26
query36 1.12 1.07 1.07
query37 0.06 0.04 0.05
query38 0.03 0.03 0.03
query39 0.03 0.02 0.02
query40 0.16 0.13 0.12
query41 0.07 0.01 0.02
query42 0.02 0.01 0.01
query43 0.02 0.02 0.02
Total cold run time: 105.35 s
Total hot run time: 30.77 s
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868426450
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 44.88 seconds
stream load tsv: 564 seconds loaded 74807831229 Bytes, about 126 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 66 seconds loaded 1101869774 Bytes, about 15 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 29.1 seconds inserted 10000000 Rows, about 343K ops/s
storage size: 17183726366 Bytes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868834295
run external
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868687540
run p0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1868953812
run beut
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1869002253
> I don't understand why we need to support this case, it seems meaningless?
If we create a rollup or a materialized view for data preaggregation, and then use `SUM(case ... when ... then)` or `SUM(if(... , ... , ...))`, Doris can't hit rollup or materialized view we created before.
simple case:
create table:
CREATE TABLE test_tbl (
key1 INT NOT NULL,
key2 INT NULL,
key3 INT NULL,
value BIGINT sum NULL
) ENGINE=OLAP
AGGREGATE KEY(key1, key2, key3)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(key1) BUCKETS 10
PROPERTIES (
"replication_num" = "1"
);
create rollup:
`alter table test_tbl add rollup test_rollup(key1,key2,value);`
I hope this query can hit rollup **test_rollup**
`select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl group by key1;`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [PR] [Enhancement](plan) Support PREAGGOPEN when the case when/if contains a constant zero/nul… [doris]
Posted by "DarvenDuan (via GitHub)" <gi...@apache.org>.
DarvenDuan commented on PR #28886:
URL: https://github.com/apache/doris/pull/28886#issuecomment-1869241479
> > > I don't understand why we need to support this case, it seems meaningless?
> >
> >
> > If we create a rollup or a materialized view for data preaggregation, and then use `SUM(case ... when ... then)` or `SUM(if(... , ... , ...))`, Doris can't hit rollup or materialized view we created before.
> > simple case:
> > create table: CREATE TABLE test_tbl ( key1 INT NOT NULL, key2 INT NULL, key3 INT NULL, value BIGINT sum NULL ) ENGINE=OLAP AGGREGATE KEY(key1, key2, key3) COMMENT 'OLAP' DISTRIBUTED BY HASH(key1) BUCKETS 10 PROPERTIES ( "replication_num" = "1" );
> > create rollup: `alter table test_tbl add rollup test_rollup(key1,key2,value);`
> > I hope this query can hit rollup **test_rollup** `select key1,sum(case when key2 > 0 then value else 0 end) as value from test_tbl group by key1;`
>
> Can this kind of rewriting be used as a general rule instead of targeting materialized views?
It not just works on Materialized views. I added this rewriting rule in both `turnOffPreAgg ` function for legacy planner and `checkAggFunc` function for nereids planner. So it can work as a general rule.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org