PostgreSQL 15.1及其他数据库中存储正则的列如何建索引？

问题背景 #

我们有一张存储正则表达式的表，需要传入指定文本，查询表中能匹配该文本的正则记录。可通过PostgreSQL的反向正则匹配运算符~实现（文本在前、存储正则的字段在后），示例代码如下：

DROP TABLE IF EXISTS public.patterntable;
CREATE TABLE IF NOT EXISTS public.patterntable
(
    id bigint NOT NULL,
    pattern text COLLATE pg_catalog."default" NOT NULL
);

INSERT INTO patterntable (id, pattern) VALUES (1, '.*');
INSERT INTO patterntable (id, pattern) VALUES (2, '^dog');
INSERT INTO patterntable (id, pattern) VALUES (3, 'dog$');

SELECT * FROM patterntable WHERE 'x' ~ pattern;

但这类查询会触发全表扫描，当表数据量大或正则字段较多时性能开销极高，因此需要解决以下两个问题：

1. 在PostgreSQL 15.1中，如何为存储正则表达式的列建立索引以优化此类查询？
2. 其他支持为正则表达式列建立索引的数据库方案有哪些？

1. PostgreSQL 15.1中的索引优化方案 #

PostgreSQL没有原生支持为反向正则匹配（文本匹配存储的正则）直接创建索引，但可以通过以下几种方案优化性能：

方式一：提取正则特征，建立B-tree索引 #

分析存储的正则表达式，提取可用于预过滤的特征（比如前缀、后缀、固定字符串片段），将这些特征存入额外字段并建立B-tree索引，查询时先通过特征过滤缩小范围，再对剩余记录执行正则匹配。
示例（针对前缀正则）：

-- 添加前缀特征字段
ALTER TABLE patterntable ADD COLUMN prefix text;
-- 更新前缀字段（仅处理^开头的正则）
UPDATE patterntable SET prefix = substring(pattern from '^\\^(.*)') WHERE pattern ~ '^\\^';
-- 建立前缀索引
CREATE INDEX idx_patterntable_prefix ON patterntable(prefix);

-- 优化后的查询：先过滤前缀匹配的记录，再执行正则验证
SELECT * FROM patterntable 
WHERE prefix IS NOT NULL AND 'dogxxx' LIKE prefix || '%'
AND 'dogxxx' ~ pattern;

方式二：使用pg_trgm扩展建立GIN/GIST索引 #

pg_trgm扩展通过三元组（trigram）匹配加速模糊查询，可借助它先过滤出与目标文本有重叠三元组的正则记录，再执行精确匹配：

1. 启用pg_trgm扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 为pattern字段建立GIN索引：

CREATE INDEX idx_patterntable_pattern_trgm ON patterntable USING GIN(pattern gin_trgm_ops);

3. 优化查询：

SELECT * FROM patterntable 
WHERE pattern % 'x'  -- 三元组相似性过滤，缩小验证范围
AND 'x' ~ pattern;

注意：该方案对简单正则效果较好，复杂正则的过滤效率可能有限。

方式三：自定义函数+表达式索引 #

如果正则有固定模式（比如都是前缀、后缀或简单通配符），可以编写自定义函数将正则转换为等价的LIKE模式，再为转换结果建立表达式索引：

-- 自定义函数：提取前缀正则的匹配字符串
CREATE OR REPLACE FUNCTION get_prefix_pattern(p text) RETURNS text AS $$
BEGIN
    IF p ~ '^\\^[^%_]+$' THEN  -- 匹配^开头且不含LIKE通配符的正则
        RETURN substring(p from '^\\^(.*)');
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 建立表达式索引
CREATE INDEX idx_patterntable_prefix_expr ON patterntable(get_prefix_pattern(pattern));

-- 查询时用索引过滤
SELECT * FROM patterntable 
WHERE get_prefix_pattern(pattern) IS NOT NULL 
AND 'dogxxx' LIKE get_prefix_pattern(pattern) || '%'
AND 'dogxxx' ~ pattern;

2. 其他数据库的支持方案 #

MySQL #

MySQL 8.0及以上版本支持REGEXP_LIKE函数，但原生不支持直接为存储的正则列建立反向匹配索引。可采用类似PostgreSQL的特征提取方案，或借助第三方插件扩展能力。

MongoDB #

MongoDB不支持直接为存储的正则表达式创建索引，但可以：

• 将正则的前缀、后缀等特征存入单独字段并建立索引，先过滤再执行正则匹配；
• 利用聚合管道的阶段过滤缩小范围，再执行正则验证；
• 结合文本索引优化模糊匹配场景。

Elasticsearch #

作为搜索引擎，Elasticsearch天然适合这类匹配场景：

• 可将正则表达式作为文档字段存储，利用其倒排索引机制加速正则查询；
• 提前将正则转换为分词特征存入索引字段，进一步提升查询效率。

Oracle #

Oracle支持REGEXP_LIKE函数，可通过函数索引优化正向正则匹配，但反向匹配仍需依赖特征提取：将正则的关键特征存入单独字段并建立B-tree或函数索引，再执行正则验证。

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