I want to collect statistics about indexes in PostgreSQL.
I found a way to collect such statistics as a number of index scans, index size and etc. This is a query:

    SELECT
      current_database()                             AS datname,
      t.schemaname,
      t.tablename,
      psai.indexrelname                              AS index_name,
      pg_relation_size(i.indexrelid)                 AS index_size,
      CASE WHEN i.indisunique THEN 1 ELSE 0 END      AS "unique",
      psai.idx_scan                                  AS number_of_scans,
      psai.idx_tup_read                              AS tuples_read,
      psai.idx_tup_fetch                             AS tuples_fetched
    FROM
      pg_tables t
      LEFT JOIN pg_class c ON t.tablename = c.relname
      LEFT JOIN pg_index i ON c.oid = i.indrelid
      LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
    WHERE
      t.schemaname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY 1, 2;

However, I do not see a way how to collect the last usage date of an index. Is it possible in PostgreSQL?

CodePudding user response：

That is not possible.

If you want to find out if a table is used within – say – the next week, you could look at pg_stat_all_indexes.idx_scan now and a week from now and see if the number has increased. Monitoring systems are great for that.

CodePudding user response：

Each row in the pg_stat_all_indexes view contains information for one index. There is no column in this view that indicates the most recent date and time for which an index was parsed.

However, it is possible to reset statistics: pg_stat_reset ()

this lets you know whether the index has been used or not since that date if idx_scan field become > 0.