I was looking at the tables (pg_stat_user_indexes and pg_stat_user_tables) and discovered many indices that are not being used.
But before I think about doing any operations to remove these indices, I need to understand what period was the analysis of this data (idx_scan), has it been since the database was created?
In the pg_stat_database (stats_reset) table is there a date that normally is today or up to 15 days ago, but does this process interfere with the tables I mentioned above?
- No command of% reset () was executed.
Do the% reset () commands clear the tables (pg_stat_user_indexes and pg_stat_user_tables)?
My goal is to understand the period of data collected so that I can make a decision.
Best Answer
Quote from the manual
In other words, the statistics are not reset on a clean shutdown and restart.
So if your server never crashed, and you never issued a "shutdown immediate" (using `pg_ctl stop -m immediate) then the statistics show the cumulated values since the instance was created (for "global" statistics") or since the database was created (for database specific statistics).
If you want to detect if there are unused indexes, I would reset the statistics, then take snapshots of them in regular intervals (e.g. every day or every hour depending on your system) and then compare the values over time after e.g. a week of normal operation.
Note that you can't (shouldn't) remove unique indexes (e.g. a primary key index) that have never been used for reading!