【详述】有一个慢sql, 是查hive外表, 数据量不大全表23w数据,但是查询总是>5s; 看 profile 就 InputEmptyTime 值比较大, 不清楚这个值的含义是什么, 是这个导致的慢吗
【背景】做过哪些操作？
【业务影响】
【StarRocks版本】例如：2.4.0
【集群规模】3fe（1 follower+2observer）+ 3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【附件】
- Profile信息
query_profile.txt (27.5 KB) - 并行度：1
- pipeline是否开启：
enable_pipeline_engine,true
enable_pipeline_query_statistic,true
pipeline_dop,0
pipeline_profile_level,1 - 执行计划：explain costs + sql
sql:
explain costs select vin,veh_status,usage_type,hoodsts,val_start_time,val_end_time,xcuvehmd from ilad_sig.tmp_signal_vehicle_value_v1 where vin=‘LW433B121N1046855’ and val_start_time <= ‘1671212528’ and val_end_time>=‘1671212528’ group by vin,veh_status,usage_type,hoodsts,val_start_time,val_end_time,xcuvehmd;
PLAN FRAGMENT 0(F02)
Output Exprs:2: vin | 3: veh_status | 4: usage_type | 5: hoodsts | 6: val_start_time | 7: val_end_time | 8: xcuvehmd
Input Partition: UNPARTITIONED
RESULT SINK
""
4:EXCHANGE
cardinality: 6659
""
PLAN FRAGMENT 1(F01)
""
" Input Partition: HASH_PARTITIONED: 2: vin, 3: veh_status, 4: usage_type, 5: hoodsts, 6: val_start_time, 7: val_end_time, 8: xcuvehmd"
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 04
""
3:AGGREGATE (merge finalize)
" | group by: [2: vin, VARCHAR, true], [3: veh_status, VARCHAR, true], [4: usage_type, VARCHAR, true], [5: hoodsts, VARCHAR, true], [6: val_start_time, VARCHAR, true], [7: val_end_time, VARCHAR, true], [8: xcuvehmd, VARCHAR, true]"
| cardinality: 6659
| column statistics:
" | * vin-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * veh_status-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * usage_type-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * hoodsts-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * val_start_time-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * val_end_time-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * xcuvehmd-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
|
2:EXCHANGE
cardinality: 9938
""
PLAN FRAGMENT 2(F00)
""
Input Partition: RANDOM
" OutPut Partition: HASH_PARTITIONED: 2: vin, 3: veh_status, 4: usage_type, 5: hoodsts, 6: val_start_time, 7: val_end_time, 8: xcuvehmd"
OutPut Exchange Id: 02
""
1:AGGREGATE (update serialize)
| STREAMING
" | group by: [2: vin, VARCHAR, true], [3: veh_status, VARCHAR, true], [4: usage_type, VARCHAR, true], [5: hoodsts, VARCHAR, true], [6: val_start_time, VARCHAR, true], [7: val_end_time, VARCHAR, true], [8: xcuvehmd, VARCHAR, true]"
| cardinality: 9938
| column statistics:
" | * vin-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * veh_status-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * usage_type-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * hoodsts-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * val_start_time-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * val_end_time-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" | * xcuvehmd-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
|
0:HdfsScanNode
TABLE: tmp_signal_vehicle_value_v1
" NON-PARTITION PREDICATES: 2: vin = 'LW433B121N1046855', 6: val_start_time <= '1671212528', 7: val_end_time >= '1671212528'"
partitions=173/173
avgRowSize=7.0
numNodes=0
cardinality: 14831
column statistics:
" * vin-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" * veh_status-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" * usage_type-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" * hoodsts-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" * val_start_time-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" * val_end_time-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
" * xcuvehmd-->[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN"
- be节点cpu和内存使用率截图