If you disable sequential scan, does it choose the index and what cost does it show?
It chooses the index, but apparently to create some intermediate structure that then later still needs to be joined on the device_id. Probably requires scanning all pages of the index, which might explain why the performance is still not ok
Ahhh. You don't have a single column index on the timestamp value or a multi column one with timestamp first. No wonder the subquery didn't help. My apologies for not realizing that before. Thanks for satisfying my curiosity why it didn't perform like it should. Certainly, that index may or may not be worth creating and maintaining.
Obviously the 3 days worth of data is also too high (just quick and safe). Depending on what your server timezone vs the the most divergent timezone on a device, that could be tightened up. Regardless. If the 5 seconds runtime you got to with the correlated subquery on the where clause is sufficient, then no need to continue I suppose.
It seems odd to me to not do any basic adjustment of random_page_cost though. It isn't a magic number that the core team know to be perfect. It is a baseline that is likely to be quite different for each use case and server config. While there are no hard and fast rules and absolute right answers, it seems prudent to at least follow the advice of the community and lower it a ways if storage is ssd style and/or cache hits are quite high.
