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
