title: Why does this index make SELECT GROUP BY queries slower?
body: <p>Let's say we have a table of 100k transactions (each row is a purchase of <code>product</code> by <code>customer</code> at timestamp <code>dt</code>).</p>
  <p>I have noticed that the query</p>
  <pre><code>SELECT product, COUNT(customer) FROM transactions GROUP BY product
  </code></pre>
  <p>is strangely faster when there is <em>no index</em>:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th>DB size</th>
  <th>SELECT query time</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>no index</td>
  <td>1.8 MB</td>
  <td>63 ms</td>
  </tr>
  <tr>
  <td>index on <code>product</code></td>
  <td>3 MB</td>
  <td>220 ms</td>
  </tr>
  <tr>
  <td>index on <code>product</code> + index on <code>customer</code></td>
  <td>4.1 MB</td>
  <td>292 ms</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Why do indexes make the queries slower in this case?</strong> (and also the database bigger, so it's a big no!)</p>
  <p>Reproducible code:</p>
  <pre class="lang-python prettyprint-override"><code>import sqlite3, time, random, string
  db = sqlite3.connect('test.db')
  db.executescript(&quot;&quot;&quot;CREATE TABLE transactions(id INTEGER PRIMARY KEY, dt INTEGER, product TEXT, customer TEXT);
                      CREATE INDEX product_idx ON transactions(product);
                      CREATE INDEX customer_idx ON transactions(customer);&quot;&quot;&quot;)
  for i in range(100*1000):
      t = random.randint(1600000000, 1600010000)                       # random datetime
      product = ''.join(random.choices(string.ascii_uppercase, k=2))   # random product among 676 products
      customer = ''.join(random.choices(string.ascii_uppercase, k=2))  # random customer among 676 customers
      db.execute(&quot;INSERT INTO transactions(dt, product, customer) VALUES (?, ?, ?)&quot;, (t, product, customer))
  
  t0 = time.time()
  for _ in db.execute(&quot;SELECT product, COUNT(customer) FROM transactions GROUP BY product&quot;):
      pass
  print (time.time()-t0)
  db.commit()
  </code></pre>