SELECT B.*
FROM 주문 B
JOIN 고객 A
ON (A.CUST_ID = B.CUST_ID)
WHERE A.SGG_CD = 'K'
ORDER BY B.ORD_DATE DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
실행계획
Limit (actual time=99.813..102.104 rows=10 loops=1)
Buffers: shared hit=13539 read=33559
-> Gather Merge (actual time=99.810..102.099 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13539 read=33559
-> Sort (actual time=90.525..90.527 rows=10 loops=3)
Sort Key: b.ord_date DESC
Sort Method: top-N heapsort Memory: 30kB
Buffers: shared hit=13538 read=33559
Worker 0: Sort Method: top-N heapsort Memory: 30kB
Worker 1: Sort Method: top-N heapsort Memory: 30kB
-> Parallel Hash Join (actual time=2.911..87.546 rows=12833 loops=3)
Hash Cond: (b.cust_id = a.cust_id)
Buffers: shared hit=13424 read=33559
-> Parallel Seq Scan on "주문" b (actual time=0.440..51.048 rows=333333
Buffers: shared hit=11896 read=33559
-> Parallel Hash (actual time=1.455..1.455 rows=128 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
Buffers: shared hit=1429
-> Parallel Seq Scan on "고객" a (actual time=0.021..4.228 rows=385
Filter: ((sgg_cd)::text = 'K'::text)
Rows Removed by Filter: 9615
Buffers: shared hit=1429
Planning:
Buffers: shared hit=239
Planning Time: 2.277 ms
Execution Time: 102.322 ms
위 SQL은 SGG_CD 값이 'K'인 고객들이 주문한 내역 중 가장 최근의 10건만 추출하는 SQL이다.
실행계획을 보면 전혀 인덱스를 access하지 못하여 102 ms 소요되고 있다.
전형적인 Top-N row 추출 포맷으로 작성되어 SQL 에는 별 문제가 없어보인다.
위 SQL을 어떻게 성능을 향상시킬 수 있을지 검토해 보자.
아래는 테스트를 위한 스크립트이다.
CREATE TABLE 고객(
CUST_ID INT
,SGG_CD VARCHAR(5)
,CONTENTS CHAR(1000));
CREATE INDEX 고객_X01 on 고객(CUST_ID);
CREATE TABLE 주문(
ORD_NO BIGINT
,CUST_ID INT
,ORD_AMT NUMERIC
,ORD_DATE DATE
,DUMMY CHAR(300));
CREATE INDEX 주문_X01 ON 주문(CUST_ID, ORD_DATE);
INSERT INTO 고객
SELECT i, chr(65+mod(i-1,26)),'K'||mod(i,10000)
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);
analdb=# select pg_relation_size('고객');
pg_relation_size
------------------
11706368
analdb=# select pg_relation_size('주문');
pg_relation_size
------------------
372367360
테이블 크기가 작아서 위의 실행계획과 같이 테이블을 full 로 읽어서 조인해서 102 ms 만에 결과가 추출되었다.
위 SQL을 빠르게 할 수 있는 간단한 방법은 '고객' 테이블의 (SGG_CD, CUST_ID)에 인덱스를 생성하는 것이다.
하지만 인덱스 추가 또는 변경을 위해서는 많은 공수(인력과 시간)이 투입되어야 한다.
다른 SQL들을 모두 분석해야 하는데, 모든 SQL을 수집하는 것 부터가 큰 부담이다.
이론상으로는 데이터모델링 시 access path 예상해서 인덱스도 함께 설계해야 하나, 필자의 경우 개발 진행 중에 수시로 SQL 분석해서 인덱스를 조정한다.
이미 서비스 오픈한 시스템이라면, SQL 분석만 적어도 1주일은 소모될 것이다.
SQL들 분석 후 (SGG_CD, CUST_ID) 인덱스 추가해도 다른 SQL에는 별 영향이 없거나, 다른 SQL도 성능이 좋아질 것이라고 분석이 끝났다고 가정하자.
그래도 인덱스 추가는 여전히 DB서버 입장에서는 꺼려지는 작업이다.
인덱스 추가되면 해당 테이블에 INSERT/UPDATE/DELETE 작업시마다, 인덱스에도 작업이 일어나므로 CPU와 DISK, Memory를 갉아먹게 된다.
또한 옵티마이저가 실행계획 만들때마다 해당 인덱스를 고려해야 하므로 고객테이블 사용하는 SQL planning time도 길어진다.
특히 PostgreSQL은 인덱스와 관련 없는 컬럼 값이 변경되어도 인덱스 크기가 증가한다.
그래서 이미 오픈된 시스템에서 SQL 튜닝을 수행하는 경우, 튜너는 인덱스 추가를 극도로 피하게 된다.(사실상 인덱스 영향도 분석 위해 모든 SQL을 분석할 시간이 없는 경우가 많다.)
위 SQL을 보면 '주문' 테이블 ord_date 순서로 TOP 10건만 추출하는 것이다.
실행계획을 보면 '고객'테이블에서 sgg_cd로 filtering 처리 후 385건을 추출했음을 알 수 있다.
'고객'에서 추출한 1200 row로 '주문'을 Nested Loop로 access해도 일량이 많지 않을 것으로 예상된다. (주문 테이블 cust_id, ord_date 인덱스 이용)
주문테이블에서 고객별로 TOP 10건만 추출하는 작업은 매우 빠르고, 그 결과로 다시 TOP 10을 추출하도록 아래와 같이 Lateral Join을 이용해 보자.
SELECT B.*
FROM 고객 A
JOIN LATERAL
(SELECT * ---고객별 주문 TOP 10 추출
FROM 주문 B
WHERE A.CUST_ID = B.CUST_ID
ORDER BY B.ORD_DATE DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY) B ON (TRUE)
WHERE A.SGG_CD = 'K'
ORDER BY B.ORD_DATE DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
--실행계획
Limit (actual time=13.843..13.844 rows=10 loops=1)
-> Sort (actual time=13.842..13.843 rows=10 loops=1)
Sort Key: b.ord_date DESC
Sort Method: top-N heapsort Memory: 33kB
-> Nested Loop (actual time=0.025..12.805 rows=3850 loops=1)
-> Seq Scan on "고객" a (actual time=0.009..7.719 rows=385 loops=1)
Filter: ((sgg_cd)::text = 'K'::text)
Rows Removed by Filter: 9615
-> Limit (actual time=0.005..0.012 rows=10 loops=385)
-> Index Scan Backward using "주문_x01" on "주문" b (actual time=0.005..0.011 rows=10 loops=385)
Index Cond: (cust_id = a.cust_id)
Planning Time: 0.344 ms
Execution Time: 13.944 ms
의도한 대로 실행계획이 세워졌으며, elapsed time도 102 ms 에서 14 ms 로 개선되었다.
위 SQL 튜닝의 핵심은 고객테이블의 건수가 sgg_cd filter 조건으로 많이 줄었다는 것이다.
그 줄어든 row 수 때문에 Nest Loop로 주문테이블을 access해도 성능이 좋은 것이다.
그럼, 처음에 생각했던 인덱스 (SGG_CD, CUST_ID)추가해 보면 어떻게 될까?
고객테이블에 (SGG_CD, CUST_ID)를 추가하면, 고객테이블 대신에 INDEX만 access하므로 성능이 많이 개선될 것 같지만, 인덱스를 생성해도 성능은 별로 개선되지 않는다.
개선 전 실행계획을 보면 고객테이블 access 시에는 4.2 ms 밖에 소요되지 않았다.
개선 전 실행계획에서 일량이 많은것은 주문테이블을 full로 읽기 때문이다.
고객테이블에 INDEX 추가해도, 주문테이블 full로 읽는 것을 없앨 수는 없다.
