PostgreSQL psql 文本搜索支持

PostgreSQL 的 psql 命令行工具提供了一套强大的命令来检查和管理文本搜索配置对象。这些命令让用户能够深入了解文本搜索系统的内部结构，包括配置、字典、解析器和模板。

文本搜索检查命令概览

基本命令格式

sql

\dF{d,p,t}[+] [PATTERN]

参数说明：

+：可选，提供更详细的信息
PATTERN：可选，用于过滤结果的模式匹配
{d,p,t}：指定对象类型
- d：字典 (Dictionaries)
- p：解析器 (Parsers)
- t：模板 (Templates)

模式匹配规则

INFO

模式匹配支持

PATTERN 支持正则表达式，可以为模式（schema）和对象名称提供单独的模式匹配。

核心命令详解

1. 文本搜索配置查看 (`\dF`)

问题陈述： 需要查看数据库中可用的文本搜索配置以及它们的详细信息。

解决方案： 使用 \dF 命令系列

基本配置列表

sql

-- 查看所有文本搜索配置
\dF

-- 查看特定配置
\dF russian

输出示例：

       List of text search configurations
 Schema |  Name   |            Description
--------+---------+------------------------------------
 public | russian | configuration for russian language

详细配置信息

sql

-- 获取配置的详细信息
\dF+ russian

输出示例：

Text search configuration "pg_catalog.russian"
Parser: "pg_catalog.default"
      Token      | Dictionaries
-----------------+--------------
 asciihword      | english_stem
 asciiword       | english_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | russian_stem
 hword_asciipart | english_stem
 hword_numpart   | simple
 hword_part      | russian_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | russian_stem

分析过程：

解析器映射：显示配置使用的解析器（这里是 default）
令牌映射：每种令牌类型对应的字典处理策略
多字典支持：不同令牌类型可以使用不同的词干提取器

实际应用场景

sql

-- 创建自定义配置用于产品搜索
CREATE TEXT SEARCH CONFIGURATION product_search (COPY = english);

-- 修改特定令牌的处理方式
ALTER TEXT SEARCH CONFIGURATION product_search
    ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;

-- 检查新配置
\dF+ product_search

2. 文本搜索字典查看 (`\dFd`)

问题陈述： 了解系统中可用的词典及其功能特点。

解决方案： 使用 \dFd 命令

sql

-- 查看所有字典
\dFd

-- 查看特定字典的详细信息
\dFd+ english_stem

输出示例：

                         List of text search dictionaries
   Schema   |      Name       |                        Description
------------+-----------------+-----------------------------------------------------------
 pg_catalog | arabic_stem     | snowball stemmer for arabic language
 pg_catalog | armenian_stem   | snowball stemmer for armenian language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language

字典分类和用途

Details

字典类型详解

Snowball 词干提取器：

用于词汇规范化
支持多种语言
将单词还原为词根形式

Simple 字典：

基础的小写转换
停用词过滤
适用于简单场景

多语言支持示例

sql

-- 创建多语言文档表
CREATE TABLE multilang_content (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    language VARCHAR(10),
    search_vector TSVECTOR
);

-- 插入测试数据
INSERT INTO multilang_content (title, content, language) VALUES
('English Article', 'PostgreSQL database management system', 'en'),
('Spanish Article', 'Sistema de gestión de bases de datos', 'es'),
('French Article', 'Système de gestion de base de données', 'fr');

-- 根据语言选择相应的字典
UPDATE multilang_content
SET search_vector = to_tsvector(
    CASE language
        WHEN 'en' THEN 'english'::regconfig
        WHEN 'es' THEN 'spanish'::regconfig
        WHEN 'fr' THEN 'french'::regconfig
        ELSE 'simple'::regconfig
    END,
    title || ' ' || content
);

-- 验证不同语言的处理效果
SELECT language, to_tsvector('spanish', 'administración') AS spanish_stem,
       to_tsvector('english', 'administration') AS english_stem;

3. 文本搜索解析器查看 (`\dFp`)

问题陈述： 深入了解文本解析器的工作机制和令牌类型。

解决方案： 使用 \dFp 命令

sql

-- 查看可用解析器
\dFp

-- 查看解析器详细信息
\dFp+

输出示例：

    Text search parser "pg_catalog.default"
     Method      |    Function    | Description
-----------------+----------------+-------------
 Start parse     | prsd_start     |
 Get next token  | prsd_nexttoken |
 End parse       | prsd_end       |
 Get headline    | prsd_headline  |
 Get token types | prsd_lextype   |

        Token types for parser "pg_catalog.default"
   Token name    |               Description
