Clickhouse PK (sortkey)

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.)