PostgreSQL使用Trigger插入后更新列性能缓慢，如何优化？

1. 触发函数存在致命逻辑问题：你写的UPDATE tableA SET amount_hkd = NEW.amounts * NEW.currency_conversion_rate;没有加任何WHERE条件，每插入1行数据，就会对tableA全表执行一次更新，插入8400行等于执行8400次全表更新，这是耗时极长的核心原因。
2. 触发器选型不合理：行级AFTER触发器本身存在额外开销，且你的计算逻辑完全不需要事后更新表，属于冗余操作。

方案1：直接修改INSERT语句（性能最优，完全无触发器开销） #

你插入时已经能拿到amounts和currency_conversion_rate两个字段，直接在INSERT时计算amount_hkd的值插入即可，完全不需要触发器，代码如下：

INSERT INTO tableA (suppliers, invoice_dates, shipment_dates, amounts, currency, currency_conversion_rate, amount_hkd)
SELECT l.supplier, l.invoice_date, l.shipment_date, l.amount, l.currency, o.currency_conversion_rate, 
       l.amount * o.currency_conversion_rate AS amount_hkd
FROM tableB l
LEFT JOIN tableC o
ON l.currency = o.currency_code
WHERE l.supplier = 'ABC'

这个方案插入8400行耗时基本和不开触发器的插入耗时一致。

方案2：改为BEFORE INSERT行级触发器（如果必须用触发器实现逻辑） #

如果业务要求必须通过触发器自动计算amount_hkd，不需要写UPDATE语句，直接在插入前修改NEW变量的值即可，修改后的触发函数和触发器如下：

触发函数 #

CREATE OR REPLACE FUNCTION cal() 
RETURNS TRIGGER AS $$ 
BEGIN 
  -- 直接给NEW的amount_hkd赋值，不需要额外UPDATE表
  NEW.amount_hkd = NEW.amounts * NEW.currency_conversion_rate;
  RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;

触发器 #

CREATE TRIGGER update_amount 
BEFORE INSERT ON tableA
FOR EACH ROW 
EXECUTE PROCEDURE cal();

这个方案避免了额外的UPDATE操作，性能仅比无触发器插入低5%以内。

方案3：修复原有AFTER触发器逻辑（不推荐，仅作错误修正参考） #

如果你坚持要用AFTER行级触发器，必须给UPDATE语句加主键过滤条件，避免全表更新，假设tableA的主键是id，修改后的触发函数如下：

CREATE OR REPLACE FUNCTION cal() 
RETURNS TRIGGER AS $$ 
BEGIN 
UPDATE tableA 
SET amount_hkd = NEW.amounts * NEW.currency_conversion_rate
WHERE id = NEW.id; -- 仅更新当前插入的行
RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;

这个方案比前两个性能差，但相比原来的全表更新逻辑，性能能提升上千倍。

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