오픈소스 DBMS로 시작하는 SQL 튜닝 입문 강의! StartUP Tuning(For PostrgreSQL) 중 하나의 이야기입니다.
오늘 다루는 이야기는 PostgreSQL에서 스칼라 서브쿼리 사용을 더욱더 자중할 필요가 있음을 알려주는 이야기입니다. 스칼라 서브쿼리를 제검함으로서 9.8초의 SQL이 2.7초로 개선됩니다.
천천히 읽어 보시고, 지금 운영 중인 시스템에 이러한 쿼리가 있는지 찾아보시기 바랍니다.
아래는 회원 정보를 조회하면서, 스칼라 서브쿼리를 사용해 ord_amt_2212(2022년 12월 주문금액)과 ord_amt_2312(2023년 12월 주문금액)을 가져오는 SQL입니다. 그리고, 인라인 뷰를 활용해 두 주문금액간의 차이(diff_ord_amt = ord_amt_2312 - ord_amt_2212)를 구하고 있습니다. SQL을 천천히 살펴보기 바랍니다.
EXPLAIN (ANALYZE,COSTS OFF)
SELECT t2.mbr_id
,t2.nick_nm
,COALESCE(t2.ord_amt_2312,0) - COALESCE(t2.ord_Amt_2212,0) diff_ord_amt
,t2.ord_amt_2212
,t2.ord_amt_2312
FROM (
SELECT t1.mbr_id ,t1.nick_nm
,( SELECT SUM(x.ord_amt)
FROM tr_ord_big x
WHERE x.mbr_id = t1.mbr_id
AND x.ord_dtm >= TO_DATE('20221201','YYYYMMDD')
AND x.ord_dtm < TO_DATE('20230101','YYYYMMDD')) ord_amt_2212
,( SELECT SUM(x.ord_amt)
FROM tr_ord_big x
WHERE x.mbr_id = t1.mbr_id
AND x.ord_dtm >= TO_DATE('20231201','YYYYMMDD')
AND x.ord_dtm < TO_DATE('20240101','YYYYMMDD')) ord_amt_2312
FROM ms_mbr_big t1
) t2
ORDER BY COALESCE(t2.ord_amt_2312,0) DESC;
위 SQL은 tr_ord_big 테이블을 사용하는 스칼라 서브쿼리가 두 번 사용되고 있습니다.(ord_amt_2212와 ord_amt_2312) 그렇다면, 논리적으로 실행계획에는 스칼라 서브쿼리가 두 번 표시되어야 합니다.
하지만, 실행계획을 보면, 다음과 같이 스칼라 서브쿼리가 다섯 번 사용되는 것을 알 수 있습니다. 실행 시간은 총 9892.613 ms가 걸렸습니다. (tr_ord_big에 대한 subplan이 1부터 5까지 네번 나온 것을 알 수 있습니다.)
Sort (actual time=9869.024..9881.486 rows=99990 loops=1)
Sort Key: (COALESCE((SubPlan 5), '0'::numeric)) DESC
Sort Method: external merge Disk: 4888kB
-> Seq Scan on ms_mbr_big t1 (actual time=315.684..9798.439 rows=99990 loops=1)
SubPlan 1
-> Aggregate (actual time=0.041..0.041 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x (actual time=0.016..0.040 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20231201' AND (ord_dtm < '20240101')
SubPlan 2
-> Aggregate (actual time=0.034..0.034 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x_1 (actual time=0.008..0.032 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20221201' AND (ord_dtm < '20230101')
SubPlan 3
-> Aggregate (actual time=0.006..0.006 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x_2 (actual time=0.003..0.004 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20221201' AND (ord_dtm < '20230101')
SubPlan 4
-> Aggregate (actual time=0.006..0.006 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x_3 (actual time=0.003..0.004 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20231201' AND (ord_dtm < '20240101')
SubPlan 5
-> Aggregate (actual time=0.005..0.005 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x_4 (actual time=0.003..0.004 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20231201' AND (ord_dtm < '20240101')
Planning Time: 0.740 ms
Execution Time: 9892.631 ms
스칼라 서브쿼리가 다섯 번 나온 이유는, 아래와 같이, 인라인 뷰 바깥에서 diff_ord_amt 를 구하는 과정에서 스칼라 서브쿼리의 결과 컬럼인 ord_amt_2312와 ord_amt_2212를 사용했고, ORDER BY 절에서도 스칼라 서브쿼리의 결과인 ord_amt_2312를 COALESCE로 가공해서 사용했기 때문입니다.
EXPLAIN (ANALYZE,COSTS OFF)
SELECT t2.mbr_id
,t2.nick_nm
,COALESCE(t2.ord_amt_2312,0) - COALESCE(t2.ord_Amt_2212,0) diff_ord_amt
...생략...
ORDER BY COALESCE(t2.ord_amt_2312,0) DESC;
이를 증명하기 위해 다음과 같이 diff_ord_amt 부분을 제거하고 SQL을 실행해봅니다. 다음과 같이 스칼라 서브쿼리에 대한 실행계획(SubPlan)이 세 번만 나오는 것을 알 수 있습니다. 실행 시간도 7493.877로 성능이 향상되었습니다.
EXPLAIN (ANALYZE,COSTS OFF)
SELECT t2.mbr_id
,t2.nick_nm
-- > 제거 ,COALESCE(t2.ord_amt_2312,0) - COALESCE(t2.ord_Amt_2212,0) diff_ord_amt
,t2.ord_amt_2212
,t2.ord_amt_2312
FROM (
SELECT t1.mbr_id ,t1.nick_nm
,( SELECT SUM(x.ord_amt)
FROM tr_ord_big x
WHERE x.mbr_id = t1.mbr_id
AND x.ord_dtm >= TO_DATE('20221201','YYYYMMDD')
AND x.ord_dtm < TO_DATE('20230101','YYYYMMDD')) ord_amt_2212
,( SELECT SUM(x.ord_amt)
FROM tr_ord_big x
WHERE x.mbr_id = t1.mbr_id
AND x.ord_dtm >= TO_DATE('20231201','YYYYMMDD')
AND x.ord_dtm < TO_DATE('20240101','YYYYMMDD')) ord_amt_2312
FROM ms_mbr_big t1
) t2
ORDER BY COALESCE(t2.ord_amt_2312,0) DESC;
Sort (actual time=7474.926..7485.871 rows=99990 loops=1)
Sort Key: (COALESCE((SubPlan 3), '0'::numeric)) DESC
Sort Method: external merge Disk: 4328kB
-> Seq Scan on ms_mbr_big t1 (actual time=198.027..7430.960 rows=99990 loops=1)
SubPlan 1
-> Aggregate (actual time=0.036..0.036 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x (actual time=0.012..0.034 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20221201' AND (ord_dtm < '20230101')
SubPlan 2
-> Aggregate (actual time=0.030..0.030 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x_1 (actual time=0.008..0.028 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20231201' AND (ord_dtm < '20240101')
SubPlan 3
-> Aggregate (actual time=0.006..0.006 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x_2 (actual time=0.003..0.004 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20231201' AND (ord_dtm < '20240101')
Planning Time: 0.347 ms
Execution Time: 7493.877 ms
이와 같은 상황에서, 원하는 결과(diff_ord_amt)를 얻으면서, 성능 개선을 위해 PostgreSQL에서 사용하는 꼼수가 있습니다. 바로 인라인 뷰 안에 의미 없는 ORDER BY를 추가하는 것입니다.
EXPLAIN (ANALYZE,COSTS OFF)
SELECT t2.mbr_id
,t2.nick_nm
,COALESCE(t2.ord_amt_2312,0) - COALESCE(t2.ord_Amt_2212,0) diff_ord_amt
,t2.ord_amt_2212
,t2.ord_amt_2312
FROM (
SELECT t1.mbr_id ,t1.nick_nm
,( SELECT SUM(x.ord_amt)
FROM tr_ord_big x
WHERE x.mbr_id = t1.mbr_id
AND x.ord_dtm >= TO_DATE('20221201','YYYYMMDD')
AND x.ord_dtm < TO_DATE('20230101','YYYYMMDD')) ord_amt_2212
,( SELECT SUM(x.ord_amt)
FROM tr_ord_big x
WHERE x.mbr_id = t1.mbr_id
AND x.ord_dtm >= TO_DATE('20231201','YYYYMMDD')
AND x.ord_dtm < TO_DATE('20240101','YYYYMMDD')) ord_amt_2312
FROM ms_mbr_big t1
ORDER BY 1 -- > 일부러 오더바이 추가
) t2
ORDER BY COALESCE(t2.ord_amt_2312,0) DESC;
Sort (actual time=3055.525..3072.047 rows=99990 loops=1)
Sort Key: (COALESCE(t2.ord_amt_2312, '0'::numeric)) DESC
Sort Method: external merge Disk: 4888kB
-> Subquery Scan on t2 (actual time=196.658..3001.225 rows=99990 loops=1)
-> Index Scan using ms_mbr_big_pk on ms_mbr_big t1 (actual time=196.654..2983.118 rows=99990 loops=1)
SubPlan 1
-> Aggregate (actual time=0.014..0.014 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x (actual time=0.005..0.012 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20221201' AND (ord_dtm < '20230101')
SubPlan 2
-> Aggregate (actual time=0.013..0.013 rows=1 loops=99990)
-> Index Scan using tr_ord_big_x12 on tr_ord_big x_1 (actual time=0.004..0.012 rows=7 loops=99990)
Index Cond: (((mbr_id) = (t1.mbr_id)) AND (ord_dtm >= '20231201' AND (ord_dtm < '20240101')
Planning Time: 0.390 ms
Execution Time: 3082.298 ms