Re: Would SSD improve Index Only Scan performance by a lot?

On Tue, Oct 8, 2019 at 7:37 PM Arya F <arya6000(at)gmail(dot)com> wrote:

> As my table has gotten bigger, it takes longer to get a single row back
> when querying a row by its btree index.
>
>
Is this in a probabilistic sense, they take longer on average, or has every
single access gotten slower? If the increase in size has caused the index
leaf pages to go from being almost entirely in cache to almost entirely not
being in cache, the slow down would probably be a lot more 3 to 4 fold.
But maybe you went from 100% in cache, to 90% in cache and 10% out of cache
(with a 40 fold slowdown for those ones), coming out to 4 fold slow down on
average. If that is the case, maybe you can get the performance back up by
tweaking some settings, rather than changing hardware.

> Right now the database is running on a traditional HDD. SSDs have a much
> faster seek time than traditional HDDs.
>
> Would switching to an SSD improve "Index Only Scan" time greatly? by at
> least 3-4 times?
>

If drive access is truly the bottleneck on every single execution, then
yes, probably far more than 3-4 times.

Cheers,

Jeff