postgresql LIKE查询在全表子集上操作时的性能影响

ou6hu8tu 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(162)

我知道LIKE查询很慢，因为它们不能被索引。然而，我很好奇在这样的情况下性能会受到什么影响：
假设我有一张table，像这样：

user_id  |  message 
-------------------
   1     |  foo bar baz
   1     |  bar buz qux
   .     .      .
   .     .      .
   2     |  bux bar foo
   2     |  bar

字符串
这里我假设有100万行，但是有10，000个用户，所以每个用户大约有100条消息。
显然，搜索类似于：

SELECT * FROM table WHERE message like '%ar%';

型
会非常缓慢。但是在我的应用程序中，我只会搜索用户的消息：

SELECT * FROM table WHERE message like '%ar%' AND user_id = 2;

型
其中user_id列将被索引。
在这样的场景中，Postgres只会在使用索引的user_id列后对用户执行缓慢的LIKE查询，而不是整个表-从而限制了我的性能，我的想法是正确的吗？
而且，这样的查询在1000万或1亿用户的情况下不会明显变慢，只要任何一个用户只有~100条消息？

postgresql

来源：https://stackoverflow.com/questions/9820526/performance-implication-of-like-query-when-operating-on-a-subset-of-full-table

2条答案

按热度按时间

pjngdqdw1#

MatBailie已清除您的主要问题。我想谈谈你的一个主张：
我知道LIKE查询很慢，因为它们不能被索引。
这并不完全正确。

首先，这已经是事实了很长一段时间，左锚定模式*可以 * 使用索引。这适用于正则表达式（~）以及LIKE（~~）和SIMILAR TO。我最近在dba.se上就此事写了一篇全面的评论：

Pattern matching with LIKE, SIMILAR TO or regular expressions的

这可能对你不起作用，因为你的问题中的模式是“没有锚定”的。如果是这样，您可以使用multicolumn index来优化性能，该multicolumn index将text pattern operator class text_pattern_ops用于message列，如下所示：

CREATE INDEX tbl_user_id_message_idx ON tbl (user_id, message text_pattern_ops);

字符串
对于查询，如：

SELECT *
FROM   tbl
WHERE  user_id = 2
AND    message ~~ 'bar%'; -- left anchored LIKE

型

其次 *，从PostgreSQL 9.1开始，您可以使用pg_trgm扩展并使用它创建GIST或GIN索引，所有模式都可以使用。存在一些限制。这种索引的维护成本更高，因此它对于只读或很少写入的表最有用。详细信息：
PostgreSQL LIKE查询性能变化

Depesz有一个相关的tutorial。

赞(0）回复(0）举报 2023-08-04

8cdiaqws2#

优化器在将SQL编译成计划时确定许多事情。
其中之一是如何在逐行应用其他条件之前过滤数据（使用索引查找等）。
在您的情况下，如果您有一个合适的索引，LIKE将只应用于过滤完成后的记录。
要了解更多信息，请获取由查询创建的计划。您应该能够看到索引用于子集/过滤数据的位置，然后是应用LIKE条件的单独步骤。

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql LIKE查询在全表子集上操作时的性能影响

2条答案

相关问题

热门标签

最新问答