FTS不支持LIKE
之前被接受的答案是错误的。全文搜索及其全文索引根本不适用于LIKE
运算符,它有自己的运算符,并且不能处理任意字符串。它基于字典和词干处理单词,支持单词的前缀匹配,但不使用LIKE
运算符:
LIKE
的Trigram索引
安装额外的模块
pg_trgm
,该模块为
GIN和GiST三元组索引提供了操作符类,以支持
所有LIKE
和ILIKE
模式,而不仅仅是左锚定的模式:
示例索引:
CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);
或者:
CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);
示例查询:
SELECT * FROM tbl WHERE col LIKE 'foo%';
SELECT * FROM tbl WHERE col LIKE '%foo%'; -- works with leading wildcard, too
SELECT * FROM tbl WHERE col ILIKE '%foo%'; -- works case insensitively as well
三元组?那么短字符串呢?
在索引值中,长度小于3个字母的单词仍然有效。手册:
在确定字符串中包含的三元组集合时,每个单词都被认为有两个前缀空格和一个后缀空格。
那么少于3个字母的搜索模式呢?手册:
对于LIKE
和正则表达式搜索,要记住没有可提取的三元组的模式将退化为完全索引扫描。
这意味着索引/位图索引扫描仍然有效(针对预处理语句的查询计划不会中断),只是不能提供更好的性能。通常情况下,这并不是很重要,因为一个或两个字母的字符串几乎没有选择性(与底层表的百分比匹配超过几个百分点),而且索引支持也不会显著改善性能,因为全表扫描更快。
前缀匹配
无前置通配符的搜索模式:col LIKE 'foo%'
。
^@
运算符 / starts_with()
函数
引用Postgres 11发行说明中的内容:
添加了前缀匹配运算符text ^@ text,该运算符由SP-GiST支持
(Ildus Kurbangaliev)
这类似于在B-tree索引中使用 var LIKE 'word%',但效率更高。
示例查询:
SELECT * FROM tbl WHERE col ^@ 'foo'; -- 无额外通配符
但是,直到改进了Postgres 15中的计划支持之前,操作员和函数的潜力仍然有限。并且必须正确记录^@
运算符。具体细节请参考发行说明。
允许使用索引来使用^@
开头运算符和starts_with()
函数,如果使用C排序规则(Tom Lane)
以前只能使用SP-GiST索引。
COLLATE "C"
自从Postgres 9.1以来,使用COLLATE "C"
的索引提供了与下面描述的text_pattern_ops
操作符类相同的功能。请参见:
示例索引:
CREATE INDEX tbl_col_text_collate_c_idx ON tbl(col COLLATE "C");
text_pattern_ops
(原始答案)
对于只有左锚定模式(没有前导通配符),您可以通过适当的操作符类来获得btree索引的最佳效果:text_pattern_ops
或varchar_pattern_ops
。这是标准Postgres的内置功能,不需要额外的模块。性能相似,但索引更小。
示例索引:
CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);
示例查询:
SELECT * FROM tbl WHERE col LIKE 'foo%'; -- 没有前导通配符
或者,如果您应该使用“C”区域设置(实际上没有区域设置),那么一切都按字节顺序排序,并且带有默认操作符类的普通B树索引可以完成工作。
进一步阅读
-
使用LIKE、SIMILAR TO或正则表达式进行模式匹配
-
LIKE是如何实现的?
-
快速在PostgreSQL中查找相似字符串
pg_trgm
时,您需要至少长度为3个字符的查询字符串,例如fo%
不会命中索引,而是执行扫描。需要注意的一点。 - Tuukka Mustonen^@
运算符和starts_with()
字符串函数能否获得与左锚定的LIKE
相同的优势? - Константин Ван