Re: Should we update the random_page_cost default value?

On 10/7/25 23:08, Peter Geoghegan wrote:
> On Tue, Oct 7, 2025 at 3:46 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>> I think this discrepancy is largely due to the fact that Tomas' is testing
>> with a cold cache (he has numbers for both), whereas most production workloads
>> have very high cache hit ratios.
>
> Any test case that fails to ensure that all relevant indexes at least
> have all of their internal B-Tree pages in shared_buffers is extremely
> unrealistic. That only requires that we cache only a fraction of 1% of
> all index pages, which is something that production workloads manage
> to do approximately all the time.
>
> I wonder how much the "cold" numbers would change if Tomas made just
> that one tweak (prewarming only the internal index pages). I don't
> think that there's a convenient way of running that experiment right
> now -- but it would be relatively easy to invent one.
>
> I'm not claiming that this extra step would make the "cold" numbers
> generally representative. Just that it might be enough on its own to
> get wildly better results, which would put the existing "cold" numbers
> in context.
>

Why would you expect that?

The index size is about 5% of the table size, so why would the internal
index pages make any meaningful difference beyond that?

Also, it's true the test starts from "cold" cache, but is still uses
shared buffers - and I'd expect the internal pages to be very hot,
compared to the heap. Heap pages are read ~21x, but very far apart. So
the internal index pages are likely cached even in the cold runs.

I can do some runs after prewarming the (whole) index, just to see if it
makes any difference.

regards

--
Tomas Vondra