Should I specify both INDEX and UNIQUE INDEX?

Question

On one of my PostgreSQL tables, I have a set of two fields that will be defined as being unique in the table, but will also both be used together when selecting data. Given this, do I only need to define a UNIQUE INDEX, or should I specify an INDEX in addition to the UNIQUE INDEX?

This?

CREATE UNIQUE INDEX mytable_col1_col2_idx ON mytable (col1, col2);

Or this?

CREATE UNIQUE INDEX mytable_col1_col2_uidx ON mytable (col1, col2);
CREATE INDEX mytable_col1_col2_idx ON mytable (col1, col2);

Dyane

Answer 1

If you have a UNIQUE INDEX then you don't also need the INDEX - it would be redundant. A UNIQUE INDEX is both a unique constraint and an index that can be used like any other index.

From the documentation:

Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Emphasis mine.

Strafe

Also note that a unique index on each field individually is not the same as a unique index on both fields together. If you need a unique index on each individual field individually a two col index will not do that. – Comus

OT, but this solved my issue as well. I ran into this using Hibernate/jpa annotations. I wasn't sure if setting a unique column also added an index. At least in Postgres it does, so there is no need to add a separate index for the column(s). – Petrinapetrine

I'm not really sure this is true. The note here just says that the index doesn't need to be created manually and not that there is no need for a (non unique) index IMHO. Looking at a few query execution plans seems to confirm that index will be used for lookups only when another (non unique) index is also added for the column. – Aiken