Re: RFC: Allow EXPLAIN to Output Page Fault Information

On Thu, 26 Dec 2024 13:15:59 +0900
torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> wrote:

> On 2024-12-25 00:52, Tom Lane wrote:
> > torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> writes:
> >> I have attached a PoC patch that modifies EXPLAIN to include page
> >> fault
> >> information during both the planning and execution phases of a
> >> query.
> >
> > Surely these numbers would be too unstable to be worth anything.
>
> Thanks for your comment!
>
> Hmm, I didn't know these are unstable. If there are any reasons, I'd
> like to know about them.
>
> I would like to explore alternative methods for measuring resource
> usage, but
> I am not aware of other approaches.
> (IIUC pg_stat_kcache[1], which is said to provide information about
> filesystem layer I/O usage also gets metrics from getrusage(2))

What I'd really like to see is a column added to pg_stat_database
called blks_read_majflts

It would be great if we could calculate a cache hit ratio that took OS
major page faults into account

Yes this could also be done in pg_stat_kcache but why not do it right
in pg_stat_database? I think it would pretty widely appreciated and
used.

-Jeremy