在日常的使用中，出现了这样的玄学问题

💬 StarRocks 用户问答 功能使用相关
#1

第一次查询

image
image1686×1172 230 KB

第二次查询，添加了sale_product字段，count_order变成了97
image
image1784×1098 234 KB

第三次查询，在platform_order_id前添加了distinct，count_order变成了2000多
image
image1822×1038 232 KB

starrocks的版本是3.0.0

#2

请您发下第二和第三种情况的执行计划：explain SQL

#3

这是第二种情况的执行计划
Explain String |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:169: date_format | 46: shortid | 170: sum | 172: count | 171: count | 232: max |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 16:MERGING-EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 231: ifnull |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 16 |
| UNPARTITIONED |
| |
| 15:SORT |
| | order by: <slot 169> 169: date_format DESC |
| | offset: 0 |
| | |
| 14:Project |
| | <slot 46> : 46: shortid |
| | <slot 169> : 169: date_format |
| | <slot 170> : 170: sum |
| | <slot 171> : 171: count |
| | <slot 172> : 172: count |
| | <slot 232> : 232: max |
| | |
| 13:HASH JOIN |
| | join op: LEFT OUTER JOIN (BUCKET_SHUFFLE(S)) |
| | colocate: false, reason: |
| | equal join conjunct: 46: shortid = 231: ifnull |
| | |
| |----12:AGGREGATE (update finalize) |
| | | output: max(186: fans_total) |
| | | group by: 231: ifnull |
| | | |
| | 11:EXCHANGE |
| | |
| 8:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 11 |
| HASH_PARTITIONED: 231: ifnull |
| |
| 10:Project |
| | <slot 186> : 186: fans_total |
| | <slot 231> : ifnull(181: account_id, 180: unique_id) |
| | |
| 9:OlapScanNode |
| TABLE: channel_talent |
| PREAGGREGATION: ON |
| PREDICATES: ifnull(181: account_id, 180: unique_id) = ‘924654377’, 205: is_delete = 0 |
| partitions=1/1 |
| rollup: channel_talent |
| tabletRatio=6/6 |
| tabletList=9052095,9052099,9052103,9052107,9052111,9052115 |
| cardinality=1 |
| avgRowSize=39.488586 |
| numNodes=0 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 169: date_format, 46: shortid |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 08 |
| HASH_PARTITIONED: 46: shortid |
| |
| 7:AGGREGATE (merge finalize) |
| | output: sum(170: sum), count(171: count), sum(172: count) |
| | group by: 169: date_format, 46: shortid |
| | having: 170: sum >= 50000 |
| | |
| 6:EXCHANGE |
| |
| PLAN FRAGMENT 4 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 169: date_format, 46: shortid, 4: platform_product_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 06 |
| HASH_PARTITIONED: 169: date_format, 46: shortid |
| |
| 5:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(233: sum), count(4: platform_product_id), sum(234: count) |
| | group by: 169: date_format, 46: shortid |
| | |
| 4:AGGREGATE (merge finalize) |
| | output: sum(233: sum), count(234: count) |
| | group by: 169: date_format, 46: shortid, 4: platform_product_id |
| | |
| 3:EXCHANGE |
| |
| PLAN FRAGMENT 5 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| HASH_PARTITIONED: 169: date_format, 46: shortid, 4: platform_product_id |
| |
| 2:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(26: estimate_settlement_amount), count(2: platform_order_id) |
| | group by: 169: date_format, 46: shortid, 4: platform_product_id |
| | |
| 1:Project |
| | <slot 2> : 2: platform_order_id |
| | <slot 4> : 4: platform_product_id |
| | <slot 26> : 26: estimate_settlement_amount |
| | <slot 46> : 46: shortid |
| | <slot 169> : date_format(1: paid_time, ‘%Y-%m’) |
| | |
| 0:OlapScanNode |
| TABLE: redu_order |
| PREAGGREGATION: ON |
| PREDICATES: date_format(1: paid_time, ‘%Y-%m’) >= ‘2023-06’, date_format(1: paid_time, ‘%Y-%m’) <= ‘2023-07’, 46: shortid = ‘924654377’, 3: platform_type = 2, find_in_set(‘6’, 6: applet_peg) = 0 |
| partitions=4/31 |
| rollup: redu_order |
| tabletRatio=24/24 |
| tabletList=9351541,9351545,9351549,9351553,9351557,9351561,9352069,9352073,9352077,9352081 … |
| cardinality=4 |
| avgRowSize=44.939835 |
| numNodes=0

