설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 81건, 최근 0 건
   

스칼라서브쿼리 성능 향상

글쓴이 : 모델광 날짜 : 2021-05-02 (일) 17:32 조회 : 450
튜닝 전 SQL

SELECT CUST_ID

,(SELECT MIN(ORD_AMT) FROM 주문 WHERE A.CUST_ID = CUST_ID) AS MIN_ORD_AMT

,(SELECT AVG(ORD_AMT) FROM 주문 WHERE A.CUST_ID = CUST_ID) as AVG_ORD_AMT

,(SELECT SUM(ORD_AMT) FROM 주문 WHERE A.CUST_ID = CUST_ID) AS SUM_ORD_AMT

,(SELECT COUNT(*) FROM 고객만족점수이력 WHERE A.CUST_ID = CUST_ID) AS EVAL_COUNT

,(SELECT AVG(POINT) FROM 고객만족점수이력 WHERE A.CUST_ID = CUST_ID) as AVG_POINT

FROM 고객 A;


위 쿼리를 보는 순간 왜 3개 테이블을 조인하지 않고, 스칼라서브쿼리로 처리했는지 의문이 들 것이다.

하지만 ERD를 보면, 스칼라서브쿼리를 사용한 이유를 추정할 수 있었다.

ERD 상에는 고객과 주문이 1:M 관계이고, 고객과 고객만족점수이력 관계도 1:M 관계이다.


먄약 위 SQL 을 아래와 같이 작성한다면 잘못된 결과 값이 도출될 것이다.

SELECT ...

FROM 고객 LEFT JOIN 주문 LEFT JOIN 고객만족점수이력

WHERE ....;

고객과 주문은 1:M 관계이고, 고객과 고객만족점수이력 도 1:M 관계이므로 3개의 테이블을 조인하면 M x M 집합이 생성되어 잘못된 결과가 나올 것이라고 sql 작성자는 생각한 것이다.


아래와 같이 테스트 데이터를 만들고 튜닝을 해 보았다.

CREATE TABLE 고객( CUST_ID INT ,CONTENTS CHAR(100)); CREATE TABLE 주문( ORD_NO BIGINT ,CUST_ID INT ,ORD_AMT NUMERIC ,ORD_DATE DATE ,DUMMY CHAR(300)); CREATE TABLE 고객만족점수이력( CUST_ID INT ,EVAL_DATE DATE ,POINT NUMERIC ,DUMMY CHAR(100));

INSERT INTO 고객 SELECT i, chr(65+mod(i-1,26)) FROM generate_series(1,10000) a(i);


INSERT INTO 주문 SELECT i, 10000-mod(i, 10000), i, current_date-i, chr(65+mod(i-1,26)) FROM generate_series(1,1000000) a(i); CREATE INDEX 주문_X01 ON 주문(CUST_ID, DUMMY);

INSERT INTO 고객만족점수이력 SELECT mod(i, 10000), current_date-mod(i,100),mod(i,100),'dummy' FROM generate_series(1,50000) a(i); CREATE INDEX 고객만족점수이력_X01 ON 고객만족점수이력(CUST_ID);


위와 같이 테스트 데이터를 생성하고, 튜닝 전 SQL을 수행하면 아래의 실행계획이 나온다.

Seq Scan on "고객" a (actual time=0.579..1867.823 rows=10000 loops=1) Buffers: shared hit=3205293 read=53824 SubPlan 1 -> Aggregate (actual time=0.070..0.070 rows=1 loops=10000) Buffers: shared hit=1003646 read=52670 -> Bitmap Heap Scan on "주문" (actual time=0.011..0.059 rows=100 loops=10000) Recheck Cond: (a.cust_id = cust_id) Heap Blocks: exact=1000000 Buffers: shared hit=1003646 read=52670 -> Bitmap Index Scan on "주문_x01" (actual time=0.006..0.006 rows=100 loops=10000) Index Cond: (cust_id = a.cust_id) Buffers: shared hit=49101 read=7215 SubPlan 2 -> Aggregate (actual time=0.053..0.053 rows=1 loops=10000) Buffers: shared hit=1056316 -> Bitmap Heap Scan on "주문" "주문_1" (actual time=0.008..0.042 rows=100 loops=10000) Recheck Cond: (a.cust_id = cust_id) Heap Blocks: exact=1000000 Buffers: shared hit=1056316 -> Bitmap Index Scan on "주문_x01" (actual time=0.004..0.004 rows=100 loops=10000) Index Cond: (cust_id = a.cust_id) Buffers: shared hit=56316 SubPlan 3 -> Aggregate (actual time=0.051..0.051 rows=1 loops=10000) Buffers: shared hit=1056316 -> Bitmap Heap Scan on "주문" "주문_2" (actual time=0.007..0.041 rows=100 loops=10000) Recheck Cond: (a.cust_id = cust_id) Heap Blocks: exact=1000000 Buffers: shared hit=1056316 -> Bitmap Index Scan on "주문_x01" (actual time=0.004..0.004 rows=100 loops=10000) Index Cond: (cust_id = a.cust_id) Buffers: shared hit=56316 SubPlan 4 -> Aggregate (actual time=0.002..0.002 rows=1 loops=10000) Buffers: shared hit=19930 read=71 -> Index Only Scan using "고객만족점수이력_x01" on "고객만족점수이력" (actual time=0.001..0.001 rows=5 loops=10000) Index Cond: (cust_id = a.cust_id) Heap Fetches: 0 Buffers: shared hit=19930 read=71

