hive外表开启Data Cache查询速度变慢

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】hive外表开启Data Cache查询速度变慢
图2所示例，DataCache已经生效

【背景】开启DataCache功能后，查询速度降低
【业务影响】
关闭DataCache功能后恢复。
【StarRocks版本】3.1.2
【集群规模】3fe + 3be
【机器信息】be节点16C 64G，SSD磁盘
【联系方式】uestc_zhanghe@qq.com

没开CACAHE的EXPLAIN:
PLAN FRAGMENT 0
OUTPUT EXPRS:25: ym | 291: if | 290: order_channel_name | 295: count | 296: sum | 297: sum | 298: sum
PARTITION: UNPARTITIONED

RESULT SINK

28:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 25: ym, 291: if, 290: order_channel_name

STREAM DATA SINK
EXCHANGE ID: 28
UNPARTITIONED

27:AGGREGATE (merge finalize)
| output: count(295: count), sum(296: sum), sum(297: sum), sum(298: sum)
| group by: 25: ym, 291: if, 290: order_channel_name
|
26:EXCHANGE

PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 9: order_channel_cd

STREAM DATA SINK
EXCHANGE ID: 26
HASH_PARTITIONED: 25: ym, 291: if, 290: order_channel_name

25:AGGREGATE (update serialize)
| STREAMING
| output: count(1), sum(292: if), sum(293: if), sum(294: if)
| group by: 25: ym, 291: if, 290: order_channel_name
|
24:Project
| <slot 25> : 25: ym
| <slot 290> : 290: order_channel_name
| <slot 291> : if(8: sale_mode_name = ‘����’, ‘����’, ‘��ʳ’)
| <slot 292> : if(301: expr, 1, 0)
| <slot 293> : if((301: expr) AND (303: expr), 1, 0)
| <slot 294> : if((((301: expr) AND (9: order_channel_cd IN (‘3rd001’, ‘3rd002’, ‘3rd003’, ‘3rd004’))) AND (303: expr)) AND (30: bill_id IS NULL), 1, 0)
| common expressions:
| <slot 301> : 22: oneid IS NOT NULL
| <slot 302> : coalesce(11: member_id, ‘’)
| <slot 303> : 302: coalesce != ‘’
|
23:HASH JOIN
| join op: LEFT OUTER JOIN (PARTITIONED)
| colocate: false, reason:
| equal join conjunct: 9: order_channel_cd = 289: order_channel_cd
|
|----22:EXCHANGE
|
20:EXCHANGE

PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 22
HASH_PARTITIONED: 289: order_channel_cd

21:HdfsScanNode
TABLE: t99_order_channel_cd
partitions=1/1
cardinality=1
avgRowSize=2.0
numNodes=0

PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 1: store_id

STREAM DATA SINK
EXCHANGE ID: 20
HASH_PARTITIONED: 9: order_channel_cd

19:Project
| <slot 8> : 8: sale_mode_name
| <slot 9> : 9: order_channel_cd
| <slot 11> : 11: member_id
| <slot 22> : 22: oneid
| <slot 25> : 25: ym
| <slot 30> : 30: bill_id
|
18:HASH JOIN
| join op: LEFT OUTER JOIN (BUCKET_SHUFFLE(S))
| colocate: false, reason:
| equal join conjunct: 1: store_id = 27: store_id
| equal join conjunct: 26: ymd = 151: ymd
| equal join conjunct: 5: bill_id = 30: bill_id
|
|----17:EXCHANGE
|
6:Project
| <slot 1> : 1: store_id
| <slot 5> : 5: bill_id
| <slot 8> : 8: sale_mode_name
| <slot 9> : 9: order_channel_cd
| <slot 11> : 11: member_id
| <slot 22> : 22: oneid
| <slot 25> : 25: ym
| <slot 26> : 26: ymd
|
5:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| colocate: false, reason:
| equal join conjunct: 1: store_id = 183: store_id
|
|----4:EXCHANGE
|
1:EXCHANGE

PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 27: store_id, 151: ymd, 30: bill_id

STREAM DATA SINK
EXCHANGE ID: 17
HASH_PARTITIONED: 27: store_id

