PostgreSQL 文本搜索函数和操作符

PostgreSQL 提供了强大的全文搜索功能，包括专门的数据类型、操作符和函数，用于构建高效的文本搜索应用。本章将详细介绍文本搜索的操作符和函数，并通过实际示例演示它们的使用方法。

文本搜索核心概念

在深入学习函数和操作符之前，我们需要理解几个核心概念：

tsvector：文本搜索向量，包含经过处理的词位（lexeme）及其位置信息
tsquery：文本搜索查询，定义搜索条件和逻辑关系
词位（lexeme）：经过标准化处理的单词形式
位置信息：词位在原文档中的位置
权重：词位的重要性等级（A、B、C、D）

文本搜索操作符

基础匹配操作符

@@ 匹配操作符

@@ 操作符是文本搜索的核心，用于判断 tsvector 是否匹配 tsquery。

基础匹配示例复杂查询示例

sql

-- tsvector 匹配 tsquery
SELECT to_tsvector('肥猫吃了老鼠') @@ to_tsquery('猫 & 老鼠');
-- 结果: t (true)

-- 文本直接匹配 tsquery（会自动转换为 tsvector）
SELECT '肥猫吃了老鼠' @@ to_tsquery('猫 & 老鼠');
-- 结果: t (true)

-- 创建示例表演示实际应用
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector
);

-- 插入测试数据
INSERT INTO articles (title, content, search_vector) VALUES
('PostgreSQL 教程', 'PostgreSQL 是一个强大的开源数据库',
 to_tsvector('PostgreSQL 教程 PostgreSQL 是一个强大的开源数据库')),
('数据库优化', '数据库性能优化是关键技能',
 to_tsvector('数据库优化 数据库性能优化是关键技能')),
('SQL 查询技巧', 'SQL 查询语句的高级用法',
 to_tsvector('SQL 查询技巧 SQL 查询语句的高级用法'));

-- 使用 @@ 操作符进行搜索
SELECT title, content
FROM articles
WHERE search_vector @@ to_tsquery('PostgreSQL');

sql

-- 逻辑与查询
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('数据库 & 优化');

-- 逻辑或查询
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('PostgreSQL | SQL');

-- 否定查询
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('数据库 & !PostgreSQL');

分析过程：

@@ 操作符检查左侧的 tsvector 是否满足右侧 tsquery 的条件
当左侧是普通文本时，会自动调用 to_tsvector() 进行转换
支持复杂的逻辑查询组合（AND、OR、NOT）

向量组合操作符

|| 连接操作符

用于连接两个 tsvector，自动调整位置信息。

sql

-- 连接两个 tsvector
SELECT 'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector;
-- 结果: 'a':1 'b':2,5 'c':3 'd':4

-- 实际应用：合并文档的不同部分
SELECT
    to_tsvector('标题：PostgreSQL 教程') ||
    to_tsvector('内容：学习 PostgreSQL 数据库') AS combined_vector;

输入数据：

第一个向量：'a:1 b:2'（a 在位置 1，b 在位置 2）
第二个向量：'c:1 d:2 b:3'（c 在位置 1，d 在位置 2，b 在位置 3）

输出结果：'a':1 'b':2,5 'c':3 'd':4

分析过程：

第二个向量的位置信息被调整：原位置 1 变成 3，位置 2 变成 4，位置 3 变成 5
相同词位（b）的位置信息会合并

查询组合操作符

&& 逻辑与操作符

sql

-- 组合两个查询条件
SELECT '肥 | 老鼠'::tsquery && '猫'::tsquery;
-- 结果: ( '肥' | '老鼠' ) & '猫'

-- 实际搜索应用
SELECT title FROM articles
WHERE search_vector @@ (to_tsquery('数据库') && to_tsquery('优化'));

|| 逻辑或操作符

sql

-- 扩展搜索范围
SELECT '肥 | 老鼠'::tsquery || '猫'::tsquery;
-- 结果: '肥' | '老鼠' | '猫'

