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