PostgreSQL 9.6存储过程的性能改进 [英] PostgreSQL 9.6 stored procedure performance improvement

问题描述

我有两个表 users 和 products ，它们之间的关联是 User有很多产品。我想将产品计数存储在用户表中，并且每次插入或删除时都应更新该计数。因此，我已经在数据库中为其编写了一个存储过程，并触发了它。问题是，当我一次插入成千上万的产品时，它执行触发器每行插入，这会花费太多时间。

i have two tables users and products and the association between them is that User has many products. I want to store the count of products in the users table and it should be updated at every insert or delete. So i have written a stored procedure in the database for it and trigger to fire it. The problem is when i am inserting thousands of products at once it is executing the trigger per row insertion and it is taking too much time.

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

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.

尝试了可延期的初始费用，但仍然不工作。我认为每行是实际问题。但是，当我用进行每个声明尝试它时，它会抛出语句无效错误。

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.

重写了上面的过程像这样：

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个产品，您将重新计算每个用户的数量（即使只是在数据库中插入单个产品）

But the problem is then when you have 1000 usres with 10000 products each, you recalculate the count of every user (even when just insert a single product in the database)

我正在使用PostgreSQL 9.6。

I'm using PostgreSQL 9.6.

推荐答案

正如注释中提到的a_horse_with_no_name一样，Postgres 10可以使用 FOR EACH STATEMENT 触发器，该触发器根据语句的个用户记录正在等待postgresql-10-实现语法在转换表之后触发/ rel = nofollow noreferrer>转换表。

As a_horse_with_no_name noted in comments, Postgres 10 can do this much more efficiently using a FOR EACH STATEMENT trigger which updates all users records at once based on the statement's transition table.

在早期版本中，您可以通过将临时表中的更改排队，然后在语句末尾使用单个 UPDATE 来应用更改，从而获得一些好处。

In earlier versions, you can get some of the benefit by queueing the changes in a temp table, and applying them at the end of the statement with a single UPDATE.

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

Initialise the queue at the start of the statement:

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 与 4073ms ）。

This may or may not be noticeably faster, depending on the details of your case, but it performed significantly better in an artificial test (184ms vs. 4073ms).

我在类似的答案，如果同时运行此实现，则可能要解决一些潜在的死锁。

As I noted in a similar answer, this implementation has some potential deadlocks which you might want to address if you're running this concurrently.

这篇关于PostgreSQL 9.6存储过程的性能改进的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！