locate-by-bucket

clickhouse-locate-by-bucket

# 103 buckets (10007 buckets do not work).

create table foo_h(user_id String, date Date, val UInt64, h UInt16) engine=MergeTree order by (h, date);

insert into foo_h select toString(cityHash64(toString(number))) user_id, '2018-12-14', number, cityHash64(toString(number)) % 103  from numbers(50000000);
insert into foo_h select toString(cityHash64(toString(number))) user_id, '2018-12-13', number, cityHash64(toString(number)) % 103  from numbers(50000000);
insert into foo_h select toString(cityHash64(toString(number))) user_id, '2018-12-12', number, cityHash64(toString(number)) % 103  from numbers(5);

select count() from foo_h prewhere user_id = '10408321403207385874';
1 rows in set. Elapsed: 0.695 sec. Processed 100.00 million rows, 

# index is used to navigate into bucket 100m/103 =~ 1.01
select count() from foo_h prewhere user_id = '10408321403207385874' and h=10408321403207385874%103;
1 rows in set. Elapsed: 0.019 sec. Processed 1.01 million rows, 


# index is used for search by second predicate (order by ...,date).

select count() from foo_h prewhere date = '2018-12-12'
1 rows in set. Elapsed: 0.005 sec. Processed 4.09 million rows, 

select count(),h from foo_h prewhere date = '2018-12-14' group by h
103 rows in set. Elapsed: 0.087 sec. Processed 52.40 million rows, 

select count() from foo_h prewhere date = '2018-12-13'
1 rows in set. Elapsed: 0.031 sec. Processed 52.54 million rows,