-- 搜索多个相关主题
SELECT title FROM articles
WHERE search_vector @@ (to_tsquery('PostgreSQL') || to_tsquery('MySQL'));

!! 否定操作符

sql

-- 排除特定词汇
SELECT !! '猫'::tsquery;
-- 结果: !'猫'

-- 查找不包含特定词的文档
SELECT title FROM articles
WHERE search_vector @@ (to_tsquery('数据库') && !!to_tsquery('PostgreSQL'));

短语查询操作符

<-> 相邻短语操作符

用于匹配相邻的词位。

sql

-- 构造短语查询
SELECT to_tsquery('肥') <-> to_tsquery('老鼠');
-- 结果: '肥' <-> '老鼠'

-- 实际应用：查找特定短语
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    search_vector tsvector
);

INSERT INTO documents (content, search_vector) VALUES
('PostgreSQL 数据库系统', to_tsvector('PostgreSQL 数据库系统')),
('MySQL 和 PostgreSQL 比较', to_tsvector('MySQL 和 PostgreSQL 比较')),
('数据库 PostgreSQL 教程', to_tsvector('数据库 PostgreSQL 教程'));

-- 查找"PostgreSQL 数据库"短语
SELECT content FROM documents
WHERE search_vector @@ (to_tsquery('PostgreSQL') <-> to_tsquery('数据库'));

包含关系操作符

@> 和 <@ 包含操作符

sql

-- 检查查询包含关系
SELECT '猫'::tsquery @> '猫 & 老鼠'::tsquery;
-- 结果: f (false，因为'猫'不包含'老鼠')

SELECT '猫'::tsquery <@ '猫 & 老鼠'::tsquery;
-- 结果: t (true，因为'猫'包含在'猫 & 老鼠'中)

-- 即使有否定操作符也返回true
SELECT '猫'::tsquery <@ '!猫 & 老鼠'::tsquery;
-- 结果: t (true，只考虑词位存在性，忽略逻辑操作符)

文本搜索函数

基础转换函数

to_tsvector() - 文本转向量

将普通文本转换为可搜索的 tsvector 格式。

基础用法JSON文档处理

sql

-- 基础转换
SELECT to_tsvector('english', 'The Fat Rats');
-- 结果: 'fat':2 'rat':3

-- 中文内容处理
SELECT to_tsvector('simple', '学习 PostgreSQL 数据库技术');
-- 结果: 'postgresql':2 '学习':1 '数据库技术':3

-- 指定配置
SELECT to_tsvector('english', 'Running and jumping quickly');
-- 结果: 'jump':3 'quick':4 'run':1

sql

-- 处理JSON字符串值
SELECT to_tsvector('english', '{"title": "The Fat Rats", "author": "John"}');
-- 结果: 'fat':2 'john':4 'rat':3

-- JSONb处理（注意字段顺序差异）
SELECT to_tsvector('english', '{"title": "The Fat Rats", "author": "John"}'::jsonb);
-- 结果可能不同，因为jsonb字段顺序依赖实现

to_tsquery() - 文本转查询

将查询文本转换为 tsquery 格式。

sql

-- 基础查询转换
SELECT to_tsquery('english', 'The & Fat & Rats');
-- 结果: 'fat' & 'rat'

-- 复杂查询构建
SELECT to_tsquery('english', '(PostgreSQL | MySQL) & database & !Oracle');
-- 结果: ( 'postgresql' | 'mysql' ) & 'databas' & !'oracl'

智能查询函数

plainto_tsquery() - 平文本查询

将普通文本自动转换为逻辑与查询，忽略标点符号。

sql

-- 自动构建AND查询
SELECT plainto_tsquery('english', '肥老鼠');
-- 结果: '肥' & '老鼠'

