Today Seven Databases in Seven Weeks introduced me to per-operator indexes.
You can index strings for pattern matching the previous queries by creating a
text_pattern_opsoperator class index, as long as the values are indexed in lowercase.
CREATE INDEX moves_title_pattern ON movies ( (lower(title) text_pattern_ops);
We used the
text_pattern_opsbecause the title is of type text. If you need to index varchars, chars, or names, use the related ops:
I find the example really confusing. Why is it useful to do this?
If the column is type text, wouldn't the other types (varchar, char, name) be cast to to text before being used as a search value?
How does that index behave differently from one using the default operator?
CREATE INDEX moves_title_pattern ON movies (lower(title));