PostgreSQL中LIKE查询性能变化

Question

我发现在我的数据库中，针对特定表格的LIKE查询响应时间变化很大。有时候我能在200-400毫秒内收到结果（非常可接受），但有时可能需要长达30秒才能返回结果。

我知道LIKE查询会消耗大量资源，但我不明白为什么响应时间会有如此大的差异。我已经在owner1字段上建立了B树索引，但我认为它对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

Answer 1

FTS不支持LIKE

之前被接受的答案是错误的。全文搜索及其全文索引根本不适用于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%';
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操作符类相同的功能。请参见：

• text_pattern_ops和COLLATE "C"之间有什么区别？

示例索引：

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中查找相似字符串

- Erwin Brandstetter

---

如果我理解得正确，使用 pg_trgm 时，您需要至少长度为3个字符的查询字符串，例如 fo％ 不会命中索引，而是执行扫描。需要注意的一点。 - Tuukka Mustonen

@TuukkaMustonen：说得好。嗯，（位图）索引扫描仍然有效，只是它们不会为您带来更好的性能。我在上面添加了一些澄清。 - Erwin Brandstetter

^@ 运算符和 starts_with() 字符串函数能否获得与左锚定的 LIKE 相同的优势？ - Константин Ван

@КонстантинВан：实际上他们在Postgres 15（目前是RC版本）中确实这样做。请考虑更新我的回答。 - Erwin Brandstetter

@Sido4odus 三元组索引也可以解决这个问题。或者更高效的方法是在反转字符串列上创建表达式索引，只针对这种情况。请参见：https://stackoverflow.com/a/62785629/939860 或 https://dev59.com/i8Hqa4cB1Zd3GeqPznDi#68402137。或者您可以提出一个新问题，说明您的具体情况。 - Erwin Brandstetter

Answer 2

可能那些快速的模式是大小写敏感的锚定模式，可以使用索引。例如，匹配字符串的开头没有通配符，因此执行器可以使用索引范围扫描。(有关该文档的相关注释，请参见这里)Lower和ilike也会失去使用索引的能力，除非您专门为此创建一个索引(请参见函数索引)。

如果您想要在字段的中间搜索字符串，则应该查看全文搜索(textsearch)或三元组索引(trigram)。前者是Postgres核心功能，后者可在contrib模块中获得。

- Ants Aasma

---

我之前没有考虑过在字段的小写值上创建索引。这样，在查询之前，我可以在后端将查询文本转换为小写。 - Jason

Answer 3

我最近遇到了一个类似的问题，需要对包含 200000 条记录的表进行重复的 LIKE 查询。在我的情况下，被搜索的字符串是固定的，而其他字段则不同。因此，我能够重写查询语句：

SELECT owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%');

as

CREATE INDEX ix_parcels ON parcels(position(lower('someones name') in lower(owner1)));

SELECT owner1 FROM parcels
WHERE position(lower('someones name') in lower(owner1)) > 0;

当查询结果快速返回并通过EXPLAIN ANALYZE验证索引正在使用时，我感到非常高兴：

 Bitmap Heap Scan on parcels  (cost=7.66..25.59 rows=453 width=32) (actual time=0.006..0.006 rows=0 loops=1)
   Recheck Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
   ->  Bitmap Index Scan on ix_parcels  (cost=0.00..7.55 rows=453 width=0) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
 Planning time: 0.075 ms
 Execution time: 0.025 ms

- Stephen Quan

Answer 4

你可以安装Wildspeed，这是PostgreSQL中不同类型的索引。Wildspeed可以使用%word%通配符，没有问题。缺点是索引的大小可能非常大。

- Frank Heikens

Answer 5

无论何时在列上使用带有函数的子句，例如LIKE、ILIKE、upper、lower等，PostgreSQL都不会考虑您的普通索引。它将对表进行全面扫描，逐行处理每个行，因此速度较慢。正确的方法是根据查询创建新索引。例如，如果我想匹配一个不区分大小写的列，并且我的列是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

Answer 6

请执行以下查询来提高PostgreSQL中LIKE查询的性能。 对于较大的表，请创建如下索引：

CREATE INDEX <indexname> ON <tablename> USING btree (<fieldname> text_pattern_ops)

- Noyal

---

只有在模式不以通配符开头时才有效-在这种情况下，前两个示例查询都以通配符开头。 - cbz

Answer 7

就这个问题而言，Django ORM倾向于对所有LIKE查询使用UPPER(text)进行大小写不敏感的处理。

在UPPER(column::text)上添加索引极大地加快了我的系统速度，这是其他任何方法都无法比拟的。

至于前导%，是的，它不会使用索引。请参阅此博客以获得很好的解释：

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

- MrE

Answer 8

你的LIKE查询可能无法使用你创建的索引，因为：

1）你的LIKE条件以通配符开头。

2）你在LIKE条件中使用了函数。

- Asaph