-- 实际应用：用户友好的搜索
CREATE OR REPLACE FUNCTION simple_search(search_text TEXT)
RETURNS TABLE(title TEXT, content TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT a.title, a.content
    FROM articles a
    WHERE a.search_vector @@ plainto_tsquery('simple', search_text);
END;
$$ LANGUAGE plpgsql;

-- 用户可以直接输入普通文本
SELECT * FROM simple_search('PostgreSQL 数据库');

phraseto_tsquery() - 短语查询

将文本转换为短语查询，保持词序。

sql

-- 短语查询
SELECT phraseto_tsquery('english', '肥老鼠');
-- 结果: '肥' <-> '老鼠'

-- 处理停用词
SELECT phraseto_tsquery('english', '猫和老鼠');
-- 结果: '猫' <2> '老鼠' (跳过停用词"和")

websearch_to_tsquery() - 网络搜索风格

模拟常见网络搜索引擎的查询语法。

sql

-- 网络搜索风格查询
SELECT websearch_to_tsquery('english', '"肥老鼠" 或 猫狗');
-- 结果: '肥' <-> '老鼠' | '猫' & '狗'

-- 支持的语法
SELECT websearch_to_tsquery('english', 'PostgreSQL -MySQL "数据库系统"');
-- 使用引号表示短语，破折号表示排除

向量操作函数

setweight() - 设置权重

为 tsvector 中的词位分配重要性权重。

基础权重设置实际应用场景

sql

-- 为所有词位设置权重
SELECT setweight('肥:2,4 猫:3 老鼠:5B'::tsvector, 'A');
-- 结果: '猫':3A '肥':2A,4A '老鼠':5A

-- 为特定词位设置权重
SELECT setweight(
    '肥:2,4 猫:3 老鼠:5,6B'::tsvector,
    'A',
    '{猫,老鼠}'
);
-- 结果: '猫':3A '肥':2,4 '老鼠':5A,6A

sql

-- 构建分层搜索索引
CREATE OR REPLACE FUNCTION build_search_vector(
    title TEXT,
    summary TEXT,
    content TEXT
) RETURNS tsvector AS $$
BEGIN
    RETURN
        setweight(to_tsvector('simple', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(summary, '')), 'B') ||
        setweight(to_tsvector('simple', COALESCE(content, '')), 'C');
END;
$$ LANGUAGE plpgsql;

-- 使用分层权重
UPDATE articles SET search_vector = build_search_vector(title, NULL, content);

-- 查询时可以根据权重排序
SELECT title, ts_rank(search_vector, to_tsquery('PostgreSQL')) as rank
FROM articles
WHERE search_vector @@ to_tsquery('PostgreSQL')
ORDER BY rank DESC;

strip() - 移除位置权重信息

sql

-- 移除位置和权重信息
SELECT strip('fat:2,4 cat:3 rat:5A'::tsvector);
-- 结果: 'cat' 'fat' 'rat'

-- 用于索引优化或存储节省
CREATE INDEX idx_articles_simple ON articles
USING gin(strip(search_vector));

信息提取函数

length() 和 numnode()

sql

-- 获取tsvector中词位数量
SELECT length('肥:2,4 猫:3 老鼠:5A'::tsvector);
-- 结果: 3

-- 获取tsquery中节点数量
SELECT numnode('(肥 & 老鼠) | 猫'::tsquery);
-- 结果: 5 (包括词位和操作符)

tsvector_to_array() 和 unnest()

数组转换详细信息展开

sql

-- 转换为词位数组
SELECT tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector);
-- 结果: {cat,fat,rat}

-- 实际应用：词汇统计
SELECT unnest(tsvector_to_array(search_vector)) as word
FROM articles
WHERE id = 1;

sql

-- 展开tsvector详细信息
SELECT * FROM unnest('cat:3 fat:2,4 rat:5A'::tsvector);
-- 结果:
-- lexeme | positions | weights
-- -------+-----------+---------
-- cat    | {3}       | {D}
-- fat    | {2,4}     | {D,D}
-- rat    | {5}       | {A}

-- 分析文档词位分布
SELECT lexeme, array_length(positions, 1) as frequency
FROM unnest((SELECT search_vector FROM articles WHERE id = 1))
ORDER BY frequency DESC;

排名和高亮函数

ts_rank() - 相关性评分

计算文档与查询的匹配度分数。

sql

