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.

2 Answers 2


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
    Commented May 6, 2019 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
    – user1822
    Commented May 6, 2019 at 19:35
  • Is running just select pg_stat_reset();the correct way to reset the statistics? Does resetting the statistics only affect the stats tables or will it affect the query planner, too? Commented Mar 21, 2020 at 12:44

Not directly an answer, but related: there are functions to reset the statistics whenever you want. From the manual:

pg_stat_reset() resets all statistics counters for the current database to zero (requires superuser privileges by default, but EXECUTE for this function can be granted to others.)

pg_stat_reset_single_table_counters(oid) resets statistics for a single table or index in the current database to zero (requires superuser privileges by default, but EXECUTE for this function can be granted to others)

Posting here as this question comes before the manual page in Google search results, and is much easier to identify.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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