order by (metric, time) VS order by (time, metric)

metrics_metrics1

create table metrics (metric UInt32, val Float64, time DateTime) engine = MergeTree() 
partition by toYYYYMMDD(time)
order by (metric, time)

insert into metrics select number%4999, rand(), (toDateTime(today())+number%2880*30)   from numbers(100000000);
insert into metrics select number%4999, rand(), (toDateTime(today()-1)+number%2880*30) from numbers(100000000);
insert into metrics select number%4999, rand(), (toDateTime(today()-2)+number%2880*30) from numbers(100000000);
insert into metrics select number%4999, rand(), (toDateTime(today()-3)+number%2880*30) from numbers(100000000);
insert into metrics select number%4999, rand(), (toDateTime(today()-4)+number%2880*30) from numbers(100000000);

create table metrics1 (metric UInt32, val Float64, time DateTime) engine = MergeTree()
partition by toYYYYMMDD(time)
order by (time, metric);
insert into metrics1 select * from metrics;
optimize table metrics final;
optimize table metrics1 final;


select metric, toStartOfHour(time) h, avg(val) 
from metrics 
prewhere metric in (333,666,999) and time between toDateTime(today()) and toDateTime(today())+3600*3
group by metric, h
12 rows in set. Elapsed: 0.004 sec. Processed 49.15 thousand rows, 453.41 KB (13.16 million rows/s., 121.40 MB/s.)


select metric, toStartOfHour(time) h, avg(val) 
from metrics1
prewhere metric in (333,666,999) and time between toDateTime(today()) and toDateTime(today())+3600*3
group by metric, h
12 rows in set. Elapsed: 0.053 sec. Processed 5.45 million rows, 43.64 MB (102.01 million rows/s., 817.21 MB/s.)

select metric, toStartOfHour(time) h, avg(val) 
from metrics
prewhere metric in (333,666,999) 
group by metric, h
360 rows in set. Elapsed: 0.008 sec. Processed 368.64 thousand rows, 5.08 MB (46.82 million rows/s., 644.61 MB/s.)

select metric, toStartOfHour(time) h, avg(val) 
from metrics1
prewhere metric in (333,666,999) 
group by metric, h
360 rows in set. Elapsed: 1.276 sec. Processed 217.99 million rows, 875.56 MB (170.86 million rows/s., 686.26 MB/s.)

select toStartOfHour(time) h, avg(val) 
from metrics 
prewhere  time between toDateTime(today()) and toDateTime(today())+3600*3
group by h
4 rows in set. Elapsed: 0.391 sec. Processed 53.49 million rows, 314.26 MB (136.96 million rows/s., 804.58 MB/s.)

select toStartOfHour(time) h, avg(val) 
from metrics1
prewhere  time between toDateTime(today()) and toDateTime(today())+3600*3
group by h
4 rows in set. Elapsed: 0.077 sec. Processed 12.54 million rows, 150.45 MB (163.88 million rows/s., 1.97 GB/s.)