-- 基础排名
SELECT ts_rank(to_tsvector('raining cats and dogs'), 'cat');
-- 结果: 0.06079271

-- 带权重的排名
SELECT ts_rank(
    ARRAY[0.1, 0.2, 0.4, 1.0],
    search_vector,
    to_tsquery('PostgreSQL')
) as weighted_rank
FROM articles
WHERE search_vector @@ to_tsquery('PostgreSQL');

ts_rank_cd() - 覆盖密度排名

使用覆盖密度算法进行更精确的相关性计算。

sql

-- 覆盖密度排名
SELECT ts_rank_cd(to_tsvector('raining cats and dogs'), 'cat');
-- 结果: 0.1

-- 比较两种排名算法
SELECT
    title,
    ts_rank(search_vector, query) as standard_rank,
    ts_rank_cd(search_vector, query) as coverage_rank
FROM articles, to_tsquery('PostgreSQL') query
WHERE search_vector @@ query;

ts_headline() - 搜索结果高亮

生成带高亮的搜索摘要。

基础高亮JSON文档高亮

sql

-- 基础高亮显示
SELECT ts_headline(
    'The fat cat ate the rat.',
    'cat'
);
-- 结果: The fat <b>cat</b> ate the rat.

-- 自定义高亮选项
SELECT ts_headline(
    'english',
    'PostgreSQL is a powerful database system. PostgreSQL supports full-text search.',
    'PostgreSQL',
    'StartSel=<mark>, StopSel=</mark>, MaxWords=20, MinWords=5'
);

sql

-- JSON文档高亮
SELECT ts_headline(
    '{"title":"raining cats and dogs", "content":"cats are amazing"}'::jsonb,
    'cat'
);
-- 结果: {"title": "raining <b>cats</b> and dogs", "content": "<b>cats</b> are amazing"}

-- 构建搜索结果页面
SELECT
    title,
    ts_headline('simple', content, query, 'MaxWords=35') as snippet
FROM articles, to_tsquery('simple', 'PostgreSQL') query
WHERE search_vector @@ query;

JSON 文档处理函数

json_to_tsvector() 和 jsonb_to_tsvector()

专门处理 JSON 文档的文本搜索。

sql

-- 选择性处理JSON字段
SELECT json_to_tsvector(
    'english',
    '{"title": "The Fat Rats", "year": 2024, "published": true}'::json,
    '["string", "numeric"]'
);
-- 结果: '2024':5 'fat':2 'rat':3

-- 处理所有类型
SELECT json_to_tsvector(
    'english',
    '{"title": "The Fat Rats", "count": 123}'::json,
    '"all"'
);
-- 结果: '123':7 'count':5 'fat':2 'rat':3 'title':1

-- 实际应用：索引JSON文档
CREATE TABLE json_docs (
    id SERIAL PRIMARY KEY,
    data JSONB,
    search_vector tsvector
);

-- 创建触发器自动更新搜索向量
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := jsonb_to_tsvector('simple', NEW.data, '"all"');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_search_vector_trigger
    BEFORE INSERT OR UPDATE ON json_docs
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

向量编辑函数

ts_delete() - 删除词位

sql

-- 删除单个词位
SELECT ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat');
-- 结果: 'cat':3 'rat':5A

-- 删除多个词位
SELECT ts_delete(
    'fat:2,4 cat:3 rat:5A'::tsvector,
    ARRAY['fat','rat']
);
-- 结果: 'cat':3

-- 实际应用：过滤停用词
CREATE OR REPLACE FUNCTION remove_stopwords(vec tsvector)
RETURNS tsvector AS $$
DECLARE
    stopwords TEXT[] := ARRAY['的', '是', '在', '有', '和', '与'];
BEGIN
    RETURN ts_delete(vec, stopwords);
END;
$$ LANGUAGE plpgsql;

ts_filter() - 按权重过滤

sql

-- 只保留特定权重的词位
SELECT ts_filter('fat:2,4 cat:3b,7c rat:5A'::tsvector, '{a,b}');
-- 结果: 'cat':3B 'rat':5A