16:AGGREGATE (merge finalize)
| group by: 27: store_id, 151: ymd, 30: bill_id
|
15:EXCHANGE

PLAN FRAGMENT 6
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 32: member_card_id

STREAM DATA SINK
EXCHANGE ID: 15
HASH_PARTITIONED: 27: store_id, 151: ymd, 30: bill_id

14:AGGREGATE (update serialize)
| STREAMING
| group by: 27: store_id, 151: ymd, 30: bill_id
|
13:Project
| <slot 27> : 27: store_id
| <slot 30> : 30: bill_id
| <slot 151> : 151: ymd
|
12:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| colocate: false, reason:
| equal join conjunct: 32: member_card_id = 152: card_id
|
|----11:EXCHANGE
|
9:EXCHANGE

PLAN FRAGMENT 7
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 11
HASH_PARTITIONED: 152: card_id

10:HdfsScanNode
TABLE: com_card_info
NON-PARTITION PREDICATES: 152: card_id IS NOT NULL
partitions=1/1
cardinality=1
avgRowSize=1.0
numNodes=0

PLAN FRAGMENT 8
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 09
HASH_PARTITIONED: 32: member_card_id

8:Project
| <slot 27> : 27: store_id
| <slot 30> : 30: bill_id
| <slot 32> : 32: member_card_id
| <slot 151> : 151: ymd
|
7:HdfsScanNode
TABLE: com_pos_sale_mas
PARTITION PREDICATES: 150: ym >= 202301, 150: ym <= 202309
partitions=273/1766
cardinality=1
avgRowSize=5.0
numNodes=0

PLAN FRAGMENT 9
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 04
HASH_PARTITIONED: 183: store_id

3:Project
| <slot 183> : 183: store_id
|
2:HdfsScanNode
TABLE: dim_store_base_info_cur
NON-PARTITION PREDICATES: 197: format_cd = ‘010’
MIN/MAX PREDICATES: 299: format_cd <= ‘010’, 300: format_cd >= ‘010’
partitions=1/1
cardinality=1
avgRowSize=2.0
numNodes=0

PLAN FRAGMENT 10
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 01
HASH_PARTITIONED: 1: store_id

0:HdfsScanNode
TABLE: com_store_oneid_sale_mas
PARTITION PREDICATES: 25: ym >= 202301, 25: ym <= 202309
partitions=273/403
cardinality=1
avgRowSize=8.0
numNodes=0

打开CACHE的EXPALIN：执行计划做了改变，子表join后将多表聚合放在同一个fragment
PLAN FRAGMENT 0
OUTPUT EXPRS:25: ym | 291: if | 290: order_channel_name | 295: count | 296: sum | 297: sum | 298: sum
PARTITION: UNPARTITIONED

RESULT SINK

26:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 25: ym, 291: if, 290: order_channel_name

STREAM DATA SINK
EXCHANGE ID: 26
UNPARTITIONED

25:AGGREGATE (merge finalize)
| output: count(295: count), sum(296: sum), sum(297: sum), sum(298: sum)
| group by: 25: ym, 291: if, 290: order_channel_name
|
24:EXCHANGE

PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 27: store_id, 151: ymd, 30: bill_id

STREAM DATA SINK
EXCHANGE ID: 24
HASH_PARTITIONED: 25: ym, 291: if, 290: order_channel_name

