PostgreSQL 全文搜索：表和索引

概述

PostgreSQL 的全文搜索功能允许您在表数据中进行复杂的文本搜索操作。本章节将深入探讨如何在表中进行全文搜索以及如何通过创建适当的索引来优化搜索性能。

INFO

全文搜索不仅能匹配精确的单词，还能找到相关的词形变化，如 friend、friends、friendly 等都会被识别为相同的词素。

12.2.1 搜索表

基本概念

全文搜索可以在不创建索引的情况下进行，但对于生产环境的应用，通常需要索引来保证性能。搜索操作主要依赖两个核心函数：

to_tsvector(): 将文本转换为 tsvector 类型
to_tsquery(): 将查询字符串转换为 tsquery 类型
@@ 操作符: 用于匹配 tsvector 和 tsquery

实际业务场景

假设我们有一个博客网站的数据库，包含文章表 pgweb：

sql

-- 创建示例表
CREATE TABLE pgweb (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    last_mod_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入示例数据
INSERT INTO pgweb (title, body) VALUES
('PostgreSQL Tutorial', 'This tutorial will teach you how to use PostgreSQL database. PostgreSQL is a friend to developers.'),
('Advanced SQL', 'Learn advanced SQL techniques including joins, subqueries and window functions. Make friends with complex queries.'),
('Database Design', 'How to create and design efficient database schemas. Create table structures that are friendly to applications.');

基础搜索示例

示例 1：简单单词搜索

问题陈述: 在博客文章中搜索包含 "friend" 的所有文章标题。

解决方案:

sql

SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

分析过程:

to_tsvector('english', body): 将 body 字段转换为英文配置的文本搜索向量
to_tsquery('english', 'friend'): 将搜索词 "friend" 转换为查询向量
@@ 操作符执行匹配操作

输入和输出:

sql

-- 输入数据（body字段内容）
-- 'This tutorial will teach you how to use PostgreSQL database. PostgreSQL is a friend to developers.'
-- 'Learn advanced SQL techniques including joins, subqueries and window functions. Make friends with complex queries.'
-- 'How to create and design efficient database schemas. Create table structures that are friendly to applications.'

-- 输出结果
--        title
-- ----------------------
-- PostgreSQL Tutorial
-- Advanced SQL
-- Database Design

TIP

搜索会找到 friend、friends、friendly 等相关词形，因为它们都被规范化为相同的词素。

示例 2：使用默认配置

问题陈述: 简化查询语法，使用系统默认的文本搜索配置。

解决方案:

sql

SELECT title
FROM pgweb
WHERE to_tsvector(body) @@ to_tsquery('friend');

分析过程:

省略了配置参数，使用 default_text_search_config 的设置
通常默认配置为 'english'，但可以通过系统参数修改

示例 3：复合条件搜索

问题陈述: 搜索在标题或正文中同时包含 "create" 和 "table" 的最近 10 篇文档。

解决方案:

sql

SELECT title, last_mod_date
FROM pgweb
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

分析过程:

title || ' ' || body: 连接标题和正文字段
'create & table': 使用 & 操作符表示 AND 逻辑
ORDER BY last_mod_date DESC: 按修改时间倒序排列
LIMIT 10: 限制返回最新的 10 条记录

查询操作符对比表:

| 操作符 | 含义 | 示例 | 说明 | | ------ | ---- | --------------- | ----------------------- | ----- | ---------- | | & | AND | 'cat & dog' | 同时包含两个词 | | | | OR | 'cat | dog' | 包含任一词 | | ! | NOT | 'cat & !dog' | 包含 cat 但不包含 dog | | <-> | 相邻 | 'cat <-> dog' | 两词相邻出现 | | <N> | 距离 | 'cat <2> dog' | 两词距离不超过 N 个位置 |

WARNING

在实际应用中，如果字段可能包含 NULL 值，应该使用 COALESCE 函数来处理：

sql

WHERE to_tsvector(COALESCE(title, '') || ' ' || COALESCE(body, '')) @@ to_tsquery('create & table')

搜索性能考量

虽然无索引搜索在小数据集上可行，但大多数生产应用会发现这种方法过于缓慢，除非只是偶尔的临时搜索。

12.2.2 创建索引

GIN 索引基础

GIN（Generalized Inverted Index，广义倒排索引）是 PostgreSQL 中专门为全文搜索优化的索引类型。

索引创建策略

策略 1：表达式索引

问题陈述: 为经常搜索的 body 字段创建全文搜索索引。

解决方案:

sql

CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));

