스칼라 서브쿼리를 주의하자

아래는 PG에서 회원 데이터를 조회하면서, 전체 주문의 마지막일자와 회원의 가입일자의 차이를 구하는 SQL이다. 서브쿼리가 발생하는 성능저하를 살펴보기 위한 샘플 SQL이다.

EXPLAIN (ANALYZE,COSTS OFF,BUFFERS)
SELECT  t1.*
        ,(SELECT MAX(x.ord_dtm) - t1.join_dtm FROM tr_ord x) join_last_order
FROM    ms_mbr t1
LIMIT 100;

Limit (actual time=177.028..6190.592 rows=100 loops=1)
  Buffers: shared hit=54402 read=528000
  ->  Seq Scan on ms_mbr t1 (actual time=68.984..6082.444 rows=100 loops=1)
        Buffers: shared hit=54402 read=528000
        SubPlan 1
          ->  Aggregate (actual time=60.813..60.813 rows=1 loops=100)
                Buffers: shared hit=54400 read=528000
                ->  Seq Scan on tr_ord x (actual time=0.001..34.857 rows=512434 loops=100)
                      Buffers: shared hit=54400 read=528000
Planning Time: 0.193 ms
Execution Time: 6192.003 ms
SQL
복사

위 SQL은 6.1초가 걸린다. 회원 100명만 조회했기 때문에 그나마 이정도 시간이 걸린 것이다. 만약에 천명 이상의 회원을 조회한다면 10배 이상의 시간이 걸리게 된다. 성능 저하의 원인은 서브쿼리 부분이 내부적으로 반복 실행되었기 때문이다. 서브쿼리 한번 실행에 0.06초 정도가 걸렸고, 이 시간이 100번 반복되어 총 6초 정도의 SQL 실행시간이 소모된 것이다. 실행계획을 잘 살펴보기 바란다.

위 SQL의 성능을 개선하기 위해서는 스칼라 서브쿼리의 외부 컬럼인 t1.join_dtm만 스칼라 서브쿼리 바깥으로 옮겨주면 된다. 아래와 같다. 전체 실행시간이 0.08초로 개선된 것을 알 수 있다.

EXPLAIN ANALYZE
SELECT  t1.*
        ,(SELECT MAX(x.ord_dtm) FROM tr_ord x) - t1.join_dtm join_last_order
FROM    ms_mbr t1;

Limit (actual time=81.012..81.042 rows=100 loops=1)
  Buffers: shared hit=578 read=5248
  InitPlan 1 (returns $0)
    ->  Aggregate (actual time=80.991..80.992 rows=1 loops=1)
          Buffers: shared hit=576 read=5248
          ->  Seq Scan on tr_ord x (actual time=0.005..43.818 rows=512434 loops=1)
                Buffers: shared hit=576 read=5248
  ->  Seq Scan on ms_mbr t1 (actual time=81.010..81.032 rows=100 loops=1)
        Buffers: shared hit=578 read=5248
Planning Time: 0.152 ms
Execution Time: 81.084 ms
SQL
복사

MySQL과 오라클도 마찬가지다. 스칼라 서브쿼리 안에 외부 테이블의 컬럼이 사용되느냐 아니냐에 따라 성능 차이가 발생한다. 그럼에도 불구하고, 오라클은 성능 차이가 크지 않다. 역시 성능은 오라클이 짱이다.

-- MySQL
EXPLAIN ANALYZE
SELECT  t1.*
        ,(SELECT MAX(x.ord_dtm) - t1.join_dtm FROM tr_ord x) join_last_order
FROM    ms_mbr t1
LIMIT 100;
-- 37.5 sec
-> Limit: 100 row(s)  (cost=1071 rows=100) (actual time=0.106..0.559 rows=100 loops=1)
    -> Table scan on t1  (cost=1071 rows=9797) (actual time=0.105..0.515 rows=100 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: max(x.ord_dtm)  (cost=102651 rows=1) (actual time=375..375 rows=1 loops=100)
        -> Table scan on x  (cost=51634 rows=510169) (actual time=0.0489..270 rows=512434 loops=100)

        
EXPLAIN ANALYZE
SELECT  t1.*
        ,(SELECT MAX(x.ord_dtm) FROM tr_ord x) - t1.join_dtm join_last_order
FROM    ms_mbr t1
LIMIT 100;
-> Limit: 100 row(s)  (cost=1071 rows=100) (actual time=0.218..0.359 rows=100 loops=1)
    -> Table scan on t1  (cost=1071 rows=9797) (actual time=0.215..0.345 rows=100 loops=1)
-> Select #2 (subquery in projection; run only once)
    -> Aggregate: max(x.ord_dtm)  (cost=102651 rows=1) (actual time=308..308 rows=1 loops=1)
        -> Table scan on x  (cost=51634 rows=510169) (actual time=0.0454..229 rows=512434 loops=1)
SQL
복사

-- ORA
SELECT  t1.*
        ,(SELECT MAX(x.ord_dtm) - t1.join_dtm FROM tr_ord x) join_last_order
        ,COUNT(*) OVER()
FROM    ms_mbr t1;
------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |    100 |00:00:00.01 |      98 |       |       |          |
|   1 |  SORT AGGREGATE    |        |     22 |      1 |     22 |00:00:00.63 |   91432 |       |       |          |
|   2 |   TABLE ACCESS FULL| TR_ORD |     22 |    512K|     11M|00:00:00.49 |   91432 |       |       |          |
|   3 |  WINDOW BUFFER     |        |      1 |   9999 |    100 |00:00:00.01 |      98 |  1116K|   557K|  991K (0)|
|   4 |   TABLE ACCESS FULL| MS_MBR |      1 |   9999 |   9999 |00:00:00.01 |      98 |       |       |          |
------------------------------------------------------------------------------------------------------------------
 
SELECT  t1.*
        ,(SELECT MAX(x.ord_dtm) FROM tr_ord x) - t1.join_dtm join_last_order
        ,COUNT(*) OVER()
FROM    ms_mbr t1;
------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |    100 |00:00:00.01 |      98 |       |       |          |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.02 |    4156 |       |       |          |
|   2 |   TABLE ACCESS FULL| TR_ORD |      1 |    512K|    512K|00:00:00.02 |    4156 |       |       |          |
|   3 |  WINDOW BUFFER     |        |      1 |   9999 |    100 |00:00:00.01 |      98 |  1116K|   557K|  991K (0)|
|   4 |   TABLE ACCESS FULL| MS_MBR |      1 |   9999 |   9999 |00:00:00.01 |      98 |       |       |          |
------------------------------------------------------------------------------------------------------------------
SQL
복사