lundi 2 février 2015

Is unique index better than unique constraint when I need an index with an operator class


PgSQL docs say:



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.



Based on this, if I want an expression index on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a lower case index?


Based on this, if I want an index with an operator class (e.g. text_patter_ops) on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need an operator class?


Case 1:



CREATE TABLE book (
id SERIAL PRIMARY KEY,
name text NOT NULL,
CONSTRAINT book_name_key UNIQUE (name)
);

CREATE INDEX book_name_like ON book (name text_pattern_ops);


Case 2:



CREATE TABLE book (
id SERIAL PRIMARY KEY,
name text NOT NULL
);

CREATE UNIQUE INDEX book_name_like ON book (name text_pattern_ops);


Update: As @Colin'tHart pointed out, these 2 cases aren't the same. I should have posted this question without the use of lower() expression. In that case, my understanding is that a CREATE UNIQUE INDEX would be better than a unique constraint and a simple index.





Aucun commentaire:

Enregistrer un commentaire