#4

第三种情况的执行计划
PLAN FRAGMENT 0 |
| OUTPUT EXPRS:169: date_format | 46: shortid | 170: sum | 172: count | 171: count | 232: max |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 13:MERGING-EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 231: ifnull |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 13 |
| UNPARTITIONED |
| |
| 12:SORT |
| | order by: <slot 169> 169: date_format DESC |
| | offset: 0 |
| | |
| 11:Project |
| | <slot 46> : 46: shortid |
| | <slot 169> : 169: date_format |
| | <slot 170> : 170: sum |
| | <slot 171> : 171: count |
| | <slot 172> : 172: count |
| | <slot 232> : 232: max |
| | |
| 10:HASH JOIN |
| | join op: LEFT OUTER JOIN (BUCKET_SHUFFLE(S)) |
| | colocate: false, reason: |
| | equal join conjunct: 46: shortid = 231: ifnull |
| | |
| |----9:AGGREGATE (update finalize) |
| | | output: max(186: fans_total) |
| | | group by: 231: ifnull |
| | | |
| | 8:EXCHANGE |
| | |
| 5:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 08 |
| HASH_PARTITIONED: 231: ifnull |
| |
| 7:Project |
| | <slot 186> : 186: fans_total |
| | <slot 231> : ifnull(181: account_id, 180: unique_id) |
| | |
| 6:OlapScanNode |
| TABLE: channel_talent |
| PREAGGREGATION: ON |
| PREDICATES: ifnull(181: account_id, 180: unique_id) = ‘924654377’, 205: is_delete = 0 |
| partitions=1/1 |
| rollup: channel_talent |
| tabletRatio=6/6 |
| tabletList=9052095,9052099,9052103,9052107,9052111,9052115 |
| cardinality=1 |
| avgRowSize=39.488586 |
| numNodes=0 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 169: date_format, 46: shortid |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 05 |
| HASH_PARTITIONED: 46: shortid |
| |
| 4:AGGREGATE (merge finalize) |
| | output: sum(170: sum), multi_distinct_count(171: count), multi_distinct_count(172: count) |
| | group by: 169: date_format, 46: shortid |
| | having: 170: sum >= 50000, 170: sum >= 50000 |
| | |
| 3:EXCHANGE |
| |
| PLAN FRAGMENT 4 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| HASH_PARTITIONED: 169: date_format, 46: shortid |
| |
| 2:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(26: estimate_settlement_amount), multi_distinct_count(4: platform_product_id), multi_distinct_count(2: platform_order_id) |
| | group by: 169: date_format, 46: shortid |
| | |
| 1:Project |
| | <slot 2> : 2: platform_order_id |
| | <slot 4> : 4: platform_product_id |
| | <slot 26> : 26: estimate_settlement_amount |
| | <slot 46> : 46: shortid |
| | <slot 169> : date_format(1: paid_time, ‘%Y-%m’) |
| | |
| 0:OlapScanNode |
| TABLE: redu_order |
| PREAGGREGATION: ON |
| PREDICATES: date_format(1: paid_time, ‘%Y-%m’) >= ‘2023-06’, date_format(1: paid_time, ‘%Y-%m’) <= ‘2023-07’, 46: shortid = ‘924654377’, 3: platform_type = 2, find_in_set(‘6’, 6: applet_peg) = 0 |
| partitions=4/31 |
| rollup: redu_order |
| tabletRatio=24/24 |
| tabletList=9351541,9351545,9351549,9351553,9351557,9351561,9352069,9352073,9352077,9352081 … |
| cardinality=4 |
| avgRowSize=44.939835 |
| numNodes=0

京ICP备2022026421号-1