PostgreSQL LIKE 查询性能变化

Question

我已经看到关于 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。

原文由 Jason 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

FTS 不支持 LIKE

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

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

LIKE

安装附加模块 pg_trgm 它为 GIN 和 GiST trigram 索引 提供运算符类以支持 所有 LIKE 和 ILIKE 模式，而不仅仅是左锚模式：

示例索引：

 CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops );

或者：

在 tbl 上创建索引 tbl_col_gist_trgm_idx 使用gist (col gist_trgm_ops );

• GiST和GIN索引的区别

示例查询：

 SELECT * FROM tbl WHERE col LIKE '%foo%'; -- 前导通配符
SELECT * FROM tbl WHERE col ILIKE '%foo%'; -- 也可以不区分大小写

八卦？较短的字符串呢？

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

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

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

对于 LIKE 和正则表达式搜索，请记住，没有可提取三元组的模式将退化为全索引扫描。

意思是，索引/位图索引扫描仍然有效（准备好的语句的查询计划不会中断），它只是不会给你带来更好的性能。通常没有大的损失，因为 1 或 2 字母字符串几乎没有选择性（超过基础表匹配的百分之几），并且索引支持一开始不会提高性能，因为全表扫描更快。

前缀匹配

^@ 运算符 / starts_with() 函数

引用 Postgres 11 的发行说明：

添加前缀匹配运算符文本 ^@ 文本，由 SP-GiST (Ildus Kurbangaliev) 支持

这类似于将 var LIKE ‘word%’ 与 btree 索引一起使用，但效率更高。

但是， 在 Postgres 15 中改进了规划器支持 并正确 记录了 ^@ 运算符 之前，运算符和功能的潜力仍然有限。 发行说明：

如果使用 C 排序规则，则允许 ^@ 以运算符和 starts_with() 函数使用 btree 索引 (Tom Lane)

以前这些只能使用 SP-GiST 索引。

COLLATE "C"

从 Postgres 9.1 开始，带有 COLLATE "C" 的索引提供了与下面描述的操作符类 text_pattern_ops 相同的功能。另见：

• text_pattern_ops 和 COLLATE “C” 之间有区别吗？

text_pattern_ops （原答案）

对于 左锚 模式（没有前导通配符），您可以通过合适的 运算符类 为 btree 索引获得最佳值： text_pattern_ops 或 varchar_pattern_ops 。这两个标准 Postgres 的内置功能，不需要额外的模块。性能相似，但索引小得多。

示例索引：

创建索引 tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops );

示例查询：

 SELECT * FROM tbl WHERE col LIKE 'foo%' ; -- 没有前导通配符

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

---

进一步阅读

• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正则表达式进行模式匹配
• LIKE 是如何实现的？
• 使用 PostgreSQL 快速查找相似字符串

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

当您在具有 LIKE、ILIKE、upper、lower 等功能的列上使用子句时，postgres 不会考虑您的正常索引。它将通过每一行对表进行全面扫描，因此速度会很慢。

正确的方法是根据您的查询创建一个新索引。例如，如果我想匹配不区分大小写的列，而我的列是 varchar。然后你可以这样做。

 create index ix_tblname_col_upper on tblname (UPPER(col) varchar_pattern_ops);

同样，如果您的专栏是文本，那么您可以执行以下操作

create index ix_tblname_col_upper on tblname (UPPER(col) text_pattern_ops);

同样，您可以将函数 upper 更改为您想要的任何其他函数。

原文由 Omer Farooq 发布，翻译遵循 CC BY-SA 4.0 许可协议