Slow performance for bitmap functions #6880
Description
cdp_tags is a table where bitmap intermediate state is stored as a column:
CREATE TABLE cdp_tags ( \
tag_id String, \
mid_seqs AggregateFunction(groupBitmap, UInt32), \
cardinality UInt32 \
) engine=ReplacingMergeTree() \
ORDER BY (tag_id) SETTINGS index_granularity=1;
There are altogether over 3000 records over this table:
SELECT count(*)
FROM cdp_tags
┌─count()─┐
│ 3752 │
└─────────┘
1 rows in set. Elapsed: 0.012 sec. Processed 3.75 thousand rows, 15.01 KB (315.92 thousand rows/s., 1.26 MB/s.)
index_granularity is set to 1 because each record within this table is pretty large, without this config by default, the time elapsed to select single record requires 4.5s, as show below:
SELECT count(*)
FROM
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'first_buy_market_NA'
)
┌─count()─┐
│ 1 │
└─────────┘
1 rows in set. Elapsed: 4.514 sec.
While after index_granularity is set to 1, the time is reduced to trivial:
SELECT count(*)
FROM
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'first_buy_market_NA'
)
┌─count()─┐
│ 1 │
└─────────┘
1 rows in set. Elapsed: 0.161 sec.
Let us see the performance of bitmap functions, the first SQL contains single subquery:
SELECT bitmapCardinality(
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'first_buy_market_NA'
))
┌─bitmapCardinality(_subquery9)─┐
│ 6440561 │
└───────────────────────────────┘
1 rows in set. Elapsed: 1.291 sec.
The second SQL contains two subqueries:
SELECT bitmapAnd(
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'first_buy_market_NA'
),
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'first_buy_province_广东省'
))
┌─bitmapAnd(_subquery10, _subquery11)─┐
│ │
└─────────────────────────────────────┘
1 rows in set. Elapsed: 3.324 sec.
The third SQL contains four subqueries:
SELECT bitmapAndCardinality(bitmapAnd(bitmapAnd(
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'first_buy_market_NA'
),
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'first_buy_province_广东省'
)),
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'member_origin_channel_NA'
)),
(
SELECT mid_seqs
FROM cdp_tags
WHERE tag_id = 'mobile_province_NA'
))
┌─bitmapAndCardinality(bitmapAnd(bitmapAnd(_subquery12, _subquery13), _subquery14), _subquery15)─┐
│ 0 │
└────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 19.888 sec.
We could see that the more number of subqueries, the larger time required---but much larger than linearly increased according to the amount of subqueries.
We think the major query overhead happens during the SQL pipeline itself, because if we execute following query which also contains 4 bitmaps, it's much faster:
SELECT groupBitmapMerge(mid_seqs)
FROM cdp_tags
WHERE has(['first_buy_market_NA', 'first_buy_province_广东省', 'member_origin_channel_NA', 'mobile_province_NA'], tag_id)
┌─groupBitmapMerge(mid_seqs)─┐
│ 48850537 │
└────────────────────────────┘
1 rows in set. Elapsed: 1.826 sec. Processed 3.75 thousand rows, 269.21 KB (2.05 thousand rows/s., 147.41 KB/s.)
Additionally, the flag of SET experimental_use_processors = 1 does not help, it could even increase the overall query time for the above situations.
What's the reason for the performance issue? And how could I improve the query performance such that the overall time is increased just linearly according to the number of bitmap records?
The version of Clickhouse used: v19.13.2.19-stable