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(at)gmail(dot)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