PostgreSQL 10 High Performance - Third Edition

Text operator classes

It's possible to use an index on a text field to speed finding rows that start with a substring of interest. A query like this that uses LIKE to match the start of a string:

SELECT * FROM t WHERE t.s LIKE 'start%';  

It can use an index on that string field to return answers more quickly. This is not useful if your comparison tries to match the middle or end of the column though. In addition, if your database uses something other than the simple C locale, the default way values are compared in indexes, can't be used for this purpose. You'll need to create the index using a special mode for locale sensitive character by character comparisons, like the following:

CREATE INDEX i ON t (s text_pattern_ops);  

In this example, ...