pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedn00b question re: indexes and constraints
5+ messages / 3 participants
[nested] [flat]
* n00b question re: indexes and constraints
@ 2020-04-22 18:25 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Wells Oliver @ 2020-04-22 18:25 UTC (permalink / raw)
To: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: n00b question re: indexes and constraints
@ 2020-04-22 18:30 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2020-04-22 18:30 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: n00b question re: indexes and constraints
@ 2020-04-22 18:39 Wells Oliver <wells.oliver@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Wells Oliver @ 2020-04-22 18:39 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: n00b question re: indexes and constraints
@ 2020-04-22 18:48 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2020-04-22 18:48 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: n00b question re: indexes and constraints
@ 2020-04-22 21:25 Rui DeSousa <rui@crazybean.net>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Rui DeSousa @ 2020-04-22 21:25 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
> 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
;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-04-22 21:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-22 18:25 n00b question re: indexes and constraints Wells Oliver <wells.oliver@gmail.com>
2020-04-22 18:30 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-22 18:39 ` Wells Oliver <wells.oliver@gmail.com>
2020-04-22 18:48 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-22 21:25 ` Rui DeSousa <rui@crazybean.net>
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