-- 构建高权重搜索
SELECT title FROM articles
WHERE ts_filter(search_vector, '{A,B}') @@ to_tsquery('PostgreSQL');

查询重写函数

ts_rewrite() - 查询重写

用于查询扩展和同义词替换。

基础重写基于表的重写

sql

-- 直接重写
SELECT ts_rewrite(
    'a & b'::tsquery,
    'a'::tsquery,
    'foo|bar'::tsquery
);
-- 结果: 'b' & ( 'foo' | 'bar' )

sql

-- 创建同义词表
CREATE TABLE synonyms (
    target tsquery,
    substitute tsquery
);

INSERT INTO synonyms VALUES
    (to_tsquery('数据库'), to_tsquery('database | db | 数据库')),
    (to_tsquery('搜索'), to_tsquery('查找 | 检索 | 搜索'));

-- 使用表数据进行查询重写
SELECT ts_rewrite(
    '数据库 & 搜索'::tsquery,
    'SELECT target, substitute FROM synonyms'
);
-- 自动扩展为同义词查询

短语查询构建函数

tsquery_phrase() - 构建短语查询

sql

-- 构建相邻短语
SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'));
-- 结果: 'fat' <-> 'cat'

-- 构建间隔短语
SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10);
-- 结果: 'fat' <10> 'cat'

-- 实际应用：构建复杂短语查询
CREATE OR REPLACE FUNCTION build_phrase_query(words TEXT[], distance INT DEFAULT 1)
RETURNS tsquery AS $$
DECLARE
    result tsquery;
    i INT;
BEGIN
    IF array_length(words, 1) < 2 THEN
        RETURN to_tsquery(words[1]);
    END IF;

    result := to_tsquery(words[1]);
    FOR i IN 2..array_length(words, 1) LOOP
        result := tsquery_phrase(result, to_tsquery(words[i]), distance);
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 使用函数构建短语查询
SELECT build_phrase_query(ARRAY['PostgreSQL', '数据库', '系统']);

调试和开发函数

这些函数主要用于文本搜索配置的开发和调试。

配置调试函数

ts_debug() - 调试文本处理

分析文档的分词和标准化过程。

sql

-- 调试文本处理过程
SELECT * FROM ts_debug('english', 'The Brightest supernovaes');
-- 显示每个词元的处理信息：
-- - 词元类型
-- - 使用的字典
-- - 标准化结果

-- 分析中文文本处理
SELECT * FROM ts_debug('simple', 'PostgreSQL数据库系统');

ts_lexize() - 测试字典

测试特定字典对词元的处理。

sql

-- 测试英文词干提取
SELECT ts_lexize('english_stem', 'stars');
-- 结果: {star}

-- 测试停用词
SELECT ts_lexize('english_stem', 'the');
-- 结果: {} (空数组，表示停用词)

-- 测试未知词
SELECT ts_lexize('english_stem', 'unknownword123');
-- 结果: NULL (未知词)

解析器调试函数

ts_parse() - 测试解析器

分析文本的分词过程。

sql

-- 使用默认解析器
SELECT * FROM ts_parse('default', 'foo - bar');
-- 结果显示词元ID和文本：
-- (1,foo)
-- (12,-)
-- (1,bar)

-- 使用OID指定解析器
SELECT * FROM ts_parse(3722, 'foo - bar');

ts_token_type() - 查看解析器支持的词元类型

sql

-- 查看默认解析器的词元类型
SELECT * FROM ts_token_type('default');
-- 结果包括：
-- (1,asciiword,"Word, all ASCII")
-- (2,word,"Word, all letters")
-- ...

统计分析函数

ts_stat() - 文档集合统计

分析文档集合中的词位统计信息。

sql

-- 分析文档集合
SELECT * FROM ts_stat('SELECT search_vector FROM articles')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
-- 显示：
-- - word: 词位
-- - ndoc: 包含该词位的文档数
-- - nentry: 词位总出现次数

