PostgreSQL with modern storage: what about a lower random_page_cost?

The topic was widely discussed last week, in favor of a lower value for random_page_cost:

100x Faster Query in Aurora Postgres with a lower random_page_cost
when we migrated ~1TB DB from heroku -> AWS, everything broke
Use random_page_cost = 1.1 on modern servers

The random_page_cost parameter accounts for the latency incurred by random reads, particularly from indexes with poor correlation factors. Its default value is 4. On the other hand, the seq_page_cost parameter accounts for the lower latency incurred by contiguous page reads, where many pages are read without additional seek. Its default value is 1. With modern storage, should you set a lower value for random_page_cost as those messages suggest?

As I’m more concerned by the predictability of performance with stable execution plans rather than tuning magic global parameters, I’m in favor of not changing the default. Let’s explain why.

HDD seek latency

It is commonly believed that default values were set based on disk latency when mechanical HDDs were prevalent.

Let’s consider an example. If your storage has a latency (seek) of 10 milliseconds and a bandwidth (throughput) of 4KB/s, then reading an 8k block would take 10+(8/4)=12 milliseconds. If you read 10 consecutive pages, it would take 10+10*(8/4)=30 milliseconds. Thus, it would be equivalent to 3 milliseconds per page. We can conclude that reading random pages is 4 times more expensive than sequential reads (12/3=4).

This could be the reason why default values were chosen in this manner:

The input/output (IO) specifications may appear low, but they are not too far from what 3600 RPM disks could achieve at that time. Oracle still employs these numbers, namely, 10ms IO seek time and 4kB/second IO transfer time when no system statistics are gathered, and this is the recommended practice. Oracle’s cost model accounts for 8 contiguous blocks in I/O calls, and then random reads are estimated to be 3.7 times more expensive. This estimate is not much different from the default setting of 4 employed by PostgreSQL.

I’m comparing with Oracle Database on purpose as this has been discussed a lot, attempting to make index access more desirable by lowering optimizer_index_cost_adj or gathering system statistics to calibrate the storage. Finally, there’s a consensus that it has brought more problems than solutions.

SSD random reads

When people are using SSDs with no mechanical latency, they tend to want to change the values since random reads are just as fast as sequential reads.

They often consider reducing the value of random_page_cost to be closer to the value of seq_page_cost. However, I believe that this is not a good practice for three reasons.

Firstly, modern storage systems are complex and have multiple levels of cache and network, which may increase or decrease the latency experienced by the database.
Secondly, it is better to have a stable and consistent model rather than having a different model for each system.
Lastly, a better outcome can be achieved by looking at the index design to get predictable performance.

Storage hierarchy

In modern systems, it is incorrect to assume that the pages that your query reads are equivalent to read requests to your HDD or SSD. In fact, the process is much more complex and involves several layers:

Firstly, the query reads pages from the PostgreSQL shared buffers.
If there are cache misses, it reads from Linux filesystem buffers.
If there are further cache misses, it prepares an I/O call.
The kernel may read more data when it detects sequential reads (readahead).
It sends an I/O call through the network (SAN, NAS, Cloud Storage).
It reads from the storage cache.
Finally, if there are still cache misses, it reads from the disk (HDD or SSD).

Is it reasonable to use the physical read latency factor of SSD or HDD at the lowest level of the chain to determine the random_page_cost applied to the estimated pages at the highest level?

There is no latency when accessing blocks stored in memory at different levels of cache, which means that the value of random_page_cost should be equal to seq_page_cost for them. In OLTP applications, a high cache hit ratio is expected, and this justifies a low value of random_page_cost, regardless of the disk latency. But there is more to consider.

PostgreSQL reads data in single blocks. Reading multiple contiguous blocks in one I/O call has a benefit due to Linux read-ahead. This advantage becomes more significant when there are delays in I/O, which historically was due to the rotation of HDDs. Back then, disks were directly attached to servers. But even if modern storage uses SSDs with lower latency, they are often behind network calls, and cloud instance limitations. As a result, the “random_page_cost” should be higher than “seq_page_cost”.

Index with good correlation factor

I have created a table with a strong correlation factor for the primary key ‘demo_key’, but a weak correlation factor for ‘demo_val’:

The table contains one hundred thousand rows, which are spread across 541 pages:

