Why does this index make SELECT GROUP BY queries slower?

Sqlite uses one index per table in a query. In a case like yours where there are multiple possible indexes, it guesses at which one to use. You can use EXPLAIN QUERY PLAN to see which one gets picked, and PRAGMA optimize or ANALYZE on a populated table to generate statistics that give it better information to make that guess with. It can also decide not to use any existing index, and might use an AUTOMATIC index, which is a temporary one built just for the query and then deleted when it's done returning rows (This naturally takes more time than using an existing index and so only happens when sqlite thinks it'll still be faster).

With an empty table:

sqlite> EXPLAIN QUERY PLAN SELECT product, COUNT(customer) FROM transactions GROUP BY product;
QUERY PLAN
`--SCAN TABLE transactions USING INDEX product_idx
sqlite> DROP INDEX product_idx;
sqlite> EXPLAIN QUERY PLAN SELECT product, COUNT(customer) FROM transactions GROUP BY product;
QUERY PLAN
|--SCAN TABLE transactions
`--USE TEMP B-TREE FOR GROUP BY

In this case, since you group on the product column, that index is used. But it still has to read every row of each group to get the count of customer, causing a lot of disk seeking. Without any indexes, it will read the table sequentially, using a temporary data structure to build up the results. This ends up being faster (Disk reads are slow).

You can read more about how sqlite uses indexes in the query planning documentation.

The best approach here, as you've found, is to use a multi-column covering index that stores all the needed information in the index itself so the table proper never has to be consulted:

CREATE INDEX product_customer_idx ON transactions(product, customer);

OK, let go by parts:

First: why is the database getting bigger with index?

It's expected the database gets bigger when adding indexes because you are adding a new support structure (the index). In this case you have:

Note this sizes are an estimation.

Why the query is getting slower with this support structures?

Maybe a bunch of reasons that justify you empirical results lets go over some possibilities

1. There is a cache that is interfering with your metrics.

This is the first thing that came to my mind, and is the first I suggest you to discard. Maybe there is some cache between those trials, try deleting and recreating everything between each test.

In your particular case this is the reason. I test your script and if you need to move your commit just before all the inserts, and after the query.

Try:

import sqlite3, time, random, string
db = sqlite3.connect('test.db')
db.executescript("""CREATE TABLE transactions(id INTEGER PRIMARY KEY, dt INTEGER, product TEXT, customer TEXT);""")
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("INSERT INTO transactions(dt, product, customer) VALUES (?, ?, ?)", (t, product, customer))

db.commit()
t0 = time.time()
for _ in db.execute("SELECT product, COUNT(customer) FROM transactions GROUP BY product"):
    pass
print (time.time()-t0)
db.commit()

I will continue explaining some possible issues an improvements but your question was already answered.

Index suggestion

Index are a complementary structure that fits in memory and improves the query by getting first the data you need (in the order you need).

In this case what you first need is to get all different products (the group By) and then you need to count the different customers for each product (the count), so in that case I would recommend to create a compose index like:

CREATE INDEX product_customer_idx 
ON transactions(product, customer)

So the the database engine will not go to the table in disk, instead will load all the index in memory and with a simple scan of the index will bring your answer.

Why is this difference is no that significant in this case?

That is simple, and you can tell by looking at the table vs index size. The index as I explained before is created to avoid fit all the table in memory (If you can fit all the table in memory, you wont get much improvement) and or to get things in the right order. In this case the order doesn't matter (you are performing a full scan with the group by) and if you realize the only column that is missing in the index is [dt] (ids are always present). So the only things that you avoid to load in memory is an integer column. Try adding some long text values in a new column, I believe that will increase the difference between the table size and the index size, and also the speed difference.

In addition to the main solution

CREATE INDEX product_customer_idx ON transactions(product, customer);

which massively improves the query, here is another way using WITHOUT ROWID, which produces the same result: ~ 15 milliseconds.

The benefit of using WITHOUT ROWID is that only 1 B-tree is used (for the primary key), and not one B-tree for rowid and one B-tree for the index like with the other method. Benefit: 2 MB database size vs. 3.3 MB.

db.executescript("""CREATE TABLE transactions(id INTEGER, dt INTEGER, product TEXT, customer TEXT,
                        PRIMARY KEY(product, customer, id)) WITHOUT ROWID;""")

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("INSERT INTO transactions(id, dt, product, customer) VALUES (?, ?, ?, ?)", 
        (i, t, product, customer))