UDF는 양날의 검같아서 개발자에게는 매우 유용한 도구이지만,
DB 성능에는 매우 큰 악영향을 줄 수 있다.
그래서 필자는 어디를 가든 UDF는 사용 금지를 원칙으로 하고 있다.
하지만 개발자들의 의견에 밀려 또는 생계 유지를 위해 UDF 사용을 허용할 때도 있다.
아래는 UDF사용시 SQL을 수정하여 성능 개선한 사례이다.
결론부터 얘기하자면,
SQL 내에서 function 사용 횟수를 최대한 줄여야 한다.
--test table 생성
CREATE TABLE T1 AS SELECT i as c1 FROM GENERATE_SERIES(1,10) A(i);
CREATE TABLE T2 AS SELECT CASE WHEN C1=1 THEN 1 ELSE C1+10 END AS C1 FROM T1;
--테스트용 function 생성
CREATE OR REPLACE FUNCTION F1 (i_val IN NUMERIC, i_second IN NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM PG_SLEEP(i_second);
RETURN 1;
END;
$function$;
--수정 전 SQL
SELECT *
FROM T1 A, T2 B
WHERE A.C1 = B.C1
AND F1(B.C1, 0.1) = 1;
위 실행계획에서 B 테이블로 hash table 생성 전에 함수를 filter 조건으로 사용하면서 1014 msec 소요되었음을 알 수 있다.(0.1 sec 소요되는 함수 10회 수행)
함수 수행횟수를 줄이기 위해서 JOIN을 먼저 수행하도록 SQL 수정하였다.
OFFSET 절이 있어야 subquery collapse가 동작하지 않는다.
SELECT *
FROM (SELECT A.C1 AS AC1, B.C1 AS BC1
FROM T1 A, T2 B
WHERE A.C1 = B.C1
OFFSET 0
) X
WHERE F1(BC1,0.1) = 1;
