4

I use PostgreSQL 11.1 and I'm trying to gather information from pg_stat_all_indexes table about indexes usage to determine whether a particular index can be removed or not - according to Index size/usage statistics section in https://wiki.postgresql.org/wiki/Index_Maintenance.

I noticed that information present there changes over time significantly (from couple of thousand index hits to 0) and I can't find information about these stats lifetime neither in the docs or this table itself. That worries me because I want to be sure, that the decision I'm going to make about index removal was based on stats from long enough period of time.

Is there any way to check how long these statistics are stored in the pg_stat_all_indexes table?

bronek
  • 43
  • 4

1 Answers1

4

Quote from the manual

When the server shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat subdirectory, so that statistics can be retained across server restarts. When recovery is performed at server start [...], all statistics counters are reset

(emphasis mine)

If you want to reset the statistics to get a baseline, you can use pg_stat_reset() and then compare the values from that point on (e.g. by storing daily snapshots and comparing the differences between days)