Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?
It's certainly possible to set the parameters automatically, but no one has submitted a patch to do it.
You need to know the sequential and non-sequential read speeds of the drive. There are a ton of ways to get this, but you may just as well use Google because it likely doesn't matter that much. For instance, a quick google search for the sequential and non-sequential read performance of the Samsung SSD 840 Pro (256GB) shows this from AnandTech, with
- Random Read 101.4/mbps
- Sequential Read 510.7/mbps
That's roughly a ratio of 1:5, so
SET random_page_cost = 5;
SET seq_page_cost = 1;
random_page_cost takes into account the cache,
Random access to mechanical disk storage is normally much more expensive than four times sequential access. However, a lower default is used (4.0) because the majority of random accesses to disk, such as indexed reads, are assumed to be in cache. The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached.
If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase
random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing
random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for
I've shown my
random_page_cost to be 5 times slower than sequential. It's still a wildcard how much of the
random_page_cost is already cached. Alas, these values don't really matter much unless the Index scan and the Sequential were so close that you could reasonably pick the Sequential scan by accident. That's seldom the case. It's not uncommon for an Index to speed things up by thousands of times over.
For example, my
0.005. AFAIK, that means scanning 1000 entries in the index is the same in the planner's eyes as going to the heap once to fetch a block.