PostgreSQL 14.1行级插入触发器性能优化：最长前缀匹配过慢求助

一、核心问题分析 #

当前行级BEFORE INSERT触发器对每条direction=1的记录执行两次独立子查询，批量导入（如CSV导入）时会触发数万次重复查询，这是性能骤降的核心原因。同时LIKE前缀匹配的查询效率仍有优化空间。

二、基础优化：索引与查询语句优化 #

1. 重构prefixes表结构 #

移除无用字段并改用生成列自动维护前缀长度，避免手动维护错误：

-- 移除不需要的network_id字段
ALTER TABLE access_analytics.prefixes DROP COLUMN network_id;

-- 替换手动维护的prefix_length为生成列
ALTER TABLE access_analytics.prefixes
DROP COLUMN prefix_length,
ADD COLUMN prefix_length INT GENERATED ALWAYS AS (LENGTH(prefix)) STORED;

2. 创建高效前缀匹配索引 #

放弃原有单一索引，创建复合索引支持范围查询与长度排序：

-- 创建前缀+长度降序的复合索引，同时支持范围匹配和最长前缀排序
DROP INDEX IF EXISTS idx_prefixes_prefix_len_desc;
CREATE INDEX idx_prefixes_prefix_len_desc 
ON access_analytics.prefixes 
USING btree (prefix, prefix_length DESC);

3. 替换LIKE为范围查询 #

LIKE 'prefix%'等价于范围查询caller_number >= prefix AND caller_number < prefix || '99999999999999999999'（假设号码最长20位），后者能更高效利用btree索引：

CREATE OR REPLACE FUNCTION cdrs.fn_cdr_prefixes()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.direction = 1 THEN
    NEW.caller_prefix = (
      SELECT p.prefix
      FROM access_analytics.prefixes p
      WHERE NEW.caller_number >= p.prefix
        AND NEW.caller_number < p.prefix || repeat('9', 20) -- 适配号码最大长度
      ORDER BY p.prefix_length DESC
      LIMIT 1
    );
    NEW.clear_prefix = (
      SELECT p.prefix
      FROM access_analytics.prefixes p
      WHERE NEW.clear_number >= p.prefix
        AND NEW.clear_number < p.prefix || repeat('9', 20)
      ORDER BY p.prefix_length DESC
      LIMIT 1
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

4. 改用SQL触发器函数替代PL/pgSQL #

SQL函数的执行开销远低于PL/pgSQL，进一步降低单条记录处理成本：

CREATE OR REPLACE FUNCTION cdrs.fn_cdr_prefixes()
RETURNS TRIGGER AS $$
SELECT
  CASE WHEN NEW.direction = 1 THEN (
    SELECT p.prefix FROM access_analytics.prefixes p
    WHERE NEW.caller_number >= p.prefix AND NEW.caller_number < p.prefix || repeat('9', 20)
    ORDER BY p.prefix_length DESC LIMIT 1
  ) ELSE NEW.caller_prefix END AS caller_prefix,
  CASE WHEN NEW.direction = 1 THEN (
    SELECT p.prefix FROM access_analytics.prefixes p
    WHERE NEW.clear_number >= p.prefix AND NEW.clear_number < p.prefix || repeat('9', 20)
    ORDER BY p.prefix_length DESC LIMIT 1
  ) ELSE NEW.clear_prefix END AS clear_prefix,
  NEW.*;
$$ LANGUAGE sql STABLE;

三、进阶优化：批量处理替代行级触发器 #

行级触发器是批量导入的性能瓶颈，改用临时表+批量LATERAL JOIN的方式处理CSV导入，将数万次单条查询合并为少量批量查询：

-- 1. 创建与目标表结构一致的临时表
CREATE TEMP TABLE temp_cdr (
  caller_number TEXT,
  clear_number TEXT,
  direction INT,
  -- 其他字段与cdrs.mc_cdr保持一致
);

-- 2. 批量导入CSV到临时表
COPY temp_cdr FROM '/path/to/your/call_data.csv' WITH (FORMAT CSV, HEADER);

-- 3. 批量匹配前缀并插入目标表
INSERT INTO cdrs.mc_cdr (caller_number, clear_number, caller_prefix, clear_prefix, -- 其他字段)
SELECT
  t.caller_number,
  t.clear_number,
  COALESCE(cp.prefix, '') AS caller_prefix,
  COALESCE(clp.prefix, '') AS clear_prefix,
  -- 映射其他字段
  t.xxx, t.yyy
FROM temp_cdr t
LEFT JOIN LATERAL (
  SELECT p.prefix
  FROM access_analytics.prefixes p
  WHERE t.caller_number >= p.prefix
    AND t.caller_number < p.prefix || repeat('9', 20)
  ORDER BY p.prefix_length DESC
  LIMIT 1
) cp ON t.direction = 1
LEFT JOIN LATERAL (
  SELECT p.prefix
  FROM access_analytics.prefixes p
  WHERE t.clear_number >= p.prefix
    AND t.clear_number < p.prefix || repeat('9', 20)
  ORDER BY p.prefix_length DESC
  LIMIT 1
) clp ON t.direction = 1;

-- 4. 清理临时表（可选）
DROP TABLE temp_cdr;

四、极端场景优化：前缀预计算与缓存 #

如果prefixes表数据极少变动（如每日更新一次），可以预计算所有可能的号码前缀映射，存入专用缓存表：

-- 创建缓存表
CREATE TABLE access_analytics.prefix_cache (
  phone_number TEXT PRIMARY KEY,
  caller_prefix TEXT,
  clear_prefix TEXT
);

-- 定期更新缓存（如每日凌晨）
TRUNCATE access_analytics.prefix_cache;
INSERT INTO access_analytics.prefix_cache (phone_number, caller_prefix, clear_prefix)
SELECT
  DISTINCT mc.caller_number,
  (SELECT p.prefix FROM access_analytics.prefixes p WHERE mc.caller_number >= p.prefix AND mc.caller_number < p.prefix || repeat('9',20) ORDER BY p.prefix_length DESC LIMIT 1),
  (SELECT p.prefix FROM access_analytics.prefixes p WHERE mc.clear_number >= p.prefix AND mc.clear_number < p.prefix || repeat('9',20) ORDER BY p.prefix_length DESC LIMIT 1)
FROM cdrs.mc_cdr mc
WHERE mc.direction = 1;

导入时直接关联缓存表获取前缀，性能接近直接插入。

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