I read that bitmap_union_count is slightly faster and memory efficient than count(distinct), but bitmap_to_array seems not
select array_agg(distinct column1) as columns1 from table1
3 seconds
select bitmap_to_array(bitmap_union(to_bitmap(column1))) as columns1 from table1
17 seconds
3.2.2-269e832
It depends. What does the query plan show?
explain analyze select
bitmap_to_array(bitmap_union(to_bitmap(warehouse_external_id))) as warehouse_external_ids
from v1_product_histories
where created_at >= ‘2024-01-01’ and created_at <= ‘2024-01-04’
limit 200
e[0mSummarye[0m
e[0mQueryId: cf24fec8-ac85-11ee-8dae-5a0c1e8c4c09e[0m
e[0mVersion: 3.2.2-269e832e[0m
e[0mState: Finishede[0m
e[0mTotalTime: 5s254mse[0m
e[0mExecutionTime: 5s224ms [Scan: 542.397ms (10.38%), Network: 10.033ms (0.19%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 10.490ms (0.20%)]e[0m
e[0mCollectProfileTime: 15mse[0m
e[0mFrontendProfileMergeTime: 2.569mse[0m
e[0mQueryPeakMemoryUsage: 8.881 MB, QueryAllocatedMemoryUsage: 15.977 GBe[0m
e[0mTop Most Time-consuming Nodes:e[0m
e[1me[31m1. AGGREGATION (id=2) [serialize, update]: 4s925ms (87.55%)e[0m
e[0m2. OLAP_SCAN (id=0) : 649.200ms (11.54%)e[0m
e[0m3. PROJECT (id=1) : 33.760ms (0.60%)e[0m
e[0m4. EXCHANGE (id=3) [GATHER]: 10.541ms (0.19%)e[0m
e[0m5. PROJECT (id=5) : 6.369ms (0.11%)e[0m
e[0m6. AGGREGATION (id=4) [finalize, merge]: 336.033us (0.01%)e[0m
e[0m7. RESULT_SINK: 271.873us (0.00%)e[0m
e[0mTop Most Memory-consuming Nodes:e[0m
e[0m1. OLAP_SCAN (id=0) : 716.293 MBe[0m
e[0m2. AGGREGATION (id=2) [serialize, update]: 164.141 KBe[0m
e[0m3. EXCHANGE (id=3) [GATHER]: 8.055 KBe[0m
e[0m4. AGGREGATION (id=4) [finalize, merge]: 4.180 KBe[0m
e[0mNonDefaultVariables:e[0m
e[0mcharacter_set_results: utf8 → NULLe[0m
e[0menable_adaptive_sink_dop: false → truee[0m
e[0menable_async_profile: true → falsee[0m
e[0menable_connector_adaptive_io_tasks: false → truee[0m
e[0menable_iceberg_column_statistics: false → truee[0m
e[0menable_profile: false → truee[0m
e[0menable_query_cache: false → truee[0m
e[0menable_sort_aggregate: false → truee[0m
e[0menable_spill: false → truee[0m
e[0mspill_operator_min_bytes: 52428800 → 10485760e[0m
e[0msql_mode_v2: 32 → 2097184e[0m
e[0msql_select_limit: 9223372036854775807 → 200e[0m
e[0mFragment 0e[0m
│ e[0mBackendNum: 1e[0m
│ e[0mInstancePeakMemoryUsage: 18.203 KB, InstanceAllocatedMemoryUsage: 50.844 KBe[0m
│ e[0mPrepareTime: 356.165use[0m
└──e[0mRESULT_SINKe[0m
│ e[0mTotalTime: 271.873us (0.00%) [CPUTime: 271.873us]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mSinkType: MYSQL_PROTOCALe[0m
└──e[0mPROJECT (id=5) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 6.369ms (0.11%) [CPUTime: 6.369ms]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mExpression: [bitmap_to_array(21: bitmap_union)]e[0m
└──e[0mAGGREGATION (id=4) [finalize, merge]e[0m
│ e[0mEstimates: [row: 200, cpu: ?, memory: ?, network: ?, cost: 1.2115337963104E13]e[0m
│ e[0mTotalTime: 336.033us (0.01%) [CPUTime: 336.033us]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mPeakMemory: 4.180 KB, AllocatedMemory: 7.133 KBe[0m
│ e[0mAggExprs: [bitmap_union(21: bitmap_union)]e[0m
│ e[0mSubordinateOperators: e[0m
│ e[0mLOCAL_EXCHANGE [Passthrough]e[0m
└──e[0mEXCHANGE (id=3) [GATHER]e[0m
e[0mEstimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 1.2115335341632E13]e[0m
e[0mTotalTime: 10.541ms (0.19%) [CPUTime: 507.626us, NetworkTime: 10.033ms]e[0m
e[0mOutputRows: 4e[0m
e[0mPeakMemory: 8.055 KB, AllocatedMemory: 46.750 KBe[0m
e[0m
e[0mFragment 1e[0m
│ e[0mBackendNum: 2e[0m
│ e[0mInstancePeakMemoryUsage: 8.806 MB, InstanceAllocatedMemoryUsage: 15.977 GBe[0m
│ e[0mPrepareTime: 420.224use[0m
└──e[0mDATA_STREAM_SINK (id=3)e[0m
│ e[0mPartitionType: UNPARTITIONEDe[0m
└──e[1me[31mAGGREGATION (id=2) [serialize, update]e[0m
│ e[1me[31mEstimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 1.211533324448E13]e[0m
│ e[1me[31mTotalTime: 4s925ms (87.55%) [CPUTime: 4s925ms]e[0m
│ e[1me[31mOutputRows: 4e[0m
│ e[1me[31mPeakMemory: 164.141 KB, AllocatedMemory: 13.233 GBe[0m
│ e[1me[31mAggExprs: [bitmap_union(to_bitmap(3: warehouse_external_id))]e[0m
│ e[1me[31mDetail Timers: e[0m
│ e[1me[31mAggComputeTime: 4s300ms [min=3s812ms, max=4s784ms]e[0m
│ e[1me[31mAggFuncComputeTime: 4s296ms [min=3s808ms, max=4s779ms]e[0m
│ e[1me[31mExprComputeTime: 1s367ms [min=1s218ms, max=1s574ms]e[0m
└──e[0mPROJECT (id=1) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 33.760ms (0.60%) [CPUTime: 33.760ms]e[0m
│ e[0mOutputRows: 346.026M (346025835)e[0m
│ e[0mExpression: [3: warehouse_external_id]e[0m
└──e[0mOLAP_SCAN (id=0) e[0m
e[0mEstimates: [row: 11553992, cpu: 12115331147328.00, memory: 0.00, network: 0.00, cost: 6057665573664.00]e[0m
e[0mTotalTime: 649.200ms (11.54%) [CPUTime: 106.802ms, ScanTime: 542.397ms]e[0m
e[0mOutputRows: 346.026M (346025835)e[0m
e[0mPeakMemory: 716.293 MB, AllocatedMemory: 2.699 GBe[0m
e[0mTable: : v1_product_historiese[0m
e[0m
explain analyze select
array_agg(distinct warehouse_external_id) as warehouse_external_ids
from v1_product_histories
where created_at >= ‘2024-01-01’ and created_at <= ‘2024-01-04’
limit 200
e[0mSummarye[0m
e[0mQueryId: ed83ce61-ac85-11ee-8dae-5a0c1e8c4c09e[0m
e[0mVersion: 3.2.2-269e832e[0m
e[0mState: Finishede[0m
e[0mTotalTime: 719mse[0m
e[0mExecutionTime: 673.271ms [Scan: 450.981ms (66.98%), Network: 23.268ms (3.46%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 9.888ms (1.47%)]e[0m
e[0mCollectProfileTime: 23mse[0m
e[0mFrontendProfileMergeTime: 4.860mse[0m
e[0mQueryPeakMemoryUsage: 8.432 MB, QueryAllocatedMemoryUsage: 2.745 GBe[0m
e[0mTop Most Time-consuming Nodes:e[0m
e[1me[31m1. OLAP_SCAN (id=0) : 530.982ms (53.71%)e[0m
e[1me[31m2. AGGREGATION (id=2) [serialize, update]: 408.136ms (41.28%)e[0m
e[0m3. PROJECT (id=1) : 21.229ms (2.15%)e[0m
e[0m4. EXCHANGE (id=6) [GATHER]: 18.547ms (1.88%)e[0m
e[0m5. EXCHANGE (id=3) [SHUFFLE]: 5.546ms (0.56%)e[0m
e[0m6. AGGREGATION (id=5) [serialize, update]: 2.385ms (0.24%)e[0m
e[0m7. AGGREGATION (id=7) [finalize, merge]: 1.580ms (0.16%)e[0m
e[0m8. RESULT_SINK: 135.991us (0.01%)e[0m
e[0m9. AGGREGATION (id=4) [merge, serialize]: 92.654us (0.01%)e[0m
e[0mTop Most Memory-consuming Nodes:e[0m
e[0m1. OLAP_SCAN (id=0) : 716.266 MBe[0m
e[0m2. EXCHANGE (id=3) [SHUFFLE]: 21.117 KBe[0m
e[0m3. AGGREGATION (id=2) [serialize, update]: 20.008 KBe[0m
e[0m4. AGGREGATION (id=4) [merge, serialize]: 19.570 KBe[0m
e[0m5. EXCHANGE (id=6) [GATHER]: 8.609 KBe[0m
e[0m6. AGGREGATION (id=5) [serialize, update]: 4.742 KBe[0m
e[0m7. AGGREGATION (id=7) [finalize, merge]: 4.711 KBe[0m
e[0mNonDefaultVariables:e[0m
e[0mcharacter_set_results: utf8 → NULLe[0m
e[0menable_adaptive_sink_dop: false → truee[0m
e[0menable_async_profile: true → falsee[0m
e[0menable_connector_adaptive_io_tasks: false → truee[0m
e[0menable_iceberg_column_statistics: false → truee[0m
e[0menable_profile: false → truee[0m
e[0menable_query_cache: false → truee[0m
e[0menable_sort_aggregate: false → truee[0m
e[0menable_spill: false → truee[0m
e[0mspill_operator_min_bytes: 52428800 → 10485760e[0m
e[0msql_mode_v2: 32 → 2097184e[0m
e[0msql_select_limit: 9223372036854775807 → 200e[0m
e[0mFragment 0e[0m
│ e[0mBackendNum: 1e[0m
│ e[0mInstancePeakMemoryUsage: 19.555 KB, InstanceAllocatedMemoryUsage: 54.008 KBe[0m
│ e[0mPrepareTime: 385.516use[0m
└──e[0mRESULT_SINKe[0m
│ e[0mTotalTime: 135.991us (0.01%) [CPUTime: 135.991us]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mSinkType: MYSQL_PROTOCALe[0m
└──e[0mAGGREGATION (id=7) [finalize, merge]e[0m
│ e[0mEstimates: [row: 200, cpu: ?, memory: ?, network: ?, cost: 9.2440328E7]e[0m
│ e[0mTotalTime: 1.580ms (0.16%) [CPUTime: 1.580ms]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mPeakMemory: 4.711 KB, AllocatedMemory: 9.852 KBe[0m
│ e[0mAggExprs: [array_agg(20: array_agg)]e[0m
│ e[0mSubordinateOperators: e[0m
│ e[0mLOCAL_EXCHANGE [Passthrough]e[0m
└──e[0mEXCHANGE (id=6) [GATHER]e[0m
e[0mEstimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 9.2440288E7]e[0m
e[0mTotalTime: 18.547ms (1.88%) [CPUTime: 349.196us, NetworkTime: 18.197ms]e[0m
e[0mOutputRows: 6e[0m
e[0mPeakMemory: 8.609 KB, AllocatedMemory: 47.813 KBe[0m
e[0m
e[0mFragment 1e[0m
│ e[0mBackendNum: 2e[0m
│ e[0mInstancePeakMemoryUsage: 45.504 KB, InstanceAllocatedMemoryUsage: 348.156 KBe[0m
│ e[0mPrepareTime: 591.607use[0m
└──e[0mDATA_STREAM_SINK (id=6)e[0m
│ e[0mPartitionType: UNPARTITIONEDe[0m
└──e[0mAGGREGATION (id=5) [serialize, update]e[0m
│ e[0mEstimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 9.2440256E7]e[0m
│ e[0mTotalTime: 2.385ms (0.24%) [CPUTime: 2.385ms]e[0m
│ e[0mOutputRows: 6e[0m
│ e[0mPeakMemory: 4.742 KB, AllocatedMemory: 37.141 KBe[0m
│ e[0mAggExprs: [array_agg(3: warehouse_external_id)]e[0m
└──e[0mAGGREGATION (id=4) [merge, serialize]e[0m
│ e[0mEstimates: [row: 296, cpu: 1184.00, memory: 1184.00, network: 0.00, cost: 92439632.00]e[0m
│ e[0mTotalTime: 92.654us (0.01%) [CPUTime: 92.654us]e[0m
│ e[0mOutputRows: 253e[0m
│ e[0mPeakMemory: 19.570 KB, AllocatedMemory: 130.469 KBe[0m
│ e[0mGroupingExprs: [3: warehouse_external_id]e[0m
│ e[0mSubordinateOperators: e[0m
│ e[0mNOOPe[0m
│ e[0mSPILL_PROCESSe[0m
└──e[0mEXCHANGE (id=3) [SHUFFLE]e[0m
e[0mEstimates: [row: 296, cpu: 1184.00, memory: 0.00, network: 1184.00, cost: 92436672.00]e[0m
e[0mTotalTime: 5.546ms (0.56%) [CPUTime: 475.997us, NetworkTime: 5.070ms]e[0m
e[0mOutputRows: 977e[0m
e[0mPeakMemory: 21.117 KB, AllocatedMemory: 119.219 KBe[0m
e[0m
e[0mFragment 2e[0m
│ e[0mBackendNum: 2e[0m
│ e[0mInstancePeakMemoryUsage: 8.337 MB, InstanceAllocatedMemoryUsage: 2.745 GBe[0m
│ e[0mPrepareTime: 354.604use[0m
└──e[0mDATA_STREAM_SINK (id=3)e[0m
│ e[0mPartitionType: HASH_PARTITIONEDe[0m
│ e[0mPartitionExprs: [3: warehouse_external_id]e[0m
└──e[1me[31mAGGREGATION (id=2) [serialize, update]e[0m
│ e[1me[31mEstimates: [row: 296, cpu: 92431936.00, memory: 1184.00, network: 0.00, cost: 92434304.00]e[0m
│ e[1me[31mTotalTime: 408.136ms (41.28%) [CPUTime: 408.136ms]e[0m
│ e[1me[31mOutputRows: 977e[0m
│ e[1me[31mPeakMemory: 20.008 KB, AllocatedMemory: 111.250 KBe[0m
│ e[1me[31mGroupingExprs: [3: warehouse_external_id]e[0m
│ e[1me[31mDetail Timers: e[0m
│ e[1me[31mAggComputeTime: 337.481ms [min=315.703ms, max=371.353ms]e[0m
└──e[0mPROJECT (id=1) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 21.229ms (2.15%) [CPUTime: 21.229ms]e[0m
│ e[0mOutputRows: 346.026M (346025835)e[0m
│ e[0mExpression: [3: warehouse_external_id]e[0m
└──e[1me[31mOLAP_SCAN (id=0) e[0m
e[1me[31mEstimates: [row: 11553992, cpu: 92431936.00, memory: 0.00, network: 0.00, cost: 46215968.00]e[0m
e[1me[31mTotalTime: 530.982ms (53.71%) [CPUTime: 80ms, ScanTime: 450.981ms]e[0m
e[1me[31mOutputRows: 346.026M (346025835)e[0m
e[1me[31mPeakMemory: 716.266 MB, AllocatedMemory: 2.699 GBe[0m
e[1me[31mTable: : v1_product_historiese[0m
e[1me[31mDetail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]e[0m
e[1me[31mIOTaskExecTime: 378.001ms [min=340.674ms, max=421.773ms]e[0m
e[1me[31mSegmentRead: 225.366ms [min=208.389ms, max=244.946ms]e[0m
e[1me[31mBlockFetch: 218.394ms [min=202.050ms, max=237.024ms]e[0m
e[1me[31mIOTaskWaitTime: 29.252ms [min=26.167ms, max=31.740ms]e[0m
e[0m
PLAN FRAGMENT 0
OUTPUT EXPRS:22: bitmap_to_array
PARTITION: UNPARTITIONED
RESULT SINK
5:Project
| <slot 22> : bitmap_to_array(21: bitmap_union)
| limit: 200
|
4:AGGREGATE (merge finalize)
| output: bitmap_union(21: bitmap_union)
| group by:
| limit: 200
|
3:EXCHANGE
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 03
UNPARTITIONED
2:AGGREGATE (update serialize)
| output: bitmap_union(to_bitmap(3: warehouse_external_id))
| group by:
|
1:Project
| <slot 3> : 3: warehouse_external_id
|
0:OlapScanNode
TABLE: v1_product_histories
PREAGGREGATION: ON
partitions=4/43
rollup: v1_product_histories
tabletRatio=4/4
tabletList=40056,40232,42041,43047
cardinality=11553992
avgRowSize=1048584.0
PLAN FRAGMENT 0
OUTPUT EXPRS:20: array_agg
PARTITION: UNPARTITIONED
RESULT SINK
7:AGGREGATE (merge finalize)
| output: array_agg(20: array_agg)
| group by:
| limit: 200
|
6:EXCHANGE
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 3: warehouse_external_id
STREAM DATA SINK
EXCHANGE ID: 06
UNPARTITIONED
5:AGGREGATE (update serialize)
| output: array_agg(3: warehouse_external_id)
| group by:
|
4:AGGREGATE (merge serialize)
| group by: 3: warehouse_external_id
|
3:EXCHANGE
PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 03
HASH_PARTITIONED: 3: warehouse_external_id
2:AGGREGATE (update serialize)
| STREAMING
| group by: 3: warehouse_external_id
|
1:Project
| <slot 3> : 3: warehouse_external_id
|
0:OlapScanNode
TABLE: v1_product_histories
PREAGGREGATION: ON
partitions=4/43
rollup: v1_product_histories
tabletRatio=4/4
tabletList=40056,40232,42041,43047
cardinality=11553992
avgRowSize=8.0
Is says that AggComputeTime occupies most of query processing time (4+s). With array_agg(distinct) it takes 300±ms
did you see this? Use Bitmap for exact Count Distinct | StarRocks
Yes
Does bitmap_to_array also require bitmap column type and aggregate table with bitmap_union to get performance boost?
yes. the column helps with computation instead of doing it on the fly.
use bitmap_agg instead of bitmap_union(to_bitmap(xxx)) will make the sql faster
3.2.3 optimize the performance of bitmap_to_array: [Enhancement] Optimize the performance of bitmap_to_array by trueeyu · Pull Request #37114 · StarRocks/starrocks · GitHub
3.2.3 will auto convert bitmap_union(to_bitmap(xxx)) to bitmap_agg(xxx): [Enhancement] Convert bitmap_union(to_bitmap(int type) to bitmap_agg by LiShuMing · Pull Request #23656 · StarRocks/starrocks · GitHub