select operation takes a long time in a 160GB sqlite3 database-CodePudding

I have a large SQLite database of 160GB. There is an index on the id column and stage column of a table hwmp.

CREATE INDEX idx_hwmp_id ON hwmp (id, stage);

When I do a count of the rows, the query returns fast. Only 0.09 seconds.

sqlite> select count (*) from hwmp where id = 2000 and stage = 4;
59397
Run Time: real 0.091 user 0.000074 sys 0.080494

However, if I do a select all, the real time is very high - 85 seconds. The user and system time combined is only 2.5 seconds. So, why should the real time be high?

select * from hwmp where id = 2000 and stage = 4;
Run Time: real 85.420 user 0.801639 sys 1.754250

Looks like there is some problem in retrieving data out of SQLite. Any tips on how to fix it?

More context: I tried another query on a sqlite3 database (300MB) i was using a few days back. It used to return data in 20ms. Today, it was taking 652ms.

Run Time: real 0.652 user 0.018766 sys 0.010595

There is something wrong with the linux environment today. I downloaded the same sqlite to my Mac and it ran quickly.

Run Time: real 0.028 user 0.005990 sys 0.010420

It is using the index:

sqlite> explain query plan select * from hwmp where id = 78 and stage = 4;
QUERY PLAN
`--SEARCH hwmp USING INDEX idx_hwmp_id (id=? AND stage=?)
Run Time: real 0.005 user 0.000857 sys 0.000451

CodePudding user response：

The relevant setting is pragma cache_size = 200000; 200000 pages of 4096 bytes. After setting that, for the first time query, it takes approximately 3s and second time query takes 0.28s. Phew.

The cache settings improved the performance for some time. We are working off an AWS linux VM with EBS SSD attached. There seems to be problem in the environment as well. The query times in my Mac is 6.3 times faster than the AWS linux / EBS environment.