跳到主要内容

全文搜索

问题​

PostgreSQL 的全文搜索如何工作？搜索性能如何？能替代 Elasticsearch 吗？

答案​

一、全文搜索原理​

PostgreSQL 内置全文搜索基于 tsvector（文档向量）和 tsquery（查询表达式）：

-- 将文本转换为 tsvector
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- 创建搜索查询
SELECT to_tsquery('english', 'fox & lazy');
-- 'fox' & 'lazi'

-- 匹配操作符 @@
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'fox & brown');
-- true

二、基本用法​

-- 创建表并添加全文搜索
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT
);

-- 方式 1：实时计算（简单但慢）
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'database & performance');

-- 方式 2：预计算列 + 索引（推荐）
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- 查询走索引
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & performance');

三、搜索权重​

使用 setweight 为不同字段设置权重（A > B > C > D）：

-- 标题权重 A，正文权重 B
setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', body), 'B')

按相关度排序：

SELECT title, 
  ts_rank(search_vector, to_tsquery('english', 'database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database')
ORDER BY rank DESC;

四、查询语法​

-- AND：两个词都包含
'fox & dog'

-- OR：任一词包含
'fox | cat'

-- NOT：排除
'fox & !cat'

-- 短语搜索（词间距为 1）
'quick <-> fox'   -- quick 紧跟 fox

-- 前缀匹配
'data:*'          -- 匹配 data 开头的词

-- 组合
'(database | postgresql) & performance & !mysql'

五、中文全文搜索​

PostgreSQL 默认不支持中文分词，需要安装扩展：

扩展说明
zhparser基于 SCWS，中文分词
pg_jieba基于结巴分词
pg_bigm基于 bigram，不需要分词
-- 安装 zhparser（需编译安装扩展）
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese
  ADD MAPPING FOR n,v,a,i,e,l WITH simple;

-- 使用中文配置
SELECT to_tsvector('chinese', '数据库性能优化指南');

六、PostgreSQL 全文搜索 vs Elasticsearch​

对比项PostgreSQL FTSElasticsearch
部署复杂度无需额外组件独立集群
数据一致性与数据库事务一致最终一致（需同步）
搜索功能基础搜索（够用）丰富（模糊、高亮、聚合）
中文支持需要扩展内置多种分词器
性能（小数据）很好过重
性能（大数据）百万级可以亿级也能处理
分布式不支持原生分布式
适用场景中小规模，简单搜索大规模，复杂搜索
选型建议
  • 简单搜索 + 数据量不大：用 PostgreSQL 全文搜索，避免引入额外组件
  • 复杂搜索 + 大数据量：用 Elasticsearch
  • 混合方案：主存储用 PostgreSQL，搜索场景同步到 ES

常见面试问题​

Q1: 全文搜索 GIN 索引和 B-tree LIKE 查询哪个好？​

答案：

  • LIKE 'keyword%'（前缀匹配）：B-tree 索引足够
  • LIKE '%keyword%'（包含匹配）：B-tree 无法使用索引，全表扫描
  • 全文搜索（GIN 索引）：专门为文本搜索设计，支持分词、排名、权重，性能远好于 LIKE '%keyword%'

Q2: tsvector 与 tsquery 是什么关系？​

答案：

  • tsvector：文档的分词结果（词 + 位置），是被搜索的对象
  • tsquery：搜索条件（词 + 逻辑运算符），是搜索的表达式
  • @@ 操作符：判断 tsvector 是否匹配 tsquery

相关链接​