Обсуждение: [GENERAL] Catching errors inside a LOOP is causing performance issues
Hi all,
I had a function with a performance issue:
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;
The function was being called 232 times (not counting the number of times the code from the FOR was accessed).
The IF from the FOR LOOP ended up being accessed 4466 times and was taking 561 seconds to complete all 4466 iterations.
For the particular data set that I had, the IF was always accessed, the SELECT from above never return data and the code was reaching the EXCEPTION branch each and every time.
I have changed the code to:
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;
Please note that for the table t1, the t1.charge column has a NOT NULL condition defined on it.
This time, the code from the IF takes 1-2 seconds to complete all 4466 iterations.
Basically, all I did was replace the
BEGIN
…
EXCEPTION
….
END;
With
IF conditionIsNotMet THEN
CONTINUE;
END IF;
Can someone please explain to me why this worked?
What happened behind the scenes?
I suspect that when you catch exceptions inside of a LOOP and the code ends up generating an exception, Postgres can’t use cached plans to optimize that code so it ends up planning the code at each iteration and this causes performance issues.
Is my assumption correct?
Thanks a lot,
Denisa Cîrstescu
I am adding an example that is runnable in order to demonstrate my point.
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
As you can see there is a difference of almost 15 seconds.
In the example that I have provided initially, the difference is bigger because the SELECT FROM t1 runs against complex data and takes more time to execute that the simple SELECT provided in this second example.
Thanks,
Denisa
From: Denisa Cirstescu
Sent: Monday, September 25, 2017 7:12 PM
To: pgsql-general@postgresql.org
Subject: Catching errors inside a LOOP is causing performance issues
Hi all,
I had a function with a performance issue:
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;
The function was being called 232 times (not counting the number of times the code from the FOR was accessed).
The IF from the FOR LOOP ended up being accessed 4466 times and was taking 561 seconds to complete all 4466 iterations.
For the particular data set that I had, the IF was always accessed, the SELECT from above never return data and the code was reaching the EXCEPTION branch each and every time.
I have changed the code to:
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;
Please note that for the table t1, the t1.charge column has a NOT NULL condition defined on it.
This time, the code from the IF takes 1-2 seconds to complete all 4466 iterations.
Basically, all I did was replace the
BEGIN
…
EXCEPTION
….
END;
With
IF conditionIsNotMet THEN
CONTINUE;
END IF;
Can someone please explain to me why this worked?
What happened behind the scenes?
I suspect that when you catch exceptions inside of a LOOP and the code ends up generating an exception, Postgres can’t use cached plans to optimize that code so it ends up planning the code at each iteration and this causes performance issues.
Is my assumption correct?
Thanks a lot,
Denisa Cîrstescu
Re: [GENERAL] Catching errors inside a LOOP is causing performance issues
Can someone please explain to me why this worked?
What happened behind the scenes?
I suspect that when you catch exceptions inside of a LOOP and the code ends up generating an exception, Postgres can’t use cached plans to optimize that code so it ends up planning the code at each iteration and this causes performance issues.
Is my assumption correct?
"David G. Johnston" <david.g.johnston@gmail.com> writes: > Not sure how much detail you are looking for but the docs say this: > "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." > https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > 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. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi Tom,
You said that trapping an arbitrary exception is a “fairly expensive mechanism”.
What if the:
begin
….
exception when others
then null;
end;
would be replaced with
begin
….
exception when NO_DATA_FOUND
then null;
end;
When the code is catching a certain exception: NO_DATA_FOUND does this make any difference?
Or it’s all about the process of setting up and ending a subtransaction?
Thanks,
Denisa Cîrstescu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, September 27, 2017 9:00 PM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Denisa Cirstescu <Denisa.Cirstescu@tangoe.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Catching errors inside a LOOP is causing performance issues
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Not sure how much detail you are looking for but the docs say this:
> "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."
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.p
> ostgresql.org%2Fdocs%2Fcurrent%2Fstatic%2Fplpgsql-control-structures.h
> tml%23PLPGSQL-ERROR-TRAPPING&data=01%7C01%7CDenisa.Cirstescu%40tangoe.
> com%7C6243898de8ae4141290a08d505d194e6%7C3ba137049b66408a9fb9db51aba57
> 9e4%7C0&sdata=iTBlh1PpcvJQiBZNPjDxsu7ExT%2BP%2BAirqr9Upz9sbJQ%3D&reser
> ved=0
> 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.
regards, tom lane
Hi Tom,
You said that trapping an arbitrary exception is a “fairly expensive mechanism”.
What if the:
begin
….
exception when others
then null;
end;
would be replaced with
begin
….
exception when NO_DATA_FOUND
then null;
end;
When the code is catching a certain exception: NO_DATA_FOUND does this make any difference?
Or it’s all about the process of setting up and ending a subtransaction?
Thanks,
Denisa Cîrstescu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, September 27, 2017 9:00 PM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Denisa Cirstescu <Denisa.Cirstescu@tangoe.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Catching errors inside a LOOP is causing performance issues
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Not sure how much detail you are looking for but the docs say this:
> "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."
> https://na01.safelinks.
protection.outlook.com/?url= https%3A%2F%2Fwww.p > ostgresql.org%2Fdocs%
2Fcurrent%2Fstatic%2Fplpgsql- control-structures.h > tml%23PLPGSQL-ERROR-TRAPPING&
data=01%7C01%7CDenisa. Cirstescu%40tangoe. > com%
7C6243898de8ae4141290a08d505d1 94e6% 7C3ba137049b66408a9fb9db51aba5 7 > 9e4%7C0&sdata=
iTBlh1PpcvJQiBZNPjDxsu7ExT% 2BP%2BAirqr9Upz9sbJQ%3D&reser > ved=0
> 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.
regards, tom lane
Re: [GENERAL] Catching errors inside a LOOP is causing performance issues
Hi Tom,
You said that trapping an arbitrary exception is a “fairly expensive mechanism”.