PostgreSQL语句触发器过渡表对比的性能优化问询

问题场景 #

需要实现语句级触发器，将test_table中批量更新的变更记录同步插入lima_four表。当前实现通过关联过渡表new_table和old_table判断记录变更，但执行计划显示触发了全量笛卡尔积（13760*13760=189337600条记录），嵌套循环过滤无结果，性能极差。

原触发器与执行逻辑 #

CREATE TRIGGER t_update_det
    AFTER UPDATE ON test_table
    REFERENCING
        NEW TABLE AS new_table
        OLD TABLE AS old_table
    FOR EACH STATEMENT
EXECUTE PROCEDURE f_trigger();

-- 触发器函数中的INSERT逻辑
INSERT INTO lima_four (quebec, a, b, c,d)
            SELECT DISTINCT ON (alpha.victor, alpha.kilo_tango) 
                two.quebec,
                alpha.kilo_tango,
                alpha.victor,
                two.november_two,
                ARRAY['price'] :: TEXT[]
            FROM new_table two
            INNER JOIN old_table papa ON two.november_two = papa.november_two AND two.november_romeo = papa.november_romeo
            INNER JOIN oscar_quebec alpha ON two.quebec = alpha.quebec AND two.zulu = alpha.zulu
            WHERE   
                    (   two.sierra IS DISTINCT FROM papa.sierra
                    OR  two.tango_delta IS DISTINCT FROM papa.tango_delta
                    OR  two.yankee_foxtrot IS DISTINCT FROM papa.yankee_foxtrot
                    OR  two.echo_six IS DISTINCT FROM papa.echo_six
                    OR  two.delta_golf IS DISTINCT FROM papa.delta_golf)
                AND alpha.lima_delta = 'delta_sierra' 
                AND alpha.kilo_tango <> 'echo_whiskey' 
                
                    AND exists(
                        SELECT 1
                        FROM echo_zulu kilo_foxtrot
                        WHERE kilo_foxtrot.quebec = alpha.quebec
                              AND kilo_foxtrot.victor = alpha.victor
                              AND kilo_foxtrot.kilo_tango = alpha.kilo_tango
                              AND kilo_foxtrot.november_two = two.november_two
                              AND kilo_foxtrot.oscar_juliet = 'delta_sierra'
                              AND kilo_foxtrot.seven = 'delta_sierra'
                              AND kilo_foxtrot.tango_alpha = 'N')

原执行计划关键问题 #

...
                                        Join Filter: ((two.november_two = papa.november_two) AND (two.november_romeo = papa.november_romeo) AND ((two.sierra IS DISTINCT FROM papa.sierra) OR ...))
                                        Rows Removed by Join Filter: 189337600
                                      ->  Named Tuplestore Scan  (rows=13760 loops=1)
                                      ->  Named Tuplestore Scan  (rows=13760 loops=13760)
...

核心问题：未先过滤变更记录，直接对new_table和old_table做全量关联，产生了海量无效笛卡尔积数据，后续过滤完全是冗余操作。

优化方案 #

1. 先筛选变更记录，避免全量笛卡尔积 #

将new_table和old_table的关联与变更过滤逻辑放到子查询中，先得到真正发生变更的记录集，再与其他表关联，从根源减少数据量：

INSERT INTO lima_four (quebec, a, b, c,d)
SELECT DISTINCT ON (alpha.victor, alpha.kilo_tango) 
    two.quebec,
    alpha.kilo_tango,
    alpha.victor,
    two.november_two,
    ARRAY['price'] :: TEXT[]
FROM (
    -- 子查询先筛选出真正变更的记录，排除无变化的行
    SELECT nt.*
    FROM new_table nt
    JOIN old_table ot 
        ON nt.november_two = ot.november_two 
        AND nt.november_romeo = ot.november_romeo
    WHERE 
        nt.sierra IS DISTINCT FROM ot.sierra
        OR nt.tango_delta IS DISTINCT FROM ot.tango_delta
        OR nt.yankee_foxtrot IS DISTINCT FROM ot.yankee_foxtrot
        OR nt.echo_six IS DISTINCT FROM ot.echo_six
        OR nt.delta_golf IS DISTINCT FROM ot.delta_golf
) two
INNER JOIN oscar_quebec alpha 
    ON two.quebec = alpha.quebec 
    AND two.zulu = alpha.zulu
WHERE   
    alpha.lima_delta = 'delta_sierra' 
    AND alpha.kilo_tango <> 'echo_whiskey' 
    AND EXISTS(
        SELECT 1
        FROM echo_zulu kilo_foxtrot
        WHERE kilo_foxtrot.quebec = alpha.quebec
              AND kilo_foxtrot.victor = alpha.victor
              AND kilo_foxtrot.kilo_tango = alpha.kilo_tango
              AND kilo_foxtrot.november_two = two.november_two
              AND kilo_foxtrot.oscar_juliet = 'delta_sierra'
              AND kilo_foxtrot.seven = 'delta_sierra'
              AND kilo_foxtrot.tango_alpha = 'N'
    );