When selecting less than 50% of rows (50000 out of 100000), PostgreSQL uses an Index Scan:

This has actually read 409 pages out of a 541 pages table. When reading more rows, it will be equivalent to read all the pages.

When I choose a search criteria that returns over 60% of the data in PostgreSQL, the query planer selects a Sequential Scan instead of using an index:

What changes can be expected if the value of random_page_cost is similar to that of seq_page_cost?

By setting random_page_cost=1.1, the query planner chose an Index Scan over a Sequential Scan for a larger number of rows.

Does it matter? Probably not. I had to query thousands of rows, which is half of the table, to see a change in the plan. Based on those numbers, I think it would be more scalable to add columns to the index to achieve an Index Only Scan with fewer pages to read rather than switch to a full table scan on a large table that will grow. Alternatively, if this query reads a large portion of the table that isn’t expected to grow, I would prefer a sequential scan even for a smaller result, to get predictable performances.

Index with bad correlation factor

When there is poor correlation between the index entries and the physical rows in a heap table, PostgreSQL utilizes a Bitmap Scan to prevent reading the same page multiple times. In my example, the inflection point where a Sequential Scan becomes preferable is between 38% and 39%:

If random_page_cost is set close to seq_page_cost, a distinct pattern emerges. A Bitmap Index Scan is used when reading less than 16% of rows, and a Seq Scan is used when reading more than 57% of rows:

Between those two thresholds, an Index Scan was introduced, when the number of rows exceeded a certain threshold, making it too large to build a bitmap, but not large enough to do a sequential scan.

Is that better? It’s unlikely that having three execution plans instead of two will improve plan stability. In a test scenario where everything is in cache, the time taken doesn’t matter. However, the number of buffers read shows a problem: the Index Scan has read 30 times more blocks than a Seq Scan, and these blocks are random reads that cannot be optimized with read-ahead. If, for any reason, more memory is used on your system and the buffer reads become cache misses, the response time can increase by several orders of magnitude.

Find the inflection point

I didn’t try and guess to see the different plans. Here is the code that I used:

This had the settings and the query for the last case and here was the result:

It can be useful to test your query for different selectivity and see when it can go wrong.

What about your data model and indexing strategy?

This test doesn’t clearly indicate the most reasonable default for random_page_cost. Therefore, I suggest sticking with the default value because it offers a significant advantage: it’s what most people use. By doing so, you can avoid any unexpected surprises that may arise from a custom configuration.

Based on the test results, it appears that the index on the val column is not well-suited for queries that read a large number of rows. When multiple rows are read, all pages from the table are read, which is not expected to remain in cache for larger tables. With the non-default random_page_cost setting, the situation becomes even worse, as it can switch to a plan that reads 30 times more pages than necessary.

There is an inflection point where the query planner has to choose between using an index or scanning the entire table. This decision may change when a single new row is added to a large table. If the table is near this point, the query’s performance will depend on several factors, such as available memory for cache, IOPS, and bandwidth limits. These factors cannot be accurately predicted because they depend on the total workload. You can modify this point using random_page_cost, but it’s best to avoid this unpredictable zone altogether.

I recommend to spend more time on looking at execution plans and less time on global parameter tuning.

Using high random_page_cost as a red flag for bad indexes

If you notice that changing the random_page_cost parameter results in different response times, it could be an indication that some of your access patterns do not have optimal indexes. In this case, the query planner is forced to come up with the “least bad” solution. Instead of decreasing the parameter to get more indexes used, you should focus on making those indexes more appealing to the query planner. When your index is used with a high value of random_page_cost and provides the expected performance, you can be confident that you will avoid unpleasant surprises when your users query slightly more rows.

If you plan on altering the random_page_cost to a lower value, it is crucial to understand why it would enhance the performance rather than basing it on just one test. With the growth of your data and increased workload, the index access that may provide a quick response time now may take much longer if the memory available for cache reduces. Thus, it is essential to evaluate the performance impact of any such change and ensure that it remains an improvement over time, rather than a short-term fix.

I believe that the decision between using an Index Scan and a Sequential Scan depends on scalability rather than response time. The response time of an Index Scan is proportional to the size of the result set, while the response time of a Sequential Scan is proportional to the size of the table being scanned. It is important to understand which one will grow to make an informed decision and provide indexes that will be chosen by the query planner without depending on dubious a global setting.