Postgres 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?
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.
Based on this, if I want an index with an operator class (e.g. text_pattern_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 a different 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);
Best Answer
For a moment I thought one might be able to use a pre-existing
text_pattern_ops
index with theUSING INDEX
clause when adding aUNIQUE CONSTRAINT
. But that fails, because:Per documentation:
For instance, a unique index like that would allow a FK constraint referencing it, but perform terribly, because it does not support standard operators.
Per documentation:
So to answer the question:
If you need a
UNIQUE CONSTRAINT
(among other reasons: to reference it with a FK), your first variant with constraint and index is the only option. Additionally, the default operator class of the index created by the constraint supports more operations (like sorting in default sort order).If you don't need any of that go with your second variant because, obviously, just one index is cheaper to maintain: just a
UNIQUE text_pattern_ops
index.Differences between index and constraint:
Alternative with COLLATE "C"
Instead of creating two indexes, there is another alternative for
xxx
_pattern_ops
indexes that may be preferable. The documentation:And:
You can create the column without collation (using
COLLATE "C"
). Then the default operator class behaves the same way astext_pattern_ops
would - plus the index can be used with all the standard operators.Now,
LIKE
can use the index:But
ILIKE
still can't:SQL Fiddle.
Consider a trigram index using the additional module pg_trgm for a more general solution.: