On Thu, 25 Aug 2022 at 03:03, Bruce Momjian <bruce@momjian.us> wrote: > > On Wed, Aug 24, 2022 at 04:01:21PM +0100, Dave Page wrote: > > On Wed, 24 Aug 2022 at 15:18, Bruce Momjian <bruce@momjian.us> wrote: > > Would it be simpler to allow the sequential and index scan columns to be > > cleared so you can look later to see if it is non-zero? Should we allow > > > > I don't think so, because then stat values wouldn't necessarily correlate with > > each other, and you wouldn't know when any of them were last reset unless we > > started tracking each individual reset. At least now you can see when they were > > all reset, and you know they were reset at the same time. > > Yeah, true. I was more asking if these two columns are in some way > special or if people would want a more general solution, and if so, is > that something we want in core Postgres.
Back when I used to do a bit of PostgreSQL DBA stuff, I had a nightly job setup to record the state of pg_stat_all_tables and put that into another table along with the current date. I then had a view that did some calculations with col - LAG(col) OVER (PARTITION BY relid ORDER BY date) to fetch the numerical values for each date. I didn't ever want to reset the stats because it messes with autovacuum. If you zero out n_ins_since_vacuum more often than auto-vacuum would trigger, then bad things happen over time (we should really warn about that in the docs).
I don't have a particular opinion about the patch, I'm just pointing out that there are other ways. Even just writing down the numbers on a post-it note and coming back in a month to see if they've changed is enough to tell if the table or index has been used.
There are usually other ways to perform monitoring tasks, but there is something to be said for the convenience of having functionality built in and not having to rely on tools, scripts, or post-it notes :-)
We do also need to consider now that stats are stored in shared memory that any fields we add are in RAM.
That is a fair point. I believe this is both minimal, and useful though.
I've attached a v2 patch that incorporates Greg's suggestions.
