Skip to content

Instantly share code, notes, and snippets.

@den-crane
Last active April 3, 2020 09:01
  • Star 3 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save den-crane/cbc6f0777d03a00417d70018378d360d to your computer and use it in GitHub Desktop.
create table test(a Int64, b Int64, c Int64, d String)
engine=MergeTree partition by tuple() order by (a,b,c);
insert into test select 1, 0, number, toString(number) from numbers(1000000);
insert into test select 2, 2, number, toString(number) from numbers(100);
insert into test select 3, 3, number, toString(number) from numbers(1000000);
select count() from test where a=2 and c=1;
1 rows in set. Elapsed: 0.002 sec.
select count() from test prewhere a=3 and c=1;
1 rows in set. Elapsed: 0.002 sec. Processed 9.44 thousand rows, 151.10 KB (5.03 million rows/s., 80.44 MB/s.)
select count() from test where a=3;
1 rows in set. Elapsed: 0.003 sec. Processed 1.00 million rows, 8.01 MB (378.03 million rows/s., 3.02 GB/s.)
SETTINGS index_granularity = 1024
select count() from test prewhere a=3 and c=1;
1 rows in set. Elapsed: 0.002 sec. Processed 2.28 thousand rows, 36.42 KB (1.15 million rows/s., 18.43 MB/s.)
select count() from test prewhere c=1;
1 rows in set. Elapsed: 0.009 sec. Processed 3.30 thousand rows, 26.40 KB (352.25 thousand rows/s., 2.82 MB/s.)
---------
2. Now the same column, the same type (a Int64) but many different values:
insert into test select number, 0, 0, toString(number) from numbers(1000000);
select count() from test where c=1;
0 rows in set. Elapsed: 0.003 sec. Processed 1.00 million rows, 8.00 MB (334.18 million rows/s., 2.67 GB/s.)
select count() from test where a=1 and c=1;
0 rows in set. Elapsed: 0.001 sec. Processed 8.19 thousand rows, 131.07 KB (5.42 million rows/s., 86.69 MB/s.)
select count() from test where a>=10000 and c=1;
0 rows in set. Elapsed: 0.007 sec. Processed 991.81 thousand rows, 15.87 MB (149.90 million rows/s., 2.40 GB/s.)
select count() from test where a>=999000 and c=1;
0 rows in set. Elapsed: 0.001 sec. Processed 8.77 thousand rows, 140.29 KB (6.48 million rows/s., 103.70 MB/s.)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment