PostgreSQL 14中高效实现字符串前缀自连接的方法

针对你在PostgreSQL 14中处理55000条医疗编码、构建前缀-编码映射表的性能问题，以下是几种远优于朴素JOIN的高效实现方案：

方案一：递归CTE（前缀树分层匹配） #

利用编码长度分层的特性，从最短编码开始递归匹配更长的前缀关联项，避免全表对全表的暴力扫描：

WITH RECURSIVE prefix_hierarchy AS (
    -- 初始节点：每个编码自身作为自己的前缀
    SELECT 
        code AS prefix, 
        code AS code,
        length(code) AS code_len
    FROM medical_codes
    
    UNION ALL
    
    -- 递归步骤：仅匹配长度比当前编码大1的候选项，缩小匹配范围
    SELECT 
        ph.prefix, 
        mc.code,
        length(mc.code) AS code_len
    FROM prefix_hierarchy ph
    JOIN medical_codes mc 
        ON length(mc.code) = ph.code_len + 1
        AND mc.code LIKE ph.code || '%'
)
SELECT prefix, code
FROM prefix_hierarchy
ORDER BY prefix, code_len;

优势 #

• 复杂度从朴素JOIN的O(n²)降至O(n*k)（k为编码最大长度与最小长度的差值，此处仅为5），性能提升显著
• 复用你已创建的medical_codes_code_length_idx索引，进一步加速长度过滤逻辑

方案二：GIN文本搜索索引 #

利用PostgreSQL的文本搜索特性，针对前缀匹配场景创建GIN索引，替代低效的动态LIKE匹配：

-- 创建前缀匹配专用的GIN索引
CREATE INDEX medical_codes_code_ts_idx ON medical_codes USING GIN (to_tsvector('simple', code));

-- 基于索引的前缀匹配查询
SELECT 
    mc1.code AS prefix,
    mc2.code AS code
FROM medical_codes mc1
JOIN medical_codes mc2 
    ON to_tsvector('simple', mc2.code) @@ to_tsquery('simple', mc1.code || ':*')
ORDER BY mc1.code, mc2.code;

优势 #

• simple文本配置不做词干处理，完美适配编码的精确前缀匹配
• GIN索引可快速定位所有匹配前缀的编码，避免逐行扫描
• 适合频繁执行前缀查询的场景，一次建索引可重复使用

方案三：预生成所有可能前缀（批量构建场景） #

如果只需一次性构建查找表并长期使用，可先预生成每个编码的所有合法前缀，再关联匹配：

WITH code_prefixes AS (
    -- 生成每个编码的所有有效前缀（从3位到自身长度）
    SELECT 
        code,
        substring(code FROM 1 FOR n) AS prefix
    FROM medical_codes,
         generate_series(3, length(code)) n
)
SELECT 
    cp.prefix,
    mc.code
FROM code_prefixes cp
JOIN medical_codes mc ON mc.code LIKE cp.prefix || '%'
GROUP BY cp.prefix, mc.code  -- 去重避免重复前缀的冗余匹配
ORDER BY cp.prefix, mc.code;

优势 #

• 提前枚举所有可能的前缀，将动态匹配转化为索引关联
• 适合批量生成静态查找表的场景，生成后可将结果存入新表并添加索引

后续优化建议 #

如果需要长期使用前缀映射表，建议将结果存入专用表并添加索引：

-- 生成持久化的前缀查找表
CREATE TABLE code_prefix_lookup AS
WITH RECURSIVE prefix_hierarchy AS (
    SELECT code AS prefix, code AS code, length(code) AS code_len
    FROM medical_codes
    UNION ALL
    SELECT ph.prefix, mc.code, length(mc.code) AS code_len
    FROM prefix_hierarchy ph
    JOIN medical_codes mc 
        ON length(mc.code) = ph.code_len + 1
        AND mc.code LIKE ph.code || '%'
)
SELECT prefix, code FROM prefix_hierarchy;

-- 添加索引加速后续查询
CREATE INDEX idx_prefix_lookup_prefix ON code_prefix_lookup(prefix);
CREATE INDEX idx_prefix_lookup_code ON code_prefix_lookup(code);

内容的提问来源于stack exchange，提问作者Frerich Raabe