3

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).

1

1 Answer 1

3

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

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.