PostgreSQL: unique constraint or unique index

Question

Should I create unique index if a column contains unique constraint and I want to fast search by this column?

For example I have a table users with column login that should be unique. I need fast search user by the login column.

Which is the best way to do it:

1. create a unique constraint (it creates internal unique index - is it used in select queries with WHERE login = 'something'?)
2. create a unique index
3. create a unique index and unique constraint (index duplicates internal index?)

Second case is unique login on not locked users (column locked = false). Postgres does not support partial conditions. Should I create a unique conditional and a partial index or is only a partial index enough?

And one more question: should I create new index for a column with a foreign key? For example: users.employee_id relates to employees.id, should I create an index on employee column for optimized query SELECT * FROM users WHERE employee_id = ....? When are internal indexes used by the optimization engine and when not?

Eusebioeusebius

Answer 1

I have a table 'users' with column login that should be unique

If this is the case you need a unique constraint. Unique constraints are enforced (behind the scenes) by unique indexes.

Conceptually:

• The unique constraint defines the uniqueness.
• The unique index implements the unique constraint.
• The unique index provides speedy searches since it allows operations such as Index Range Scan and Index Seeks.

Is it used in select queries with WHERE login = 'something'?

Yes, it is.

Second case is unique login on not locked users (column locked = false).

If this is the case a unique constraint won't work. Maybe a trigger on insert could help here.

should I create new index for column with foreign key?

No, it's not needed (at least in the current version 10 and perhaps the later versions), s. documentation:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. [...] There's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Lampas

@automatix I think PostgreSQL does not create indexes for foreign keys by default, and your edit may be incorrect. The documentation you included is related to unique indexes, not foreign keys. – Lampas

Unfortunately I read the quote wrong. The context of the question was the UNIQUE CONSTRAINT, so I read the text as "should I create new index for column with unique constraint?". So, yes, my edit is incorrect. Please reject it. – Hindquarter