如何在PostgreSQL中实现大文档多词全文/模糊搜索？

针对你的需求（支持多词搜索、拼写容错，覆盖title和content字段），可以结合PostgreSQL的全文搜索和pg_trgm扩展来实现，以下是具体步骤：

1. 启用pg_trgm扩展 #

pg_trgm用于计算字符串的trigram相似度，能很好处理拼写错误的模糊匹配：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建优化索引 #

2.1 全文搜索索引 #

先创建一个包含title和content的全文检索向量字段，再建立GIN索引提升性能：

-- 添加自动生成的全文检索向量列
ALTER TABLE documents ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
  to_tsvector('english', title || ' ' || content)
) STORED;

-- 为向量列创建GIN索引
CREATE INDEX idx_documents_search_vector ON documents USING GIN(search_vector);

2.2 Trigram模糊匹配索引 #

为title和content创建trigram索引，支持拼写错误的模糊匹配：

CREATE INDEX idx_documents_title_trgm ON documents USING GIN(title gin_trgm_ops);
CREATE INDEX idx_documents_content_trgm ON documents USING GIN(content gin_trgm_ops);

3. 多词容错搜索查询 #

以下查询同时结合全文搜索的精准匹配和trigram的拼写容错，返回最相关的结果：

SELECT id, title, content,
  -- trigram相似度分数（0-1，越高越匹配）
  similarity(title || ' ' || content, 'the quixk bronw fox') AS sim_score,
  -- 全文搜索匹配排名
  ts_rank(search_vector, websearch_to_tsquery('english', 'the quixk bronw fox')) AS ts_rank
FROM documents
WHERE 
  -- 匹配全文搜索结果
  search_vector @@ websearch_to_tsquery('english', 'the quixk bronw fox')
  -- 或匹配相似度高于阈值的结果（可根据需求调整阈值，比如0.2）
  OR similarity(title || ' ' || content, 'the quixk bronw fox') > 0.2
-- 按匹配度排序，优先显示精准匹配的结果
ORDER BY ts_rank DESC, sim_score DESC;

简化版模糊查询（侧重拼写容错） #

如果更关注拼写错误的匹配，也可以直接用trigram的%操作符：

SELECT id, title, content
FROM documents
WHERE (title || ' ' || content) % 'the quixk bronw fox'
ORDER BY similarity(title || ' ' || content, 'the quixk bronw fox') DESC;

针对你的示例数据，搜索the quixk bronw fox时，第一条数据The Brown Fox会因为高相似度排在首位。

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