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.
>
> 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* your query is disk I/O bound, SSD can help a lot.

If your data is already in memory, or file system cache, and your query is
bound by CPU or bloated/corrupted indexes, or some query inefficiency, then
faster disks really won't do anything.

Depending on the data type and size of the data you may be able to help
your query performance by choosing an index type other than the
out-of-the-box btree as well (such as a hash or brin index) or maybe even a
different sort order on the index, or a partial index.