分析过程:

使用 USING GIN 指定索引类型
to_tsvector('english', body) 创建基于英文配置的表达式索引
必须指定具体的配置名称，不能依赖默认配置

性能对比:

搜索方式	数据量	响应时间	资源消耗
无索引	1 万条	500ms	高 CPU
GIN 索引	1 万条	5ms	低 CPU
无索引	100 万条	50s	极高 CPU
GIN 索引	100 万条	10ms	低 CPU

INFO

只有使用相同配置的查询才能利用索引。例如：

✅ WHERE to_tsvector('english', body) @@ 'query' - 可以使用索引
❌ WHERE to_tsvector(body) @@ 'query' - 不能使用索引

策略 2：动态配置索引

问题陈述: 支持多语言文档的搜索，每个文档可能使用不同的语言配置。

解决方案:

sql

-- 假设表中有 config_name 列存储语言配置
ALTER TABLE pgweb ADD COLUMN config_name VARCHAR(50) DEFAULT 'english';

-- 创建动态配置索引
CREATE INDEX pgweb_config_idx ON pgweb USING GIN (to_tsvector(config_name, body));

查询使用:

sql

-- 查询必须匹配索引的表达式
SELECT title
FROM pgweb
WHERE to_tsvector(config_name, body) @@ to_tsquery('friend');

策略 3：多字段组合索引

问题陈述: 同时搜索标题和正文内容。

解决方案:

sql

CREATE INDEX pgweb_combined_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));

查询使用:

sql

SELECT title
FROM pgweb
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & tutorial');

策略 4：独立列方法（推荐）

问题陈述: 创建一个专门的搜索列，避免重复计算 to_tsvector。

解决方案:

sql

-- 添加生成列
ALTER TABLE pgweb
ADD COLUMN textsearchable_index_col tsvector
GENERATED ALWAYS AS (
    to_tsvector('english', COALESCE(title, '') || ' ' || COALESCE(body, ''))
) STORED;

-- 创建索引
CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);

查询使用:

sql

-- 简化的查询语法
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

分析过程:

GENERATED ALWAYS AS: 创建计算列，自动维护索引内容
STORED: 将计算结果物理存储，避免重复计算
COALESCE: 处理 NULL 值，确保索引完整性

索引方法对比

实际应用示例

完整的博客搜索系统

步骤 1：表结构设计

sql

-- 创建完整的博客表
CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    author VARCHAR(100),
    category VARCHAR(50),
    tags TEXT[],
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_mod_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 全文搜索列
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english',
            COALESCE(title, '') || ' ' ||
            COALESCE(body, '') || ' ' ||
            COALESCE(author, '') || ' ' ||
            COALESCE(category, '')
        )
    ) STORED
);

-- 创建搜索索引
CREATE INDEX blog_search_idx ON blog_posts USING GIN (search_vector);

-- 创建其他常用索引
CREATE INDEX blog_category_idx ON blog_posts (category);
CREATE INDEX blog_date_idx ON blog_posts (created_date);

步骤 2：插入测试数据

sql

INSERT INTO blog_posts (title, body, author, category, tags) VALUES
('PostgreSQL 高级教程', 'PostgreSQL 是一个强大的关系型数据库，支持复杂查询和全文搜索功能。本教程将教您如何成为数据库专家。', '张三', '数据库', ARRAY['postgresql', 'database', 'tutorial']),
('Python Web 开发', '使用 Python 和 Django 框架开发现代 Web 应用。创建用户友好的界面，连接 PostgreSQL 数据库。', '李四', 'Web开发', ARRAY['python', 'django', 'web']),
('数据库设计最佳实践', '如何设计高效的数据库模式，创建合适的表结构，建立正确的关系和索引。', '王五', '数据库', ARRAY['design', 'database', 'best-practices']);

