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

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.

 

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

Cheers,

Jeff