PostgreSQL 9.6 存储过程性能提升

Question

我有两张表，分别是users和products，它们之间的关联是User has many products。我想在用户表中存储产品数量，并且每次插入或删除时都应该更新该数量。因此，我已经在数据库中编写了存储过程，并使用触发器来触发它。问题在于，当我一次插入数千个产品时，它会执行每行插入的触发器per row insertion，这需要太多时间。

  CREATE FUNCTION update_product_count()
  RETURNS trigger AS $$
  BEGIN
    IF TG_OP = 'DELETE' THEN
      UPDATE users SET products_count = products_count - 1 WHERE id = OLD.user_id;
    END IF;

    IF TG_OP = 'INSERT' THEN
      UPDATE users SET products_count = products_count + 1 WHERE id = NEW.user_id;
    END IF;

    RETURN NULL;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER update_user_products_count
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW EXECUTE PROCEDURE update_product_count();

更新

1. i have added: SET CONSTRAINTS update_promotion_products_count DEFERRED; but seems like it is not making any progress because right now it is taking 6100ms which is somewhat similar to before.

2. Tried DEFERRABLE INITIALLY DEFERRED but it is still not working. I think FOR EACH ROW is the actual issue. But when i tried it with FOR EACH STATEMENT it throws statement invalid error.

3. Rewrote the above procedure like this:

   CREATE FUNCTION update_product_count()
    RETURNS trigger AS $$
     BEGIN
       IF TG_OP = 'DELETE' OR TG_OP = 'INSERT' THEN
         UPDATE users SET products_count = (SELECT COUNT(1) FROM products WHERE products.user_id = users.id);
       END IF;
   
       RETURN NULL;
     END;
     $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER update_user_products_count
   AFTER INSERT OR UPDATE OR DELETE ON products
   FOR EACH STATEMENT EXECUTE PROCEDURE update_product_count();
   

然而问题在于，当你有1000个用户，每个用户有10000个产品时，即使只向数据库插入一个产品，你也需要重新计算每个用户的数量。

我正在使用PostgreSQL 9.6。

- Ahmad hamza

---

你使用的是哪个Postgres版本？在Postgres 10中，您可以使用语句级触发器来完成此操作。 - user330315

版本号是9.6。在问题中已更新。 - Ahmad hamza

@a_horse_with_no_name 有什么想法吗？ - Ahmad hamza

@Ahmad：你可以编写一个行级触发器，将更改排队到临时表中，以及一个语句级触发器，在最后应用更改：https://dev59.com/N6fja4cB1Zd3GeqPx5XV#47909709 - Nick Barnes

@NickBarnes 对不起，实际上我并没有完全理解它。如果您能在答案中写出问题的背景，那将是非常好的，这样我就可以更好地与您交流，并将其标记为被接受的答案。 - Ahmad hamza

Answer 1

正如a_horse_with_no_name在评论中指出的那样，Postgres 10可以使用FOR EACH STATEMENT触发器更有效地完成此操作，该触发器基于语句的转换表一次性更新所有users记录。

在早期版本中，您可以通过将更改排队到一个临时表中，并在语句结束时使用单个UPDATE应用它们来获得某些好处。

在语句开始时初始化队列：

CREATE FUNCTION create_queue_table() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  CREATE TEMP TABLE pending_changes(user_id INT UNIQUE, count INT) ON COMMIT DROP;
  RETURN NULL;
END
$$;

CREATE TRIGGER create_queue_table_if_not_exists
  BEFORE INSERT OR UPDATE OF user_id OR DELETE
  ON products
  FOR EACH STATEMENT
  WHEN (to_regclass('pending_changes') IS NULL)
  EXECUTE PROCEDURE create_queue_table();

记录每行的变化：

CREATE FUNCTION queue_change() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP IN ('DELETE', 'UPDATE') THEN
    INSERT INTO pending_changes (user_id, count) VALUES (old.user_id, -1)
    ON CONFLICT (user_id) DO UPDATE SET count = pending_changes.count - 1;
  END IF;

  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    INSERT INTO pending_changes (user_id, count) VALUES (new.user_id, 1)
    ON CONFLICT (user_id) DO UPDATE SET count = pending_changes.count + 1;
  END IF;
  RETURN NULL;
END
$$;

CREATE TRIGGER queue_change
  AFTER INSERT OR UPDATE OF user_id OR DELETE
  ON products
  FOR EACH ROW
  EXECUTE PROCEDURE queue_change();

将更改应用于语句的末尾。

CREATE FUNCTION process_pending_changes() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  UPDATE users
  SET products_count = products_count + pending_changes.count
  FROM pending_changes
  WHERE users.id = pending_changes.user_id;

  DROP TABLE pending_changes;
  RETURN NULL;
END
$$;

CREATE TRIGGER process_pending_changes
  AFTER INSERT OR UPDATE OF user_id OR DELETE
  ON products
  FOR EACH STATEMENT
  EXECUTE PROCEDURE process_pending_changes();

这可能会更快，也可能不会，具体情况取决于您的情况细节，但在人工测试中表现显著优于（184ms vs. 4073ms）。

正如我在类似答案中指出的那样，如果您正在同时运行此代码，则该实现可能存在一些潜在的死锁问题，您可能需要解决这些问题。

- Nick Barnes

Answer 2

在您的情况下，当产品的 user_id 改变时，计数不会更新， 因此，我建议使用 Rails 的 counter_cache。

class Product < ActiveRecord::Base
  belongs_to :user, counter_cache: true
end

也可以看看这个宝石

注意：- 这不会解决你的按行插入问题

你需要编写自定义计数器，类似以下的内容

class Product < ApplicationRecord
  has_many :products
  attr_accessor :update_count

  belongs_to :user#, counter_cache: true

  after_save do
    update_counter_cache
  end

  after_destroy do
    update_counter_cache
  end

  def update_counter_cache
    return unless update_count
    user.products_count = user.products.count
    user.save
  end
end

在 Rails 控制台中

10.times{|n| Product.new(name: "Latest New Product #{n}", update_count: n == 9, user_id: user.id).save}

- Salil

---

我的问题是每行插入，因为我一次要插入2万条记录。 - Ahmad hamza