PG는 실행계획을 트리(Tree) 형태로 표현해 줍니다. 많은 DBMS가 이와 같은 트리 형태 실행계획을 제공합니다. MySQL 역시 8버젼 이상부터는 트리 형태로 실행계획을 제공합니다.
실행계획은 우리가 실행한 SQL이 내부적으로 어떻게 처리되는지 알려줍니다. SQL 성능 개선을 위해서는 실행계획을 읽고 해석할 수 있어야 합니다. 아래와 같이 SQL을 실행해보기 바랍니다.
EXPLAIN
/*+ NestLoop(t1 t2) */
SELECT t1.* ,t2.ordamt
FROM member t1 LEFT OUTER JOIN (
SELECT a.memberid, sum(a.ordamt) ordamt
FROM ord_big a
GROUP BY a.memberid
) t2
ON (t2.memberid = t1.memberid)
WHERE t1.joindtm = TO_DATE('20190324','YYYYMMDD')
;
SQL
복사
위 SQL을 실행하면 다음과 같은 실행계획이 출력됩니다.
•
각자의 환경(테이블 구조, 데이터 구성, DBMS 설정)에 따라 다른 실행계획이 나올 수 있습니다.
1. Nested Loop Left Join (cost=1096500.60..1099538.43 rows=17 width=99)
2. Join Filter: ((t2.memberid)::text = (t1.memberid)::text)
3. -> Seq Scan on member t1 (cost=0.00..271.99 rows=17 width=67)
4. Filter: (joindtm = to_date('20190324'::text, 'YYYYMMDD'::text))
5. -> Materialize (cost=1096500.60..1096772.25 rows=9878 width=38)
6. -> Subquery Scan on t2 (cost=1096500.60..1096722.86 rows=9878 width=38)
7. -> HashAggregate (cost=1096500.60..1096624.08 rows=9878 width=38)
8. Group Key: a.memberid
9. -> Seq Scan on ord_big a (cost=0.00..888542.40 rows=41591640 width=11)
SQL
복사
설명의 편의를 위해 실행계획의 각 라인 별로 번호를 표시했습니다. 트리 형태의 실행계획을 구성하는 각 단계 간에는 선후 관계의 순서가 존재합니다. 다만, 이러한 순서를 100% 정확히 해석하기는 어렵습니다. 또한 SQL 성능 개선을 위해 실행계획의 각 단계별 처리 순서를 100% 정확히 파악해야 할 필요도 없습니다. 실행계획의 중요 부분에 대한 처리 흐름을 이해하고 비효율을 찾아 낼 수 있으면 충분히 성능 개선을 할 수 있습니다.
실행계획의 번호 별로 처리되는 내용을 해석해보면 다음과 같습니다.
1.
Nested Loop Left Join
a.
두 데이터 집합을 NL(Nested Loop) 방식으로 조인 처리합니다.
b.
일반적으로 자식 단계 중 위쪽(3) 단계를 먼저 접근한 후에, 아래쪽(5) 단계를 반복 접근하는 방식으로 조인을 처리합니다.
2.
Join Filter: 상위에 표시된 (1)Nested Loop Left Join에서 사용한 조건을 표시해주고 있습니다.
a.
(1)Nested Loop Left Join과 관련된 추가 정보입니다.
b.
(1)과 별도 단계가 아닌 같은 단계로 해석해야 합니다.
3.
Seq Scan on member t1
a.
Seq Scan은 TABLE FULL SCAN을 뜻합니다.
b.
조건에 맞는 데이터를 찾기 위해 member 테이블 전체를 스캔했음을 알려주고 있습니다.
4.
Filter
a.
상위에 표시된 (3)Seq Scan에서 데이터 검색을 위해 사용한 조건을 표시해주고 있습니다.
b.
(3)Seq Scan과 별도 단계가 아닌 같은 단계로 해석해야 합니다.
5.
Materialize
a.
SQL 실행 중 특정 데이터를 메모리에 담아 놓았음을 표시합니다.
b.
(5)의 자식 단계인 (6)번의 결과를 메모리 영역에 올려 놓습니다.
6.
Subquery Scan on t2
a.
t2 서브쿼리 부분을 검색했음을 표시합니다.
b.
서브쿼리의 내용은 자식 단계인 (7)의 결과가 됩니다.
7.
HashAggregate
a.
GROUP BY와 같은 데이터 집계가 있을 경우 발생하는 단계입니다.
8.
Group Key: a.memberid
a.
(7)HashAggregate와 관련된 정보로서 GROUP BY를 처리한 기준 컬럼을 표시해줍니다.
b.
(7)HashAggregate와 별도 단계가 아닌 같은 단계로 해석해야 합니다.
9.
SeqScan on t2
a.
Seq Scan은 TABLE FULL SCAN을 뜻합니다.
b.
t2에 따른 조건이 없으므로 테이블 전체를 스캔합니다.
트리 형태를 구성하는 하나의 단계는 자신을 기준으로 부모나 자식, 또는 형제를 가질 수 있습니다. 자신보다 상위 단계가 부모가 되며, 자신의 하위 단계가 자식이 됩니다. 그리고 자신과 같은 레벨이며, 부모가 같은 단계가 형제 단계가 됩니다. 형제 단계의 경우는 자신보다 위쪽에 표시되면 형이 되고 아래쪽이 표시되면 동생이 됩니다. 이와 같은 기준으로 실행계획의 몇 가지 단계를 정리해보면 다음과 같습니다.
•
1번 단계
◦
최상위 단계이므로 부모가 없습니다.
◦
자신의 하위 단계인 3번과 5번이 자식 단계입니다.
◦
2번 단계는 1번의 자식 단계처럼 들여쓰기 되어 있지만, 실질적으로 1번 단계의 추가 정보입니다.(PG에서는 → 표시 유무로 자식 단계를 파악해야 하는거 같습니다.
◦
최상위 단계이므로 형제가 없습니다.
•
3번 단계
◦
자신의 상위 단계인 1번이 부모입니다.
◦
하위 단계가 없으므모 자식이 존재하지 않습니다.
◦
자신과 같은 레벨이면서 부모가 같은 5번 단계는 형제 단계입니다.
▪
5번 단계보다 3번이 위쪽에 표시되고 있으므로 3번이 형, 5번이 동생이 됩니다.
◦
4번 단계는 3번의 하위(자식) 단계가 아닌, 3번 단계의 추가 정보로 해석해야 합니다.
•
5번 단계
◦
자신의 상위 단계인 1번이 부모입니다.
◦
하위 단계인 6번이 자식 단계입니다.
◦
자신과 같은 레벨이면서 부모가 같은 3번 단계가 형제 단계입니다.
▪
3번 단계가 위쪽에 표시되어 있으므로 3번이 형이 되고 5번 단계가 동생이 됩니다.
일반적인 실행계획의 처리 순서 규칙은 부모보다는 자식 먼저, 형제 간에는 형 먼저입니다. 하지만 이러한 순서가 100% 정확한 것은 아닙니다. 상황(단계별 처리 내용과 DBMS)에 따라서 실질적인 처리 순서는 완전히 달라질 수 있습니다. 실행계획의 순서를 해석하기 위해 이 규칙을 참고할 수 있으나, 정확할 수 없다는 점을 명심하기 바랍니다. 이제 지금까지 살펴본 내용을 종합해 실행계획의 큰 줄기를 정리해보면 다음과 같습니다.
•
3번 단계와 5번 단계를 NL(Nested Loop) 방식으로 조인 처리하고 있습니다.
•
3번 단계는 t1(member) 테이블을 검색한 결과입니다.
•
5번 단계는 t2(ord) 테이블을 검색해 GROUP BY 처리한 결과입니다.
•
NL 조인은 두 개의 데이터 집합(3번과 5번)을 조인 처리합니다.
◦
이때, 실행계획에 위쪽에 표시된 데이터 집합(NL의 자식 중 형)에 먼저 접근하고,
◦
아래쪽에 표시된 데이터 집합(NL의 자식 중 동생)을 나중에 접근합니다.
◦
그러므로 3번을 먼저 접근한 후에 5번에 접근하는 방식으로 NL 조인을 처리합니다.
위와 같이 실행계획의 흐름을 해석할 수 있습니다. 아직은 실행계획이 눈에 잘 들어오지 않아도 상관 없습니다. 앞으로 많은 SQL과 실제 실행계획을 통해 점차 눈에 익게 될 것입니다.