如何在PostgreSQL中结合全文搜索排序与pg_trgm模糊匹配能力？

问题根源 #

pg_trgm的similarity函数基于整个字符串的三元组匹配占比计算得分，因此会出现以下不合理情况：

• 短查询词匹配长文档时，匹配的三元组占比被稀释，得分骤降
• 仅统计整体字符串的匹配度，不区分文档内是否包含精确匹配的词

而PostgreSQL内置的全文搜索（ts_rank）虽能根据词的存在和权重合理排序长文档，但不支持模糊匹配，无法识别拼写相近的词（如Foot和Foo）。

解决方案 #

通过混合两种机制的得分或使用词级匹配的trgm函数，可以同时兼顾全文搜索的排序能力和pg_trgm的模糊匹配能力：

1. 用word_similarity替代similarity（PostgreSQL 13+）

word_similarity是词级别的三元组匹配，会单独计算查询词与文档中每个词的相似度，取最高值作为结果，完美解决长文档得分被稀释的问题：

-- 示例查询
SELECT 
  word_similarity('Foo', 'Foo Bar Ball Bob Beast Baby Boy') AS exact_long_doc_score,
  word_similarity('Foo', 'Foot Bar') AS fuzzy_short_doc_score;

结果中前者得分接近1.0（因包含精确匹配的Foo词），后者约为0.75，符合直觉排序逻辑。

2. 混合全文搜索得分与trgm得分

将ts_rank的全文排序能力和trgm的模糊匹配能力结合，通过加权计算组合得分，兼顾精确匹配权重和模糊匹配能力：

-- 组合得分查询示例
SELECT 
  doc,
  -- 加权比例可根据业务需求调整
  (ts_rank(to_tsvector('english', doc), to_tsquery('english', 'foo')) * 0.7) + 
  (word_similarity('foo', doc) * 0.3) AS combined_score
FROM documents
-- 先用trgm模糊匹配过滤候选集（可利用gin_trgm_ops索引加速）
WHERE doc % 'foo'
ORDER BY combined_score DESC;

3. 索引优化

为保证查询性能，需创建对应索引：

• 针对trgm模糊匹配：

  CREATE INDEX idx_doc_trgm ON documents USING gin (doc gin_trgm_ops);
  

• 针对全文搜索：

  CREATE INDEX idx_doc_tsvector ON documents USING gin (to_tsvector('english', doc));
  

额外优化 #

如果需要更精细的匹配规则，可自定义加权逻辑，比如对包含精确匹配词的文档额外加分：

SELECT 
  doc,
  (ts_rank(to_tsvector('english', doc), to_tsquery('english', 'foo')) * 0.7) + 
  (word_similarity('foo', doc) * 0.2) +
  -- 精确匹配词额外加0.1分
  CASE WHEN doc ~* '\mFoo\M' THEN 0.1 ELSE 0 END AS combined_score
FROM documents
WHERE doc % 'foo'
ORDER BY combined_score DESC;

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