PostgreSQL DBA Queries

trash_indexes.sql

SELECT s.schemaname AS sch,
       s.relname AS rel,
       s.indexrelname AS idx,
       s.idx_scan AS scans,
       pg_size_pretty(pg_relation_size(s.relid)) AS ts,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS "is"
FROM pg_stat_user_indexes s
       INNER JOIN pg_index i ON i.indexrelid = s.indexrelid
       LEFT JOIN pg_constraint c ON i.indrelid = c.conrelid AND array_to_string(i.indkey, ' ') = array_to_string(c.conkey, ' ')
WHERE i.indisunique IS FALSE
       AND pg_relation_size(s.relid) > 1000000
       AND s.idx_scan < 100000
       AND c.confrelid IS NULL
ORDER BY s.idx_scan ASC, pg_relation_size(s.relid) DESC;