In curious how your indices are twice the data. Sounds like you just put indices in anything you see.
It's not carelessness, it's performance.
Quite simply, I have a table with 4 columns -- A, B, C, D. Each column is just an 8-byte integer. It has hundreds of millions of rows. It has an index on B+C+D, an index on C+D, and one on D.
All of these are required because the user needs to be able to retrieve aggregate data based on range conditions around lots of combinations of the columns. Without all the indices, certain queries take a couple minutes. With them, each query takes milliseconds to a couple seconds.
I thought of every possible way to avoid having all three indices, but it just wasn't possible. It's just how performant data lookup works.
You shouldn't assume people are being careless with indices. Far too often I see the opposite.
Set this on the connection:
PRAGMA mmap_size=…
https://www.sqlite.org/mmap.html
I think this works as it removes data copying between the os and your process.
Your process reads data from the os to execute the query.
With mmap the data reads have less overhead, so a full table scan may be fast enough.