PostgreSQL 15：将行级前置插入触发器改为语句级优化批量插入

核心问题分析 #

行级触发器的性能瓶颈在于每行插入时都要执行两次独立的前缀查询，批量插入大量数据时，重复查询会导致严重的性能损耗。而语句级BEFORE INSERT触发器无法直接修改NEW行集合（语句级触发器无法感知单条行的NEW数据），因此需要换用更高效的批量处理方案。

方案1：插入时直接关联前缀表（最优） #

放弃触发器，直接在INSERT语句中通过LATERAL JOIN批量匹配最长前缀，一次性完成插入和前缀填充。这种方式避免了触发器的额外开销，所有逻辑在单条SQL中完成，性能提升最明显。

示例代码 #

假设你从CSV导入的原始数据先存入临时表（不含前缀字段），再批量插入主表：

-- 1. 创建临时表存放CSV原始数据
CREATE TEMP TABLE temp_calls (
    the_time timestamptz,
    a_number text,
    b_number text
);

-- 2. 导入CSV到临时表（替换为你的实际文件路径和参数）
COPY temp_calls FROM '/path/to/your/calls.csv' WITH (FORMAT csv, HEADER);

-- 3. 批量插入到calls表，同时填充前缀
INSERT INTO calls (the_time, a_number, a_prefix, b_number, b_prefix)
SELECT
    tc.the_time,
    tc.a_number,
    a_p.prefix AS a_prefix,
    tc.b_number,
    b_p.prefix AS b_prefix
FROM temp_calls tc
-- 匹配a_number的最长前缀
LEFT JOIN LATERAL (
    SELECT p.prefix
    FROM prefixes p
    WHERE tc.a_number LIKE p.prefix || '%'
    ORDER BY p.prefix_length DESC
    LIMIT 1
) a_p ON TRUE
-- 匹配b_number的最长前缀
LEFT JOIN LATERAL (
    SELECT p.prefix
    FROM prefixes p
    WHERE tc.b_number LIKE p.prefix || '%'
    ORDER BY p.prefix_length DESC
    LIMIT 1
) b_p ON TRUE;

-- 清理临时表
DROP TABLE temp_calls;

方案2：用AFTER INSERT语句级触发器批量更新 #

如果必须保留触发器的方式（比如不想修改现有插入逻辑），可以使用语句级AFTER INSERT触发器，批量更新刚插入的行，仅执行两次批量查询，而非每行两次查询。

实现代码 #

-- 先确保calls表有主键（无主键则无法精准筛选刚插入的行）
ALTER TABLE calls ADD COLUMN id SERIAL PRIMARY KEY;

-- 创建批量处理前缀的函数
CREATE OR REPLACE FUNCTION calls_batch_prefixes()
RETURNS TRIGGER AS $$
BEGIN
    -- 批量更新a_prefix
    UPDATE calls c
    SET a_prefix = p.prefix
    FROM (
        SELECT
            c2.id,
            (SELECT p.prefix
             FROM prefixes p
             WHERE c2.a_number LIKE p.prefix || '%'
             ORDER BY p.prefix_length DESC
             LIMIT 1) AS prefix
        FROM calls c2
        WHERE c2.id IN (SELECT id FROM NEW)
    ) p
    WHERE c.id = p.id AND c.a_prefix IS NULL;

    -- 批量更新b_prefix
    UPDATE calls c
    SET b_prefix = p.prefix
    FROM (
        SELECT
            c2.id,
            (SELECT p.prefix
             FROM prefixes p
             WHERE c2.b_number LIKE p.prefix || '%'
             ORDER BY p.prefix_length DESC
             LIMIT 1) AS prefix
        FROM calls c2
        WHERE c2.id IN (SELECT id FROM NEW)
    ) p
    WHERE c.id = p.id AND c.b_prefix IS NULL;

    RETURN NULL; -- 语句级触发器无需返回NEW/OLD
END;
$$ LANGUAGE plpgsql;

-- 创建语句级AFTER INSERT触发器
CREATE TRIGGER calls_batch_prefix_trigger
AFTER INSERT ON calls
FOR EACH STATEMENT EXECUTE PROCEDURE calls_batch_prefixes();

额外性能优化建议 #

1. 给prefixes表加专用索引：加速LIKE前缀匹配操作：

   CREATE INDEX idx_prefixes_prefix_ops ON prefixes USING btree (prefix text_pattern_ops);
   

2. 优化前缀排序索引：结合prefix_length创建索引，进一步减少排序开销：

   CREATE INDEX idx_prefixes_length_prefix ON prefixes (prefix_length DESC, prefix text_pattern_ops);
   

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