Identifying and Removing Redundant Indexes in Your Database

				
					CREATE OR REPLACE VIEW redundant_index_vw AS
WITH index_info AS (
  SELECT
    idx.indexrelid AS index_oid,
    idx.indrelid AS table_oid,
    idx.indisunique,
    pg_get_expr(idx.indpred, idx.indrelid) IS NOT DISTINCT FROM NULL AS is_partial,
    pg_get_expr(idx.indpred, idx.indrelid) AS where_clause,
    i.relname AS index_name,
    ns.nspname AS schema_name,
    tbl.relname AS table_name,
    pg_get_indexdef(idx.indexrelid) AS indexdef,
    CASE
      WHEN con.contype = 'p' THEN 'primary'
      WHEN idx.indisunique THEN 'unique'
      ELSE 'regular'
    END AS index_type,
    ARRAY(
      SELECT a.attname
      FROM generate_subscripts(idx.indkey, 1) AS s(i)
      JOIN pg_attribute a ON a.attrelid = idx.indrelid AND a.attnum = idx.indkey[s.i]
    ) AS index_columns
  FROM pg_index idx
  JOIN pg_class i ON i.oid = idx.indexrelid
  JOIN pg_class tbl ON tbl.oid = idx.indrelid
  JOIN pg_namespace ns ON ns.oid = i.relnamespace
  LEFT JOIN pg_constraint con ON con.conindid = idx.indexrelid
  WHERE ns.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
),
redundant_indexes AS (
  SELECT
    a.schema_name,
    a.index_name AS redundant_index,
    a.table_name,
    a.index_type,
    COALESCE(psui.idx_scan, 0) AS idx_scan,
    b.index_name AS covering_index,
    a.index_oid,
    pg_get_indexdef(a.index_oid) AS indexdef,
    format('DROP INDEX IF EXISTS %I.%I;', a.schema_name, a.index_name) AS base_drop_stmt
  FROM index_info a
  JOIN index_info b
    ON a.table_oid = b.table_oid
   AND a.index_oid <> b.index_oid
   AND a.is_partial = b.is_partial
   AND a.where_clause IS NOT DISTINCT FROM b.where_clause
   AND (
     cardinality(a.index_columns) <= cardinality(b.index_columns)
     AND a.index_columns = b.index_columns[1:cardinality(a.index_columns)]
   )
   AND (
     NOT a.indisunique OR b.indisunique
   )
  LEFT JOIN pg_stat_user_indexes psui ON psui.indexrelid = a.index_oid
)
SELECT
  schema_name,
  redundant_index,
  table_name,
  index_type,
  idx_scan,
  covering_index,
  CASE
    WHEN indexdef ILIKE '%include (%' THEN base_drop_stmt || ' -- WARNING: This index has INCLUDE columns'
    ELSE base_drop_stmt
  END AS drop_statement
FROM redundant_indexes
ORDER BY idx_scan, schema_name, table_name, redundant_index;