-----------------+------------------------------------------
 asciihword      | Hyphenated word, all ASCII
 asciiword       | Word, all ASCII
 email           | Email address
 file            | File or path name
 float           | Decimal notation
 host            | Host
 hword           | Hyphenated word, all letters
 int             | Signed integer
 url             | URL
 word            | Word, all letters

解析器工作流程

令牌类型实际应用

sql

-- 创建技术文档表
CREATE TABLE tech_docs (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    doc_vector TSVECTOR
);

-- 插入包含多种令牌类型的文档
INSERT INTO tech_docs (title, content) VALUES
('PostgreSQL Setup', 'Contact [email protected] for server config at 192.168.1.100:5432'),
('Software Release', 'Download postgresql-14.2.tar.gz from https://postgresql.org/download/');

-- 创建搜索向量
UPDATE tech_docs SET doc_vector = to_tsvector('english', title || ' ' || content);

-- 测试不同令牌类型的搜索
SELECT title,
       doc_vector @@ to_tsquery('email') AS has_email,
       doc_vector @@ to_tsquery('postgresql-14.2') AS has_version,
       doc_vector @@ to_tsquery('192.168.1.100:5432') AS has_host
FROM tech_docs;

4. 文本搜索模板查看 (`\dFt`)

问题陈述： 了解可用的字典模板以创建自定义字典。

解决方案： 使用 \dFt 命令

sql

-- 查看所有模板
\dFt

-- 查看模板详细信息
\dFt+ snowball

输出示例：

                       List of text search templates
   Schema   |   Name    |                        Description
------------+-----------+-----------------------------------------------------------
 pg_catalog | ispell    | ispell dictionary
 pg_catalog | simple    | simple dictionary: just lower case and check for stopword
 pg_catalog | snowball  | snowball stemmer
 pg_catalog | synonym   | synonym dictionary: replace word by its synonym
 pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution

自定义字典创建示例

sql

-- 1. 创建同义词字典
CREATE TEXT SEARCH DICTIONARY product_synonyms (
    TEMPLATE = synonym,
    SYNONYMS = product_synonyms  -- 指向同义词文件
);

-- 2. 创建自定义词典文件内容 (product_synonyms.syn)
-- mobile,smartphone,phone
-- laptop,notebook,computer
-- db,database

-- 3. 创建自定义配置
CREATE TEXT SEARCH CONFIGURATION product_config (COPY = english);

-- 4. 应用自定义字典
ALTER TEXT SEARCH CONFIGURATION product_config
    ALTER MAPPING FOR asciiword WITH product_synonyms, english_stem;

-- 5. 测试同义词效果
SELECT to_tsvector('product_config', 'mobile phone') @@
       to_tsquery('product_config', 'smartphone') AS synonym_match;

高级模式匹配技巧

模式匹配语法详解

sql

-- 按名称模式匹配
\dF *fulltext*        -- 名称包含 fulltext 的配置

-- 按模式和名称组合匹配
\dF *.fulltext*       -- 任意模式下名称包含 fulltext 的配置

-- 正则表达式匹配
\dF ^pg_catalog.*     -- pg_catalog 模式下的所有配置

实际输出示例：

sql

=> \dF *fulltext*
       List of text search configurations
 Schema |  Name        | Description
--------+--------------+-------------
 public | fulltext_cfg |

=> \dF *.fulltext*
       List of text search configurations
 Schema   |  Name        | Description
----------+--------------+-------------
 fulltext | fulltext_cfg |
 public   | fulltext_cfg |

批量检查脚本

sql

-- 创建文本搜索对象检查脚本
DO $$
DECLARE
    config_rec RECORD;
    dict_count INTEGER;
BEGIN
    -- 检查每个配置的字典使用情况
    FOR config_rec IN
        SELECT schemaname, configname
        FROM pg_ts_config_map m
        JOIN pg_ts_config c ON m.mapcfg = c.oid
        JOIN pg_namespace n ON c.cfgnamespace = n.oid
        GROUP BY schemaname, configname
    LOOP
        SELECT COUNT(*) INTO dict_count
        FROM pg_ts_config_map m
        JOIN pg_ts_config c ON m.mapcfg = c.oid
        JOIN pg_namespace n ON c.cfgnamespace = n.oid
        WHERE n.nspname = config_rec.schemaname
        AND c.cfgname = config_rec.configname;

        RAISE NOTICE 'Configuration %.% uses % dictionaries',
            config_rec.schemaname, config_rec.configname, dict_count;
    END LOOP;
END $$;

性能监控和调试

1. 配置性能分析

sql

-- 分析文本搜索配置的使用效率
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM documents
WHERE content_vector @@ to_tsquery('postgresql & performance');

-- 查看索引使用情况
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname LIKE '%_gin_idx';

2. 配置对比测试

sql