SubPlan 5 -> Aggregate (actual time=0.008..0.008 rows=1 loops=10000) Buffers: shared hit=69360 read=635 -> Bitmap Heap Scan on "고객만족점수이력" "고객만족점수이력_1" (actual time=0.003..0.006 rows=5 loops=10000) Recheck Cond: (a.cust_id = cust_id) Heap Blocks: exact=49995 Buffers: shared hit=69360 read=635 -> Bitmap Index Scan on "고객만족점수이력_x01" (actual time=0.001..0.001 rows=5 loops=10000) Index Cond: (cust_id = a.cust_id) Buffers: shared hit=20000 Planning: Buffers: shared hit=3 Planning Time: 0.664 ms Execution Time: 2744.609 ms

고객 테이블의 10000 rows 수 만큼 스칼라서브쿼리 5개를 acess했음을 알 수 있다.

위 실행계획을 보고 아래의 SQL 튜닝 원칙을 생각해야 한다.

( 같은 테이블을 1회만 access해야 한다.)

위 실행계획은 주무 과 고객만족점수이력 을 각각 3회, 2회 엑세스하는 비효율이 있다.


아래와 같이 LATERVAL JOIN을 하며, 각 테이블 access를 1회만 하도록 할 수 있다.

SELECT A.CUST_ID

, B.MIN_AMT, B.AVG_AMT, B.SUM_AMT, COALESCE(C.EVAL_COUNT,0), C.AVG_POINT

FROM 고객 A LEFT JOIN LATERAL

(SELECT CUST_ID , MIN(ORD_AMT) MIN_AMT, AVG(ORD_AMT) AVG_AMT

, SUM(ORD_AMT) SUM_AMT

FROM 주문 B

GROUP BY CUST_ID) B

ON (A.CUST_ID = B.CUST_ID) LEFT JOIN LATERAL

(SELECT CUST_ID, COUNT(*) AS EVAL_COUNT, AVG(POINT) AS AVG_POINT

FROM 고객만족점수이력 C

GROUP BY CUST_ID) C

ON (A.CUST_ID = C.CUST_ID) ;

--실행계획

