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

[Matthew Hall <mhall@xxxxxxxxxxxxxxx>]

For indexes the SSDs are at least 4X faster but you won't get that to happen unless you fix the planner tunable for the random page fetch cost first. Super important change for SSDs. 

Matthew Hall

On Oct 8, 2019, at 5:12 PM, Rick Otten <rottenwindfish@xxxxxxxxx> wrote:

On Tue, Oct 8, 2019 at 7:37 PM Arya F <arya6000@xxxxxxxxx> 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.