为了更快的定位您的问题，请提供以下信息，谢谢
【详述】使用bitmap函数与物化视图做预聚合,去重统计时bitmap_union_count函数花费时间过长
【是否存算分离】存算一体
【StarRocks版本】例如：3.4.1
【集群规模】例如：1fe+1be
【机器信息】CPU虚拟核/内存/网卡，例如：16C/64G/万兆
【联系方式】buling_charon@163.com
【附件】
–profile
Query:
Summary:
- Query ID: ce7cd30d-aa5e-11f0-98b1-aa7cbd145b2c
- Start Time: 2025-10-16 15:07:41
- End Time: 2025-10-16 15:07:50
- Total: 8s372ms
- Query Type: Query
- Query State: Finished
- StarRocks Version: 3.4.1-2f78e09
- User: root
- Default Db: db_1772467461864071168
- Sql Statement: SELECT
perform_org_code,
DATE_FORMAT(perform_time_day,’%Y-%m’) AS exam_date
,bitmap_union_count(exam_pat_cnt_by_day) AS exam_patient_cnt
FROM
ads_query_acce_observation_main
WHERE
perform_time_day BETWEEN ‘2025-01-01 00:00:00’ AND ‘2025-12-31 23:59:59’
GROUP BY
perform_org_code,
exam_date
ORDER BY
perform_org_code,
exam_date
- Variables: parallel_fragment_exec_instance_num=16,max_parallel_scan_instance_num=-1,pipeline_dop=0,enable_adaptive_sink_dop=true,enable_runtime_adaptive_dop=false,runtime_profile_report_interval=10,resource_group=default_wg
- NonDefaultSessionVariables: {“big_query_profile_threshold”:{“defaultValue”:“0s”,“actualValue”:“500ms”},“pipeline_profile_level”:{“defaultValue”:1,“actualValue”:2},“parallel_fragment_exec_instance_num”:{“defaultValue”:1,“actualValue”:16},“cboPushDownAggregateMode_v1”:{“defaultValue”:0,“actualValue”:-1},“enable_adaptive_sink_dop”:{“defaultValue”:false,“actualValue”:true},“enable_profile”:{“defaultValue”:false,“actualValue”:true}}
- Collect Profile Time: 631ms
- IsProfileAsync: true
Planner:
- – Parser[1] 0
- – Total[1] 41ms
- – Analyzer[1] 0
- – Lock[1] 0
- – AnalyzeDatabase[1] 0
- – AnalyzeTemporaryTable[1] 0
- – AnalyzeTable[1] 0
- – Transformer[1] 0
- – Optimizer[1] 31ms
- – MVPreprocess[1] 0
- – MVTextRewrite[1] 0
- – RuleBaseOptimize[1] 29ms
- – CostBaseOptimize[1] 0
- – PhysicalRewrite[1] 0
- – DynamicRewrite[1] 0
- – PlanValidate[1] 0
- – InputDependenciesChecker[1] 0
- – TypeChecker[1] 0
- – CTEUniqueChecker[1] 0
- – ColumnReuseChecker[1] 0
- – ExecPlanBuild[1] 2ms
- – Pending[1] 0
- – Prepare[1] 0
- – Deploy[1] 24ms
- – DeployLockInternalTime[1] 24ms
- – DeploySerializeConcurrencyTime[3] 1ms
- – DeployStageByStageTime[9] 0
- – DeployWaitTime[9] 21ms
- – DeployAsyncSendTime[3] 0
- DynamicApplyTuningGuides: Plan had been tuned by Plan Advisor.
Original query id:c2b2db96-aa5c-11f0-98b1-aa7cbd145b2c
Original time cost: 8047 ms
1: StreamingAggTuningGuide
Reason: AggNode 2, group by keys: [3: perform_org_code, 12: date_format] has good aggregation effect.
Advice: Use force_preaggregation mode in this streaming agg node.
- CandidateTuningGuides: PlanNode 2:
StreamingAggTuningGuide
Reason: AggNode 2, group by keys: [3: perform_org_code, 12: date_format] has good aggregation effect.
Advice: Use force_preaggregation mode in this streaming agg node.
- DeployDataSize: 76746
Reason:
Execution:
Fragment 0:
Instance ce7cd30d-aa5e-11f0-98b1-aa7cbd145b2d (host=TNetworkAddress(hostname:192.168.110.16, port:9060)):
- Address: 192.168.110.16:9060
- InstanceId: ce7cd30d-aa5e-11f0-98b1-aa7cbd145b2d
- InitialProcessDriverCount: 0
- InitialProcessMem: 8.914 GB
- InstanceAllocatedMemoryUsage: 536.648 KB
- InstanceDeallocatedMemoryUsage: 297.344 KB
- InstancePeakMemoryUsage: 239.406 KB
- JITCounter: 0
- JITTotalCostTime: 0ns
- QueryCumulativeCpuTime: 1m5s
- QueryExecutionWallTime: 7s695ms
- QueryMemoryLimit: -1.000 B
- QueryPeakMemoryUsage: 3.034 GB
- QuerySpillBytes: 0.000 B
Pipeline (id=1):
- isGroupExecution: false
- DegreeOfParallelism: 1
- TotalDegreeOfParallelism: 1
PipelineDriver (id=8):
- ActiveTime: 2.797ms
- BlockByInputEmpty: 1
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DriverTotalTime: 7s693ms
- PeakDriverQueueSize: 0
- PendingTime: 7s690ms
- InputEmptyTime: 7s690ms
- FirstInputEmptyTime: 7s689ms
- ScheduleCount: 2
- YieldByLocalWait: 0
- YieldByPreempt: 0
- YieldByTimeLimit: 0
RESULT_SINK (plan_node_id=-1):
CommonMetrics:
- IsFinalSink
- OperatorTotalTime: 2.435ms
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 0ns
- PushChunkNum: 1
- PushRowNum: 2.207K (2207)
- PushTotalTime: 2.419ms
UniqueMetrics:
- SinkType: MYSQL_PROTOCAL
- AppendChunkTime: 1.743ms
- ResultRendTime: 654.600us
- TupleConvertTime: 1.734ms
- NumSentRows: 2.207K (2207)
CHUNK_ACCUMULATE (plan_node_id=-1):
CommonMetrics:
- IsSubordinate
- OperatorTotalTime: 239.700us
- PullChunkNum: 1
- PullRowNum: 2.207K (2207)
- PullTotalTime: 470ns
- PushChunkNum: 8
- PushRowNum: 2.207K (2207)
- PushTotalTime: 236.770us
UniqueMetrics:
LOCAL_EXCHANGE_SOURCE (plan_node_id=6):
CommonMetrics:
- IsSubordinate
- OperatorTotalTime: 43.650us
- PullChunkNum: 8
- PullRowNum: 2.207K (2207)
- PullTotalTime: 7.430us
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
- RuntimeBloomFilterNum: 0
- RuntimeInFilterNum: 0
UniqueMetrics:
Pipeline (id=0):
- isGroupExecution: false
- DegreeOfParallelism: 8
- TotalDegreeOfParallelism: 8
PipelineDriver (id=0):
- ActiveTime: 313.260us
- BlockByInputEmpty: 0
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DriverTotalTime: 7s690ms
- PeakDriverQueueSize: 0
- PendingTime: 7s687ms
- InputEmptyTime: 7s687ms
- FirstInputEmptyTime: 7s687ms
- ScheduleCount: 7
- ScheduleTime: 2.462ms
- YieldByLocalWait: 6
- YieldByPreempt: 0
- YieldByTimeLimit: 0
LOCAL_EXCHANGE_SINK (plan_node_id=6):
CommonMetrics:
- IsSubordinate
- OperatorTotalTime: 14us
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 0ns
- PushChunkNum: 8
- PushRowNum: 2.207K (2207)
- PushTotalTime: 12.880us
UniqueMetrics:
- Type: Passthrough
- ShuffleNum: 1
- LocalExchangePeakMemoryUsage: 79.844 KB
GLOBAL_PARALLEL_MERGE_SOURCE (plan_node_id=6):
CommonMetrics:
- OperatorTotalTime: 216.060us
- PullChunkNum: 8
- PullRowNum: 2.207K (2207)
- PullTotalTime: 214.740us
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
UniqueMetrics:
- Limit: -1
- Offset: 0
- StreamingBatchSize: 131072
- LateMaterialization: False
- BufferUnplugCount: 0
- BytesPassThrough: 0.000 B
- BytesReceived: 13.076 KB
- ClosureBlockCount: 0
- ClosureBlockTime: 0ns
- DecompressChunkTime: 33.950us
- DeserializeChunkTime: 307.241us
- LateMaterializationMaxBufferChunkNum: 0
- OverallStageCount: 24
- 1-InitStageCount: 1
- 2-PrepareStageCount: 6
- 3-ProcessStageCount: 3
- 4-SplitChunkStageCount: 3
- 5-FetchChunkStageCount: 10
- 6-PendingStageCount: 0
- 7-FinishedStageCount: 1
- OverallStageTime: 192.080us
- 1-InitStageTime: 39.740us
- 2-PrepareStageTime: 43us
- 3-ProcessStageTime: 81.890us
- LateMaterializationGenerateOrdinalTime: 0ns
- SortedRunProviderTime: 26.510us
- 4-SplitChunkStageTime: 1.880us
- LateMaterializationRestoreAccordingToOrdinalTime: 0ns
- 5-FetchChunkStageTime: 3.050us
- 6-PendingStageTime: 0ns
- 7-FinishedStageTime: 90ns
- PeakBufferMemoryBytes: 13.076 KB
- ReceiverProcessTotalTime: 378.621us
- RequestReceived: 1
- WaitLockTime: 740ns
PipelineDriver (id=1):
- ActiveTime: 156.730us
- BlockByInputEmpty: 0
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DriverTotalTime: 7s690ms
- PeakDriverQueueSize: 3
- PendingTime: 7s688ms
- InputEmptyTime: 7s688ms
- FollowupInputEmptyTime: 7s687ms
- ScheduleCount: 5
- ScheduleTime: 2.045ms
- YieldByLocalWait: 4
- YieldByPreempt: 0
- YieldByTimeLimit: 0
LOCAL_EXCHANGE_SINK (plan_node_id=6):
CommonMetrics:
–物化视图语句
CREATE MATERIALIZED VIEW ads_query_acce_observation_main
DISTRIBUTED BY HASH(system_id) BUCKETS 1
REFRESH ASYNC START(‘2020-01-01 00:00:00’) EVERY (INTERVAL 5 MINUTE)
PARTITION BY date_trunc(‘DAY’,id_date)
PROPERTIES(“partition_refresh_number” = “7”)
AS
SELECT
id_date,
perform_org_code,
system_id,
DATE_FORMAT(perform_time,’%Y-%m-%d’) AS perform_time_day,
result_state,
bitmap_agg(patient_master_id) AS exam_pat_cnt_by_day
FROM
ods_idcas_1_0_observation_main
GROUP BY
id_date,
perform_org_code,
system_id,
perform_time,
result_state;