You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "starocean999 (via GitHub)" <gi...@apache.org> on 2024/03/08 09:22:44 UTC
[PR] [enhancement](nereids)support unnest subquery with group by and having clause [doris]
starocean999 opened a new pull request, #32002:
URL: https://github.com/apache/doris/pull/32002
## Proposed changes
Issue Number: close #xxx
<!--Describe your changes.-->
## 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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1993728069
<details>
<summary>ClickBench: <b>Total hot run time: 30.71 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 336d0fa5d664fa889abdd30808e886786693da4f, data reload: false
query1 0.05 0.04 0.04
query2 0.06 0.03 0.02
query3 0.22 0.06 0.06
query4 1.67 0.10 0.10
query5 0.51 0.51 0.51
query6 1.20 0.63 0.64
query7 0.02 0.02 0.01
query8 0.03 0.02 0.03
query9 0.54 0.50 0.51
query10 0.53 0.56 0.54
query11 0.12 0.09 0.10
query12 0.12 0.09 0.09
query13 0.56 0.56 0.56
query14 0.74 0.75 0.76
query15 0.80 0.78 0.78
query16 0.39 0.38 0.39
query17 1.03 1.03 0.96
query18 0.18 0.25 0.25
query19 1.80 1.66 1.81
query20 0.02 0.01 0.01
query21 15.40 0.55 0.55
query22 2.20 1.66 1.62
query23 17.16 0.85 0.68
query24 3.25 2.39 1.48
query25 0.20 0.11 0.17
query26 0.67 0.11 0.11
query27 0.05 0.03 0.02
query28 9.91 0.88 0.89
query29 12.52 3.19 3.26
query30 0.70 0.60 0.58
query31 2.80 0.33 0.33
query32 3.40 0.44 0.43
query33 2.83 2.77 2.86
query34 16.36 4.38 4.41
query35 4.32 4.35 4.34
query36 1.05 1.00 1.00
query37 0.07 0.06 0.05
query38 0.04 0.03 0.03
query39 0.03 0.01 0.02
query40 0.16 0.14 0.13
query41 0.08 0.02 0.02
query42 0.03 0.02 0.02
query43 0.03 0.02 0.03
Total cold run time: 103.85 s
Total hot run time: 30.71 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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1993732602
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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "starocean999 (via GitHub)" <gi...@apache.org>.
starocean999 commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1985340850
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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1993732638
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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow commented on code in PR #32002:
URL: https://github.com/apache/doris/pull/32002#discussion_r1522399555
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpCorrelatedFilterUnderApplyAggregateProject.java:
##########
@@ -57,27 +61,39 @@
* child
* </pre>
*/
-public class PullUpCorrelatedFilterUnderApplyAggregateProject extends OneRewriteRuleFactory {
+public class PullUpCorrelatedFilterUnderApplyAggregateProject implements RewriteRuleFactory {
@Override
- public Rule build() {
- return logicalApply(any(), logicalAggregate(logicalProject(logicalFilter())))
- .when(LogicalApply::isCorrelated).then(apply -> {
- LogicalAggregate<LogicalProject<LogicalFilter<Plan>>> agg = apply.right();
+ public List<Rule> buildRules() {
+ return ImmutableList.of(logicalApply(any(), logicalAggregate(
+ logicalProject(logicalFilter()))).when(LogicalApply::isCorrelated).then(
+ PullUpCorrelatedFilterUnderApplyAggregateProject::pullUpCorrelatedFilter)
+ .toRule(RuleType.PULL_UP_CORRELATED_FILTER_UNDER_APPLY_AGGREGATE_PROJECT),
Review Comment:
use different rule type for these two rules
--
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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "starocean999 (via GitHub)" <gi...@apache.org>.
starocean999 commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1993669656
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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1993732153
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Load test result on commit 336d0fa5d664fa889abdd30808e886786693da4f with default session variables
Stream load json: 18 seconds loaded 2358488459 Bytes, about 124 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: 13.7 seconds inserted 10000000 Rows, about 729K 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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1985340725
Thank you for your contribution to Apache Doris.
Don't know what should be done next? See [How to process your PR](https://cwiki.apache.org/confluence/display/DORIS/How+to+process+your+PR)
--
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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1993707722
<details>
<summary>TPC-H: <b>Total hot run time: 36405 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 336d0fa5d664fa889abdd30808e886786693da4f, data reload: false
------ Round 1 ----------------------------------
q1 17666 4216 4106 4106
q2 2019 151 143 143
q3 10577 914 893 893
q4 4644 655 721 655
q5 7595 2729 2767 2729
q6 191 127 130 127
q7 1189 799 792 792
q8 9463 2015 2029 2015
q9 7291 6461 6490 6461
q10 8163 2485 2517 2485
q11 421 219 212 212
q12 790 300 283 283
q13 17974 3017 2979 2979
q14 271 251 259 251
q15 493 464 446 446
q16 481 394 393 393
q17 935 513 529 513
q18 6594 5770 5794 5770
q19 1562 1411 1422 1411
q20 544 274 274 274
q21 7372 3181 3254 3181
q22 845 286 297 286
Total cold run time: 107080 ms
Total hot run time: 36405 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4065 4087 4045 4045
q2 325 220 232 220
q3 2972 2830 2855 2830
q4 1850 1542 1588 1542
q5 5181 5240 5183 5183
q6 196 117 115 115
q7 2255 1832 1805 1805
q8 3125 3286 3253 3253
q9 8529 8473 8504 8473
q10 6094 3718 3702 3702
q11 530 442 439 439
q12 711 581 577 577
q13 12792 2879 2839 2839
q14 261 249 256 249
q15 475 438 445 438
q16 445 420 407 407
q17 1707 1465 1448 1448
q18 7414 7219 7117 7117
q19 5217 1501 1491 1491
q20 1885 1695 1690 1690
q21 4871 4572 4744 4572
q22 518 414 460 414
Total cold run time: 71418 ms
Total hot run time: 52849 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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #32002:
URL: https://github.com/apache/doris/pull/32002#issuecomment-1993721084
<details>
<summary>TPC-DS: <b>Total hot run time: 187117 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 336d0fa5d664fa889abdd30808e886786693da4f, data reload: false
query1 922 366 331 331
query2 7624 2089 1953 1953
query3 6709 220 213 213
query4 30975 20561 20622 20561
query5 4289 422 402 402
query6 260 174 184 174
query7 4622 296 295 295
query8 226 167 168 167
query9 9971 2270 2248 2248
query10 440 234 258 234
query11 17202 14112 14377 14112
query12 128 84 84 84
query13 1638 422 417 417
query14 11671 11169 10801 10801
query15 271 229 184 184
query16 8084 260 256 256
query17 1936 559 529 529
query18 2095 276 268 268
query19 273 152 143 143
query20 94 83 85 83
query21 195 131 120 120
query22 4614 4513 4455 4455
query23 31749 31033 31111 31033
query24 10802 2877 2855 2855
query25 586 349 358 349
query26 1214 159 156 156
query27 2500 353 349 349
query28 7760 1901 1845 1845
query29 863 626 600 600
query30 306 144 147 144
query31 989 697 734 697
query32 88 58 58 58
query33 754 249 248 248
query34 1072 474 481 474
query35 845 617 588 588
query36 967 891 895 891
query37 116 76 79 76
query38 3285 3149 3157 3149
query39 1406 1397 1357 1357
query40 205 125 112 112
query41 48 46 47 46
query42 98 99 100 99
query43 500 463 449 449
query44 1328 705 707 705
query45 270 269 263 263
query46 1097 713 695 695
query47 1669 1570 1592 1570
query48 432 344 351 344
query49 1115 327 317 317
query50 764 374 373 373
query51 6764 6727 6757 6727
query52 106 89 89 89
query53 342 266 274 266
query54 295 232 232 232
query55 89 78 77 77
query56 232 216 223 216
query57 1079 992 1012 992
query58 224 191 196 191
query59 2732 2587 2573 2573
query60 252 244 238 238
query61 98 88 87 87
query62 614 394 399 394
query63 306 275 275 275
query64 5550 3832 3912 3832
query65 3090 3008 2981 2981
query66 855 345 325 325
query67 14872 14487 14297 14297
query68 5299 534 520 520
query69 577 378 385 378
query70 1161 1105 1118 1105
query71 440 273 282 273
query72 6342 2758 2615 2615
query73 703 317 319 317
query74 7092 6565 6621 6565
query75 8773 8033 8078 8033
query76 3533 892 1047 892
query77 433 280 258 258
query78 10290 9626 9621 9621
query79 8290 517 525 517
query80 1569 409 445 409
query81 538 211 217 211
query82 1132 202 208 202
query83 211 145 149 145
query84 285 83 80 80
query85 1571 387 361 361
query86 464 296 312 296
query87 3458 3197 3234 3197
query88 5214 2300 2323 2300
query89 530 361 361 361
query90 2044 176 172 172
query91 153 129 127 127
query92 62 46 45 45
query93 6401 507 491 491
query94 1140 172 176 172
query95 413 333 318 318
query96 608 293 271 271
query97 4396 4236 4286 4236
query98 224 219 213 213
query99 1079 753 752 752
Total cold run time: 308992 ms
Total hot run time: 187117 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](nereids)support unnest subquery with group by and having clause [doris]
Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow merged PR #32002:
URL: https://github.com/apache/doris/pull/32002
--
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