PostgreSQLLike 查询性能变化

关于对数据库中某个特定表的 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。

120505 次浏览
小开

可能快速的模式是锚定模式，像这样区分大小写的模式可以使用索引。例如，在匹配字符串的开头没有通配符，因此执行器可以使用索引范围扫描。(文件中的相关评论在这里)除非你专门为此目的创建一个索引，否则更低和更相似的索引也将失去你使用索引的能力(参见 功能性索引)。

如果要在字段中间搜索字符串，应该查看 全文或 三角形索引。其中一个是在 Postgres 核心，另一个是可用的贡献模块。

小开

您的 like 查询可能无法使用您创建的索引，因为:

1)您的 LIKE 条件以通配符开始。

2)您已经使用了带有 LIKE 条件的函数。

小开

您可以在 PostgreSQL 中安装 狂野速度，这是另一种类型的索引。Wildspeed 可以使用% word% 通配符，没有问题。缺点是指数的大小，这可能很大，非常大。

小开
最佳答案

FTS 不支持 LIKE

以前接受的答案不正确。全文检索的全文索引对于 LIKE操作符来说就是 没有，它有自己的操作符，并且不适用于任意字符串。它在基于字典和词干的 文字上运行。它支持 单词前缀匹配，但不支持 LIKE操作员:

LIKE的三角形索引

安装额外的模块 强 > pg_trgm强，它为 GIN 和 GST 三元索引提供操作符类来支持 所有的 ABC1和 ILIKE模式，而不仅仅是左锚定的操作符类:

示例索引:

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

Or:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

示例查询:

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和正则表达式搜索，请记住 没有可提取的三元模式将退化为全索引扫描。

这意味着，索引/位图索引扫描仍然可以工作(准备语句的查询计划不会中断) ，只是不会为您带来更好的性能。通常不会有大的损失，因为1或2个字母的字符串几乎没有选择性(超过底层表匹配的百分之几) ，而且索引支持一开始就不会提高性能(很多) ，因为全表扫描更快。

前缀匹配

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

^@操作符/starts_with()函数

引用 Postgres 11 发行说明的话:

添加前缀匹配操作符 text ^@text，这是 SP-GiST 所支持的 (Ildus Kurbangaliev)

这类似于在 btree 索引中使用 var LIKE‘ word%’，但是它 更有效率。

示例查询:

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

But the potential of operator and function stays limited until planner support is improved in Postgres 15 and the ^@ operator is documented properly. The release notes:

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

Previously these could only use SP-GiST indexes.

COLLATE "C"

Since Postgres 9.1, an index with COLLATE "C" provides the same functionality as the operator class text_pattern_ops described below. See:

text_pattern_ops (original answer)

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.

Example 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

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.

Further reading

小开

请执行下面提到的查询，以提高 postgreql 中的 LIKE 查询性能。 为更大的表创建这样的索引:

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

值得一提的是，姜戈 ORM 倾向于对所有 LIKE查询使用 UPPER(text)，以使其不区分大小写,

在 UPPER(column::text)上添加一个索引极大地加快了我的系统，不像其他任何事情。

至于前导% ，是的，它不会使用索引:

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

小开

我最近遇到了一个类似的问题，有一个包含200000条记录的表，我需要重复执行 LIKE 查询。在我的例子中，正在搜索的字符串是固定的。其他领域各不相同。因为这样，我才能重写:

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

作为

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
小开

当你在一个列上使用子句的时候，例如，LIKE，ILIKE，top，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);

类似地，您可以将上面的函数更改为所需的任何其他函数。


提交答案