你发现嵌套regexp_replace比分步赋值快10%这个结果完全符合预期，这背后其实是PostgreSQL对表达式求值的优化逻辑在起作用。

为什么嵌套写法更快？ #

当使用嵌套写法时，PostgreSQL会把两次正则替换作为一个连续的表达式来处理，避免了中间结果存储到变量时的额外开销——虽然单步变量存储的开销看起来微乎其微，但在百万级循环或大量记录处理场景下，累积起来的差异就会很明显。分步赋值需要把每次替换的结果写入变量内存，再读取出来进行下一次替换；而嵌套写法可以直接把第一次替换的输出作为第二次的输入，减少了内存读写的往返操作。

嵌套写法是否推荐？ #

这种嵌套写法完全是值得推荐的，尤其是当你的替换逻辑是线性的（上一次的输出就是下一次的输入）时，它既简洁又高效。不过需要注意：如果后续你的替换规则变得复杂，比如需要分支判断（某些条件下才执行某条替换），那分步赋值的可读性会更好，这时候就要在性能和代码可维护性之间做权衡。

其他更优的优化方案 #

除了嵌套写法，还有几个可以进一步提升性能的方向：

• 合并正则规则：如果替换逻辑允许，把多条规则合并成一个正则表达式是最有效的优化。比如你测试代码里的两次替换，可以直接合并成regexp_replace(..., '(A|B|C|1)', 'x', 'g')，跳过中间替换成空格的步骤，彻底减少一次正则引擎的调用。
• 用translate替代简单单字符替换：如果只是单字符对单字符的替换（比如把空格换成x），translate函数的性能远高于regexp_replace，因为它不需要启动正则引擎。比如translate(_output, ' ', 'x')会比regexp_replace(_output, ' ', 'x', 'g')快很多。
• 预编译正则表达式：在PostgreSQL 12及以上版本中，你可以用pg_regex_compile预编译正则表达式，重复使用编译后的结果，减少每次替换时的正则解析开销。
• 批量处理而非逐行循环：如果是处理百万级记录，尽量避免在PL/pgSQL函数里逐行处理，直接用SQL批量更新，利用PostgreSQL的批量处理能力，效率会提升数倍。

你的测试代码对比 #

非嵌套写法

do $$ declare i record; _output text; begin for i in 1..100000 loop _output = regexp_replace((SELECT string_agg(c, '') FROM (SELECT c FROM regexp_split_to_table( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', '' ) c ORDER BY random() LIMIT 6 ) AS q) ,'A|B|C|1',' ','g'); _output = regexp_replace(_output,' ','x','g'); end loop; end; $$ ;

嵌套写法

do $$ declare i record; _output text; begin for i in 1..100000 loop _output = regexp_replace(regexp_replace((SELECT string_agg(c, '') FROM (SELECT c FROM regexp_split_to_table( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', '' ) c ORDER BY random() LIMIT 6 ) AS q) ,'A|B|C|1',' ','g'),' ','x','g'); end loop; end; $$ ;

实际地址清洗函数的优化版本 #

结合嵌套写法和预编译正则，适合百万级记录的处理场景：

CREATE OR REPLACE FUNCTION address_appartment_cleanup_test(_input text) 
RETURNS text 
LANGUAGE plpgsql 
AS $body$
DECLARE
  regex_apartment regproc := pg_regex_compile('^APARTMENT ', 'g');
  regex_suite regproc := pg_regex_compile('^SUITE ', 'g');
BEGIN
  _input := regexp_replace(regexp_replace(_input, regex_apartment, 'APT '), regex_suite, 'STE ');
  RETURN _input;
END; 
$body$ VOLATILE COST 100;

测试调用：

SELECT address_appartment_cleanup_test('APARTMENT 10'); -- 输出 APT 10
SELECT address_appartment_cleanup_test('SUITE 10'); -- 输出 STE 10

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