> > 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. Indeed. Typical queries use both device id and a timestamp range. Only in some queries more than a few devices are needed.
> > 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. Yeah, I am ok with the performance now. The overall query where this was extracted from (and which had multiple full table scans) went from more than 2 hours to 30 seconds. Given it’s a daily ‘async’ report that is sufficient. > > 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. Ic. Well I don’t mine experimenting with it, and will certainly remember it next time. I guess I was demotivated because I read lot’s of warnings but these might have been about disabling sequential scans and not about page cost settings.
