Hubert 'depesz' Lubaczewski：PostgreSQL 正则表达式的新事物（pg_tre 和 pg_re2）
内容提要
PostgreSQL 新增 pg_tre 和 pg_re2 扩展以优化正则表达式查询。pg_tre 支持模糊匹配，但索引构建耗时且体积大；pg_re2 基于 RE2 库，索引构建较快，查询速度显著优于传统 trigram 索引，尤其对复杂正则表达式性能提升明显。两者各有优劣，但均展示了 PostgreSQL 正则表达式优化的潜力。
延伸解读
索引构建成本与查询性能的权衡
pg_tre 索引构建耗时超过 7 小时，体积达 21GB，而 pg_trgm 索引构建约 17 分钟，体积 1.7GB。pg_re2 索引构建约 15 分钟，体积 2.9GB。尽管 pg_tre 查询速度尚可，但构建时间和存储开销巨大，实际使用需权衡。pg_re2 在构建时间和查询性能上表现更均衡，尤其对复杂正则表达式，查询速度约为 pg_trgm 的一半。
正则表达式支持范围的差异
pg_tre 不支持某些正则语法，如后行断言（lookbehind），而 pg_re2 也因 RE2 库限制不支持该语法。但 pg_tre 支持模糊匹配（基于 Levenshtein 距离），可用于拼写纠错或近似搜索，这是 pg_trgm 和 pg_re2 不具备的。用户需根据实际需求选择，若需模糊匹配可考虑 pg_tre，但需接受其构建成本。
实际应用建议
对于简单模式匹配，pg_trgm 仍是可靠选择，因其构建快、体积小且查询性能良好。pg_re2 适合复杂正则表达式且对查询速度要求高的场景，但需注意其索引体积较大。pg_tre 目前更适合小数据集或对模糊匹配有特殊需求的场景，其构建时间过长可能限制其在大数据量下的实用性。
Q&A
PostgreSQL 中新增的 pg_tre 和 pg_re2 扩展分别是什么？
pg_tre 和 pg_re2 是 PostgreSQL 的两个扩展，用于优化正则表达式查询。pg_tre 支持模糊匹配，基于 TRE 库；pg_re2 基于 RE2 库，索引构建较快，查询速度显著优于传统 trigram 索引。
pg_tre 扩展的索引构建时间和大小如何？
在测试中，pg_tre 索引构建耗时约 7 小时 15 分钟，索引大小为 21GB，远大于 trigram 索引的 1.6GB。
pg_re2 扩展相比 pg_trgm 在查询性能上有何优势？
pg_re2 在查询复杂正则表达式时速度更快。例如，对于正则表达式 '(su){3}'，pg_re2 索引查询耗时约 0.96 秒，而 pg_trgm 索引耗时约 1.6 秒；对于更复杂的正则表达式，pg_re2 耗时约 2.3 秒，而 pg_trgm 耗时约 39.4 秒。
pg_tre 扩展支持模糊匹配吗？如何使用？
是的，pg_tre 支持模糊匹配，通过 tre_pattern 函数和 Levenshtein 距离实现。例如，可以使用 tre_pattern('postgresql', 1) 来匹配与 'postgresql' 编辑距离为 1 的单词。
pg_re2 扩展不支持哪些正则表达式特性？
pg_re2 基于 RE2 库，为了性能优化，不支持某些特性，如后行断言（lookbehind）。例如，正则表达式 '(?<=e.)aa[bc]c[b-d]' 在 pg_re2 中无法使用，需要改写为等价形式 '(^|^.|[^e].)aa[bc]c[b-d]'。
pg_tre 和 pg_re2 的索引构建时间相比 pg_trgm 如何？
pg_tre 索引构建时间最长，约 7 小时 15 分钟；pg_re2 索引构建时间约 14 分 58 秒，比 pg_trgm 的 16 分 47 秒略快。
