PostgreSQL 9.6存储过程性能改进

Question

我有两个表users和products，它们之间的关联是User has many products。我希望将产品的数量存储在users表中，并且应该在每次插入或删除时更新它。因此，我在数据库中为它编写了一个存储过程，并触发了它。问题是，当我一次插入数千个产品时，它正在执行触发器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. 我补充说：SET CONSTRAINTS update_promotion_products_count DEFERRED;，但似乎没有取得任何进展，因为现在它需要6100 is，这有点类似于以前。
2. 尝试过DEFERRABLE INITIALLY DEFERRED，但它仍然不起作用。我认为FOR EACH ROW才是真正的问题。但是当我用FOR EACH STATEMENT尝试时，它会抛出语句无效的错误。
3. 重写上述过程如下： 创建函数update_product_count()在$$开始时返回触发器如果TG_OP =‘删除’或TG_OP =‘插入’，然后更新用户集合products_count =(从产品中选择products.user_id = users.id)；END IF；返回NULL；END；$$语言plpgsql；在插入或更新或删除每个语句执行过程update_product_count()后创建触发器update_user_products_count；

但问题是，当您有1000个usres和每个10000个产品时，您需要重新计算每个用户的数量(即使只是在数据库中插入一个产品时)。

我使用的是PostgreSQL 9.6。

Answer 1

在您的情况下，当产品的user_id更改时，count将不会更新，因此，我将推荐rails的counter_cache

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

也请看一下这个创业板

注意:-但这不能解决您的per row insertion问题

然后，您必须编写自定义计数器，如下所示

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}

Answer 2

正如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();

这可能会或不会明显地加快速度，这取决于您的案例的细节，但它在人工测试中的表现要好得多(184与4073毫秒)。

正如我在一个相似答案中所指出的，这个实现有一些潜在的死锁，如果您同时运行这些死锁，您可能需要解决这些问题。