Re: Should we update the random_page_cost default value?

Hi,

On 2025-10-08 21:12:37 +0200, Tomas Vondra wrote:
> 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.

I also didn't expect anything major here. The query execution relevant uses of
AIO in 18 just don't change the picture that much:

Seqscans already had readahead from the OS level and bitmap heap scans had
readahead via posix_fadvise. The AIO use for e.g. VACUUM can have vastly
bigger impact, but we don't use cost based planning for that.

That's not to say we don't need to improve this for 18-as-is. E.g.:

- we already did not properly cost bitmap heap scan taking
effective_io_concurrency into account. It's very easy to see 1-2 orders of
magnitude difference for bitmap heap scans for different
effective_io_concurrency values, that is clearly big enough that it ought to
be reflected in the cost.

- we already did not account for the fact that backward index scans are
*vastly* slower than forward index scans.

Once we use AIO for plain index scans, costing probably ought to account for
AIO effects - it's again pretty easy to to see 1-2 orders of magnitude in
execution time difference on real-world hardware. That should move the needle
towards preferring index scans over seqscans in plenty of situations.

Greetings,

Andres Freund