Postgresql – How to get a list of postgresql indexes that have not been used in any queries recently

postgresql

Is there a way to get a list of the indexes that have not been used by any queries since some arbitrary cutoff time in the past? Or more simply, a list of all indexes along with a timestamp of when each one was last used in the execution of a query?

What I've tried so far

I've read the Wall Of Text at https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW, but a solution is not obvious (I'm mostly a MySQL user and I find PostgreSQL documentation very unclear, lost in the details).

Best Answer

The time of last use is not stored, but you can use the query from my blog to find all indexes that have never been scanned and server no other purpose either:

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

Indexes can serve other purposes than scanning:

  • implement constraints
  • make autoanalyze gather statistics on the indexed expression
Related Question