On Wed, 24 Aug 2022 at 16: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: > > On Tue, Aug 23, 2022 at 10:55:09AM +0100, Dave Page wrote: > > Often it is beneficial to review one's schema with a view to removing > indexes > > (and sometimes tables) that are no longer required. It's very difficult > to > > understand when that is the case by looking at the number of scans of a > > relation as, for example, an index may be used infrequently but may be > critical > > in those times when it is used. > > > > The attached patch against HEAD adds optional tracking of the last scan > time > > for relations. It updates pg_stat_*_tables with new last_seq_scan and > > last_idx_scan columns, and pg_stat_*_indexes with a last_idx_scan column > to > > help with this. > > 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.
They're special in the sense that they're the ones you're most likely going to look at to see how much a relation is used I think (at least, I'd look at them rather than the tuple counts).
There are certainly other things for which a last usage value may be useful. Functions/procedures for example, or views. The benefits to removing unused objects of that type are far, far lower than indexes or tables of course.
There are other potential use cases for similar timestamps, such as object creation times (and creating user), but they are more useful for auditing than monitoring and optimisation.
