PostgreSQL 10中优化多子串regexp_replace性能的技术咨询

针对你在PostgreSQL 10里处理20万行数据、用regexp_replace移除大量停用词性能不足的问题，结合你已经为Names列创建了trigram/GIN索引的情况，我整理了几个亲测有效的提速方案：

1. 精准筛选需要处理的行，避免全表扫描 #

既然你已经有了Names_trgm索引，一定要利用它快速定位包含停用词的行，只处理这些行而不是全表20万行，这能直接减少大量不必要的计算。

假设你有一个存储停用词的表stop_words(word text)，可以用以下条件筛选目标行：

-- 只处理包含至少一个停用词的行
SELECT * FROM NameTable nt
WHERE EXISTS (
    SELECT 1 FROM stop_words sw 
    WHERE nt.Names % sw.word  -- 利用trigram索引快速匹配
);

2. 替换方案：拆分-过滤-拼接（比正则更高效） #

如果你的Names列是空格分隔的多词字符串，直接拆分单词、过滤停用词再拼接回去，性能会比多次regexp_replace好很多，因为避开了正则表达式的开销：

UPDATE NameTable nt
SET Names = (
    -- 拆分单词，过滤停用词后重新拼接
    SELECT string_agg(w.word, ' ')
    FROM unnest(string_to_array(nt.Names, ' ')) AS w(word)
    WHERE NOT EXISTS (
        SELECT 1 FROM stop_words sw WHERE sw.word = w.word
    )
)
-- 只更新需要处理的行
WHERE EXISTS (
    SELECT 1 FROM stop_words sw 
    WHERE nt.Names % sw.word
);

如果你的词分隔符不是空格，只需要把string_to_array的第二个参数改成对应的分隔符即可。

3. 批量正则替换（适合停用词数量较少的场景） #

如果停用词数量不多（比如几百个以内），可以把所有停用词合并成一个正则表达式，一次性完成替换，减少函数调用次数：

WITH stop_regex AS (
    -- 生成匹配词边界的正则，避免误替换子串（比如"cat"不会替换"category"）
    SELECT '\m(' || string_agg(DISTINCT word, '|') || ')\M' AS pattern
    FROM stop_words
)
UPDATE NameTable
SET Names = regexp_replace(Names, (SELECT pattern FROM stop_regex), '', 'g')
WHERE Names ~ (SELECT pattern FROM stop_regex);

注意：如果停用词数量过千，这个正则表达式会变得很长，反而会降低性能，这时优先用方案2。

4. 用临时表减少IO开销 #

处理20万行数据时，直接更新原表可能会产生大量日志和IO操作。可以先把需要处理的行导入临时表，在临时表完成替换后再更新原表，临时表默认在内存中操作，速度更快：

-- 1. 把需要处理的行导入临时表
CREATE TEMP TABLE temp_process_names AS
SELECT id, Names
FROM NameTable nt
WHERE EXISTS (
    SELECT 1 FROM stop_words sw WHERE nt.Names % sw.word
);

-- 2. 在临时表中完成替换
UPDATE temp_process_names tn
SET Names = (
    SELECT string_agg(w.word, ' ')
    FROM unnest(string_to_array(tn.Names, ' ')) AS w(word)
    WHERE NOT EXISTS (
        SELECT 1 FROM stop_words sw WHERE sw.word = w.word
    )
);

-- 3. 更新原表
UPDATE NameTable nt
SET Names = tn.Names
FROM temp_process_names tn
WHERE nt.id = tn.id;

-- 4. 清理临时表
DROP TABLE temp_process_names;

5. 开启并行查询加速 #

PostgreSQL 10已经支持并行查询，可以通过调整参数或添加查询提示来让数据库使用多个进程并行处理数据：

• 临时调整参数（会话级）：

SET max_parallel_workers_per_gather = 4;  -- 根据你的CPU核心数设置

• 在查询中添加并行提示：

UPDATE NameTable nt
SET Names = ...
WHERE ...
PARALLEL 4;

额外优化点 #

• 给stop_words表的word列也创建trigram索引，提升匹配速度：

CREATE INDEX stop_words_trgm ON stop_words USING GIN (word gin_trgm_ops);

• 避免使用NOT IN，改用NOT EXISTS，防止停用词表中出现NULL值时导致的意外结果。

内容的提问来源于stack exchange，提问作者Evan W.