Does a multicolumn constraint create indexes for each column or a unified index?

Question

I have a unique constraint over two columns in an association table, e.g.:

CREATE TABLE PersonImage (
id serial PRIMARY KEY,
person_id integer REFERENCES Person (id),
image_id integer REFERENCES Image (id),
CONSTRAINT person_image_uc UNIQUE (person_id, image_id));

Does the constraint make indexes for person_id and image_id independently or does it create a unified index?

My aim is make it faster to search through person_id and image_id independently by creating indexes, but I don't want to create more overhead if this is automatically done in the constraint.

Unless there's something completely unique to Postgree, the index is a composite one. Keep in mind that an Index is something that will speed up your searches but will slow down your inserts and updates (when affecting the indexed columns). So, a good practice is to keep an eye on statistics before creating indexes just because. — Marcelo Myara, Dec 28 '17 at 01:46
One more thing (that I'm geting from what your code is suggesting me): Do you ever search or reference the column "id" somewhere? Cause if it is just there to act as a PK, you could take it off your model and use a composite PK (person_id, image_id) without the need for the constraint (PKs already have indexes and already are unique enforced). — Marcelo Myara, Dec 28 '17 at 01:50
Thanks, Marcelo. I had to have an independent, id column because it's apparently a Django standard. — John Ching, Dec 28 '17 at 04:29

score 1 · Answer 1 · answered Dec 28 '17 at 01:48

1

This is functionally equivalent to:

create unique index unq_personimage_person_image on personimage(person_id, image_id);

The unique index is used to implement the constraint. Because the constraint in one two columns, both must be in the index.

answered Dec 28 '17 at 01:48

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Dec 28 '17 at 02:45

A UNIQUE constraint is almost, but not quite the same as a UNIQUE index on the same columns in the same order. But there are several subtle differences.

See:

Effectively, you get a single multicolumn unique index on (person_id, image_id) with your constraint. Since you also want to search on image_id independently, add another index on (image_id) or (image_id, person_id). Detailed explanation:

Is a composite index also good for queries on the first field?

Does a multicolumn constraint create indexes for each column or a unified index?

2 Answers2