text: -- Author: Josh Berkus
  -- url: https://gist.github.com/jberkus/6b1bcaf7724dfc2a54f3
  WITH table_scans as (
      SELECT relid,
          tables.idx_scan + tables.seq_scan as all_scans,
          ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                  pg_relation_size(relid) as table_size
          FROM pg_stat_user_tables as tables
  ),
  all_writes as (
      SELECT sum(writes) as total_writes
      FROM table_scans
  ),
  indexes as (
      SELECT idx_stat.relid, idx_stat.indexrelid,
          idx_stat.schemaname, idx_stat.relname as tablename,
          idx_stat.indexrelname as indexname,
          idx_stat.idx_scan,
          pg_relation_size(idx_stat.indexrelid) as index_bytes,
          indexdef ~* 'USING btree' AS idx_is_btree
      FROM pg_stat_user_indexes as idx_stat
          JOIN pg_index
              USING (indexrelid)
          JOIN pg_indexes as indexes
              ON idx_stat.schemaname = indexes.schemaname
                  AND idx_stat.relname = indexes.tablename
                  AND idx_stat.indexrelname = indexes.indexname
      WHERE pg_index.indisunique = FALSE
  ),
  index_ratios AS (
  SELECT schemaname, tablename, indexname,
      idx_scan, all_scans,
      round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
          ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
      writes,
      round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
          as scans_per_write,
      pg_size_pretty(index_bytes) as index_size,
      pg_size_pretty(table_size) as table_size,
      idx_is_btree, index_bytes
      FROM indexes
      JOIN table_scans
      USING (relid)
  ),
  index_groups AS (
  SELECT 'Never Used Indexes' as reason, *, 1 as grp
  FROM index_ratios
  WHERE
      idx_scan = 0
      and idx_is_btree
  UNION ALL
  SELECT 'Low Scans, High Writes' as reason, *, 2 as grp
  FROM index_ratios
  WHERE
      scans_per_write <= 1
      and index_scan_pct < 10
      and idx_scan > 0
      and writes > 100
      and idx_is_btree
  UNION ALL
  SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp
  FROM index_ratios
  WHERE
      index_scan_pct < 5
      and scans_per_write > 1
      and idx_scan > 0
      and idx_is_btree
      and index_bytes > 100000000
  UNION ALL
  SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp 
  FROM index_ratios, all_writes
  WHERE
      ( writes::NUMERIC / total_writes ) > 0.02
      AND NOT idx_is_btree
      AND index_bytes > 100000000
  ORDER BY grp, index_bytes DESC )
  SELECT reason, schemaname, tablename, indexname,
      index_scan_pct, scans_per_write, index_size, table_size
  FROM index_groups;
  