Search

8-4-3. 코드명 조회 - 스칼라 서브쿼리 vs. 조인

출간 준비 중인 < 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
복사
위 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
복사
위 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
SQL
복사
힌트를 제거하자 옵티마이저는 해시 조인을 선택했다. 결과적으로 Memoize를 활용한 NL 조인보다 실행 시간(275.136ms → 176.352ms)이 더 단축되었다. (테스트 시점의 컴퓨터 환경에 따라 해시 조인 방식이 더 느릴 수 있다. 하지만 반복 테스트해본 결과 전반적으로 해시 조인 방식이 약간 더 빠르다)
PostgreSQL에서는 현재 스칼라 서브쿼리에 대한 캐시(Memoize) 기능(17.6 기준)이 제공되지 않는다. 따라서 코드명 처리와 같이 값의 종류가 적으면서 반복적으로 참조되는 데이터는 조인 방식으로 작성하는 것이 바람직하다. 향후 PostgreSQL에서도 스칼라 서브쿼리에 Memoize와 같은 캐시 기능이 추가되기를 기대해 본다. 참고로 AWS Aurora는 버전에 따라 스칼라 서브쿼리에 Memoize를 제공한다고 알려져 있다. AWS Aurora 환경이라면 테스트해 보기 바란다.
마지막으로 한 가지 당부하고 싶은 말이 있다. 이런 글을 보고 ‘스칼라 서브쿼리 전면 금지’라는 규칙을 만드는 경우가 종종 있다. 성능 측면에서는 바람직해 보이지만, 이미 개발된 모든 SQL을 일괄적으로 수정하는 것은 현실적으로 어렵다. 특히 스칼라 서브쿼리가 복잡한 비즈니스 로직을 담고 있다면, 무리하게 조인으로 변경하다가 로직 오류로 데이터 정합성 문제가 생길 수 있다. 게다가 상황에 따라 스칼라 서브쿼리가 오히려 성능상 유리한 경우도 있다.
따라서 실용적인 접근이 필요하다. 신규 개발 시에는 가급적 조인 방식을 권장하되, 기존 SQL은 성능 모니터링을 통해 영향이 큰 부분부터 선별적으로 개선하자. 빠듯한 개발 일정에서 모든 스칼라 서브쿼리를 무조건 제거하라고 요구하는 것은 프로젝트에 불필요한 부담만 가중시킬 뿐이다.

Sweet Data Story의 책 소개