Re: n00b question re: indexes and constraints

> On Apr 22, 2020, at 2:39 PM, Wells Oliver <wells(dot)oliver(at)gmail(dot)com> wrote:
>
> Thanks, I should have been more specific, these are unique constraints I am concerned about: so yeah, creating an index on 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
;