GoKawiilA 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.
POSETTE happened last week, with my pre-recorded talk about this very topic (and many other great talks, BTW). Which reminded me that I started thinking about random_page_cost a bit differently. So here’s an update with some more thoughts.
I did already touch on some of these things in the old post, and then also in the POSETTE talk in more detail.
Before I get to that, let me share a chart with random_page_cost results from rotational SATA drives. I completely forgot I have these disks in the machine until I opened the case to do some maintenance. Those are likely much closer to the storage used for the original experiments in ~2000. Those would likely be PATA or SCSI drives, but still rotational. 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. It’s not possible to have a fast/cheap cost model that accurately mimics every tiny detail. You may make it more and more detailed, but at some point it’d become as large as the original system. And then why have a model? It’d be pretty useless.
Our cost model has a couple gaps that I think matter here.
... continue reading