-- 对比不同配置的处理效果
WITH config_comparison AS (
    SELECT
        'english' AS config_name,
        to_tsvector('english', 'running runners run') AS tokens
    UNION ALL
    SELECT
        'simple' AS config_name,
        to_tsvector('simple', 'running runners run') AS tokens
)
SELECT config_name, tokens FROM config_comparison;

输出对比：

 config_name |      tokens
-------------+------------------
 english     | 'run':1,2,3      -- 词干提取后统一为 'run'
 simple      | 'run':3 'runner':2 'running':1  -- 保持原始形式

故障排除指南

常见问题诊断

WARNING

配置问题检查清单

字典文件缺失：检查字典文件是否在正确位置
权限问题：确认用户有访问配置的权限
编码问题：验证文本编码与数据库编码匹配
内存不足：大文档解析可能需要更多内存

sql

-- 检查配置完整性
SELECT c.cfgname, d.dictname, t.tmplname
FROM pg_ts_config c
JOIN pg_ts_config_map m ON c.oid = m.mapcfg
JOIN pg_ts_dict d ON m.mapdict = d.oid
JOIN pg_ts_template t ON d.dicttemplate = t.oid
WHERE c.cfgname = 'your_config_name';

-- 测试配置是否正常工作
SELECT to_tsvector('your_config_name', 'test text') IS NOT NULL AS config_works;

性能优化建议

TIP

优化策略

选择合适的配置：根据语言选择对应的配置
限制字典数量：避免在一个令牌类型上使用过多字典
定期维护：使用 VACUUM 和 REINDEX 维护索引性能
监控使用情况：定期检查配置和字典的使用统计

实际业务场景应用

企业文档管理系统

sql

-- 创建企业文档搜索系统
CREATE TABLE enterprise_docs (
    id SERIAL PRIMARY KEY,
    department VARCHAR(50),
    title TEXT,
    content TEXT,
    doc_type VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    search_vector TSVECTOR
);

-- 创建部门特定的搜索配置
CREATE TEXT SEARCH CONFIGURATION hr_config (COPY = english);
CREATE TEXT SEARCH CONFIGURATION tech_config (COPY = english);

-- 根据部门使用不同配置
UPDATE enterprise_docs
SET search_vector = to_tsvector(
    CASE department
        WHEN 'HR' THEN 'hr_config'::regconfig
        WHEN 'Engineering' THEN 'tech_config'::regconfig
        ELSE 'english'::regconfig
    END,
    title || ' ' || content
);

-- 部门特定搜索
SELECT department, title,
       ts_rank(search_vector, query) AS relevance
FROM enterprise_docs,
     to_tsquery('tech_config', 'database & optimization') AS query
WHERE department = 'Engineering'
AND search_vector @@ query
ORDER BY relevance DESC;

通过掌握这些 psql 文本搜索支持命令，您可以深入了解和管理 PostgreSQL 的全文搜索系统，从而构建高效、准确的搜索功能。这些工具不仅帮助诊断问题，还能指导您优化搜索配置以获得最佳性能。

PostgreSQL psql 文本搜索支持 ​

文本搜索检查命令概览 ​

基本命令格式 ​

模式匹配规则 ​

核心命令详解 ​

1. 文本搜索配置查看 (\dF) ​

基本配置列表 ​

详细配置信息 ​

实际应用场景 ​

2. 文本搜索字典查看 (\dFd) ​

字典分类和用途 ​

多语言支持示例 ​

3. 文本搜索解析器查看 (\dFp) ​

解析器工作流程 ​

令牌类型实际应用 ​

4. 文本搜索模板查看 (\dFt) ​

自定义字典创建示例 ​

高级模式匹配技巧 ​

模式匹配语法详解 ​

批量检查脚本 ​

性能监控和调试 ​

1. 配置性能分析 ​

2. 配置对比测试 ​

故障排除指南 ​

常见问题诊断 ​

性能优化建议 ​

实际业务场景应用 ​

企业文档管理系统 ​

PostgreSQL psql 文本搜索支持

文本搜索检查命令概览

基本命令格式

模式匹配规则

核心命令详解

1. 文本搜索配置查看 (`\dF`)

基本配置列表

详细配置信息

实际应用场景

2. 文本搜索字典查看 (`\dFd`)

字典分类和用途

多语言支持示例

3. 文本搜索解析器查看 (`\dFp`)

解析器工作流程

令牌类型实际应用

4. 文本搜索模板查看 (`\dFt`)

自定义字典创建示例

高级模式匹配技巧

模式匹配语法详解

批量检查脚本

性能监控和调试

1. 配置性能分析

2. 配置对比测试

故障排除指南

常见问题诊断

性能优化建议

实际业务场景应用

企业文档管理系统