From: Rui DeSousa <rui@crazybean.net>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: n00b question re: indexes and constraints
Date: Wed, 22 Apr 2020 17:25:29 -0400
Message-ID: <CABDA054-5DF4-4C09-9DDC-F544E3DBC043@crazybean.net> (raw)
In-Reply-To: <CAOC+FBXtsauGmMUSrg65Cay0ki8y+QTkexHU4bFFSRy5fASCKg@mail.gmail.com>
References: <CAOC+FBUcDbLb=fjwUzTyTrdfhLu81M9uGdqwfK=BFG3QU5iZuA@mail.gmail.com>
<CAKFQuwZu9+VpqMFDYuxLs8sjTPj5wOWR+it8Pug4VPbMv7riBA@mail.gmail.com>
<CAOC+FBXtsauGmMUSrg65Cay0ki8y+QTkexHU4bFFSRy5fASCKg@mail.gmail.com>
> 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 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
;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui@crazybean.net, wells.oliver@gmail.com, david.g.johnston@gmail.com
Subject: Re: n00b question re: indexes and constraints
In-Reply-To: <CABDA054-5DF4-4C09-9DDC-F544E3DBC043@crazybean.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox