PostgreSQL 文本搜索附加功能

PostgreSQL 的文本搜索系统不仅提供基础的搜索能力，还包含丰富的附加功能来增强搜索体验。本章节将深入探讨文档操作、查询操作、自动触发器和统计分析等高级功能。

文档操作 (tsvector 操作)

核心概念

文档操作主要针对已经转换为 tsvector 格式的文档进行处理，包括向量连接、权重设置、长度计算和位置信息剥离等操作。

向量连接运算符 (`||`)

基本原理

tsvector 连接运算符将两个向量的词素和位置信息合并，右侧向量的位置会基于左侧向量的最大位置进行偏移。

业务场景：多部分文档索引

在电商平台中，商品信息通常包含标题、描述、规格等多个部分，需要分别处理后合并。

sql

-- 创建商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT,
    specifications TEXT,
    search_vector tsvector
);

-- 插入示例数据
INSERT INTO products (title, description, specifications) VALUES
('iPhone 15 Pro', '最新款苹果手机，配备 A17 芯片', '存储：256GB，颜色：深空黑色');

-- 分别生成向量并连接
SELECT
    to_tsvector('english', title) AS title_vector,
    to_tsvector('english', description) AS desc_vector,
    to_tsvector('english', specifications) AS spec_vector,
    to_tsvector('english', title) ||
    to_tsvector('english', description) ||
    to_tsvector('english', specifications) AS combined_vector
FROM products WHERE id = 1;

输出结果：

title_vector: '15':2 'iphon':1 'pro':3
desc_vector: 'a17':6 '芯片':7 '最新':1 '款':2 '苹果':3 '手机':4 '配备':5
spec_vector: '256gb':2 '存储':1 '颜色':3 '深空':4 '黑色':5
combined_vector: '15':2 'a17':9 'iphon':1 'pro':3 '芯片':10 '最新':4 ...

分析过程：

标题向量包含位置 1-3
描述向量的位置会从 4 开始（3+1）
规格向量的位置继续递增，避免位置冲突

TIP

连接优势使用向量连接而非文本连接的优势：

可以对不同部分使用不同的文本搜索配置
能够为不同部分设置不同的权重
提高处理效率，避免重复解析

权重设置函数 (`setweight`)

权重系统概述

PostgreSQL 支持四种权重级别：A（最高）、B、C、D（最低，默认）。权重影响相关性排名的计算。

实战示例：新闻文章索引

sql

-- 创建新闻表
CREATE TABLE news_articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    subtitle TEXT,
    content TEXT,
    tags TEXT,
    search_vector tsvector
);

-- 插入示例数据
INSERT INTO news_articles (title, subtitle, content, tags) VALUES
(
    'AI 技术突破性进展',
    '机器学习在医疗诊断中的应用',
    '近日，研究人员开发出新的机器学习算法，能够更准确地诊断疾病...',
    '人工智能,医疗,技术'
);

-- 设置不同权重并合并
UPDATE news_articles
SET search_vector =
    setweight(to_tsvector('chinese', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('chinese', coalesce(subtitle, '')), 'B') ||
    setweight(to_tsvector('chinese', coalesce(content, '')), 'C') ||
    setweight(to_tsvector('chinese', coalesce(tags, '')), 'D')
WHERE id = 1;

-- 查看权重设置结果
SELECT search_vector FROM news_articles WHERE id = 1;

输出示例：

'ai':1A 'breakthrough':3A 'technology':2A 'machine':1B 'learning':2B 'medical':3B 'diagnosis':4B 'researchers':1C 'developed':2C 'algorithm':4C 'artificial':1D 'intelligence':2D 'healthcare':3D

权重对排名的影响

sql

-- 创建测试查询，比较不同权重的影响
SELECT
    title,
    ts_rank(search_vector, to_tsquery('chinese', 'AI')) AS rank_with_weights,
    ts_rank(strip(search_vector), to_tsquery('chinese', 'AI')) AS rank_without_weights
FROM news_articles
WHERE search_vector @@ to_tsquery('chinese', 'AI')
ORDER BY rank_with_weights DESC;

WARNING

权重注意事项

权重信息存储在位置标记中，如果使用 strip() 函数会丢失权重
权重设置应该在向量连接之前进行
合理的权重分配能显著提升搜索相关性

向量长度和剥离操作

长度函数 (`length`)

sql

-- 比较不同文档的向量长度
SELECT
    title,
    length(search_vector) AS vector_length,
    length(strip(search_vector)) AS stripped_length
FROM news_articles;

剥离函数 (`strip`)

剥离函数移除位置和权重信息，减小存储空间但降低搜索质量。

sql

-- 演示剥离前后的差异
SELECT
    search_vector AS original,
    strip(search_vector) AS stripped,
    pg_column_size(search_vector) AS original_size,
    pg_column_size(strip(search_vector)) AS stripped_size
FROM news_articles LIMIT 1;

Details

何时使用剥离 适用场景：

存储空间极度受限
只需要简单的关键词匹配
不需要相关性排名

不适用场景：

需要短语搜索 (<-> 操作符)
需要精确的相关性排名
需要位置敏感的搜索

查询操作 (tsquery 操作)

查询运算符

PostgreSQL 提供了丰富的查询运算符来构建复杂的搜索条件。

实战示例：高级搜索功能

逻辑运算符组合

sql

-- 创建博客文章表
CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    category TEXT,
    search_vector tsvector
);

-- 插入测试数据
INSERT INTO blog_posts (title, content, category) VALUES
('PostgreSQL 性能优化指南', '本文介绍 PostgreSQL 数据库的性能优化技巧...', '数据库'),
('Python 机器学习实战', '使用 Python 和机器学习算法解决实际问题...', '编程'),
('PostgreSQL 与 Python 集成', '如何在 Python 项目中高效使用 PostgreSQL...', '数据库');

-- 更新搜索向量
UPDATE blog_posts
SET search_vector = to_tsvector('chinese', title || ' ' || content);

-- 复合查询示例
-- 1. 必须包含"PostgreSQL"且包含"性能"或"优化"
SELECT title, content
FROM blog_posts
WHERE search_vector @@ (
    to_tsquery('chinese', 'PostgreSQL') &&
    (to_tsquery('chinese', '性能') || to_tsquery('chinese', '优化'))
);

-- 2. 包含"Python"但不包含"机器学习"
SELECT title, content
FROM blog_posts
WHERE search_vector @@ (
    to_tsquery('chinese', 'Python') &&
    !!to_tsquery('chinese', '机器学习')
);

位置敏感搜索

sql

-- 短语搜索：查找"PostgreSQL 性能"这个短语
SELECT title,
       search_vector,
       to_tsquery('chinese', 'PostgreSQL') <-> to_tsquery('chinese', '性能') AS phrase_query
FROM blog_posts
WHERE search_vector @@ (to_tsquery('chinese', 'PostgreSQL') <-> to_tsquery('chinese', '性能'));

-- 距离搜索：查找"PostgreSQL"和"Python"之间距离不超过5个词的内容
SELECT title,
       tsquery_phrase(to_tsquery('chinese', 'PostgreSQL'), to_tsquery('chinese', 'Python'), 5) AS distance_query
FROM blog_posts
WHERE search_vector @@ tsquery_phrase(to_tsquery('chinese', 'PostgreSQL'), to_tsquery('chinese', 'Python'), 5);

查询分析和优化

查询节点分析

sql

-- 分析查询复杂度
SELECT
    query_text,
    numnode(to_tsquery('chinese', query_text)) AS node_count,
    querytree(to_tsquery('chinese', query_text)) AS query_tree
FROM (VALUES
    ('PostgreSQL'),
    ('PostgreSQL & 性能'),
    ('PostgreSQL | Python | 机器学习'),
    ('!!停用词')
) AS queries(query_text);

输出示例：

query_text                    | node_count | query_tree
PostgreSQL                   | 1          | 'postgresql'
PostgreSQL & 性能            | 3          | 'postgresql' & '性能'
PostgreSQL | Python | 机器学习| 5          | 'postgresql' | 'python' | '机器' & '学习'
!!停用词                     | 0          |

INFO

查询优化提示

numnode() 返回 0 表示查询只包含停用词或无效词素
querytree() 显示实际用于搜索的查询结构
复杂查询会增加计算成本，需要平衡功能和性能

查询重写系统

查询重写是一个强大的功能，允许动态扩展或修改搜索查询，常用于同义词扩展和查询优化。

基础重写示例

sql

-- 创建同义词映射表
CREATE TABLE search_synonyms (
    original tsquery PRIMARY KEY,
    replacement tsquery
);

-- 插入同义词规则
INSERT INTO search_synonyms VALUES
(to_tsquery('chinese', 'AI'), to_tsquery('chinese', 'AI | 人工智能 | 机器智能')),
(to_tsquery('chinese', 'DB'), to_tsquery('chinese', 'DB | 数据库 | database')),
(to_tsquery('chinese', 'ML'), to_tsquery('chinese', 'ML | 机器学习 | machine_learning'));

-- 应用重写规则
SELECT
    original_query,
    ts_rewrite(
        to_tsquery('chinese', original_query),
        'SELECT original, replacement FROM search_synonyms'
    ) AS rewritten_query
FROM (VALUES
    ('AI'),
    ('DB & 性能'),
    ('ML | AI')
) AS queries(original_query);

高级重写策略

sql

-- 创建复杂的重写规则表
CREATE TABLE advanced_synonyms (
    target tsquery,
    substitute tsquery,
    category TEXT,
    priority INTEGER
);

-- 插入分类的同义词规则
INSERT INTO advanced_synonyms VALUES
(to_tsquery('chinese', '性能'), to_tsquery('chinese', '性能 | 效率 | 速度'), '技术', 1),
(to_tsquery('chinese', '教程'), to_tsquery('chinese', '教程 | 指南 | 手册'), '学习', 2),
(to_tsquery('chinese', '问题'), to_tsquery('chinese', '问题 | 故障 | 错误 | bug'), '技术', 1);

-- 按优先级应用重写
WITH prioritized_rules AS (
    SELECT target, substitute
    FROM advanced_synonyms
    WHERE category = '技术'
    ORDER BY priority
)
SELECT ts_rewrite(
    to_tsquery('chinese', 'PostgreSQL & 性能 & 问题'),
    'SELECT target, substitute FROM (' ||
    'SELECT target, substitute FROM advanced_synonyms WHERE category = ''技术'' ORDER BY priority' ||
    ') AS rules'
) AS final_query;

自动更新触发器

触发器原理

自动触发器确保当源文档发生变化时，对应的 tsvector 列能够自动更新，维护索引的一致性。

基础触发器实现

单配置触发器

sql

-- 创建消息表
CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    search_vector tsvector
);

-- 创建自动更新触发器
CREATE TRIGGER messages_search_update
BEFORE INSERT OR UPDATE ON messages
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.chinese', title, body);

-- 测试触发器
INSERT INTO messages (title, body) VALUES
('重要通知', '系统将于明天进行维护升级'),
('会议安排', '下周一下午两点在会议室召开项目讨论会');

-- 验证自动生成的搜索向量
SELECT id, title, search_vector
FROM messages;

多配置动态触发器

sql

-- 创建支持多语言的文档表
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    language regconfig DEFAULT 'pg_catalog.chinese'::regconfig,
    search_vector tsvector
);

-- 创建动态语言配置触发器
CREATE TRIGGER documents_search_update
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger_column(search_vector, language, title, content);

-- 测试多语言文档
INSERT INTO documents (title, content, language) VALUES
('中文文档', '这是一份中文技术文档', 'pg_catalog.chinese'::regconfig),
('English Document', 'This is an English technical document', 'pg_catalog.english'::regconfig);

-- 验证不同语言的处理结果
SELECT title, language, search_vector
FROM documents;

高级自定义触发器

权重分级触发器

sql

-- 创建新闻表
CREATE TABLE news (
    id SERIAL PRIMARY KEY,
    headline TEXT,
    subtitle TEXT,
    content TEXT,
    tags TEXT,
    search_vector tsvector
);

-- 创建自定义权重触发器函数
CREATE OR REPLACE FUNCTION news_search_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('pg_catalog.chinese', coalesce(NEW.headline, '')), 'A') ||
        setweight(to_tsvector('pg_catalog.chinese', coalesce(NEW.subtitle, '')), 'B') ||
        setweight(to_tsvector('pg_catalog.chinese', coalesce(NEW.content, '')), 'C') ||
        setweight(to_tsvector('pg_catalog.chinese', coalesce(NEW.tags, '')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER news_search_update
BEFORE INSERT OR UPDATE ON news
FOR EACH ROW EXECUTE FUNCTION news_search_trigger();

-- 测试分级权重
INSERT INTO news (headline, subtitle, content, tags) VALUES
(
    'AI 技术突破',
    '深度学习算法新进展',
    '研究人员在深度学习领域取得重大突破，新算法在图像识别准确率上提升了15%...',
    '人工智能,深度学习,图像识别'
);

-- 验证权重分配
SELECT headline, search_vector
FROM news WHERE id = 1;

条件触发器

sql

-- 创建带条件逻辑的触发器
CREATE OR REPLACE FUNCTION smart_search_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- 只有当实际内容发生变化时才更新
    IF (TG_OP = 'INSERT') OR
       (OLD.headline IS DISTINCT FROM NEW.headline) OR
       (OLD.content IS DISTINCT FROM NEW.content) THEN

        NEW.search_vector :=
            setweight(to_tsvector('pg_catalog.chinese', coalesce(NEW.headline, '')), 'A') ||
            setweight(to_tsvector('pg_catalog.chinese', coalesce(NEW.content, '')), 'C');

        -- 记录更新日志
        INSERT INTO search_update_log (table_name, record_id, updated_at)
        VALUES (TG_TABLE_NAME, NEW.id, NOW());
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

WARNING

触发器性能考量 优化建议：

避免在触发器中执行复杂计算
使用条件判断减少不必要的更新
考虑使用 AFTER 触发器减少锁定时间
定期监控触发器的执行时间

潜在问题：

大批量更新时可能影响性能
触发器失败会导致整个事务回滚
递归触发器可能导致无限循环

文档统计分析

统计函数概述

ts_stat 函数提供了强大的文档集统计分析能力，帮助理解文档内容分布和优化搜索配置。

基础统计分析

全文档统计

sql

-- 创建博客文章样本数据
CREATE TABLE blog_articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    category TEXT,
    search_vector tsvector
);

-- 插入样本数据
INSERT INTO blog_articles (title, content, category) VALUES
('PostgreSQL 性能调优', 'PostgreSQL 数据库性能优化是一个复杂的主题...', '数据库'),
('Python 编程技巧', 'Python 是一种强大的编程语言，适合各种应用开发...', '编程'),
('机器学习算法', '机器学习是人工智能的核心技术之一...', '人工智能'),
('数据分析实战', '数据分析在现代商业决策中发挥重要作用...', '数据科学');

-- 更新搜索向量
UPDATE blog_articles
SET search_vector = to_tsvector('chinese', title || ' ' || content);

-- 基础统计分析
SELECT word, ndoc, nentry,
       round((ndoc::float / (SELECT count(*) FROM blog_articles))::numeric, 3) AS doc_frequency,
       round((nentry::float / ndoc)::numeric, 2) AS avg_occurrences
FROM ts_stat('SELECT search_vector FROM blog_articles')
ORDER BY nentry DESC, ndoc DESC
LIMIT 15;

输出示例：

word     | ndoc | nentry | doc_frequency | avg_occurrences
---------|------|--------|---------------|----------------
数据     | 3    | 5      | 0.750         | 1.67
技术     | 2    | 3      | 0.500         | 1.50
PostgreSQL| 1   | 2      | 0.250         | 2.00
Python   | 1    | 2      | 0.250         | 2.00

权重统计分析

sql

-- 按权重分别统计
SELECT
    'A级权重' AS weight_level,
    word, ndoc, nentry
FROM ts_stat('SELECT search_vector FROM blog_articles', 'a')
ORDER BY nentry DESC LIMIT 5

UNION ALL

SELECT
    'B级权重' AS weight_level,
    word, ndoc, nentry
FROM ts_stat('SELECT search_vector FROM blog_articles', 'b')
ORDER BY nentry DESC LIMIT 5

UNION ALL

SELECT
    'C级权重' AS weight_level,
    word, ndoc, nentry
FROM ts_stat('SELECT search_vector FROM blog_articles', 'c')
ORDER BY nentry DESC LIMIT 5;

高级统计应用

停用词识别

sql

-- 识别可能的停用词候选
WITH word_stats AS (
    SELECT word, ndoc, nentry,
           (ndoc::float / (SELECT count(*) FROM blog_articles)) AS doc_ratio
    FROM ts_stat('SELECT search_vector FROM blog_articles')
),
stopword_candidates AS (
    SELECT word, ndoc, nentry, doc_ratio,
           CASE
               WHEN doc_ratio > 0.8 THEN '强停用词候选'
               WHEN doc_ratio > 0.6 THEN '中等停用词候选'
               WHEN doc_ratio > 0.4 THEN '弱停用词候选'
               ELSE '正常词汇'
           END AS stopword_level
    FROM word_stats
)
SELECT stopword_level, word, ndoc, nentry,
       round(doc_ratio::numeric, 3) AS document_frequency
FROM stopword_candidates
WHERE stopword_level != '正常词汇'
ORDER BY doc_ratio DESC;

内容主题分析

sql

-- 主题词分析
WITH topic_analysis AS (
    SELECT
        word,
        ndoc,
        nentry,
        CASE
            WHEN word IN ('数据', '分析', '统计') THEN '数据科学'
            WHEN word IN ('编程', 'Python', '开发') THEN '软件开发'
            WHEN word IN ('机器', '学习', '算法', '智能') THEN '人工智能'
            WHEN word IN ('数据库', 'PostgreSQL', '性能') THEN '数据库技术'
            ELSE '其他'
        END AS topic
    FROM ts_stat('SELECT search_vector FROM blog_articles')
    WHERE nentry >= 2  -- 过滤低频词
)
SELECT
    topic,
    count(*) AS word_count,
    sum(nentry) AS total_occurrences,
    array_agg(word ORDER BY nentry DESC) AS top_words
FROM topic_analysis
WHERE topic != '其他'
GROUP BY topic
ORDER BY total_occurrences DESC;

文档相似度分析

sql

-- 基于词汇重叠的文档相似度
CREATE OR REPLACE FUNCTION document_similarity(doc1_id INT, doc2_id INT)
RETURNS FLOAT AS $$
DECLARE
    common_words INT;
    total_words INT;
BEGIN
    -- 计算共同词汇数量
    WITH doc1_words AS (
        SELECT unnest(string_to_array(strip(search_vector)::text, ' ')) AS word
        FROM blog_articles WHERE id = doc1_id
    ),
    doc2_words AS (
        SELECT unnest(string_to_array(strip(search_vector)::text, ' ')) AS word
        FROM blog_articles WHERE id = doc2_id
    )
    SELECT
        count(DISTINCT d1.word) AS common_count,
        (SELECT count(DISTINCT word) FROM doc1_words) +
        (SELECT count(DISTINCT word) FROM doc2_words) AS total_count
    INTO common_words, total_words
    FROM doc1_words d1
    JOIN doc2_words d2 ON d1.word = d2.word;

    RETURN CASE
        WHEN total_words > 0 THEN (2.0 * common_words) / total_words
        ELSE 0
    END;
END;
$$ LANGUAGE plpgsql;

-- 计算文档间相似度矩阵
SELECT
    a1.id AS doc1_id,
    a1.title AS doc1_title,
    a2.id AS doc2_id,
    a2.title AS doc2_title,
    round(document_similarity(a1.id, a2.id)::numeric, 3) AS similarity
FROM blog_articles a1
CROSS JOIN blog_articles a2
WHERE a1.id < a2.id
ORDER BY similarity DESC;

性能监控和优化

索引效果评估

sql

-- 评估文本搜索索引的选择性
WITH index_stats AS (
    SELECT
        word,
        ndoc,
        nentry,
        (SELECT count(*) FROM blog_articles) AS total_docs,
        ndoc::float / (SELECT count(*) FROM blog_articles) AS selectivity
    FROM ts_stat('SELECT search_vector FROM blog_articles')
)
SELECT
    CASE
        WHEN selectivity < 0.1 THEN '高选择性'
        WHEN selectivity < 0.3 THEN '中等选择性'
        WHEN selectivity < 0.6 THEN '低选择性'
        ELSE '极低选择性'
    END AS selectivity_level,
    count(*) AS word_count,
    round(avg(selectivity)::numeric, 3) AS avg_selectivity
FROM index_stats
GROUP BY
    CASE
        WHEN selectivity < 0.1 THEN '高选择性'
        WHEN selectivity < 0.3 THEN '中等选择性'
        WHEN selectivity < 0.6 THEN '低选择性'
        ELSE '极低选择性'
    END
ORDER BY avg_selectivity;

Details

统计分析最佳实践

定期分析建议：

每周分析：识别新的停用词候选
每月分析：评估搜索配置效果
季度分析：调整权重分配策略

优化策略：

基于统计结果调整停用词列表
根据词频分布优化索引策略
使用主题分析改进内容分类

性能考量：

ts_stat 函数会扫描所有文档，适合离线分析
大数据集建议使用采样或分批处理
结合数据库监控工具评估查询性能

综合实战案例

电商搜索系统

让我们构建一个完整的电商产品搜索系统，整合所有学到的功能。

sql

-- 1. 创建产品表结构
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT,
    brand TEXT,
    price DECIMAL(10,2),
    tags TEXT,
    search_vector tsvector,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. 创建搜索配置表
CREATE TABLE search_config (
    category TEXT PRIMARY KEY,
    language regconfig DEFAULT 'pg_catalog.chinese'::regconfig,
    name_weight CHAR(1) DEFAULT 'A',
    description_weight CHAR(1) DEFAULT 'C',
    brand_weight CHAR(1) DEFAULT 'B',
    tags_weight CHAR(1) DEFAULT 'D'
);

-- 3. 插入配置数据
INSERT INTO search_config (category, name_weight, description_weight, brand_weight, tags_weight) VALUES
('电子产品', 'A', 'C', 'B', 'D'),
('服装', 'A', 'B', 'B', 'C'),
('图书', 'A', 'B', 'C', 'D');

-- 4. 创建智能触发器
CREATE OR REPLACE FUNCTION product_search_trigger()
RETURNS TRIGGER AS $$
DECLARE
    config_rec RECORD;
BEGIN
    -- 获取分类配置
    SELECT * INTO config_rec
    FROM search_config
    WHERE category = NEW.category;

    -- 如果没有配置则使用默认值
    IF NOT FOUND THEN
        config_rec.language := 'pg_catalog.chinese'::regconfig;
        config_rec.name_weight := 'A';
        config_rec.description_weight := 'C';
        config_rec.brand_weight := 'B';
        config_rec.tags_weight := 'D';
    END IF;

    -- 构建搜索向量
    NEW.search_vector :=
        setweight(to_tsvector(config_rec.language, coalesce(NEW.name, '')), config_rec.name_weight) ||
        setweight(to_tsvector(config_rec.language, coalesce(NEW.description, '')), config_rec.description_weight) ||
        setweight(to_tsvector(config_rec.language, coalesce(NEW.brand, '')), config_rec.brand_weight) ||
        setweight(to_tsvector(config_rec.language, coalesce(NEW.tags, '')), config_rec.tags_weight);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 5. 创建触发器
CREATE TRIGGER product_search_update
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION product_search_trigger();

-- 6. 插入测试数据
INSERT INTO products (name, description, category, brand, price, tags) VALUES
('iPhone 15 Pro Max', '苹果最新旗舰手机，配备 A17 仿生芯片，支持 5G 网络', '电子产品', '苹果', 9999.00, '手机,5G,拍照,游戏'),
('MacBook Pro M3', '专业级笔记本电脑，适合开发和设计工作', '电子产品', '苹果', 15999.00, '笔记本,办公,设计,编程'),
('Nike Air Max', '经典运动鞋，舒适透气，适合日常穿着', '服装', 'Nike', 899.00, '运动鞋,跑步,时尚,舒适');

-- 7. 创建同义词扩展
CREATE TABLE product_synonyms (
    original tsquery,
    expanded tsquery,
    category TEXT
);

INSERT INTO product_synonyms VALUES
(to_tsquery('chinese', '手机'), to_tsquery('chinese', '手机 | 电话 | phone | 移动电话'), '电子产品'),
(to_tsquery('chinese', '笔记本'), to_tsquery('chinese', '笔记本 | laptop | 电脑 | 计算机'), '电子产品'),
(to_tsquery('chinese', '鞋子'), to_tsquery('chinese', '鞋子 | 鞋 | shoes | 运动鞋'), '服装');

-- 8. 创建智能搜索函数
CREATE OR REPLACE FUNCTION smart_product_search(search_term TEXT, search_category TEXT DEFAULT NULL)
RETURNS TABLE(
    product_id INT,
    product_name TEXT,
    product_category TEXT,
    product_brand TEXT,
    product_price DECIMAL,
    relevance_score FLOAT
) AS $$
DECLARE
    base_query tsquery;
    expanded_query tsquery;
BEGIN
    -- 构建基础查询
    base_query := to_tsquery('chinese', search_term);

    -- 应用同义词扩展
    SELECT ts_rewrite(
        base_query,
        format('SELECT original, expanded FROM product_synonyms WHERE category = %L OR category IS NULL',
               COALESCE(search_category, ''))
    ) INTO expanded_query;

    -- 执行搜索
    RETURN QUERY
    SELECT
        p.id,
        p.name,
        p.category,
        p.brand,
        p.price,
        ts_rank(p.search_vector, expanded_query) AS score
    FROM products p
    WHERE p.search_vector @@ expanded_query
      AND (search_category IS NULL OR p.category = search_category)
    ORDER BY score DESC, p.price;
END;
$$ LANGUAGE plpgsql;

-- 9. 测试搜索功能
SELECT * FROM smart_product_search('苹果 手机');
SELECT * FROM smart_product_search('笔记本 编程', '电子产品');

搜索分析报告

sql

-- 生成搜索效果分析报告
WITH search_analysis AS (
    SELECT
        category,
        count(*) AS product_count,
        avg(length(search_vector)) AS avg_vector_length,
        (SELECT count(DISTINCT word)
         FROM ts_stat(format('SELECT search_vector FROM products WHERE category = %L', category))
        ) AS unique_words
    FROM products
    GROUP BY category
),
category_stats AS (
    SELECT
        s.category,
        s.product_count,
        s.avg_vector_length,
        s.unique_words,
        round((s.unique_words::float / s.product_count)::numeric, 2) AS word_diversity
    FROM search_analysis s
)
SELECT
    category AS "产品分类",
    product_count AS "产品数量",
    round(avg_vector_length::numeric, 1) AS "平均向量长度",
    unique_words AS "唯一词汇数",
    word_diversity AS "词汇多样性"
FROM category_stats
ORDER BY product_count DESC;

TIP

企业级实施建议

架构设计：

使用分区表处理大量产品数据
实施读写分离优化搜索性能
考虑使用外部搜索引擎如 Elasticsearch 处理复杂场景

运维监控：

定期分析搜索热词和无结果查询
监控触发器执行时间和频率
建立搜索质量评估机制

业务优化：

基于用户行为数据调整权重配置
实施 A/B 测试评估搜索改进效果
结合商业逻辑（如库存、价格）优化排序

总结

PostgreSQL 的文本搜索附加功能为构建强大的搜索系统提供了丰富的工具集。通过合理运用文档操作、查询操作、自动触发器和统计分析功能，我们可以构建出既高效又灵活的企业级搜索解决方案。

关键要点回顾：

文档操作：使用向量连接和权重设置优化搜索相关性
查询操作：运用查询重写和位置运算符实现智能搜索
自动触发器：确保搜索索引的实时性和一致性
统计分析：基于数据驱动的方式优化搜索配置

在实际应用中，需要根据具体的业务需求和数据特点，合理组合这些功能，并持续监控和优化搜索效果。

PostgreSQL 文本搜索附加功能 ​

文档操作 (tsvector 操作) ​

核心概念 ​

向量连接运算符 (||) ​

基本原理 ​

业务场景：多部分文档索引 ​

权重设置函数 (setweight) ​

权重系统概述 ​

实战示例：新闻文章索引 ​

权重对排名的影响 ​

向量长度和剥离操作 ​

长度函数 (length) ​

剥离函数 (strip) ​

查询操作 (tsquery 操作) ​

查询运算符 ​

实战示例：高级搜索功能 ​

逻辑运算符组合 ​

位置敏感搜索 ​

查询分析和优化 ​

查询节点分析 ​

查询重写系统 ​

基础重写示例 ​

高级重写策略 ​

自动更新触发器 ​

触发器原理 ​

基础触发器实现 ​

单配置触发器 ​

多配置动态触发器 ​

高级自定义触发器 ​

权重分级触发器 ​

条件触发器 ​

文档统计分析 ​

统计函数概述 ​

基础统计分析 ​

全文档统计 ​

权重统计分析 ​

高级统计应用 ​

停用词识别 ​

内容主题分析 ​

文档相似度分析 ​

性能监控和优化 ​

索引效果评估 ​

综合实战案例 ​

电商搜索系统 ​

搜索分析报告 ​

总结 ​

PostgreSQL 文本搜索附加功能

文档操作 (tsvector 操作)

核心概念

向量连接运算符 (`||`)

基本原理

业务场景：多部分文档索引

权重设置函数 (`setweight`)

权重系统概述

实战示例：新闻文章索引

权重对排名的影响

向量长度和剥离操作

长度函数 (`length`)

剥离函数 (`strip`)

查询操作 (tsquery 操作)

查询运算符

实战示例：高级搜索功能

逻辑运算符组合

位置敏感搜索

查询分析和优化

查询节点分析

查询重写系统

基础重写示例

高级重写策略

自动更新触发器

触发器原理

基础触发器实现

单配置触发器

多配置动态触发器

高级自定义触发器

权重分级触发器

条件触发器

文档统计分析

统计函数概述

基础统计分析

全文档统计

权重统计分析

高级统计应用

停用词识别

内容主题分析

文档相似度分析

性能监控和优化

索引效果评估

综合实战案例

电商搜索系统

搜索分析报告

总结