1

I am wondering what the (side-) effects are if you create an index on a column/columns which is/are already covered by a unique consraint.

drop table if exists person;
create table person
(
    a   integer     not null,
    b   integer     not null,

    unique(a,b)
);

Here, a unique key constraint is put on a,b. I understood that this internally makes two indices: one on a and another on a,b.

Now I create two indices:

create index on person(a);
create index on person(a,b);

What is the effect of this?

Improve this question
6
  • 2
    No, the unique constraint creates a single unique index on (a,b). Have you tried creating the extra indexes, to see if PostgreSQL allows it? Commented Nov 19, 2015 at 10:50
  • 1
    You don't need the indexes, the constraint creates a unique index on (a,b). (in some cases an extra index on (b,a) could be indicated) Commented Nov 19, 2015 at 10:51
  • @joop What do you mean with 'indicated'? Commented Nov 19, 2015 at 10:53
  • @DavidAldridge Yes, PostgreSQL allows it. Commented Nov 19, 2015 at 10:54
  • 1
    @JadeDezo: this kind of structure occurs often in junction tables , such as table marriage( person1 integer NOT NULL references persons(id), person2 integer NOT NULL references persons(id) , PRIMARY KEY (person1,person2) ); In such a case an extra index om (person2,person1) can be helpful in supporting the FK constraints. Commented Nov 19, 2015 at 11:01

2 Answers 2

Reset to default
2

I understood that this internally makes two indices: one on a and another on a,b

Nope, this (unique(a,b)) internally creates just one index - the seccond one on (a,b)

As far as side effects of create index on person(a,b) is concearned. It's "only" redundancy which will:

  • decrease performance of opprations which are maintaining idexes such as INSERT, UPDATE, REINDEX, VACUUM FULL etc.
  • increase disk space used by realation (index is datastructure which must be stored on disk)
  • there's no gain really since query planner may used one or anathor index depending on each execution
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Ah, I see. I'll remove my comment :)
No problem:) I delete my answer to your comment and update my answer to by more straightforward:)
1

I understood that this internally makes two indices: one on a and another on a,b.

No, the unique index on (a,b) is created. After creating the two indexes you'll have three indexes:

"person_a_b_key" UNIQUE CONSTRAINT, btree (a, b)
"person_a_b_idx" btree (a, b)
"person_a_idx" btree (a)

The first and the second overlap and one of them will not be used.

Improve this answer

3 Comments

I understand that the first and the second overlap. But isn't the third one also redundant, as a btree on a,b can be used for lookups on just a?
Yes, both additional indexes are redundant.
@JadeDezo, yes, index on (a,b) can be used for lookups on just a. But, if the query needs to read just a without b it could help to have an additional index just on a. Index just on a takes less bytes on disk than index on (a,b), so it is faster to read. (Of course, extra index means extra overhead for updates and extra disk space.) @klin, my point is that additional index on a is not completely redundant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.