PostgreSQL LIKE查询性能变化

2018-07-01 06:39:18

在我的数据库中的某个特定表的LIKE查询的响应时间方面，我看到了相当大的变化。有时我会在200-400毫秒（非常可接受）内得到结果，但其他时间可能需要30秒才能返回结果。

我知道LIKE查询是非常耗费资源的，但我不明白为什么响应时间会有如此之大的差异。我已经在owner1字段上构建了一个btree索引，但我认为这对LIKE查询没有帮助。有人有主意吗？

示例SQL：

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

我也试过：

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

和：

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

具有类似的结果。
表行数：约95,000。

以前接受的答案是不正确的。具有全文索引的全文本搜索根本不适用于LIKE运算符，它具有自己的运算符并且不适用于任意字符串。它根据词典和词干进行操作。它支持词的前缀匹配，但不支持LIKE运算符：

从GIN索引TSVECTOR列获取部分匹配

`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);

GiST和GIN指数的区别

示例查询：

SELECT * FROM tbl WHERE col LIKE '%foo%';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitively as well

索引值小于3个字母的单词仍然有效。手册：

确定字符串中包含的三元组集合时，每个单词都被认为有两个空格前缀和一个空格后缀。

和少于3个字母的搜索模式？手册：

对于LIKE和正则表达式搜索，请记住，不具有可抽取卦的模式将退化为全索引扫描。

这意味着，索引/位图索引扫描仍然有效（准备好的语句的查询计划不会中断），它只是不会为您带来更好的性能。通常没有什么大的损失，因为1或2个字母的字符串几乎没有选择性（超过底层表的几个百分比匹配），并且索引支持不会提高性能，因为全表扫描速度更快。

对于左锚定模式（无引导通配符），您可以使用适合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%';  -- no leading wildcard

或者，如果您应该使用'C'语言环境（实际上没有语言环境）来运行数据库，那么无论如何都按字节顺序对所有内容进行排序，而使用默认操作符类的普通btree索引完成这项工作。

有关dba.SE的相关解答中的更多细节，解释，示例和链接：

在PostgreSQL中与LIKE，SIMILAR TO或正则表达式匹配的模式

LIKE是如何实现的？

快速找到与PostgreSQL类似的字符串

可能快速的那些是锚定模式，并且可以使用索引来区分大小写。即在匹配字符串的开头没有通配符，因此执行者可以使用索引范围扫描。（文档中的相关评论在此处）除非您专门为此目的创建索引（请参阅功能索引），否则较低和ilike也将失去使用该索引的能力。

如果要在字段中间搜索字符串，则应查看全文或三字节索引。首先是在Postgres核心，另一个在contrib模块中可用。

您可以在PostgreSQL中安装Wildspeed，这是一种不同类型的索引。 Wildspeed可以使用％word％通配符，没问题。缺点是指数的大小，这可能很大，非常大。

链接地址: http://www.djcxy.com/p/87231.html