A couple months back I posted about maybe adjusting random_page_cost to better reflect how current storage handles random and sequential access. I had a bunch of great discussions about the topic since then, but ultimately I got distracted by other stuff. POSET TE happened last week, with my pre-recorded talk about this very topic. Which reminded me that I started thinking about random_page_cost a bit differently. So here’s an update with some more thoughts.

Before I get to that, let me share a chart with random_page_cost results from rotational SATA drives. Those are likely much closer to the storage used for the original experiments in ~2000. Maybe that will give us values closer to the 4.0 default? Clearly not. In fact, the estimated random_page_cost is ~125, about 2-4x the estimate for SSD storage. So with SSDs it’s getting closer to the default, but that’s just a coincidence. Perhaps there’s some fundamental piece of the old experiment that we failed to recall? Or maybe the “raw” results were adjusted in some way. But it seems the 4.0 default never was the “raw” cost of random I/O.

I got a lot of feedback from people who tried increasing random_page_cost in the past. In their experience it definitely did not improve the performance, it hurt it. How is that possible, if it makes the costing less accurate? I believe it comes to random_page_cost “compensating” for the cost model being incomplete. It’s not accounting for various caching effects and resources related to plans performing a lot of random I/O.

Every cost model is an approximation, and a relatively crude one. Our cost model has a couple gaps that I think matter here. It ignores memory, and it ignores locality of access. The cost of an operation is calculated from the amount of CPU and I/O used, but it ignores memory another important resource. Also, plans doing a lot of random I/O tend to be more localized, accessing only the “interesting” data.