PostgreSQL 14前缀匹配插入触发器性能优化求助

1. 优化索引结构，适配前缀匹配查询 #

当前普通B-tree索引对LIKE 'prefix%'的匹配效率有限，建议创建带文本模式算子的复合覆盖索引，让查询直接通过索引完成，避免回表和全表扫描：

-- 为prefixes表创建适配前缀匹配的覆盖索引，包含前缀和长度（降序）
CREATE INDEX idx_prefixes_prefix_length ON prefixes 
USING btree (prefix text_pattern_ops, prefix_length DESC)
INCLUDE (prefix); -- 确保索引包含prefix字段，实现覆盖查询

该索引会优先匹配前缀，同时按prefix_length降序排序，刚好满足取最长前缀的需求，查询时可直接从索引中获取结果，无需访问表数据。

2. 改为从最长到最短的精确匹配查询 #

放弃原LIKE模糊匹配+排序逻辑，针对NEW.from/NEW.to从13位到1位依次尝试精确匹配，一旦找到匹配项就终止查询，避免全表排序和扫描：

CREATE OR REPLACE FUNCTION foo.bar()
RETURNS TRIGGER AS $$
DECLARE
  v_from text := NEW.from;
  v_to text := NEW.to;
BEGIN
  -- 从最长的13位前缀开始尝试，找到即返回
  NEW.from_prefix = COALESCE(
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 13) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 12) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 11) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 10) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 9) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 8) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 7) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 6) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 5) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 4) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 3) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 2) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_from, 1) LIMIT 1)
  );

  NEW.to_prefix = COALESCE(
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 13) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 12) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 11) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 10) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 9) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 8) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 7) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 6) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 5) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 4) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 3) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 2) LIMIT 1),
    (SELECT prefix FROM prefixes WHERE prefix = left(v_to, 1) LIMIT 1)
  );

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这种方式利用精确匹配的索引快速查找，且一旦找到最长匹配就停止后续查询，性能提升明显。

3. 预分组物化视图优化查询范围 #

将prefixes表按prefix_length降序分组，创建物化视图存储每组的前缀集合，查询时从最长长度组开始检查，避免遍历全表：

-- 创建按前缀长度降序分组的物化视图
CREATE MATERIALIZED VIEW mv_prefixes_grouped AS
SELECT prefix_length, array_agg(prefix) AS prefix_list
FROM prefixes
GROUP BY prefix_length
ORDER BY prefix_length DESC;

-- 为物化视图创建长度索引
CREATE INDEX idx_mv_prefixes_length ON mv_prefixes_grouped (prefix_length DESC);

修改触发器函数，遍历物化视图的分组，逐个检查当前长度的前缀是否在组内：

CREATE OR REPLACE FUNCTION foo.bar()
RETURNS TRIGGER AS $$
DECLARE
  v_from text := NEW.from;
  v_to text := NEW.to;
  v_prefix text;
BEGIN
  -- 查找from的最长前缀
  FOR v_prefix IN 
    SELECT unnest(prefix_list) 
    FROM mv_prefixes_grouped 
    WHERE left(v_from, prefix_length) = ANY(prefix_list)
    LIMIT 1
  LOOP
    NEW.from_prefix = v_prefix;
    EXIT;
  END LOOP;

  -- 查找to的最长前缀
  FOR v_prefix IN 
    SELECT unnest(prefix_list) 
    FROM mv_prefixes_grouped 
    WHERE left(v_to, prefix_length) = ANY(prefix_list)
    LIMIT 1
  LOOP
    NEW.to_prefix = v_prefix;
    EXIT;
  END LOOP;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

注意：若prefixes表有更新，需定期刷新物化视图：REFRESH MATERIALIZED VIEW mv_prefixes_grouped;

4. 触发器函数的轻量优化 #

• 提前将NEW.from/NEW.to赋值给局部变量，避免多次引用NEW行的开销
• 禁用不必要的序列扫描（仅在索引确实有效的情况下使用）

CREATE OR REPLACE FUNCTION foo.bar()
RETURNS TRIGGER AS $$
DECLARE
  v_from text := NEW.from;
  v_to text := NEW.to;
BEGIN
  -- 强制使用索引，避免序列扫描
  SET LOCAL enable_seqscan = off;

  NEW.from_prefix = (SELECT p.prefix FROM prefixes p 
                     WHERE v_from LIKE p.prefix || '%' 
                     ORDER BY p.prefix_length DESC LIMIT 1);
  NEW.to_prefix = (SELECT p.prefix FROM prefixes p 
                    WHERE v_to LIKE p.prefix || '%' 
                    ORDER BY p.prefix_length DESC LIMIT 1);

  -- 恢复默认设置
  RESET enable_seqscan;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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