Autodetect ‘random_page_cost’ vs ‘seq_page_cost’

Question

I read this article about PostgreSQL performance on SSD:

https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0

These two configurations seem to be important random_page_cost vs seq_page_cost

Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?

Update

I have these steps on my mind:

1. Script creates some dummy tables
2. Scripts inserts data to the tables
3. Script does some queries
4. Script shows matching values for random_page_cost and seq_page_cost
5. Human or an automated system takes these values and updates the config. This step is not part of the question.

Answer 1

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;

Warning, 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 random_page_cost.

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