sql - 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。

Answer 1

FTS 不支持LIKE

previously accepted answer是不正确的。 Full Text Search其全文索引对于 LIKE 运算符根本不，它有自己的运算符并且不适用于任意字符串。它根据字典和词干对 词 进行操作。它确实支持单词的前缀匹配，但不支持 LIKE 运算符:

• Get partial match from GIN indexed TSVECTOR column

LIKE的三元组索引

安装附加模块 pg_trgm它为 GIN and GiST trigram indexes 提供运算符类支持所有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);

• Difference between GiST and GIN index

示例查询:

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

八卦？更短的字符串怎么样？

索引值中少于 3 个字母 的词仍然有效。 The manual:

Each word is considered to have two spaces prefixed and one spacesuffixed when determining the set of trigrams contained in the string.

搜索少于 3 个字母的模式？ The manual:

For both LIKE and regular-expression searches, keep in mind that apattern with no extractable trigrams will degenerate to a full-index scan.

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

前缀匹配

没有前导通配符的搜索模式:col LIKE 'foo%'。

^@ 运算符/starts_with() 函数

引用release notes of Postgres 11 :

Add prefix-match operator text ^@ text, which is supported by SP-GiST(Ildus Kurbangaliev)

This is similar to using var LIKE 'word%' with a btree index, but itis more efficient.

示例查询:

SELECT * FROM tbl WHERE col ^@ 'foo';  -- no added wildcard

但是运算符和函数的潜力在 planner support is improved in Postgres 15 之前都是有限的和 ^@ operator is documented适本地。 release notes :

Allow the ^@ starts-with operator and the starts_with() function touse btree indexes if using the C collation (Tom Lane)

Previously these could only use SP-GiST indexes.

整理“C”

从 Postgres 9.1 开始，带有 COLLATE "C" 的索引提供与下面描述的运算符类 text_pattern_ops 相同的功能。见:

• Is there a difference between text_pattern_ops and COLLATE "C"?

text_pattern_ops(原始答案)

对于左锚定 模式(无前导通配符)，您可以使用合适的 operator class 获得最佳效果。对于 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 索引就可以完成这项工作。

---

进一步阅读

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
• How is LIKE implemented?
• Finding similar strings with PostgreSQL quickly