PostgreSQL 9.6 存储过程性能提升

Question

我有两个表users和products，它们之间的关联是User has many products。我想将产品数量存储在用户表中，并且应该在每次插入或删除时更新。所以我在数据库中为它编写了一个存储过程并触发它。问题是当我一次插入数千个产品时它正在执行触发器每行插入并且它花费了太多时间。

  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 毫秒，这与以前有点相似。

2. 尝试了 DEFERRABLE INITIALLY DEFERRED 但它仍然无法正常工作。我认为 FOR EACH ROW 是实际问题。但是当我用 FOR EACH STATEMENT 尝试它时，它会抛出语句无效错误。

3. 将上面的程序改写成这样:

   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。

Answer 1

在您的情况下，当产品的 user_id 发生变化时，计数不会更新，所以，我会推荐counter_cache铁轨

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

也看看这个 gem

注意:- 虽然这不会解决您的每行插入问题

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

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}