출간 준비 중인 < SQL TUNER for PostgreSQL · 기본원리편 >의 미리보기입니다.
아래 SQL을 살펴보자. 코드성 데이터의 명칭을 처리하기 위해 스칼라 서브쿼리를 활용하고 있다. 일반적으로 자주 사용되는 패턴이다.
-- [SQL-8-4-3-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
,(SELECT s1.base_cd_nm FROM startdbpg.cm_base_cd s1
WHERE s1.base_cd_dv = 'ord_st' AND s1.base_cd = t1.ord_st
) ord_st_nm
,(SELECT s2.base_cd_nm FROM startdbpg.cm_base_cd s2
WHERE s2.base_cd_dv = 'pay_tp' AND s2.base_cd = t1.pay_tp
) pay_tp_nm
FROM startdbpg.tr_ord_big t1
WHERE t1.ord_dtm >= '2025-01-01'::date
AND t1.ord_dtm < '2025-02-01'::date;
Index Scan using tr_ord_big_x23 on tr_ord_big t1 (actual time=120.534..1199.422 rows=514440 loops=1)
Index Cond: ((ord_dtm >= '2025-01-01'::date) AND (ord_dtm < '2025-02-01'::date))
Buffers: shared hit=1036363
SubPlan 1
-> Seq Scan on cm_base_cd s1 (actual time=0.001..0.001 rows=1 loops=514440)
Filter: (((base_cd_dv)::text = 'ord_st'::text) AND ((base_cd)::text = (t1.ord_st)::text))
Rows Removed by Filter: 23
Buffers: shared hit=514440
SubPlan 2
-> Seq Scan on cm_base_cd s2 (actual time=0.001..0.001 rows=1 loops=514440)
Filter: (((base_cd_dv)::text = 'pay_tp'::text) AND ((base_cd)::text = (t1.pay_tp)::text))
Rows Removed by Filter: 23
Buffers: shared hit=514440
Planning:
Buffers: shared hit=8
Planning Time: 0.465 ms
Execution Time: 1213.198 ms
위 SQL은 ord_st(주문상태)와 pay_tp(지불유형)에 대한 명칭을 스칼라 서브쿼리를 활용해 처리하고 있다. 총 실행 시간이 1213.198ms이고 전체 I/O가 무려 1,036,363이다. 스칼라 서브쿼리가 반복 실행되면서 많은 I/O가 발생한 것이다. 다행히도 메모리에서 반복된 블록을 읽기 때문에 I/O만큼 실행 시간이 크게 증가하지는 않았다.
스칼라 서브쿼리로 처리한 부분은 아우터 조인으로 대체할 수 있다. 아래와 같이 아우터 조인으로 변경해보자. PostgreSQL의 Memoize(자식 오퍼레이션의 결과를 캐시에 저장하여 재사용하는 기능) 효과를 얻기 위해 일부러 NL 조인 힌트를 추가해 보자. Memoize는 NL 조인에 대해서만 발동이 가능하다.
-- [SQL-8-4-3-b]: [SQL-8-4-3-a]의 스칼라 서브쿼리를 아우터 조인으로 변경
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading(((t1 t2) t3)) NestLoop(t1 t2) NestLoop(t1 t2 t3) */
SELECT t1.*, t2.base_cd_nm ,t3.base_cd_nm
FROM startdbpg.tr_ord_big t1
LEFT OUTER JOIN startdbpg.cm_base_cd t2
ON (t2.base_cd_dv = 'ord_st' AND t2.base_cd = t1.ord_st)
LEFT OUTER JOIN startdbpg.cm_base_cd t3
ON (t3.base_cd_dv = 'pay_tp' AND t3.base_cd = t1.pay_tp)
WHERE t1.ord_dtm >= '2025-01-01'::date
AND t1.ord_dtm < '2025-02-01'::date;
Nested Loop Left Join (actual time=0.020..263.189 rows=514440 loops=1)
Buffers: shared hit=7493
-> Nested Loop Left Join (actual time=0.017..160.302 rows=514440 loops=1)
Buffers: shared hit=7489
-> Index Scan using tr_ord_big_x23 on tr_ord_big t1 (actual time=0.008..43.015 rows=514440 …
Index Cond: ((ord_dtm >= '2025-01-01'::date) AND (ord_dtm < '2025-02-01'::date))
Buffers: shared hit=7483
-> Memoize (actual time=0.000..0.000 rows=1 loops=514440)
Cache Key: t1.ord_st
Cache Mode: logical
Hits: 514437 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=6
-> Index Scan using cm_base_cd_pk on cm_base_cd t2 (actual time=0.005..0.005 rows=1 …
Index Cond: (((base_cd_dv)::text = 'ord_st'::text) AND ((base_cd)::text = …
Buffers: shared hit=6
-> Memoize (actual time=0.000..0.000 rows=1 loops=514440)
Cache Key: t1.pay_tp
Cache Mode: logical
Hits: 514438 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=4
-> Index Scan using cm_base_cd_pk on cm_base_cd t3 (actual time=0.001..0.001 rows=1 loops=2)
Index Cond: (((base_cd_dv)::text = 'pay_tp'::text) AND ((base_cd)::text = (t1.pay_tp) …
Buffers: shared hit=4
Planning:
Buffers: shared hit=8
Planning Time: 0.286 ms
Execution Time: 275.136 ms
위 SQL의 실행계획을 보면 cm_base_cd(기준코드) 테이블을 드리븐으로 처리할 때 Memoize 기능이 작동하고 있다. 덕분에 실행 시간(275.136ms)과 I/O(Shared hit 7,493)가 모두 획기적으로 감소했다.
Memoize의 처리 성능을 위해서는 드리븐으로 입력되는 값의 종류 수가 적어야 한다. 코드 데이터는 대표적으로 값의 종류 수가 적기 때문에 Memoize와 같은 캐시 기능을 활용하기에 적합한 속성이다. 위 실행계획의 11번 라인과 19번 라인에서 이를 확인할 수 있다. Misses 수치에 비해 Hits 수치가 압도적으로 높다. 이는 입력되는 값의 종류가 매우 적다는 것을 의미하며, 그 결과 Memoize가 효과적으로 작동하고 있음을 보여준다.
결과적으로 PostgreSQL에서 코드명 처리는 [SQL-8-4-3-b]와 같은 조인 방식이 훨씬 효율적이다. 참고로 Oracle이라면 [SQL-8-4-3-a]의 스칼라 서브쿼리 방식도 좋은 성능을 보여준다. Oracle은 스칼라 서브쿼리에 대해 서브쿼리 캐시 기능을 제공하여 Memoize와 유사하게 처리하기 때문이다.
마지막으로 [SQL-8-4-3-b]에서 옵티마이저가 조인 방식을 알아서 선택하도록 NL 조인 힌트를 제거해 보자.
-- [SQL-8-4-3-c]: [SQL-8-4-3-b]의 힌트를 제거하고 실행
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*, t2.base_cd_nm ,t3.base_cd_nm
…생략…
Hash Left Join (actual time=0.023..164.460 rows=514440 loops=1)
Hash Cond: ((t1.pay_tp)::text = (t3.base_cd)::text)
Buffers: shared hit=7485
-> Hash Left Join (actual time=0.019..109.832 rows=514440 loops=1)
Hash Cond: ((t1.ord_st)::text = (t2.base_cd)::text)
Buffers: shared hit=7484
-> Index Scan using tr_ord_big_x23 on tr_ord_big t1 (actual time=0.007..38.347 rows=514440 …
Index Cond: ((ord_dtm >= '2025-01-01'::date) AND (ord_dtm < '2025-02-01'::date))
Buffers: shared hit=7483
-> Hash (actual time=0.008..0.010 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on cm_base_cd t2 (actual time=0.005..0.006 rows=3 loops=1)
Filter: ((base_cd_dv)::text = 'ord_st'::text)
Rows Removed by Filter: 21
Buffers: shared hit=1
-> Hash (actual time=0.003..0.004 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on cm_base_cd t3 (actual time=0.002..0.002 rows=2 loops=1)
Filter: ((base_cd_dv)::text = 'pay_tp'::text)
Rows Removed by Filter: 22
Buffers: shared hit=1
Planning:
Buffers: shared hit=8
Planning Time: 0.175 ms
Execution Time: 176.352 ms
힌트를 제거하자 옵티마이저는 해시 조인을 선택했다. 결과적으로 Memoize를 활용한 NL 조인보다 실행 시간(275.136ms → 176.352ms)이 더 단축되었다. (테스트 시점의 컴퓨터 환경에 따라 해시 조인 방식이 더 느릴 수 있다. 하지만 반복 테스트해본 결과 전반적으로 해시 조인 방식이 약간 더 빠르다)
PostgreSQL에서는 현재 스칼라 서브쿼리에 대한 캐시(Memoize) 기능(17.6 기준)이 제공되지 않는다. 따라서 코드명 처리와 같이 값의 종류가 적으면서 반복적으로 참조되는 데이터는 조인 방식으로 작성하는 것이 바람직하다. 향후 PostgreSQL에서도 스칼라 서브쿼리에 Memoize와 같은 캐시 기능이 추가되기를 기대해 본다. 참고로 AWS Aurora는 버전에 따라 스칼라 서브쿼리에 Memoize를 제공한다고 알려져 있다. AWS Aurora 환경이라면 테스트해 보기 바란다.