PostgreSQL触发器更新商品平均评分耗时5秒优化咨询

在PostgreSQL中部署MediaStore数据库时，计划通过触发器实现新评论插入后自动更新对应商品的平均评分，实际使用中插入单条评论耗时超过5秒，需要可落地的优化方案。

涉及表结构DDL #

create table review
(
    review_id bigint generated by default as identity primary key,
    rating     integer not null CHECK (rating BETWEEN 1 AND 5),
    helpful    integer not null CHECK (helpful >= 0),
    reviewDate date,
    benutzer varchar(255),
    summary varchar(255),
    comment text,
    produkt_id bigint NOT NULL references produkt ON DELETE CASCADE
);
create table produkt
(
    produkt_id       bigint generated by default as identity primary key,
    asin     varchar(255) unique NOT NULL,
    titel varchar(1000) NOT NULL,
    rating   double precision,
    bild     varchar(1000),
    verkaufsrang integer
);

当前触发器实现 #

CREATE OR REPLACE FUNCTION update_rating()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE produkt
    SET rating =
      (SELECT AVG(rating) AS rating 
       FROM review 
       GROUP BY produkt_id 
       Having review.produkt_id = new.produkt_id)
  WHERE produkt_id = new.produkt_id;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_rating
    AFTER INSERT ON review
    FOR EACH ROW
    EXECUTE PROCEDURE update_rating();

现有触发器逻辑存在严重的性能缺陷：每次插入单条评论时，触发器内的子查询会全表扫描review表所有数据，按所有商品分组计算全量平均评分后，才过滤出当前商品的结果。随着review表数据量增长，这个全表聚合的耗时会线性上升，是导致插入慢的核心原因。
另外review表的produkt_id外键字段没有创建索引，即使修正查询逻辑，筛选指定商品的评论时也会出现不必要的扫描开销。

根据性能要求可选择两种优化层级：

方案1：最小改动优化（无需改表结构） #

• 先给关联字段加索引，避免按商品筛选评论时扫全表：

CREATE INDEX IF NOT EXISTS idx_review_produkt_id ON review(produkt_id);

• 修正触发器内的聚合逻辑，先过滤当前商品的评论再计算平均值，彻底去掉全表分组操作：

CREATE OR REPLACE FUNCTION update_rating()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE produkt
    SET rating = (
      SELECT AVG(rating)
      FROM review 
      WHERE produkt_id = NEW.produkt_id
    )
  WHERE produkt_id = NEW.produkt_id;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

优化后触发器只会扫描当前商品对应的评论，不会触碰其他商品的数据，在单商品评论量不超过千级的场景下，插入耗时可以降到毫秒级。

方案2：增量计算优化（高并发大数据量场景） #

如果单商品评论量很高（比如单商品评论过万），即使只扫描单商品的评论计算平均，还是会有一定开销，可以通过冗余评论计数字段实现纯内存增量计算，完全不需要查询review表：

• 给商品表增加评论计数字段，并初始化历史数据：

-- 增加评论计数字段
ALTER TABLE produkt ADD COLUMN IF NOT EXISTS review_count integer NOT NULL DEFAULT 0;
-- 初始化现有商品的平均评分和评论数
UPDATE produkt p
SET 
  rating = COALESCE((SELECT AVG(rating) FROM review r WHERE r.produkt_id = p.produkt_id), 0),
  review_count = COALESCE((SELECT COUNT(*) FROM review r WHERE r.produkt_id = p.produkt_id), 0);

• 改写触发器为增量计算逻辑：

CREATE OR REPLACE FUNCTION update_rating()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE produkt
    SET 
      rating = (rating * review_count + NEW.rating) / (review_count + 1),
      review_count = review_count + 1
  WHERE produkt_id = NEW.produkt_id;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

该方案下插入评论时触发器只需要更新商品表的单行数据，做简单的数值计算即可，插入耗时可以降到亚毫秒级，完全不会出现卡顿。如果后续需要支持评论删除、评分修改的场景，只需要对应调整增量计算逻辑即可。

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