Обсуждение: n00b question re: indexes and constraints
Hope all are well.
Are constraints also indexes, or would you want an additional index on top of a constraint if you wanted that column/combo indexed?
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
On Wed, Apr 22, 2020 at 11:26 AM Wells Oliver <wells.oliver@gmail.com> wrote:
Are constraints also indexes, or would you want an additional index on top of a constraint if you wanted that column/combo indexed?
No, having a constraint does not imply that an index exists. However, the enforcement of a "unique" constraint is implemented by auto-creating a unique index.
David J.
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?
On Wed, Apr 22, 2020 at 11:31 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Apr 22, 2020 at 11:26 AM Wells Oliver <wells.oliver@gmail.com> wrote:Are constraints also indexes, or would you want an additional index on top of a constraint if you wanted that column/combo indexed?No, having a constraint does not imply that an index exists. However, the enforcement of a "unique" constraint is implemented by auto-creating a unique index.David J.
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
For a single column unique definitely. For a multi-column separate indexes to get subsets or different column order might be worthwhile.
David J.
On Wed, Apr 22, 2020 at 11:40 AM 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?On Wed, Apr 22, 2020 at 11:31 AM David G. Johnston <david.g.johnston@gmail.com> wrote:On Wed, Apr 22, 2020 at 11:26 AM Wells Oliver <wells.oliver@gmail.com> wrote:Are constraints also indexes, or would you want an additional index on top of a constraint if you wanted that column/combo indexed?No, having a constraint does not imply that an index exists. However, the enforcement of a "unique" constraint is implemented by auto-creating a unique index.David J.
--Wells Oliver
wells.oliver@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 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 ;