CH AggregatingMergeTree uniqState uniqMerge

uniqState_uniqMerge

drop table z; 
drop table mvz;
create table z(d Date, u String) Engine=MergeTree partition by tuple() order by tuple();
CREATE MATERIALIZED VIEW mvz ENGINE = AggregatingMergeTree(d, (d), 8192) as select d, uniqState(u) as us from z group by d
insert into z select today()-number%571, concat('usr',toString(rand()%664579)) from numbers(100000000);
optimize table mvz final;
optimize table z final;

select  (uniqMerge(us)) as unique from mvz group by d order by d;
571 rows in set. Elapsed: 0.300 sec.

select (uniq(u)) as unique from z group by d order by d;
571 rows in set. Elapsed: 5.751 sec. Processed 100.00 million rows, 1.98 GB (17.39 million rows/s., 344.87 MB/s



insert into z select today()-number%571, concat('usr',toString(rand()%103)) from numbers(100000000);

select  (uniqMerge(us)) as unique from mvz group by d order by d;
571 rows in set. Elapsed: 0.005 sec.

select (uniq(u)) as unique from z group by d order by d;
571 rows in set. Elapsed: 1.061 sec. Processed 100.00 million rows, 1.59 GB (94.26 million rows/s., 1.50 GB/s.)

---------------------------


create table z(d Date, z String, u String) Engine=MergeTree partition by tuple() order by tuple();
CREATE MATERIALIZED VIEW mvz ENGINE = AggregatingMergeTree order by (z,d)  
as select d, z,uniqState(u) as us from z group by z,d;

insert into z select today()-1, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);
insert into z select today()-2, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);
insert into z select today()-3, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);

select  (uniqMerge(us)) as unique from mvz prewhere z='555' group by d order by d;
3 rows in set. Elapsed: 2.030 sec. Processed 10.00 thousand rows, 1.40 MB (4.93 thousand rows/s., 688.22 KB/s.)


VS

CREATE MATERIALIZED VIEW mvz ENGINE = AggregatingMergeTree order by (z,d) settings index_granularity = 8
as select d, z,uniqState(u) as us from z group by z,d;
select  (uniqMerge(us)) as unique from mvz prewhere z='555' group by d order by d;
3 rows in set. Elapsed: 0.026 sec.