Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: unique constraint or unique index

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?

like image 654
Alexander Avatar asked Jun 23 '18 18:06

Alexander


People also ask

Is a unique constraint the same as a unique index?

A constraint has different meaning to an index. It gives the optimiser more information and allows you to have foreign keys on the column, whereas a unique index doesn't.

Is a unique constraint an index PostgreSQL?

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

Is unique constraint create index?

Yes, absolutely. A unique constraint creates a unique index.

What is the difference between index and constraint?

Index - improves the performance of retrieval and sort operations on Table data. Unique Constraints - a combination of values that uniquely identify a row in the Table.


1 Answers

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.

like image 176
The Impaler Avatar answered Oct 19 '22 07:10

The Impaler