postgresql 优化Postgres以搜索未知长度的子字符串

6tr1vspr 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(143)

我有一个名为message的character varying列，在一个表messages中存储来自IRC频道的用户消息。通过聊天机器人，我允许用户搜索某个术语被键入了多少次。这个术语可以是任何东西：一个字符，一个单词或多个单词。该表有大约1500万行，查询时间可能很长。
我使用以下查询来查找所有匹配term的不区分大小写的子字符串：

select sum(array_length(string_to_array(LOWER(message), LOWER('term')), 1) -1) from messages;

字符串
查询使用顺序扫描。当我set enable_seqscan = off;它使用btree索引，我也在表上。表也有一个三元组索引，但它从来没有得到使用。
您将从什么Angular 来提高查询性能？
我使用Postgres 14.9

postgresql

来源：https://stackoverflow.com/questions/77401794/optimize-postgres-for-searching-substrings-of-unknown-length

1条答案

按热度按时间

46qrfjad1#

你能为这个应用程序使用PostgreSQL优化吗？可以，但不是你组织它的方式。
首先计算message列包含任意用户提供的搜索词的messages行。

SELECT COUNT(*) FROM messages WHERE message ILIKE '%term%'

字符串
然后，在你想要搜索的列上创建一个所谓的trigram index。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY message_text ON messages
  USING GIN (message gin_trgm_ops);

型
这个索引设置是PostgreSQL独有的，它加速了LIKE和ILIKE predicate 。
此技术返回包含搜索词的 * 一个或多个 * 占用项的消息的数量，而您的需求要求返回搜索词的占用项的 * 总数量 *。要获得总数量，您可以使用子查询来过滤包含搜索词的任何占用项的消息，然后对它们进行计数。这将比搜索所有消息更快。

select sum(array_length(string_to_array(LOWER(message), LOWER('term')), 1) -1)
  from (
     SELECT message FROM messages WHERE message ILIKE '%term%'
  ) subset

型
如果这是我的应用程序，在投入生产之前，我会禁止搜索短于三个或四个字母的词，我甚至会创建一个不允许的停用词表。这是因为这些查询在返回大量计数时会减慢速度。有人可以使用短搜索词，如'e'，拒绝为您的用户提供服务。

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 优化Postgres以搜索未知长度的子字符串

1条答案

相关问题

热门标签

最新问答