Обсуждение: n00b question re: indexes and constraints

> On Apr 22, 2020, at 2:39 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Thanks, I should have been more specific, these are unique constraints I am concerned about: so yeah, creating an
indexon a set of columns where there's a unique constraint is redundant, correct? 
>

Correct. Here is a view that I created to help find duplicate indexes in such cases where redundant indexes where
created.   


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
;