如何结合PostgreSQL的ts_headline、ts_rank_cd与pg_tgrm实现模糊搜索？

兼容性说明 #

pg_tgrm与PostgreSQL原生全文搜索（tsvector/tsquery）完全兼容，两者可结合使用，且GIN索引同时支持tsvector类型和pg_tgrm的gin_trgm_ops操作符，不存在兼容性冲突。

之前尝试的问题分析 #

1. 全表扫描导致耗时过长：直接对entire_text字段计算SIMILARITY会触发全表扫描，即便建了GIN索引，也因未使用pg_tgrm专用的gin_trgm_ops操作符，无法利用索引加速。
2. 相似性阈值不合理：用单个错拼词与整个文本计算相似性，结果值极低，几乎无法满足similarity > 0的过滤条件，导致错拼词tarture无匹配结果。

正确集成方案 #

第一步：优化索引 #

先为pg_tgrm创建专用GIN索引，用于加速模糊匹配：

CREATE INDEX idx_processed_judgment_html_trgm ON processed_judgment_html USING GIN (entire_text gin_trgm_ops);

保留原有的tsvector索引用于全文搜索即可。

方案一：先修复错拼词，再执行全文搜索 #

适合优先返回精准匹配结果的场景：通过pg_tgrm从全文索引词库中找到与错拼词最相似的候选词，再用该词执行带高亮和排序的全文搜索。

完整查询语句：

WITH corrected_query AS (
    -- 从全文索引词库中匹配最相似的词，生成tsquery
    SELECT websearch_to_tsquery('english', COALESCE(
        (SELECT word
         FROM ts_stat('SELECT textsearchable_index_col FROM processed_judgment_html')
         WHERE word % 'tarture'
         ORDER BY similarity(word, 'tarture') DESC
         LIMIT 1),
        'tarture' -- 无相似词时 fallback 到原输入
    )) AS query
)
SELECT item_id,
       requests_url,
       ts_headline('english', entire_text, c.query, 'StartSel = , StopSel = , ShortWord = 1') AS entire_text_highlights,
       ts_rank_cd(textsearchable_index_col, c.query) AS rank
FROM processed_judgment_html, corrected_query c
WHERE textsearchable_index_col @@ c.query
ORDER BY rank DESC
LIMIT 10;

方案二：同时返回精准匹配+模糊匹配结果，加权排序 #

适合需要同时覆盖精准匹配和模糊匹配的场景，通过加权排序让精准结果优先，模糊结果按相似度排序：

WITH input_term AS (SELECT 'tarture' AS term),
full_text_results AS (
    -- 精准全文搜索结果，赋予更高权重
    SELECT item_id,
           requests_url,
           ts_headline('english', entire_text, query, 'StartSel = , StopSel = , ShortWord = 1') AS entire_text_highlights,
           ts_rank_cd(textsearchable_index_col, query) AS rank,
           1.0 AS weight
    FROM processed_judgment_html,
         input_term,
         websearch_to_tsquery('english', input_term.term) AS query
    WHERE textsearchable_index_col @@ query
),
fuzzy_results AS (
    -- pg_tgrm模糊匹配结果，排除已在精准结果中的项避免重复
    SELECT item_id,
           requests_url,
           ts_headline('english', entire_text, websearch_to_tsquery('english', input_term.term), 'StartSel = , StopSel = , ShortWord = 1') AS entire_text_highlights,
           0 AS rank,
           similarity(entire_text, input_term.term) AS weight
    FROM processed_judgment_html, input_term
    WHERE entire_text % input_term.term
    AND item_id NOT IN (SELECT item_id FROM full_text_results)
)
-- 合并结果，按综合得分排序
SELECT item_id,
       requests_url,
       entire_text_highlights
FROM (
    SELECT *, rank * weight AS score FROM full_text_results
    UNION ALL
    SELECT *, weight AS score FROM fuzzy_results
) combined
ORDER BY score DESC NULLS LAST
LIMIT 10;

关键优化点 #

• 使用pg_tgrm的%操作符替代直接计算SIMILARITY，配合gin_trgm_ops索引实现快速模糊匹配。
• 避免用OR条件组合全文搜索和模糊匹配，改用UNION ALL合并结果，让数据库充分利用各自的索引。
• 为不同类型结果赋予不同权重，保证排序逻辑符合搜索预期。

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