PostgreSQL LIKE 查询性能变化 [英] PostgreSQL LIKE query performance variations

问题描述

我发现对于数据库中特定表的 LIKE 查询的响应时间有很大差异.有时我会在 200-400 毫秒内得到结果(非常可接受)，但有时可能需要多达 30 秒才能返回结果.

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 seconds to return results.

我知道 LIKE 查询非常占用资源，但我只是不明白为什么响应时间会有如此大的差异.我在 owner1 字段上建立了一个 btree 索引，但我认为它对 LIKE 查询没有帮助.有人有什么想法吗?

I understand that LIKE queries are very resource intensive but I just don't understand why there would be such a large difference in response times. I have built a btree index on the owner1 field but I don't think it helps with LIKE queries. Anyone have any ideas?

示例 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.

With similar results.
Table Row Count: about 95,000.

推荐答案

FTS不支持LIKE

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

FTS does not support LIKE

The previously accepted answer was incorrect. Full Text Search with its full text indexes is not for the LIKE operator at all, it has its own operators and doesn't work for arbitrary strings. It operates on words based on dictionaries and stemming. It does support prefix matching for words, but not with the LIKE operator:

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

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

Install the additional module pg_trgm which provides operator classes for GIN and GiST trigram indexes to support all LIKE and ILIKE patterns, not just left-anchored ones:

示例索引:

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 个字母的词仍然有效.手册:

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

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

以及少于 3 个字母的搜索模式?手册:

And search patterns with less than 3 letters? The manual:

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

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

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

Meaning, that index / bitmap index scans still work (query plans for prepared statement won't break), it just won't buy you better performance. Typically no big loss, since 1- or 2-letter strings are hardly selective (more than a few percent of the underlying table matches) and index support would not improve performance to begin with, because a full table scan is faster.

对于左锚模式(无前导通配符)，您可以使用合适的运算符类，用于 btree 索引:text_pattern_ops 或 varchar_pattern_ops.标准 Postgres 的两个内置功能，不需要额外的模块.性能相似，但索引要小得多.

For just left-anchored patterns (no leading wildcard) you get the optimum with a suitable operator class for a btree index: text_pattern_ops or varchar_pattern_ops. Both built-in features of standard Postgres, no additional module needed. Similar performance, but much smaller index.

示例索引:

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 索引可以完成这项工作.

Or, if you should be running your database with the 'C' locale (effectively no locale), then everything is sorted according to byte order anyway and a plain btree index with default operator class does the job.

dba.SE 上这些相关答案中的更多详细信息、解释、示例和链接:

More details, explanation, examples and links in these related answers on dba.SE:

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

这篇关于PostgreSQL LIKE 查询性能变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！