Re: Should we update the random_page_cost default value?

On 10/8/25 12:01, Tomas Vondra wrote:
> On 10/8/25 06:02, David Rowley wrote:
>> On Wed, 8 Oct 2025 at 08:15, Greg Sabino Mullane <htamfids@gmail.com> wrote:
>>> I've been doing this sort of thing for clients a long time, and I always test both directions when I come across a query that should be faster. For real-world queries, 99% of them have no change or improve with a lowered rpc, and 99% get worse via a raised rpc. So color me unconvinced.
>>
>> I wonder how much past experience for this on versions before v18
>> count in now that we have AIO. The bar should have moved quite
>> significantly with v18 in terms of how often Seq Scans spend waiting
>> for IO vs Index Scans. So maybe Tomas's results shouldn't be too
>> surprising. Maybe the graph would look quite different with io_method
>> = 'sync'.. ?
>>
> 
> Interesting idea, and I'll try to run this on 17 and/or on 18/sync. I
> should have some results tomorrow.
> 
> But based on the testing I've done on 18beta1 (in the thread about what
> should be the default for io_method), I doubt it'll change the outcome
> very much. It showed no change for indexscans, and seqscans got about 2x
> as fast. So the random_page_cost will be about 1/2 of what the earlier
> results said - that's a change, but it's still more than 2x of the
> current value.
> 
> Let's see if the results agree with my guess ...
> 

I did a run on PG17 (on the NVMe RAID), and it's not all that different
from PG18:

                seqscan (s)      index scan (s)  random_page_cost
 -----------------------------------------------------------------
  PG18 NVMe/RAID0        24               25462             49.3
  PG17 NVMe/RAID0        32               25533             38.2

Yes, there's a difference, mostly due to seqscans being slower on PG17
(which matches the measurements in the io_method thread). It'd be a bit
slower with checksums enabled on PG17 (by ~10-20%).

It's just a single run, from a single hw configuration. But the results
are mostly as I expected.


regards

-- 
Tomas Vondra