unused_indexes.sql

CREATE OR REPLACE VIEW public.unused_indexes AS
  SELECT
    (pg_stat_all_indexes.schemaname :: TEXT || '.' :: TEXT) || pg_stat_all_indexes.relname :: TEXT,
    pg_stat_all_indexes.indexrelname,
    pg_stat_all_indexes.idx_scan,
    pg_size_pretty(
        pg_relation_size(((pg_stat_all_indexes.schemaname :: TEXT || '.' :: TEXT) || pg_stat_all_indexes.indexrelname :: TEXT) :: REGCLASS)) AS pg_size_pretty
  FROM pg_stat_all_indexes
  WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY ['pg_toast' :: NAME, 'pg_catalog' :: NAME])) AND pg_stat_all_indexes.idx_scan = 0 AND
        pg_relation_size(((pg_stat_all_indexes.schemaname :: TEXT || '.' :: TEXT) || pg_stat_all_indexes.indexrelname :: TEXT) :: REGCLASS) >
        (1024 * 1024 * 1024)
  ORDER BY (pg_relation_size(((pg_stat_all_indexes.schemaname :: TEXT || '.' :: TEXT) || pg_stat_all_indexes.indexrelname :: TEXT) :: REGCLASS)) DESC;