PostgreSQL - index on key

Question

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?

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? — David Aldridge
– David Aldridge, Commented Nov 19, 2015 at 10:50
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) — joop
– joop, Commented Nov 19, 2015 at 10:51
@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. — joop
– joop, Commented Nov 19, 2015 at 11:01

Gabriel's Messanger · Accepted Answer · 2015-11-19 10:59:07Z

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

edited Nov 19, 2015 at 10:59

answered Nov 19, 2015 at 10:52

Gabriel's Messanger

3,36822 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wolph Over a year ago

Ah, I see. I'll remove my comment :)

Gabriel's Messanger Over a year ago

No problem:) I delete my answer to your comment and update my answer to by more straightforward:)

klin · Accepted Answer · 2015-11-19 10:50:05Z

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.

answered Nov 19, 2015 at 10:50

klin

123k15 gold badges240 silver badges262 bronze badges

3 Comments

Jade Dezo Over a year ago

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?

klin Over a year ago

Yes, both additional indexes are redundant.

Vladimir Baranov Over a year ago

@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.

Collectives™ on Stack Overflow

PostgreSQL - index on key

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related