[Question] groupUniqArrayMergeIf, when the array length becomes longer, the speed becomes slower

[lamberken]

Hi, I try to use AggregatingMergeTree to save the uniq users every day to calculate the intersection
but when the size of users becomes larger, the speed of query become slower.
minimal sample:

Simulation Data

DROP TABLE test.dayliyuniqusers;

CREATE TABLE test.dayliyuniqusers
( 
  intotime  Date,
  pageid UInt32,
  users AggregateFunction(groupUniqArray, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY (intotime)
ORDER BY intotime
SETTINGS index_granularity = 512 ;

INSERT INTO test.dayliyuniqusers (intotime, pageid, users) 
SELECT
  '2018-02-21',
  2538,
  groupUniqArrayState(number) AS users
FROM 
(
  select number from system.numbers limit 30000000
);

INSERT INTO test.dayliyuniqusers (intotime, pageid, users) 
SELECT
  '2018-02-22',
  2538,
  groupUniqArrayState(number) AS users
FROM 
(
  select number from system.numbers limit 5000000
);

Query

SELECT 
    day1, 
    day2
FROM 
(
    SELECT 
        groupUniqArrayMergeIf(users, intotime = '2018-02-21') AS aa, 
        groupUniqArrayMergeIf(users, intotime = '2018-02-22') AS bb, 
        length(arrayFilter(x -> (x >= 2), arrayEnumerateUniq(arrayConcat(aa, aa)))) AS day1, 
        length(arrayFilter(x -> (x >= 2), arrayEnumerateUniq(arrayConcat(aa, bb)))) AS day2
    FROM test.dayliyuniqusers 
) 


┌─────day1─┬────day2─┐
│ 30000000 │ 5000000 │
└──────────┴─────────┘

1 rows in set. Elapsed: 11.073 sec. 

↙ Progress: 2.00 rows, 8.21 KB (1.42 rows/s., 5.84 KB/s.) ▏

Question

How to optimizate the query, thank you

[chhaya-sharma]

Hi @lamber-ken,
Have you able to solve this issue? I am facing the same issue. Please revert.