Re: Execution plan does not use index

On Tue, Nov 10, 2020, 10:51 PM Peter Coppens <peter(dot)coppens(at)datylon(dot)com>
wrote:

> 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.