Re: Dynamic gathering the values for seq_page_cost/xxx_cost

On Sat, Sep 26, 2020 at 8:17 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:
>
> As for the testing with cache considered, I found how to estimate cache hit
> ratio is hard or how to control a hit ratio to test is hard. Recently I am thinking
> a method that we can get a page_reads, shared_buffer_hit from pg_kernel
> and the real io (without the file system cache hit) at os level (just as what
> iotop/pidstat do). then we can know the shared_buffer hit ratio and file system
> cache hit ratio (assume it will be stable after a long run). and then do a testing.
> However this would be another branch of manual work and I still have not got
> it done until now.

FWIW pg_stat_kcache [1] extension accumulates per (database, user,
queryid) physical reads and writes, so you can easily compute a
shared_buffers / IO cache / disk hit ratio.

[1] https://github.com/powa-team/pg_stat_kcache