-- 带权重的统计
SELECT * FROM ts_stat(
    'SELECT search_vector FROM articles',
    'A'  -- 只统计权重A的词位
) ORDER BY nentry DESC;

实战应用示例

构建完整的文档搜索系统

sql

-- 创建文档表
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT,
    content TEXT NOT NULL,
    category TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    search_vector tsvector
);

-- 创建分层搜索向量函数
CREATE OR REPLACE FUNCTION build_document_search_vector(
    title TEXT,
    author TEXT,
    content TEXT,
    category TEXT
) RETURNS tsvector AS $$
BEGIN
    RETURN
        setweight(to_tsvector('simple', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(author, '')), 'B') ||
        setweight(to_tsvector('simple', COALESCE(category, '')), 'B') ||
        setweight(to_tsvector('simple', COALESCE(content, '')), 'C');
END;
$$ LANGUAGE plpgsql;

-- 创建触发器自动更新搜索向量
CREATE OR REPLACE FUNCTION update_document_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := build_document_search_vector(
        NEW.title, NEW.author, NEW.content, NEW.category
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_document_search_vector_trigger
    BEFORE INSERT OR UPDATE ON documents
    FOR EACH ROW EXECUTE FUNCTION update_document_search_vector();

-- 创建GIN索引
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);

-- 插入测试数据
INSERT INTO documents (title, author, content, category) VALUES
('PostgreSQL高级特性', '张三', 'PostgreSQL提供了强大的全文搜索功能，支持复杂查询和排名算法', '数据库'),
('MySQL性能优化', '李四', 'MySQL数据库的性能调优技巧和最佳实践方法', '数据库'),
('NoSQL数据库介绍', '王五', '介绍MongoDB、Redis等NoSQL数据库的特点和应用场景', '数据库');

-- 创建搜索函数
CREATE OR REPLACE FUNCTION search_documents(
    search_text TEXT,
    limit_count INT DEFAULT 10
) RETURNS TABLE(
    id INT,
    title TEXT,
    author TEXT,
    category TEXT,
    rank REAL,
    headline TEXT
) AS $$
DECLARE
    query tsquery;
BEGIN
    -- 构建查询
    query := websearch_to_tsquery('simple', search_text);

    RETURN QUERY
    SELECT
        d.id,
        d.title,
        d.author,
        d.category,
        ts_rank_cd(d.search_vector, query) as rank,
        ts_headline('simple', d.content, query, 'MaxWords=25') as headline
    FROM documents d
    WHERE d.search_vector @@ query
    ORDER BY rank DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

-- 使用搜索函数
SELECT * FROM search_documents('PostgreSQL 全文搜索');

多语言文档处理

sql

-- 创建多语言文档表
CREATE TABLE multilang_docs (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    language TEXT DEFAULT 'simple',
    search_vector tsvector
);

-- 多语言搜索向量构建函数
CREATE OR REPLACE FUNCTION build_multilang_search_vector(
    title TEXT,
    content TEXT,
    lang TEXT
) RETURNS tsvector AS $$
DECLARE
    config regconfig;
BEGIN
    -- 根据语言选择配置
    CASE lang
        WHEN 'english' THEN config := 'english';
        WHEN 'chinese' THEN config := 'simple';  -- 或使用中文配置
        ELSE config := 'simple';
    END CASE;

    RETURN
        setweight(to_tsvector(config, COALESCE(title, '')), 'A') ||
        setweight(to_tsvector(config, COALESCE(content, '')), 'C');
END;
$$ LANGUAGE plpgsql;