Hash Left Join (actual time=234.002..238.908 rows=10000 loops=1) Hash Cond: (a.cust_id = b.cust_id) Buffers: shared hit=13444 read=33108 -> Hash Left Join (actual time=30.546..33.770 rows=10000 loops=1) Hash Cond: (a.cust_id = c.cust_id) Buffers: shared hit=300 read=783 -> Seq Scan on "고객" a (actual time=0.013..1.046 rows=10000 loops=1) Buffers: shared hit=29 read=144 -> Hash (actual time=30.472..30.474 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 636kB Buffers: shared hit=271 read=639 -> Subquery Scan on c (actual time=24.623..29.177 rows=10000 loops=1) Buffers: shared hit=271 read=639 -> HashAggregate (actual time=24.622..28.544 rows=10000 loops=1) Group Key: c_1.cust_id Batches: 1 Memory Usage: 3217kB Buffers: shared hit=271 read=639 -> Seq Scan on "고객만족점수이력" c_1 (actual time=0.013..5.504 rows=50000 loops=1) Buffers: shared hit=271 read=639 -> Hash (actual time=203.384..203.437 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 675kB Buffers: shared hit=13144 read=32325 -> Subquery Scan on b (actual time=168.749..200.056 rows=10000 loops=1) Buffers: shared hit=13144 read=32325 -> Finalize GroupAggregate (actual time=168.748..199.381 rows=10000 loops=1) Group Key: b_1.cust_id Buffers: shared hit=13144 read=32325 -> Gather Merge (actual time=168.733..175.111 rows=30000 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=13144 read=32325 -> Sort (actual time=156.913..157.854 rows=10000 loops=3) Sort Key: b_1.cust_id Sort Method: quicksort Memory: 1791kB Buffers: shared hit=13144 read=32325 Worker 0: Sort Method: quicksort Memory: 1791kB Worker 1: Sort Method: quicksort Memory: 1791kB -> Partial HashAggregate (actual time=134.097..140.108 rows=10000 loops=3) Group Key: b_1.cust_id

                                       Group Key: b_1.cust_id

                                       Batches: 1  Memory Usage: 3985kB

                                       Buffers: shared hit=13130 read=32325

                                       Worker 0:  Batches: 1  Memory Usage: 3985kB

                                       Worker 1:  Batches: 1  Memory Usage: 3985kB

                                       ->  Parallel Seq Scan on "주문" b_1 (actual time=0.013..45.581 rows=333333 loops=3)

                                             Buffers: shared hit=13130 read=32325

 Planning:

   Buffers: shared hit=5 read=7

 Planning Time: 0.943 ms

 Execution Time: 240.280 ms

  Lateral Join을 수행하니, Hash Join으로 조인하였고, 주문과 고객만족이력을 각각 1회씩만 access하였다.

옵티마이저가 "주문" 테이블 access 시에 parallel processing을 수행하였다. "고객만족점수이력","고객" 테이블 access 시에는 parallel이 동작하지 않았다. 모든 테이블을 parallel로 access시의 성능을 보고 싶은데 PG는 마음대로 parallel 수행하도록 강제할 수 있는 힌트를 제공하지 않아서 아쉽다.

아뭏든 Lateral Join 으로 수정하니, elapsed time 이 7X 배 빨라졌다.                                     

굳이 일반 조인으로 수행하려면 아래와 같이 인라인뷰를 활용해야 한다.

SELECT X.CUST_ID, X.MIN_AMT, X.AVG_AMT, X.SUM_AMT

,COALESCE(COUNT(C.CUST_ID),0) AS EVAL_COUNT

,AVG(C.POINT) AS AVG_POINT

FROM (SELECT A.CUST_ID

, MIN(ORD_AMT) AS MIN_AMT

, AVG(ORD_AMT) AS AVG_AMT

, SUM(ORD_AMT) AS SUM_AMT

FROM 고객 A LEFT JOIN 주문 B


모델광 2021-05-02 (일) 17:40
ON A.CUST_ID = B.CUST_ID
              GROUP BY A.CUST_ID) X LEFT JOIN
              고객만족점수이력 C
    ON (X.CUST_ID = C.CUST_ID)
GROUP BY X.CUST_ID, X.MIN_AMT, X.AVG_AMT, X.SUM_AMT;
 HashAggregate (actual time=539.119..543.111 rows=10000 loops=1)
  Group Key: a.cust_id, (min(b.ord_amt)), (avg(b.ord_amt)), (sum(b.ord_amt))
  Batches: 1  Memory Usage: 3729kB
  Buffers: shared hit=14405 read=32133
  ->  Hash Left Join (actual time=508.211..521.839 rows=49996 loops=1)
        Hash Cond: (a.cust_id = c.cust_id)
        Buffers: shared hit=14405 read=32133
        ->  HashAggregate (actual time=497.099..503.161 rows=10000 loops=1)
              Group Key: a.cust_id
              Batches: 1  Memory Usage: 3985kB
              Buffers: shared hit=13495 read=32133
              ->  Hash Right Join (actual time=2.873..311.393 rows=1000000 loops=1)
                    Hash Cond: (b.cust_id = a.cust_id)
                    Buffers: shared hit=13495 read=32133
                    ->  Seq Scan on "주문" b (actual time=0.005..129.518 rows=1000000 loops=1)
                          Buffers: shared hit=13322 read=32133
                    ->  Hash (actual time=2.853..2.854 rows=10000 loops=1)
                          Buckets: 16384  Batches: 1  Memory Usage: 480kB
                          Buffers: shared hit=173
                          ->  Seq Scan on "고객" a (actual time=0.029..1.562 rows=10000 loops=1)
                                Buffers: shared hit=173
        ->  Hash (actual time=11.074..11.075 rows=50000 loops=1)
              Buckets: 65536  Batches: 1  Memory Usage: 2513kB
              Buffers: shared hit=910
              ->  Seq Scan on "고객만족점수이력" c (actual time=0.005..4.784 rows=50000 loops=1)
                    Buffers: shared hit=910
 Planning:
  Buffers: shared hit=12
 Planning Time: 0.319 ms
 Execution Time: 546.150 ms
Lateral Join에 비해서 elapsed time은 2배 더 소요되나, 수정 전 SQL 보다는 많이 성능이 개선되었다.
JOIN으로 변경한 마지막 SQL도 parallel processing이 동작하도록 강제하고 싶으나, PG는 힌트를 제공하지 않는다.(pg_hint_plan extension도 parallel 강제 힌트 없음) 차기 버전에서는 다양한 힌트를 사용할 수 있도록 개선 되기를 바란다.
댓글주소
   

postgresdba.com