23:AGGREGATE (update serialize)
| STREAMING
| output: count(1), sum(292: if), sum(293: if), sum(294: if)
| group by: 25: ym, 291: if, 290: order_channel_name
|
22:Project
| <slot 25> : 25: ym
| <slot 290> : 290: order_channel_name
| <slot 291> : if(8: sale_mode_name = ‘����’, ‘����’, ‘��ʳ’)
| <slot 292> : if(301: expr, 1, 0)
| <slot 293> : if((301: expr) AND (303: expr), 1, 0)
| <slot 294> : if((((301: expr) AND (9: order_channel_cd IN (‘3rd001’, ‘3rd002’, ‘3rd003’, ‘3rd004’))) AND (303: expr)) AND (30: bill_id IS NULL), 1, 0)
| common expressions:
| <slot 301> : 22: oneid IS NOT NULL
| <slot 302> : coalesce(11: member_id, ‘’)
| <slot 303> : 302: coalesce != ‘’
|
21:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| colocate: false, reason:
| equal join conjunct: 9: order_channel_cd = 289: order_channel_cd
|
|----20:EXCHANGE
|
18:Project
| <slot 8> : 8: sale_mode_name
| <slot 9> : 9: order_channel_cd
| <slot 11> : 11: member_id
| <slot 22> : 22: oneid
| <slot 25> : 25: ym
| <slot 30> : 30: bill_id
|
17:HASH JOIN
| join op: INNER JOIN (BROADCAST)
| colocate: false, reason:
| equal join conjunct: 1: store_id = 183: store_id
|
|----16:EXCHANGE
|
13:Project
| <slot 1> : 1: store_id
| <slot 8> : 8: sale_mode_name
| <slot 9> : 9: order_channel_cd
| <slot 11> : 11: member_id
| <slot 22> : 22: oneid
| <slot 25> : 25: ym
| <slot 30> : 30: bill_id
|
12:HASH JOIN
| join op: LEFT OUTER JOIN (BUCKET_SHUFFLE(S))
| colocate: false, reason:
| equal join conjunct: 1: store_id = 27: store_id
| equal join conjunct: 26: ymd = 151: ymd
| equal join conjunct: 5: bill_id = 30: bill_id
|
|----11:AGGREGATE (merge finalize)
| | group by: 27: store_id, 151: ymd, 30: bill_id
| |
| 10:EXCHANGE
|
1:EXCHANGE

PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 20
UNPARTITIONED

19:HdfsScanNode
TABLE: t99_order_channel_cd
partitions=1/1
cardinality=80
avgRowSize=2.0
numNodes=0

PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 16
UNPARTITIONED

15:Project
| <slot 183> : 183: store_id
|
14:HdfsScanNode
TABLE: dim_store_base_info_cur
NON-PARTITION PREDICATES: 197: format_cd = ‘010’
MIN/MAX PREDICATES: 299: format_cd <= ‘010’, 300: format_cd >= ‘010’
partitions=1/1
cardinality=615
avgRowSize=2.0
numNodes=0

PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 152: card_id

STREAM DATA SINK
EXCHANGE ID: 10
HASH_PARTITIONED: 27: store_id, 151: ymd, 30: bill_id

9:AGGREGATE (update serialize)
| STREAMING
| group by: 27: store_id, 151: ymd, 30: bill_id
|
8:Project
| <slot 27> : 27: store_id
| <slot 30> : 30: bill_id
| <slot 151> : 151: ymd
|
7:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| colocate: false, reason:
| equal join conjunct: 152: card_id = 32: member_card_id
|
|----6:EXCHANGE
|
3:EXCHANGE

PLAN FRAGMENT 6
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 06
HASH_PARTITIONED: 32: member_card_id

5:Project
| <slot 27> : 27: store_id
| <slot 30> : 30: bill_id
| <slot 32> : 32: member_card_id
| <slot 151> : 151: ymd
|
4:HdfsScanNode
TABLE: com_pos_sale_mas
PARTITION PREDICATES: 150: ym >= 202301, 150: ym <= 202309
partitions=273/1766
cardinality=1808762
avgRowSize=5.0
numNodes=0

PLAN FRAGMENT 7
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 03
HASH_PARTITIONED: 152: card_id

2:HdfsScanNode
TABLE: com_card_info
NON-PARTITION PREDICATES: 152: card_id IS NOT NULL
partitions=1/1
cardinality=14501755
avgRowSize=1.0
numNodes=0

PLAN FRAGMENT 8
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 01
HASH_PARTITIONED: 1: store_id, 26: ymd, 5: bill_id

0:HdfsScanNode
TABLE: com_store_oneid_sale_mas
PARTITION PREDICATES: 25: ym >= 202301, 25: ym <= 202309
partitions=273/403
cardinality=4288489
avgRowSize=8.0
numNodes=0