Considering simple table and a query

DROP TABLE IF EXISTS orders;
CREATE TABLE orders ( time INT, client TEXT, item TEXT );
CREATE INDEX wrong_index ON orders ( client, time );
CREATE INDEX good_index ON orders ( time, client );
EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE time > 10 GROUP BY client;

outputs: --SCAN orders USING COVERING INDEX wrong_index

Why? Isn't it optimal to use good_index and speed up query dramatically? In my case a more complex table has many million records and it is extremely slow to query orders made on a particular day or an hour.

good_index is not helpful for this query. GROUP BY client part needs rows with the same client next to each other, but in good_index they aren't.

Try running ANALYZE on your actual database. It's possible that the optimizer will then be able to determine that time > 10 condition selects a small subset of records, and it's worth using the index for that part and then sort the resulting records for the GROUP BY part.

Or, you can make it GROUP BY +client. The unary plus is a no-op but will suppress the use of any index for the GROUP BY. Chances are then high that good_index will be selected to satisfy time > 10 condition.

Hm, running ANALIZE changed the behavior from SCAN to SEARCH using the same wrong index but queries become much faster. I am now curious what magic info did it gather. But anyway thanks for the good hint!