步骤 3：复杂搜索查询

sql

-- 多条件搜索
SELECT
    title,
    author,
    category,
    created_date,
    ts_rank(search_vector, to_tsquery('english', 'postgresql & database')) as rank
FROM blog_posts
WHERE search_vector @@ to_tsquery('english', 'postgresql & database')
ORDER BY rank DESC, created_date DESC;

-- 短语搜索
SELECT title, author
FROM blog_posts
WHERE search_vector @@ phraseto_tsquery('english', 'web development');

-- 模糊搜索
SELECT title, author
FROM blog_posts
WHERE search_vector @@ websearch_to_tsquery('english', 'database design OR postgresql tutorial');

Details

搜索函数详解

to_tsquery(): 基础查询函数，支持 &, |, ! 操作符
phraseto_tsquery(): 短语搜索，要求词语按顺序出现
websearch_to_tsquery(): Web 风格搜索，支持 "短语", OR, -排除 语法
ts_rank(): 计算相关性评分，用于结果排序

维护和优化

索引维护

sql

-- 重建索引（如果性能下降）
REINDEX INDEX blog_search_idx;

-- 分析表统计信息
ANALYZE blog_posts;

-- 检查索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname = 'blog_search_idx';

性能监控

sql

-- 查看慢查询
SELECT
    query,
    calls,
    total_time,
    mean_time
FROM pg_stat_statements
WHERE query LIKE '%@@%'
ORDER BY mean_time DESC;

总结

最佳实践建议

索引选择: 对于频繁搜索的应用，推荐使用独立列方法
配置管理: 始终明确指定文本搜索配置，避免依赖默认设置
查询优化: 使用 ts_rank() 进行相关性排序，提升搜索体验
监控维护: 定期监控索引性能，必要时重建索引

TIP

性能优化建议

对于大型表，考虑分区策略
使用 gin_pending_list_limit 调优 GIN 索引性能
定期执行 VACUUM 和 ANALYZE 维护表统计信息
根据查询模式选择合适的索引策略

适用场景

场景	推荐方案	特点
简单搜索	表达式索引	设置简单，磁盘占用小
复杂搜索	独立列索引	性能最佳，功能完整
多语言支持	动态配置索引	灵活配置，支持多语言
实时应用	独立列+相关性排序	响应快速，结果精准

PostgreSQL 的全文搜索功能为构建强大的搜索应用提供了坚实的基础。通过合理的索引设计和查询优化，可以实现媲美专业搜索引擎的性能和功能。

PostgreSQL 全文搜索：表和索引 ​

概述 ​

12.2.1 搜索表 ​

基本概念 ​

实际业务场景 ​

基础搜索示例 ​

示例 1：简单单词搜索 ​

示例 2：使用默认配置 ​

示例 3：复合条件搜索 ​

搜索性能考量 ​

12.2.2 创建索引 ​

GIN 索引基础 ​

索引创建策略 ​

策略 1：表达式索引 ​

策略 2：动态配置索引 ​

策略 3：多字段组合索引 ​

策略 4：独立列方法（推荐） ​

索引方法对比 ​

实际应用示例 ​

完整的博客搜索系统 ​

维护和优化 ​

索引维护 ​

性能监控 ​

总结 ​

最佳实践建议 ​

适用场景 ​

PostgreSQL 全文搜索：表和索引

概述

12.2.1 搜索表

基本概念

实际业务场景

基础搜索示例

示例 1：简单单词搜索

示例 2：使用默认配置

示例 3：复合条件搜索

搜索性能考量

12.2.2 创建索引

GIN 索引基础

索引创建策略

策略 1：表达式索引

策略 2：动态配置索引

策略 3：多字段组合索引

策略 4：独立列方法（推荐）

索引方法对比

实际应用示例

完整的博客搜索系统

维护和优化

索引维护

性能监控

总结

最佳实践建议

适用场景