-- 创建更新触发器
CREATE OR REPLACE FUNCTION update_multilang_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := build_multilang_search_vector(
        NEW.title, NEW.content, NEW.language
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_multilang_search_vector_trigger
    BEFORE INSERT OR UPDATE ON multilang_docs
    FOR EACH ROW EXECUTE FUNCTION update_multilang_search_vector();

性能优化建议

索引策略

索引选择建议

GIN 索引：适合大多数文本搜索场景
GiST 索引：适合频繁更新的场景
分区索引：适合大型数据集

sql

-- 创建GIN索引（推荐）
CREATE INDEX idx_documents_gin ON documents USING gin(search_vector);

-- 创建GiST索引（更新频繁时）
CREATE INDEX idx_documents_gist ON documents USING gist(search_vector);

-- 部分索引（针对特定条件）
CREATE INDEX idx_documents_category_search
ON documents USING gin(search_vector)
WHERE category = '数据库';

查询优化

sql

-- 使用EXPLAIN分析查询性能
EXPLAIN (ANALYZE, BUFFERS)
SELECT title, ts_rank(search_vector, query) as rank
FROM documents, to_tsquery('simple', 'PostgreSQL') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 优化：预编译查询
PREPARE search_plan (text) AS
SELECT title, ts_rank(search_vector, to_tsquery('simple', $1)) as rank
FROM documents
WHERE search_vector @@ to_tsquery('simple', $1)
ORDER BY rank DESC;

EXECUTE search_plan('PostgreSQL');

常见问题和解决方案

中文分词问题

中文处理注意事项

PostgreSQL 内置的文本搜索主要针对英文设计，中文处理需要特殊考虑：

使用simple配置避免词干提取问题
考虑使用第三方中文分词扩展
合理处理标点符号和特殊字符

sql

-- 中文文本处理示例
SELECT to_tsvector('simple', '学习PostgreSQL数据库技术');
-- 建议预处理中文文本，添加空格分隔
SELECT to_tsvector('simple', '学习 PostgreSQL 数据库 技术');

性能调优

sql

-- 设置相关参数
SET default_text_search_config = 'simple';
SET work_mem = '256MB';  -- 增加排序内存

-- 定期维护统计信息
ANALYZE documents;

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

通过掌握这些文本搜索函数和操作符，您可以构建强大而高效的全文搜索应用。记住要根据具体的应用场景选择合适的函数组合，并注意性能优化。

PostgreSQL 文本搜索函数和操作符 ​

文本搜索核心概念 ​

文本搜索操作符 ​

基础匹配操作符 ​

@@ 匹配操作符 ​

向量组合操作符 ​

|| 连接操作符 ​

查询组合操作符 ​

&& 逻辑与操作符 ​

|| 逻辑或操作符 ​

!! 否定操作符 ​

短语查询操作符 ​

<-> 相邻短语操作符 ​

包含关系操作符 ​

@> 和 <@ 包含操作符 ​

文本搜索函数 ​

基础转换函数 ​

to_tsvector() - 文本转向量 ​

to_tsquery() - 文本转查询 ​

智能查询函数 ​

plainto_tsquery() - 平文本查询 ​

phraseto_tsquery() - 短语查询 ​

websearch_to_tsquery() - 网络搜索风格 ​

向量操作函数 ​

setweight() - 设置权重 ​

strip() - 移除位置权重信息 ​

信息提取函数 ​

length() 和 numnode() ​

tsvector_to_array() 和 unnest() ​

排名和高亮函数 ​

ts_rank() - 相关性评分 ​

ts_rank_cd() - 覆盖密度排名 ​

ts_headline() - 搜索结果高亮 ​

JSON 文档处理函数 ​

json_to_tsvector() 和 jsonb_to_tsvector() ​

向量编辑函数 ​

ts_delete() - 删除词位 ​

ts_filter() - 按权重过滤 ​

查询重写函数 ​

ts_rewrite() - 查询重写 ​

短语查询构建函数 ​

tsquery_phrase() - 构建短语查询 ​

调试和开发函数 ​

配置调试函数 ​

ts_debug() - 调试文本处理 ​

ts_lexize() - 测试字典 ​

解析器调试函数 ​

ts_parse() - 测试解析器 ​

ts_token_type() - 查看解析器支持的词元类型 ​

统计分析函数 ​

ts_stat() - 文档集合统计 ​

实战应用示例 ​

构建完整的文档搜索系统 ​

多语言文档处理 ​

性能优化建议 ​

索引策略 ​

查询优化 ​

常见问题和解决方案 ​

中文分词问题 ​

性能调优 ​