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_ops
operator 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_ops
because the title is of type text. If you need to index varchars, chars, or names, use the related ops:varchar_pattern_ops
,bpchar_pattern_ops
, andname_pattern_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));