projections.sql

projections.sql

create table z(Browser String, Country UInt8, D Date, F Float64) Engine=MergeTree order by (Browser, Country, D);

insert into z  select toString(number%9999), number%11111, today()-7, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-6, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-5, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-4, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-3, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-2, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-1, 1 from numbers(100000000);

alter table z add projection pp (select Browser,Country, count(), sum(F)  group by Browser,Country);
alter table z materialize projection pp settings mutations_sync=2;

select sum(F), Browser from z group by Browser format Null settings allow_experimental_projection_optimization=0;
0 rows in set. Elapsed: 1.451 sec. Processed 700.00 million rows, 14.62 GB (482.39 million rows/s., 10.08 GB/s.)

select sum(F), Browser from z group by Browser format Null settings allow_experimental_projection_optimization=1;
0 rows in set. Elapsed: 0.020 sec. Processed 5.12 million rows, 170.42 MB (258.17 million rows/s., 8.59 GB/s.)



select sum(F), Browser, Country from z where Browser = '42' group by Browser, Country format Null settings allow_experimental_projection_optimization=0;
0 rows in set. Elapsed: 0.005 sec. Processed 81.92 thousand rows, 1.66 MB (17.99 million rows/s., 364.76 MB/s.)

select sum(F), Browser, Country from z where Browser = '42' group by Browser, Country format Null settings allow_experimental_projection_optimization=1;
0 rows in set. Elapsed: 0.002 sec. Processed 16.38 thousand rows, 611.84 KB (6.86 million rows/s., 256.27 MB/s.)