查询当前表时，Postgresql插入触发器变慢

When inserting a lot number into a table we are counting the number times the base number exists, and adding a -## to the end of the new number based on that count.

当将批号插入表中时，我们计算基数存在的次数，并根据该计数在新数字的末尾添加 - ##。

I have stripped out most the logic (we check for other things as well). I also am aware of the logic flaw here that would skip -1.

我已经删除了大部分逻辑（我们也检查了其他内容）。我也知道这里的逻辑缺陷会跳过-1。

-- Function: stone._lsuniqueid()

-- DROP FUNCTION stone._lsuniqueid();

CREATE OR REPLACE FUNCTION stone._lsuniqueid()
  RETURNS trigger AS
$BODY$
DECLARE
 _count INTEGER;

BEGIN
  -- Obtain the number of occurences of this new ls_number
  SELECT COUNT(ls_number) into _count
  FROM ls
  WHERE ls_number LIKE CAST(NEW.ls_number || '%' AS text);

  -- Allow new ls_numbers to be entered as is, otherwise add "-#{count + 1}"
  -- to the end of the ls_number
  if _count > 0 THEN
    NEW.ls_number = NEW.ls_number || '-' || CAST(_count + 1 AS text);
  END IF;      

  RETURN NEW;
END
$BODY$

INSERT INTO ls VALUES (NEXTVAL('ls_ls_id_seq'),7285,UPPER('20151012'));
--> Query returned successfully: one row affected, 391 ms execution time.

The count query is plenty fast

计数查询速度很快

SELECT COUNT(ls_number)
FROM ls
WHERE ls_number LIKE CAST('20151012' || '%' AS text);
--> 19ms

For comparison I tried a similar trigger, but ran the count against a different table with same amount of rows, and similar query time.

为了进行比较，我尝试了类似的触发器，但是针对具有相同行数和相似查询时间的不同表运行计数。

SELECT COUNT(lsdetail_id)
FROM lsdetail
WHERE lsdetail_id > 2433308
--> 20ms

Running the same insert with the count running against a different table returns the result 20 times faster.

使用针对不同表运行的计数运行相同的插入返回结果的速度快20倍。

INSERT INTO ls VALUES (NEXTVAL('ls_ls_id_seq'),7285,UPPER('20151012'));
 --> Query returned successfully: one row affected, 20 ms execution time.

The ls table has about 2.5 million rows

ls表有大约250万行

I've tried a couple of different things and the issue seems to be when selecting from the same table I'm inserting into.

我尝试了几个不同的东西，问题似乎是从我插入的同一个表中选择。

I would like to know why this happening, but I would also be open to a better way to create "sub-lot" numbers.

我想知道为什么会发生这种情况，但我也愿意更好地创建“子批次”数字。

Thanks!

谢谢！

1 个解决方案

#1

Found the answer here: http://www.postgresql.org/message-id/[e-mail]

在这里找到答案：http：//www.postgresql.org/message-id/[e-mail]

Re: How to analyze function performance

Re：如何分析功能性能

"Mindaugas" writes:

“Mindaugas”写道：

Is it possible to somehow analyze function performance? E.g. we are using function cleanup() which takes obviously too much time to execute but I have problems trying to figure what is slowing things down.

有可能以某种方式分析功能性能吗？例如。我们正在使用函数cleanup（），这显然需要花费太多时间来执行，但是我在尝试找出减慢速度的问题时遇到了问题。

When I explain analyze function lines step by step it show quite acceptable performance.

当我逐步解释分析功能线时，它表现出相当可接受的性能。

--

-

Are you sure you are "explain analyze"ing the same queries the function is really doing? You have to account for the fact that what plpgsql is issuing is parameterized queries, and sometimes that limits the planner's ability to pick a good plan. For instance, if you have

你确定你是“解释分析”函数真正做的相同查询吗？你必须考虑到plpgsql发布的是参数化查询这一事实，有时这限制了规划者选择一个好计划的能力。例如，如果你有

declare x int;
begin
    ...
    for r in select * from foo where key = x loop ...

then what is really getting planned and executed is "select * from foo where key = $1" --- every plpgsql variable gets replaced by a parameter symbol "$n". You can model this for EXPLAIN purposes with a prepared statement:

然后真正计划和执行的是“select * from foo where key = $ 1”---每个plpgsql变量都被参数符号“$ n”替换。您可以使用预准备语句为EXPLAIN目的建模：

prepare p1(int) as select * from foo where key = $1;
explain analyze execute p1(42);

If you find out that a particular query really sucks when parameterized, you can work around this by using EXECUTE to force the query to be planned afresh on each use with literal constants instead of parameters:

如果您在参数化时发现特定查询确实很糟糕，您可以通过使用EXECUTE强制查询在每次使用时使用文字常量而不是参数重新计划：

Then I looked into this: http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

然后我调查了这个：http：//www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

39.5.4. Executing Dynamic Commands

39.5.4。执行动态命令

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands (as discussed in Section 39.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:

通常，您需要在PL / pgSQL函数中生成动态命令，即每次执行时将涉及不同表或不同数据类型的命令。 PL / pgSQL正常尝试缓存命*（如第39.10.2节所述）在这种情况下不起作用。为了处理这类问题，提供了EXECUTE语句：

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;

--

-

So in the end it was a matter of updating count select to this:

所以最后这是一个更新计数选择到这个问题：

 EXECUTE 'SELECT COALESCE(COUNT(ls_number), 0) FROM ls WHERE ls_number LIKE $1 || ''%'';'
 INTO _count
 USING NEW.ls_number;