Index Merges vs. Composite Indexes in Postgres and MySQL
Comparison on Clickhouse, also runs in about 30-40ms, however there&#x27;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(&#x27;b&#x27;, 1024) as one_kib,
      repeat(&#x27;b&#x27;, 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)
    ;
  
  
  &gt; 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&#x2F;s., 4.65 GB&#x2F;s.)
</code></pre>
The same table but with 1B rows instead, runs in ~1800ms<p><pre><code>  &gt; 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&#x2F;s., 8.87 GB&#x2F;s.)
</code></pre>
[1] Converted the table create and insert logic from here: <a href="https:&#x2F;&#x2F;github.com&#x2F;sirupsen&#x2F;napkin-math&#x2F;blob&#x2F;master&#x2F;newsletter&#x2F;20-compound-vs-combining-indexes&#x2F;test.rb" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;sirupsen&#x2F;napkin-math&#x2F;blob&#x2F;master&#x2F;newslett...</a>