Comparison on Clickhouse, also runs in about 30-40ms, however there's no indexing being used and this is a full-table scan.<p><pre><code> create table if not exists test_table
(
id UInt64,
text1 String,
text2 String,
int1000 UInt64,
int100 UInt64,
int10 UInt64,
int10_2 UInt64
)
engine = MergeTree()
order by (id)
;
insert into test_table
with
repeat('b', 1024) as one_kib,
repeat('b', 255) as bytes_255
select
number as id,
one_kib,
bytes_255,
rand() % 1000 as int1000,
rand() % 100 as int100,
rand() % 10 as int10,
rand() % 10 as int10_2
from numbers(10e6)
;
> select count(*) from test_table where int1000 = 1 and int100 = 1;
┌─count()─┐
│ 9949 │
└─────────┘
1 row in set. Elapsed: 0.034 sec. Processed 10.00 million rows, 160.00 MB (290.93 million rows/s., 4.65 GB/s.)
</code></pre>
The same table but with 1B rows instead, runs in ~1800ms<p><pre><code> > select count(*) from test_table where int1000 = 1 and int100 = 1;
┌─count()─┐
│ 999831 │
└─────────┘
1 row in set. Elapsed: 1.804 sec. Processed 1.00 billion rows, 16.00 GB (554.24 million rows/s., 8.87 GB/s.)
</code></pre>
[1] Converted the table create and insert logic from here: <a href="https://github.com/sirupsen/napkin-math/blob/master/newsletter/20-compound-vs-combining-indexes/test.rb" rel="nofollow">https://github.com/sirupsen/napkin-math/blob/master/newslett...</a>