Re: Should we update the random_page_cost default value? - Mailing list pgsql-hackers

On Mon, 6 Oct 2025 at 12:24, Tomas Vondra <tomas@vondra.me> wrote:
> On 10/6/25 07:29, Tom Lane wrote:
> > Another angle is that I expect that the ongoing AIO work will largely
> > destroy the existing model altogether, at least if you think in terms
> > of the model as trying to predict query execution time.  But if what
> > we're trying to model is net resource demands, with an eye to
> > minimizing the total system load not execution time of any one query,
> > maybe we can continue to work with something close to what we've
> > traditionally done.
> >
> > No answers here, just more questions ...
> >
>
> I had the same thought, when working on the (index) prefetching. Which
> of course now relies on AIO. Without concurrency, there wasn't much
> difference between optimizing for resources and time, but AIO changes
> that. In fact, parallel query has a similar effect, because it also
> spreads the work to multiple concurrent processes.
>
> Parallel query simply divides the cost between workers, as if each use a
> fraction of resources. And the cost of the parallel plan is lower than
> summing up the per-worker costs. Maybe AIO should do something similar?
> That is, estimate the I/O concurrency and lower the cost a bit?

OS and/or disk read-ahead is muddying the water here. Most modern
storage can saturate their bandwidth capability with enough concurrent
or large enough requests. The read-ahead is effectively increasing
request concurrency behind PostgreSQLs back while random is running
with concurrency 1. It would be very interesting to see what
debug_io_direct does, and also fio numbers for direct io.

It seems to me too that the increased capability to utilize I/O
concurrency from AIO significantly changes how this needs to be
modeled. In addition to random/sequential distinction there is now
also prefetchable/non-predictable distinction. And it would be good to
incorporate some cost to "wasting" resources. If we would apply
effective_io_concurrency blindly, then scanning 1600 predictable pages
would be cheaper than 100 unpredictable. And it would be correct that
it is faster, but maybe not by enough to justify the extra resource
usage.

I think Laurenz was on the right track with introducing a tunable that
allows to slide between time and resource use optimization. It doesn't
have to be all or nothing, like for 2x faster execution, one could
allow 10% or 2x or any amount of extra resources to be used.

But for that to have a chance of working reasonably the cost model
needs to be better. Right now the per page costs effectively encode
expected cache hit ratios, io cost vs page access cpu cost in a very
coarse and unintuitive way. I don't think the actual buffers read
number is anywhere close to how many times the planner accounts
page_cost for anything but a sequential scan of a huge table.

Regards,
Ants Aasma