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.


Quote from the manual

When recovery is performed at server start (e.g. after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset

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!

  • Hmmm... interesting. I would just wonder why one shouldn't remove a PRIMARY KEY from a table which has, say, 7 records - say a table like title - "Mr", "Mrs", "Ms", "Dr", "Fr", "Prof", "Gen"...? My understanding is that the optimiser will just say, "OK, table too small, do full scan" rather than bothering to use the index? – Vérace May 6 at 18:51
  • @Vérace: because the primary key (or unique constraint) is a business constraint that ensures the correctness of the data. It is not necessarily there for performance reasons – a_horse_with_no_name May 6 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.