Re: Should we update the random_page_cost default value?

On 10/8/25 21:37, Andres Freund wrote:
> Hi,
>
> On 2025-10-08 21:25:53 +0200, Tomas Vondra wrote:
>> On 10/8/25 19:23, Robert Haas wrote:
>>>> I think in the past we mostly assumed we can't track cache size per
>>>> table, because we have no visibility into page cache. But maybe direct
>>>> I/O would change this?
>>>
>>> I think it's probably going to work out really poorly to try to use
>>> cache contents for planning. The plan may easily last much longer than
>>> the cache contents.
>>>
>>
>> Why wouldn't that trigger invalidations / replanning just like other
>> types of stats? I imagine we'd regularly collect stats about what's
>> cached, etc. and we'd invalidate stale plans just like after ANALYZE.
>
> You can't just handle it like other such stats - the contents of
> shared_buffers can differ between primary and standby and other stats that
> trigger replanning are all in system tables that can't differ between primary
> and hot standby instances.
>
> We IIRC don't currently use shared memory stats for planning and thus have no
> way to trigger invalidation for relevant changes. While it seems plausible to
> drive this via shared memory stats, the current cumulative counters aren't
> really suitable, we'd either need something that removes the influence of
> olders hits/misses or a new field tracking the current number of buffers for a
> relation [fork].
>

I don't think I mentioned pgstat (i.e. the shmem stats) anywhere, and I
mentioned ANALYZE which has nothing to do with pgstats either. So I'm a
bit confused why you argue we can't use pgstat.

What I imagined is more like a process that regularly walks shared
buffers, counts buffers per relation (or relfilenode), stores the
aggregated info into some shared memory (so that standby can have it's
own concept of cache contents). And then invalidates plans the same way
ANALYZE does.

regards

--
Tomas Vondra