출간 준비 중인 < SQL TUNER for PostgreSQL · 기본원리편 >의 미리보기입니다.
관계형 데이터베이스에서 조인은 피할 수 없으며 그에 따른 부하가 발생하는 것도 사실이다. 이 때문에 반정규화를 고려하기도 한다. 성능과 SQL 구현 복잡성을 줄이기 위해 부분적인 반정규화는 필요하다. 하지만 반정규화를 과도하게 적용하면 시간이 갈수록 데이터 정합성에 문제가 발생할 가능성이 높다. 따라서 반정규화는 신중하게 접근해야 한다. 조인을 피할 수 없다면 조인 부하를 줄여야 한다. 가장 효과적인 방법은 조인에 참여하는 데이터를 최소화하는 것이다.
아래 [SQL-8-4-4-a]는 ms_shop_big과 tr_ord_big을 조인한 후 shop_id와 ord_ym(주문년월)별로 주문금액 합계를 구하고 있다.
-- [SQL-8-4-4-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.shop_id ,TO_CHAR(t2.ord_dtm,'YYYYMM') ord_ym
,MAX(t1.shop_nm) shop_nm ,SUM(t2.ord_amt) ord_amt_sum
FROM startdbpg.ms_shop_big t1
INNER JOIN startdbpg.tr_ord_big t2
ON (t1.shop_id = t2.shop_id)
WHERE t2.ord_dtm >= '2024-01-01'::date
AND t2.ord_dtm < '2024-04-01'::date
GROUP BY t1.shop_id ,TO_CHAR(t2.ord_dtm,'YYYYMM')
ORDER BY t1.shop_id ,TO_CHAR(t2.ord_dtm,'YYYYMM');
GroupAggregate (actual time=1233.647..1638.389 rows=867 loops=1)
Group Key: t1.shop_id, (to_char(t2.ord_dtm, 'YYYYMM'::text))
Buffers: shared read=26864, temp read=18726 written=18763
-> Sort (actual time=1232.330..1423.236 rows=1848528 loops=1)
Sort Key: t1.shop_id, (to_char(t2.ord_dtm, 'YYYYMM'::text))
Sort Method: external merge Disk: 74928kB
Buffers: shared read=26864, temp read=18726 written=18763
-> Hash Join (actual time=8.038..670.462 rows=1848528 loops=1)
Hash Cond: ((t2.shop_id)::text = (t1.shop_id)::text)
Buffers: shared read=26864
->Index Scan using tr_ord_big_x23 on tr_ord_big t2 (actual time= … 189.726 rows=1848528 …
Index Cond: ((ord_dtm >= '2024-01-01'::date) AND (ord_dtm < '2024-04-01'::date))
Buffers: shared read=26860
->Hash (actual time=7.998..8.000 rows=300 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 24kB
Buffers: shared read=4
-> Seq Scan on ms_shop_big t1 (actual time=7.838..7.952 rows=300 loops=1)
Buffers: shared read=4
Planning:
Buffers: shared hit=3 read=11
Planning Time: 0.354 ms
Execution Time: 1646.256 ms
SQL
복사
실행계획 8번 라인을 보면 Hash Join이 수행되고 있다. ms_shop_big을 빌드 입력(14번, 17번 라인)으로, tr_ord_big을 프로브 입력(11번 라인)으로 처리하고 있다. 11번 라인의 actual rows를 보면 프로브 입력으로 조인에 참여하는 데이터가 약 백8십만 건이다. 제법 많은 데이터가 조인에 참여하고 있다. 이로 인해 Hash Join 완료까지 670.462ms가 소요(8번 라인의 actual time)되었다.
Hash Join 이후에는 Sort(4번 라인)가 수행되고 있다. Sort가 발생한 이유는 부모 오퍼레이션인 GroupAggregate(1번 라인)가 정렬된 데이터를 필요로 하기 때문이다. Sort 완료까지 추가로 752.774ms(1423.236 - 670.462)가 소요되었다.
위 SQL의 총 실행 시간 1646.256ms 중 대부분을 Hash Join과 Sort가 차지하고 있다. 이는 너무 많은 데이터를 조인하고 정렬하기 때문이다. 이러한 부하를 줄이려면 SQL을 변경해야 한다.
[SQL-8-4-4-a]는 ‘1:M 조인 후 GROUP BY’ 패턴이다. 이를 ‘인라인 뷰를 활용한 1:1 조인’ 패턴으로 변경하면 부하를 줄일 수 있다. ‘인라인 뷰를 활용한 1:1 조인’ 패턴은 M쪽 테이블을 인라인 뷰에서 먼저 GROUP BY로 집계하여 1 집합으로 만든 후 1:1로 조인하는 방식이다. 조인에 참여하는 데이터가 크게 줄어들므로 조인 부하를 줄일 수 있다. [SQL-8-4-4-a]를 아래와 같이 변경해 보자. (해당 패턴에 대한 자세한 설명은 필자의 이전 서적 ‘SQL STARTER’를 참고하기 바란다)
-- [SQL-8-4-4-b]: [SQL-8-4-4-a]를 인라인 뷰를 활용한 1:1 조인 패턴으로 변경
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.shop_id ,t2.ym
,t1.shop_nm ,t2.ord_amt_sum
FROM startdbpg.ms_shop_big t1
INNER JOIN (
SELECT a.shop_id ,TO_CHAR(a.ord_dtm,'YYYYMM') ym
,SUM(a.ord_amt) ord_amt_sum
FROM startdbpg.tr_ord_big a
WHERE a.ord_dtm >= '2024-01-01'::date
AND a.ord_dtm < '2024-04-01'::date
GROUP BY a.shop_id ,TO_CHAR(a.ord_dtm,'YYYYMM')
) t2
ON (t1.shop_id = t2.shop_id)
ORDER BY t1.shop_id ,t2.ym;
Merge Join (actual time=991.884..1331.922 rows=867 loops=1)
Merge Cond: ((a.shop_id)::text = (t1.shop_id)::text)
Buffers: shared read=26865, temp read=12302 written=12335
-> GroupAggregate (actual time=991.844..1331.323 rows=867 loops=1)
Group Key: a.shop_id, (to_char(a.ord_dtm, 'YYYYMM'::text))
Buffers: shared read=26860, temp read=12302 written=12335
-> Sort (actual time=990.827..1170.736 rows=1848528 loops=1)
Sort Key: a.shop_id, (to_char(a.ord_dtm, 'YYYYMM'::text))
Sort Method: external merge Disk: 49240kB
Buffers: shared read=26860, temp read=12302 written=12335
-> Index Scan using tr_ord_big_x23 on tr_ord_big a (actual time= … 469.043 rows=1848528 …
Index Cond: ((ord_dtm >= '2024-01-01'::date) AND (ord_dtm < '2024-04-01'::date))
Buffers: shared read=26860
-> Index Scan using ms_shop_big_pk on ms_shop_big t1 (actual time=0.030..0.252 rows=300 loops=1)
Buffers: shared read=5
Planning Time: 0.127 ms
Execution Time: 1346.262 ms
SQL
복사
[SQL-8-4-4-b]는 tr_ord_big을 인라인 뷰에서 먼저 GROUP BY 처리한 후 ms_shop_big과 조인하고 있다. SQL을 변경했으므로 실행계획도 함께 변경되었다.
SQL의 인라인 뷰(6 ~ 11번 라인)는 실행계획의 4 ~ 13번 라인에 해당한다. 실행계획을 보면 tr_ord_big 테이블에서 데이터를 가져와(11번 라인) Sort 처리(7번 라인)한 후 GroupAggregate로 집계(4번 라인)하고 있다. 이후 이 결과(인라인 뷰의 결과)와 ms_shop_big을 Merge Join(1번 라인) 방식으로 조인하고 있다. 이전에는 tr_ord_big의 백8십만 건이 모두 조인에 참여했지만, 지금은 백8십만 건을 인라인 뷰에서 집계 처리한 867건(4번 라인의 actual rows)만 조인에 참여한다. 조인 대상이 줄어들면서 총 실행 시간이 1346.262ms로 개선되었다.
‘1:M 조인 후 GROUP BY’ 패턴을 ‘인라인 뷰를 활용한 1:1 조인’ 패턴으로 변경하는 이 기법은 실제 튜닝 과정에서 종종 사용된다. 다만 모든 경우에 적용 가능한 것은 아니며, 변경한다고 무조건 성능이 개선되는 것도 아니다. 중요한 것은 변경 가능성과 부하 감소 효과를 고민하며 시도해 보는 것이다. 단, 변경 전후 SQL의 결과가 반드시 동일해야 한다. 따라서 데이터를 이해하고 정확한 SQL을 작성하는 능력이 SQL 튜닝에는 반드시 필요하다.
[SQL-8-4-4-b]는 추가 개선이 가능하다. 실행계획을 보면 tr_ord_big에서 데이터를 가져오는 데 469.043ms(11번 라인)가 소요되었고, 이후 Sort(7번 라인)와 GroupAggregate(4번 라인) 완료까지 추가로 862.28ms(1331.323 - 469.043)가 소요되었다. Sort와 GroupAggregate 과정이 전체 실행 시간의 많은 부분을 차지하고 있다.
데이터 집계 방식은 GroupAggregate와 HashAggregate 두 가지가 있다. GroupAggregate는 정렬된 데이터를 이용하므로 Sort를 동반한다. 반면 HashAggregate는 해시 테이블을 활용하므로 정렬이 불필요하다. 따라서 집계 방식을 HashAggregate로 변경하면 Sort를 생략할 수 있다.
하지만 PostgreSQL은 GroupAggregate와 HashAggregate를 직접 제어하는 힌트를 제공하지 않는다. 대신 실행계획을 분석하여 간접적인 유도를 시도해 볼 수 있다.
[SQL-8-4-4-b]의 실행계획을 보면 두 데이터 집합이 Merge Join으로 처리되고 있다. Merge Join은 정렬된 데이터를 전제로 한다. 옵티마이저는 Merge Join에서 정렬을 생략하기 위해 tr_ord_big을 조인 키(shop_id) 순서로 미리 정렬하여 집계했을 가능성이 있다. 결국 GroupAggregate가 선택된 이유는 Merge Join 때문으로 추정된다.
이 추리가 맞는지 확인하기 위해 아래와 같이 조인 방식을 Hash Join으로 변경해 보자.
-- [SQL-8-4-4-c]: [SQL-8-4-4-b]에 힌트 적용
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ HashJoin(t1 t2) */
…[SQL-8-4-4-b]…
Sort (actual time=799.046..799.072 rows=867 loops=1)
Sort Key: t1.shop_id, (to_char(a.ord_dtm, 'YYYYMM'::text))
Sort Method: quicksort Memory: 76kB
Buffers: shared hit=4 read=26860
-> Hash Join (actual time=798.495..798.812 rows=867 loops=1)
Hash Cond: ((a.shop_id)::text = (t1.shop_id)::text)
Buffers: shared hit=4 read=26860
-> HashAggregate (actual time=700.230..700.457 rows=867 loops=1)
Group Key: a.shop_id, to_char(a.ord_dtm, 'YYYYMM'::text)
Batches: 1 Memory Usage: 1297kB
Buffers: shared read=26860
-> Index Scan using tr_ord_big_x23 on tr_ord_big a (actual time= …447.294 rows=1848528 …
Index Cond: ((ord_dtm >= '2024-01-01'::date) AND (ord_dtm < '2024-04-01'::date))
Buffers: shared read=26860
-> Hash (actual time=98.254..98.254 rows=300 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 24kB
Buffers: shared hit=4
-> Seq Scan on ms_shop_big t1 (actual time=98.190..98.213 rows=300 loops=1)
Buffers: shared hit=4
Planning Time: 0.141 ms
Execution Time: 799.787 ms
SQL
복사
힌트로 인해 조인 방식이 Hash Join(5번 라인)으로 변경되었고, tr_ord_big의 집계 과정도 HashAggregate(8번 라인)로 변경되었다. 그 결과 Sort 오퍼레이션이 제거되어 총 실행 시간은 799.787ms로 [SQL-8-4-4-a]의 절반 수준으로 개선되었다.
이 사례에서 SQL 튜닝의 접근 방식을 확인할 수 있다. 조인 대상을 줄일 수 있는지, 과도한 Sort 오퍼레이션을 생략할 수 있는지를 항상 고민해야 한다. 단순히 힌트로 해결하려 하지 말고, 오퍼레이션이 나타난 이유를 추리하고 그에 맞는 적절한 힌트나 SQL 변경을 시도해야 한다.