다음은 필자와 어느 신입 개발자간의 대화이다.
튜너 : (함수의 부작용 설명 후) 그러므로 간단한 로직은 함수를 사용하지 말고, 조인 문장을 사용하십시오.
개발자 : 함수가 DB성능을 악화시킨다는 것은 이해가 가는데, 튜너님 지침을 따르면 개발기간이 길어집니다. 조인 문장 만드느라 시간을 더 쓰게 된다면 프로젝트 입장에서는 ROI가 낮아지는 것 아닙니까?
튜너 : (나이도 어린게 시키는 데로 할것이지...라고 생각하며)함수를 남발하게 되면 결국 DBMS를 교체하거나 Hardware를 교체해야 하는 상황까지도 갈 수 있습니다. 나만 편하자고 함수를 쓰다보면, 결국 그 함수때문에 사용하지 않아도 되는 예산을 더 쓰게 될 수도 있습니다.
개발자 : (나이 많은 노친네가 고집만 세네...라고 생각하며)네 알겠습니다.
....며칠 후 개발자가 googling 열심히 하더니 Function Inlining 개념을 사용해서 함수가 Join보다 더 성능이 좋을 수 있음을 튜너에게 보여줌
개발자 : 튜너님, PostgreSQL은 Function Inlining 개념이 있습니다. 저희 프로젝트에서 빈번히 사용하는 코드는 Function을 이용해서 코드명을 가져오도록 개발하겠습니다.
튜너 : (Function Inlining 검색 후 얼굴이 붉어지며)어... 네 그렇게 하십시오.
(SQL Server 최신 버전도 간단한 함수는 Inlining 동작한다.)
다음은 필자가 개발자에게 설명한 Function의 부작용 내용과, Function Inlining에 대한 예시이다.
SELECT A.ORD_NO
, F_GET_PROD_NM(A.PROD_ID) AS PROD_NM
FROM ORDERS_DETAIL A
WHERE A.ORD_NO BETWEEN 1 AND 100000;
위 쿼리는 PROD_NM을 추출하기 위해서 F_GET_PROD 함수를 사용하고 있다.
| Gather (actual time=32.892..2255.193 rows=1000000 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| Buffers: shared hit=1001631 read=8801 written=3636 |
| -> Parallel Bitmap Heap Scan data-on orders_detail a (actual time=29.616..2127.167 rows=333333 loops=3) |
| Recheck Cond: ((ord_no >= 1) AND (ord_no <= 100000)) |
| Heap Blocks: exact=2676 |
| Buffers: shared hit=1001631 read=8801 written=3636 |
| -> Bitmap Index Scan data-on orders_detail_x01 (actual time=30.348..30.348 rows=1000000 loops=1) |
| Index Cond: ((ord_no >= 1) AND (ord_no <= 100000)) |
| Buffers: shared hit=3 read=1149 |
| Planning: |
| Buffers: shared hit=1 read=3 |
| Planning Time: 0.113 ms |
| Execution Time: 2318.876 m
위 실행계획을 보면 f_get_prod_nm 함수가 1000000회 수행되었음을 알 수 있다.
f_get_prod_nm 함수의 소스코드는 아래와 같다.
CREATE OR REPLACE FUNCTION portal.f_get_prod_nm(i_prod_id character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
O_PROD_NM VARCHAR(100);
BEGIN
SELECT PROD_NM
INTO O_PROD_NM
FROM PROD
WHERE PROD_ID = I_PROD_ID;
RETURN O_PROD_NM;
END; $function$;
위 SQL을 아래와 같이 JOIN 문장으로 수정하였다.
SELECT A.ORD_NO, B.PROD_NM
FROM ORDERS_DETAIL A, PROD B
WHERE A.PROD_ID = B.PROD_ID
AND A.ORD_NO BETWEEN 1 AND 100000;
| Hash Join (actual time=24.446..303.434 rows=999999 loops=1) |
| Hash Cond: ((a.prod_id)::text = (b.prod_id)::text) |
| Buffers: shared hit=9497 |
| -> Bitmap Heap Scan data-on orders_detail a (actual time=24.416..124.040 rows=1000000 loops=1) |
| Recheck Cond: ((ord_no >= 1) AND (ord_no <= 100000)) |
| Heap Blocks: exact=8344 |
| Buffers: shared hit=9496 |
| -> Bitmap Index Scan data-on orders_detail_x01 (actual time=23.610..23.610 rows=1000000 loops=1) |
| Index Cond: ((ord_no >= 1) AND (ord_no <= 100000)) |
| Buffers: shared hit=1152 |
| -> Hash (actual time=0.015..0.016 rows=5 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| Buffers: shared hit=1 |
| -> Seq Scan data-on prod b (actual time=0.009..0.011 rows=5 loops=1) |
| Buffers: shared hit=1 |
| Planning: |
| Buffers: shared hit=4 |
| Planning Time: 0.371 ms |
| Execution Time: 335.462 ms
함수를 없애고 JOIN 문으로 수정하니, block I/O가 1001631-->9497,
elapsed time이 2318 ms --> 335ms 성능 개선이 되었다.
만약 f_get_prod_nm 함수가 자주 사용되는 함수라면, DB서버의 부하나 성능에 미치는 영향은 엄청나다. DB서버 부하로 H/W 교체 검토중인 사이트에 가보면 위와 같은 비효율적인 SQL 몇 개만 수정해도 DB서버 부하가 확 떨어지고, 연쇄적으로 lock 현상도 줄어들어 다른 SQL 성능들도 좋아지는 경우가 많다.
신입 개발자가 찾아낸 PG의 Function Inlining을 활용하려면 위의 함수르 아래와 같이 수정한다.
CREATE OR REPLACE FUNCTION portal.f_sqlget_prod_nm(i_prod_id character varying)
RETURNS character varying
LANGUAGE sql
STABLE PARALLEL SAFE
AS $$
SELECT CASE WHEN $1 IN ('PP0') THEN '999998TEST_NAME'
WHEN $1 IN ('PP1') THEN '999997TEST_NAME'
WHEN $1 IN ('PP2') THEN '999999TEST_NAME'
WHEN $1 IN ('PP3') THEN '333333TEST_NAME'
WHEN $1 IN ('PP4') THEN '444444TEST_NAME'
ELSE 'N/A'
END;
$$;
위 함수를 이용해서 SQL을 다시 수행해 보자.
SELECT A.ORD_NO
, F_SQLGET_PROD_NM(A.PROD_ID) AS PROD_NM
FROM ORDERS_DETAIL A
WHERE A.ORD_NO BETWEEN 1 AND 100000;
| Bitmap Heap Scan data-on portal.orders_detail a (actual time=25.666..227.984 rows=1000000 loops=1)
| Output: ord_no, CASE WHEN ((prod_id)::text = 'PP0'::text) THEN '999998TEST_NAME'::text WHEN ((prod_id)::text = 'PP1'::text) THEN '999997TEST_NAME'::text WHEN ((prod_id)::text = 'PP2'::text) THEN '999999TEST_NAME'::text WHEN ((prod_id)::text = 'PP3'::text) THEN '333333TEST_NAME'::text WHEN ((prod_id)::text = 'PP4'::text) THEN '444444TEST_NAME'::text ELSE 'N/A'::text END |
| Recheck Cond: ((a.ord_no >= 1) AND (a.ord_no <= 100000))
| Heap Blocks: exact=8344
| Buffers: shared hit=9496
| -> Bitmap Index Scan data-on orders_detail_x01 (actual time=24.782..24.783 rows=1000000 loops=1)
| Index Cond: ((a.ord_no >= 1) AND (a.ord_no <= 100000))
| Buffers: shared hit=1152
| Planning:
| Buffers: shared hit=4
| Planning Time: 0.157 ms
| Execution Time: 262.608 ms
실행계획을 보면 함수를 사용하였으나, optimizer가 함수를 풀어 헤쳐서 단순 SQL 문장으로 Transformation을 했음을 알 수 있다.
JOIN 문장에 비해서 block I/O는 9497 --> 8344,
elapsed time은 335ms --> 262 ms 로 개선되었다.
코드테이블을 엑세스 하지 않으니 성능이 좋아지는 것은 당연한 결과다.
위와 같이 Funtion Inlining 의 장단점은 아래와 같다.
Disadvantages
1. 코드테이블 변경 시 반드시 코드 값 입력시 이름을 추출하는 함수도 수정해야 한다.
- 몇 번 개발자가 교체되면 실수할 가능성이 높다.
- 모든 코드그룹에 대해서 함수를 만들게 되면 관리할 함수 수가 너무 많아진다.
2. 매우 간단한 business logic만 inlining 동작하는 함수로 작성 가능하다.
Advantages
1. 코드테이블 JOIN하는 SQL보다 성능이 좋다.
- 자주 사용하는 함수라면, 그 impact는 엄청나다.
2. 개발 생산성이 높아진다.
- 코드 데이터 수정때마다 함수도 함께 수정해야 하는 단점이 있기는 하다.
- 사용 빈도 TOP 5개 정도의 코드그룹만 선정해서 Inlining Function 사용해도 DB서버 성능개선이 큰 효과가 있다.
