在存储来自IRC通道的用户消息的表messages中，我有一个名为message的character varying列.有了聊天机器人，我允许用户搜索某个词被输入了多少次.这个术语可以是任何东西:一个字符、一个单词或多个单词.该表有大约1,500万行，查询时间可能会很长.
我使用以下查询来查找匹配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优化吗？是的，但不是你组织的方式.
首先计算messages行，其中第message列包含任意用户提供的搜索词.您可以使用此查询来执行此操作.
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谓词.
该技术返回包含one or more个搜索词的消息数量，而您的需求要求返回搜索词的total number个匹配项.要获得总数，您可以使用子查询来筛选出现该术语的任何消息，然后对这些消息进行计数.这将比搜索所有消息更快.
select sum(array_length(string_to_array(LOWER(message), LOWER('term')), 1) -1)
from (
SELECT message FROM messages WHERE message ILIKE '%term%'
) subset
如果这是我的应用程序，在投入生产之前，我会禁止搜索少于三四个字母的词条，我甚至可能会创建一个不允许的停用词的表格.这是因为当这些查询返回大量计数时，它们会减慢速度.有人可能会使用像'e'这样的短搜索词来拒绝向您的用户提供服务.