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
