postgresql - 为什么在 LOOP 中捕获错误会导致性能问题？

Question

我的函数存在性能问题:

totalCharge := 0;
FOR myRecord IN ... LOOP
    ......
    IF severalConditionsAreMet THEN
        BEGIN

            SELECT t1.charge INTO STRICT recordCharge
            FROM t1
            WHERE t1.id = myRecord.id AND otherComplexConditionsHere;

            totalCharge := totalCharge + recordCharge;

            ...........
        EXCEPTION
             WHEN OTHERS THEN 
                 NULL;
        END;
    END IF;

END LOOP;

该函数被调用了 232 次(不计算来自 FOR 的代码被访问的次数)。来自 FOR 循环的 IF 最终被访问了 4466 次，并花费了 561 秒来完成所有 4466 次迭代。

对于我拥有的特定数据集，IF 总是被访问，上面的 SELECT 从不返回数据，代码每次都到达 EXCEPTION 分支。我已将代码更改为:

totalCharge := 0;
FOR myRecord IN ... LOOP
    ......
    IF severalConditionsAreMet THEN

        SELECT t1.charge INTO recordCharge
        FROM t1
        WHERE t1.id = myRecord.id AND otherComplexConditionsHere;

        IF (recordCharge IS NULL) THEN
            CONTINUE;
        END IF;

        totalCharge := totalCharge + recordCharge;

        ...........

    END IF;

END LOOP;

请注意，对于表 t1，t1.charge 列定义了 NOT NULL 条件。这一次，来自 IF 的代码需要 1-2 秒才能完成所有 4466 次迭代。

基本上，我所做的就是替换

BEGIN
…
EXCEPTION
….
END;

与

IF conditionIsNotMet THEN
    CONTINUE;         
END IF;

有人可以向我解释为什么这有效吗？幕后发生了什么？我怀疑当您在 LOOP 中捕获异常并且代码最终生成异常时，Postgres 无法使用缓存的计划来优化该代码，因此它最终会在每次迭代中规划代码，这会导致性能问题。我的假设是否正确？

稍后编辑:

我修改了 Vao Tsun 提供的示例以反射(reflect)我想要说明的案例。

CREATE OR REPLACE FUNCTION initialVersion()
RETURNS VOID AS $$
declare
  testDate DATE;
begin
  for i in 1..999999 loop
    begin
    select now() into strict testDate where 1=0;
    exception when others 
    then null;
    end;
  end loop;
end;
$$ Language plpgsql;

CREATE OR REPLACE FUNCTION secondVersion()
RETURNS VOID AS $$
declare
    testDate DATE;
begin
  for i in 1..999999 loop
    select now() into testDate where 1=0;
    if testDate is null then 
      continue;
    end if;
  end loop;
end;
$$ Language plpgsql;

select initialVersion(); -- 19.7 seconds

select secondVersion(); -- 5.2

如您所见，相差将近 15 秒。在我最初提供的示例中，差异更大，因为 SELECT FROM t1 针对复杂数据运行并且比第二个示例中提供的简单 SELECT 花费更多的时间来执行。

Answer 1

我问了同样的问题here , 在 PostgreSQL - general mailing group 中，得到了一些回复，为我阐明了这个“谜团”:

大卫·约翰斯顿:

"​Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need."

I'm somewhat doubting "plan caching" has anything to do with this; I suspect its basically that there is high memory and runtime overhead to deal with the possibilities of needing to convert a exception into a branch instead of allowing it to be fatal.

汤姆莱恩:

Yeah, it's about the overhead of setting up and ending a subtransaction. That's a fairly expensive mechanism, but we don't have anything cheaper that is able to recover from arbitrary errors.

还有 David G. Johnston 的补充:

[...] setting up the pl/pgsql execution layer to trap "arbitrary SQL-layer exceptions"​ is fairly expensive. Even if the user specifies specific errors the error handling mechanism in pl/pgsql is code for generic (arbitrary) errors being given to it.

这些答案帮助我了解了一些事情是如何运作的。我在这里发布这个答案是因为我希望这个答案能帮助其他人。