2. 为过渡表创建临时索引 #

语句级触发器的过渡表new_table和old_table默认没有索引，批量更新时关联效率极低。可以在触发器函数中临时创建索引加速关联：

CREATE OR REPLACE FUNCTION f_trigger() RETURNS TRIGGER AS $$
BEGIN
    -- 为过渡表的关联键创建临时索引，大幅提升JOIN效率
    CREATE TEMP INDEX IF NOT EXISTS tmp_new_idx ON new_table (november_two, november_romeo);
    CREATE TEMP INDEX IF NOT EXISTS tmp_old_idx ON old_table (november_two, november_romeo);

    -- 上述优化后的INSERT逻辑...

    -- 可选：触发器执行完毕后删除临时索引（会话结束会自动清除）
    DROP INDEX IF EXISTS tmp_new_idx;
    DROP INDEX IF EXISTS tmp_old_idx;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

3. 调整关联顺序，优先过滤小结果集 #

把过滤条件更严格、数据量更小的表放在关联链的前端，比如先关联oscar_quebec并过滤，再关联变更后的记录集，减少后续处理的数据量：

INSERT INTO lima_four (quebec, a, b, c,d)
SELECT DISTINCT ON (alpha.victor, alpha.kilo_tango) 
    two.quebec,
    alpha.kilo_tango,
    alpha.victor,
    two.november_two,
    ARRAY['price'] :: TEXT[]
FROM oscar_quebec alpha
-- 先过滤oscar_quebec的条件，得到小结果集后再关联变更记录
WHERE alpha.lima_delta = 'delta_sierra' 
  AND alpha.kilo_tango <> 'echo_whiskey'
INNER JOIN (
    SELECT nt.*
    FROM new_table nt
    JOIN old_table ot 
        ON nt.november_two = ot.november_two 
        AND nt.november_romeo = ot.november_romeo
    WHERE 
        nt.sierra IS DISTINCT FROM ot.sierra
        OR nt.tango_delta IS DISTINCT FROM ot.tango_delta
        OR nt.yankee_foxtrot IS DISTINCT FROM ot.yankee_foxtrot
        OR nt.echo_six IS DISTINCT FROM ot.echo_six
        OR nt.delta_golf IS DISTINCT FROM ot.delta_golf
) two
    ON two.quebec = alpha.quebec 
    AND two.zulu = alpha.zulu
AND EXISTS(
    SELECT 1
    FROM echo_zulu kilo_foxtrot
    WHERE kilo_foxtrot.quebec = alpha.quebec
          AND kilo_foxtrot.victor = alpha.victor
          AND kilo_foxtrot.kilo_tango = alpha.kilo_tango
          AND kilo_foxtrot.november_two = two.november_two
          AND kilo_foxtrot.oscar_juliet = 'delta_sierra'
          AND kilo_foxtrot.seven = 'delta_sierra'
          AND kilo_foxtrot.tango_alpha = 'N'
);

4. 替换EXISTS为INNER JOIN（适用于关联唯一场景） #

如果echo_zulu的关联条件能保证每行只匹配一次，可以将EXISTS子查询改为INNER JOIN，帮助优化器生成更高效的执行计划：

INSERT INTO lima_four (quebec, a, b, c,d)
SELECT DISTINCT ON (alpha.victor, alpha.kilo_tango) 
    two.quebec,
    alpha.kilo_tango,
    alpha.victor,
    two.november_two,
    ARRAY['price'] :: TEXT[]
FROM (
    SELECT nt.*
    FROM new_table nt
    JOIN old_table ot 
        ON nt.november_two = ot.november_two 
        AND nt.november_romeo = ot.november_romeo
    WHERE 
        nt.sierra IS DISTINCT FROM ot.sierra
        OR nt.tango_delta IS DISTINCT FROM ot.tango_delta
        OR nt.yankee_foxtrot IS DISTINCT FROM ot.yankee_foxtrot
        OR nt.echo_six IS DISTINCT FROM ot.echo_six
        OR nt.delta_golf IS DISTINCT FROM ot.delta_golf
) two
INNER JOIN oscar_quebec alpha 
    ON two.quebec = alpha.quebec 
    AND two.zulu = alpha.zulu
INNER JOIN echo_zulu kilo_foxtrot
    ON kilo_foxtrot.quebec = alpha.quebec
    AND kilo_foxtrot.victor = alpha.victor
    AND kilo_foxtrot.kilo_tango = alpha.kilo_tango
    AND kilo_foxtrot.november_two = two.november_two
WHERE   
    alpha.lima_delta = 'delta_sierra' 
    AND alpha.kilo_tango <> 'echo_whiskey'
    AND kilo_foxtrot.oscar_juliet = 'delta_sierra'
    AND kilo_foxtrot.seven = 'delta_sierra'
    AND kilo_foxtrot.tango_alpha = 'N';

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