Re: Ideas to deal with table corruption

Correct, and there is no need to create an index on a unique constraint or primary key as they are already implemented
viaindexes.  I can’t count how many duplicate indexes I’ve dropped in the past.  I use this view help find duplicates
ina given system.  Duplicate indexes just use up space and rob performance during updates and inserts.    

/*======================================================================================================
   q$Id: duplicate_index.sql 1 2015-12-19 15:40:29Z rui $
   Description: Find duplicate indexes
======================================================================================================*/
create or replace view duplicate_index
as
select base.indrelid::regclass as table_name
  , string_agg((dup.indexrelid::regclass)::text, E'\n') as indexes
  , pg_size_pretty(avg(pg_relation_size(dup.indexrelid))) as avg_size
from pg_index base
join pg_index dup on dup.indrelid = base.indrelid  -- table identifier
  and dup.indkey = base.indkey  --  columns indexed
  and dup.indclass = base.indclass  -- columns types
  and (
    dup.indexprs = base.indexprs -- expression predicate for columns
    or  (
      dup.indexprs is null
      and base.indexprs is null
    )
  )
  and (
    dup.indpred = base.indpred  -- expression predicate for where clause
    or (
       dup.indpred is null
       and base.indpred is null
      )
  )
  and dup.indexrelid != base.indexrelid  --index identifier
group by base.indrelid::regclass
  , concat(base.indkey::text, base.indclass::text, base.indexprs, base.indpred)
order by avg_size desc
  , base.indrelid::regclass
;