PostgreSQL全文检索返回过多无关结果且性能低下如何优化

问题根源 #

PostgreSQL默认english_stem字典使用的Porter词干提取算法会将animal、anime、animation统一提取为词根anim，属于算法的固有特性，与现有配置无关。

可选优化方案 #

方案1：添加自定义例外词典（改动最小，推荐优先尝试） #

该方案无需修改现有查询逻辑，仅需维护自定义例外列表，适合词根冲突词汇不多的场景
针对不需要合并的特定词汇，新增高优先级的例外映射，规避默认词干提取的合并逻辑：

1. 在PostgreSQL安装目录的share/tsearch_data路径下新建english_exception.tbl文件，每行定义一条例外规则，格式为原词 目标词根，示例如下：

anime anime
animal animal
animation animation
animals animal
animalia animal
animated animate

2. 基于simple模板创建例外字典：

CREATE TEXT SEARCH DICTIONARY english_exception (
    TEMPLATE = simple,
    FILE = english_exception
);

3. 修改自定义文本搜索配置，将例外字典放在unaccent之后、english_stem之前，匹配到例外规则的词汇不会再进入词干提取流程：

ALTER TEXT SEARCH CONFIGURATION english_unaccent
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_exception, english_stem;

4. 重新生成documentFts列的所有数据，重建GIN索引即可生效。

方案2：双向量加权排序（解决排序不准问题，无需修改现有召回逻辑） #

该方案不改变召回范围，仅优化排序逻辑，适合不想调整词干提取规则的场景
保留现有词干向量做召回，新增精确匹配向量提升精准结果的排序权重：

1. 新建无词干提取的文本搜索配置，只做去重音处理：

CREATE TEXT SEARCH CONFIGURATION simple_unaccent (
  COPY = simple
);
ALTER TEXT SEARCH CONFIGURATION simple_unaccent
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, simple;

2. 新增精确匹配向量列并填充数据：

ALTER TABLE "Entries" ADD COLUMN "documentExact" tsvector;
UPDATE "Entries"
SET "documentExact" = (setweight(to_tsvector('simple_unaccent', coalesce(title)), 'A') || setweight(to_tsvector('simple_unaccent', coalesce(body)), 'C'))
WHERE "language" = 'english';
-- 为该列创建GIN索引提升查询效率
CREATE INDEX idx_entries_document_exact ON "Entries" USING GIN("documentExact");

3. 修改查询的排序逻辑，为精确匹配结果增加额外权重（权重系数可根据实际效果调整）：

select
      count(*) OVER() AS full_count,
      id,
      url, 
      (("urlScore" / 100) + ts_rank("documentFts", websearch_to_tsquery($4, $1)) + 2 * ts_rank("documentExact", plainto_tsquery('simple_unaccent', $1))) as "finalScore",
      ts_headline('english_unaccent', title, websearch_to_tsquery($4, $1)) as title,
      ts_headline('english_unaccent', body, websearch_to_tsquery($4, $1)) as body,
      "possibleEncoding",
      "responseYear"
from "Entries"
where 
  "language" = $3 and 
  "documentFts" @@ websearch_to_tsquery($4, $1)
order by "finalScore" desc limit 20 offset $2;

方案3：替换为更精准的词形还原字典（从根源解决词根合并过度问题） #

该方案从根源解决词根合并过度问题，适合冲突词汇多、不想维护例外列表的场景
默认english_stem的词干提取规则比较激进，可替换为Hunspell词典实现更精准的词形还原：

1. 安装对应语言的Hunspell词典文件，放入share/tsearch_data目录
2. 创建Hunspell字典：

CREATE TEXT SEARCH DICTIONARY english_hunspell (
    TEMPLATE = ispell,
    DictFile = en_US,
    AffFile = en_US,
    Stopwords = english
);

3. 修改english_unaccent配置，用english_hunspell替换english_stem即可，后续同样需要重新生成向量数据、重建索引。

额外性能优化建议 #

现有查询中的count(*) OVER()会扫描所有符合条件的行，结果集较大时会严重拖慢查询速度。如果不需要完全精确的总条数，可使用pg_class系统表的估算值替代，或安装pg_estimate_count扩展实现快速估算，大幅提升大结果集下的查询效率。

内容的提问来源于stack exchange，提问作者Eric.M