님아! 스칼라 서브쿼리를 쓰지마오!

오픈소스 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
복사

위 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
SQL
복사

스칼라 서브쿼리가 다섯 번 나온 이유는, 아래와 같이, 인라인 뷰 바깥에서 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;
SQL
복사

이를 증명하기 위해 다음과 같이 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
SQL
복사

이와 같은 상황에서, 원하는 결과(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
SQL
복사

위와 같이 서브쿼리가 사용된 인라인 뷰 안쪽에 ORDER BY를 일부러 추가하면 인라인 뷰가 별도 처리되므로, 서브쿼리가 내부적으로 반복 사용되는 것을 막을 수 있습니다.

하지만, 위와 같은 방법은 말 그대로 꼼수입니다. 가장 현명한 방법은 스칼라 서브쿼리 자체를 제거하는 것입니다. 아래와 같이 SQL을 작성할 수 있습니다. 서브쿼리에 대한 실행계획이 사라지고, 해시 조인을 활용하면서, 총 실행시간이 2.7초로 더욱 개선된 것을 알 수 있습니다.

EXPLAIN (ANALYZE,COSTS OFF)
SELECT  t1.mbr_id ,t1.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    ms_mbr_big t1
        LEFT OUTER JOIN (
            SELECT  x.mbr_id
                    ,SUM(CASE WHEN (x.ord_dtm >= '20221201' AND x.ord_dtm < TO_DATE('20230101','YYYYMMDD')) THEN x.ord_amt END) ord_amt_2212
                    ,SUM(CASE WHEN (x.ord_dtm >= '20231201' AND x.ord_dtm < TO_DATE('20240101','YYYYMMDD')) THEN x.ord_amt END) ord_amt_2312
            FROM    tr_ord_big x
            WHERE   (x.ord_dtm >= '20221201' AND x.ord_dtm < TO_DATE('20230101','YYYYMMDD'))
                    OR
                    (x.ord_dtm >= '20231201' AND x.ord_dtm < TO_DATE('20240101','YYYYMMDD'))
            GROUP BY x.mbr_id
            ) t2
            ON (t2.mbr_id = t1.mbr_id)
ORDER BY COALESCE(t2.ord_amt_2312,0) DESC;

Sort (actual time=2713.365..2724.230 rows=99990 loops=1)
  Sort Key: (COALESCE((sum(CASE WHEN ((x.ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (x.ord_dtm < '20240101') THEN x.ord_amt ELSE NULL::numeric END)), '0'::numeric)) DESC
  Sort Method: external merge  Disk: 4880kB
  ->  Hash Right Join (actual time=2125.271..2677.204 rows=99990 loops=1)
        Hash Cond: ((x.mbr_id) = (t1.mbr_id))
        ->  HashAggregate (actual time=1968.267..2490.703 rows=83744 loops=1)
              Group Key: x.mbr_id
              Batches: 21  Memory Usage: 8881kB  Disk Usage: 52320kB
              ->  Bitmap Heap Scan on tr_ord_big x (actual time=67.415..1077.158 rows=1471788 loops=1)
                    Recheck Cond: (((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '20230101') OR ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '20240101'))
                    Heap Blocks: exact=20136
                    ->  BitmapOr (actual time=65.099..65.100 rows=0 loops=1)
                          ->  Bitmap Index Scan on tr_ord_big_x13 (actual time=28.408..28.408 rows=738864 loops=1)
                                Index Cond: ((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '20230101')
                          ->  Bitmap Index Scan on tr_ord_big_x13 (actual time=36.680..36.680 rows=732924 loops=1)
                                Index Cond: ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '20240101')
        ->  Hash (actual time=156.919..156.920 rows=99990 loops=1)
              Buckets: 131072  Batches: 1  Memory Usage: 5841kB
              ->  Seq Scan on ms_mbr_big t1 (actual time=129.232..145.471 rows=99990 loops=1)
Planning Time: 3.059 ms
Execution Time: 2754.866 ms
SQL
복사

준비한 내용은 여기까지입니다.

다음과 같은 일정으로 2025년 상반기 StartUP SQL Tuning(For PostgreSQL) 강의를 합니다.

< 일정 >

•

1/7(일), 1/14(일), 1/21(일)  오전 9시 ~ 오후 5시

•

(3일 × 7시간 = 총 21시간)

< 장소 >

•

DBian 교육센터 : 서울시 영등포구 당산로31길 16-1 JUM빌딩 201호

◦

(2호선 영등포구청역 3번 출구 도보 2분, 5호선 영등포구청역 7번 출구 도보 5분)

강의 모집 URL

•

https://cafe.naver.com/dbian/8709

많은 관심 부탁드립니다.