如何优化Postgres pg_trgm文本相似度，提升相似文本排序优先级？

问题背景 #

使用Postgres的pg_trgm扩展基于三元组匹配新闻标题相似度时，出现排序不合理的情况：相关标题（如《What We Know About the Plane Crash》）相似度低于无关标题，甚至无关标题排名更靠前；使用<->距离操作符排序时，完全不相关的标题也排在相关内容前面。

优化方法 #

• 提高相似度阈值，减少噪声结果
  当前设置的set_limit(0.17)阈值过低，会引入大量低相似度的无关文本，干扰排序逻辑。建议先提高阈值（比如0.3~0.5，可根据实际数据调整），先过滤掉明显不相关的内容：

  SELECT set_limit(0.3); -- 调整阈值
  SELECT
      similarity(title, 'A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash') AS similarity,
      title
  FROM RECORD
  WHERE title % 'A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash'
  ORDER BY title <-> 'A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash' ASC;
  

  排序时优先用<->（三元组距离，值越小越相似），比单纯按similarity排序的稳定性更好。

• 预处理文本，去除停用词干扰
  pg_trgm会把通用停用词（如a、in、to、the）的三元组纳入计算，这些词对语义相似度没有帮助，反而会稀释关键信息的权重。可以自定义函数去除停用词：

  -- 创建去除停用词的函数
  CREATE OR REPLACE FUNCTION remove_stopwords(input_text text) RETURNS text AS $$
  SELECT array_to_string(
      array_remove(
          string_to_array(lower(trim(input_text)), ' '),
          unnest(ARRAY['a','an','the','in','to','for','of','on']) -- 可根据需求扩展停用词列表
      ),
      ' '
  );
  $$ LANGUAGE sql IMMUTABLE;
  

  然后基于处理后的文本计算相似度：

  SELECT
      similarity(remove_stopwords(title), remove_stopwords('A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash')) AS similarity,
      title
  FROM RECORD
  WHERE remove_stopwords(title) % remove_stopwords('A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash')
  ORDER BY remove_stopwords(title) <-> remove_stopwords('A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash') ASC;
  

• 使用单词相似度函数，侧重完整词匹配
  Postgres 11+支持word_similarity函数，它更关注完整单词的重叠，而非任意三元组，能有效提升语义相关内容的排名。搭配<%操作符（单词相似度匹配）使用：

  SELECT
      word_similarity(title, 'A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash') AS similarity,
      title
  FROM RECORD
  WHERE title <% 'A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash'
  ORDER BY similarity DESC;
  

• 给关键短语加权，强化语义关联
  提取目标标题中的核心短语（如"Plane Crash"、"Skating Club"、"Massachusetts"），给包含这些短语的结果额外加权，让语义更相关的内容排名更靠前：

  SELECT
      similarity(title, 'A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash') AS similarity,
      title,
      CASE
          WHEN title LIKE '%Plane Crash%' THEN 0.4
          WHEN title LIKE '%Skating Club%' THEN 0.3
          WHEN title LIKE '%Massachusetts%' THEN 0.2
          ELSE 0
      END AS keyword_weight
  FROM RECORD
  WHERE title % 'A Skating Club in Massachusetts Gathered to Grieve Members Killed in Plane Crash'
  ORDER BY (similarity + keyword_weight) DESC, created_at DESC;
  

• 创建优化后的索引
  如果数据量较大，基于预处理后的文本创建GIN/GIST索引，既能提升查询效率，也能让相似度计算和排序更精准：

  CREATE INDEX idx_record_title_trgm ON RECORD USING GIN (remove_stopwords(title) gin_trgm_ops);
  

内容的提问来源于stack exchange，提问作者sudoExclamationExclamation