Re: How is random_page_cost=4 ok?

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> I'm kind of curious where the value of 4 for random_page_cost came from.
> IIRC, Tom, you mentioned it came from tests you ran -- were those raw i/o
> tests or Postgres cost estimates compared to execution times?

It was based on actual query execution times, but that was with 1990s
hardware. It doesn't surprise me at all that modern drives would show a
larger ratio --- seems like transfer rate has improved more than seek
times. And it's also the case that we've squeezed a lot of overhead
out of Postgres' tuple processing code since then, so that might be
another way in which seqscans have gotten cheaper.

> I'm concerned that if we start recommending such large random_page_costs as
> these it'll produce plans that are very different from what people have grown
> used to. And the value of 4 seems to work well in practice.

Yeah, it seems like raising random_page_cost is not something we ever
recommend in practice. I suspect what we'd really need here to make any
progress is a more detailed cost model, not just fooling with the
parameters of the existing one.

> I suspect the root of all this is that random_page_cost is encoding within it
> the effects of caching. If that's true shouldn't we find a way to model
> caching using effective_cache_size instead so that people can set
> random_page_cost realistically based on their hardware?

We do model caching using effective_cache_size. One thing we definitely
lack is any understanding of the effects of caching across multiple
queries. I'm not sure what other first-order effects are missing from
the model ...

regards, tom lane