Re: Should we update the random_page_cost default value?

On Mon, 6 Oct 2025 at 13:59, Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> Unless I did some silly mistakes, these results suggest the current 4.0
> value is a bit too low, and something like ~20 would be better even on
> SSDs. This is not the first time it was suggested a higher default might
> be better - see this 2008 post [3]. Of course, that's from before SSDs
> became a thing, it's about evolution in hard disks and our code.

Thanks for going to all that effort to calculate that. It was an
interesting read and also very interesting that you found the opposite
to the typical advice that people typically provide.

I don't have any HDDs around to run the script to check the results. I
do have a machine with 2 SSDs, one PCIe3 and one PCIe4. I'll see if I
can get you some results from that.

It would be interesting to see how your calculated values fare when
given a more realistic workload. Say TPC-H or Join Order Benchmark. I
recall that TPCH has both a power and a throughput result, one to test
concurrency and one for single query throughput. I wonder if the same
random_page_cost setting would be preferred in both scenarios. I can
imagine that it might be more useful to have more index pages in
shared buffers when there's strong contention for buffers. It would be
interesting to run some pg_buffercache queries with GROUP BY relkind
to see how much of an effect changing random_page_cost has on the
number of buffers per relkind after each query.

I wonder if the OtterTune people collected any "phone-home"
information feeding back about what the software picked for GUCs. It
would be interesting to know if there was some trend to show what the
best random_page_cost setting was or if the best setting varied based
on the server and workload.

David