PostgreSQL全文检索：如何查找与输入文档相似的文档？

嘿，我完全懂你想在PostgreSQL 9.6+里实现类似Elasticsearch more_like_this的需求——用全文检索找相似文档，又不想因为来回转换ts_query或者重复处理原始文档带来高额开销对吧？我这里整理了几个实用的方案，你可以按需尝试：

这个思路的核心是直接复用你已经预生成好的ts_vector，通过ts_stat快速提取里面的高频、高权重关键词，再用这些词构建查询去匹配其他文档，全程不用重新处理源文档的原始内容，开销非常低。

假设你的表结构是这样的（已经预生成了全文检索向量）：

CREATE TABLE documents (
    id INT PRIMARY KEY,
    content TEXT,
    content_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);
-- 别忘了给ts_vector建索引，大幅提升查询速度
CREATE INDEX documents_content_tsv_idx ON documents USING GIN (content_tsv);

针对指定源文档（比如id=1），提取关键词并查询相似文档的示例代码：

-- 第一步：从源文档的ts_vector里提取优质关键词，过滤冷门/无效词
WITH source_terms AS (
    SELECT word
    FROM ts_stat($$SELECT content_tsv FROM documents WHERE id = 1$$)
    WHERE nentry > 1  -- 至少在2个文档出现，避免太冷门的词
      AND length(word) > 2  -- 过滤无意义的短词
    ORDER BY ndoc DESC, nentry DESC  -- 按文档覆盖率、总出现次数排序
    LIMIT 10  -- 取Top10最具代表性的词
)
-- 第二步：用这些词构建查询，匹配相似文档并按相关性排序
SELECT d.id, d.content, ts_rank(d.content_tsv, query) AS similarity
FROM documents d,
     (SELECT to_tsquery('english', array_agg(word || ':*')::TEXT) AS query FROM source_terms) q
WHERE d.id != 1  -- 排除源文档本身
  AND d.content_tsv @@ q.query
ORDER BY similarity DESC
LIMIT 20;

你可以根据数据集调整nentry、length、LIMIT这些参数，平衡查询的精准度和召回率。

有时候纯全文检索的关键词匹配会漏掉一些结构或专有名词的相似性，这时候可以结合pg_trgm扩展的字符片段相似性来增强效果，而且索引后的查询速度也很可观。

首先安装扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

给内容字段建trigram索引：

CREATE INDEX documents_content_trgm_idx ON documents USING GIN (content gin_trgm_ops);

下面是结合全文检索和trigram相似性的示例，兼顾关键词匹配和内容结构相似：

WITH source_doc AS (
    SELECT content FROM documents WHERE id = 1
),
candidates AS (
    -- 先用全文检索过滤出候选文档
    SELECT d.id, d.content, ts_rank(d.content_tsv, to_tsquery('english', (SELECT content FROM source_doc))) AS ts_similarity
    FROM documents d, source_doc s
    WHERE d.id != 1
      AND d.content_tsv @@ to_tsquery('english', s.content)
)
-- 再用trigram相似性加权排序，平衡两种匹配逻辑
SELECT id, content, ts_similarity, 
       similarity(content, (SELECT content FROM source_doc)) AS trgm_similarity,
       (ts_similarity * 0.7 + trgm_similarity * 0.3) AS combined_similarity
FROM candidates
ORDER BY combined_similarity DESC
LIMIT 20;

你可以根据业务需求调整两种相似性的权重比例，比如如果更看重语义关键词就调高ts_similarity的权重，反之则调高trgm_similarity。

如果上面的方案还满足不了语义级的相似性需求，比如需要识别同义词、上下文相似的文档，可以结合pgvector扩展（PostgreSQL 9.6+支持），把文档转换成语义向量后用余弦相似度匹配。

步骤如下：

1. 安装pgvector扩展：

CREATE EXTENSION IF NOT EXISTS vector;

2. 添加向量字段（维度根据你用的NLP模型调整，比如BERT是768维）：

ALTER TABLE documents ADD COLUMN content_vector vector(768);

3. 离线处理文档：用Python的transformers等库把content转换成语义向量，再导入到PostgreSQL的content_vector字段。

4. 创建向量索引：

CREATE INDEX documents_content_vector_idx ON documents USING ivfflat (content_vector vector_cosine_ops);

5. 查询相似文档：

SELECT id, content, 1 - (content_vector <=> (SELECT content_vector FROM documents WHERE id = 1)) AS cosine_similarity
FROM documents
WHERE id != 1
ORDER BY content_vector <=> (SELECT content_vector FROM documents WHERE id = 1)
LIMIT 20;

这个方案精度最高，但需要额外的NLP处理步骤和向量存储开销，适合对相似性要求极高的场景。

• 务必保持content_tsv是自动生成的（用GENERATED ALWAYS AS STORED），这样更新content时会自动同步向量，不用手动维护。
• 对于大数据量的表，ts_stat的结果可以考虑缓存，避免重复计算。
• 如果用pg_trgm，GIN索引比GIST索引的查询速度更快，适合大规模数据集。

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