postgres 对于文本匹配的索引

参考postgres文档https://www.postgresql.org/docs/current/indexes-opclass.html

索引创建格式

CREATE INDEX _`name`_ ON _`table`_ (_`column`_ _`opclass`_ [ ( _`opclass_options`_ ) ] [_`sort options`_] [, ...]);

创建支持字符串模式匹配的索引

The operator classes `text_pattern_ops`, `varchar_pattern_ops`, and `bpchar_pattern_ops` support B-tree indexes on the types `text`, `varchar`, and `char` respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (`LIKE` or POSIX regular expressions) when the database does not use the standard “C” locale. As an example, you might index a `varchar` column like this:

即字符串的模式比较是支持B-tree索引的，所以在创建的时候增加比较器即可

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);