Search

PostgreSQL의 실행계획

EXPLAIN 이해하기

실행계획은 옵티마이저가 만드는 SQL의 처리 계획서다. 실행계획을 살펴봄으로써, 비효율적인 데이터 접근 방법이나, 불필요한 데이터에 접근이 있는지 찾아낼 수 있으며, 이러한 지점을 개선함으로써, SQL의 성능을 개선할 수 있다.
PostgreSQL에서 SQL의 실행계획을 보려면, SQL 맨 앞에 ‘EXPLAIN’ 키워드를 붙여 실행하면 된다. 아래 예처럼 실행해 보자. Index Scan 실행계획이 표시된다.
EXPLAIN SELECT t1.* FROM tr_ord_big t1 WHERE t1.ord_no = 3; Index Scan using tr_ord_big_pk on tr_ord_big t1 (cost=0.44..8.46 rows=1 width=66) Index Cond: (ord_no = '3'::numeric)
SQL
복사
위 SQL의 실행계획에는 아래와 같은 정보가 담겨져 있다.
•
Index Scan using tr_ord_big_pk on tr_ord_big t1
◦
tr_ord_big 테이블의 tr_ord_big_pk 인덱스를 Index Scan이란 오퍼레이션으로 사용한다.
•
(cost=0.44..8.46 rows=1 width=66)
◦
해당 Index Scan 오퍼레이션과 관련된 ‘예상’ 수치다.
◦
cost=0.44..8.46
▪
해당 오퍼레이션의 처리 비용으로서 PostgreSQL 내부의 상대적 비용 지표다.
▪
앞쪽의 0.44는 해당 오퍼레이션에서 첫 레코드가 출력되는 시점의 비용이다.
▪
뒤쪽의 8.46은 해당 오퍼레이션의 마지막 레코드가 출력되는 시점의 비용이다.
◦
rows = 1
▪
해당 오퍼레이션에서 출력되는 레코드의 예상 건수다.
◦
width = 66
▪
해당 오퍼레이션에서 출력되는 레코드별 예상 평균 크기로 byte 단위다..
◦
cost, rows, width는 실제 수치가 아닌 예상 수치다.
•
Index Cond: (ord_no = ‘3’::numeric)
◦
윗 줄의 Index Scan과 다른 줄에 표시되어 있지만, Index Scan의 추가 정보다.
◦
Index Scan에 사용한 조건 정보가 표시되어 있다.

Operation

실행 계획은 여러 개의 오퍼레이션으로 구성되어 있다. SQL의 FROM 절에 사용하는 테이블이 늘어날 수록 오퍼레이션이 늘어날 가능성이 높다. 아래 SQL은 두 테이블을 조인하고 있다. 실행계획을 살펴보면 총 다섯 개의 오퍼레이션이 출력된다.
EXPLAIN SELECT t1.pay_tp ,COUNT(*) ord_cnt FROM tr_ord t1 INNER JOIN ms_shop t2 ON (t2.shop_id = t1.shop_id) WHERE t1.ord_dtm >= TO_DATE('20240101','YYYYMMDD') AND t1.ord_dtm < TO_DATE('20240201','YYYYMMDD') GROUP BY t1.pay_tp; 1 HashAggregate (cost=771.11..771.13 rows=2 width=13) 2 Group Key: t1.pay_tp 3 -> Hash Join (cost=15.18..686.53 rows=16916 width=5) 4 Hash Cond: ((t1.shop_id)::text = (t2.shop_id)::text) 5 -> Index Scan using tr_ord_x01 on tr_ord t1 (cost=0.43..626.75 rows… 6 Index Cond: ((ord_dtm >= to_date('20240101'::text, 'YYYYMMDD’::t… 7 -> Hash (cost=11.00..11.00 rows=300 width=5) 8 -> Seq Scan on ms_shop t2 (cost=0.00..11.00 rows=300 width=5)
SQL
복사
PostgreSQL에서 각각의 오퍼레이션은 ‘->’ 표시로 구분된다. 단, 최상위 오퍼레이션은 ‘->’ 표시가 생략된다. 이 기준으로 오퍼레이션을 나누어 보면, 아래와 같이 다섯 개의 오퍼레이션으로 나온다.
•
1번, 2번 라인: HashAggregate
◦
Hash 알고리즘을 사용해 데이터를 요약(집계) 처리
◦
t1.pay_tp 컬럼을 키 값으로 데이터를 요약한다.(2번 라인)
•
3번, 4번 라인: Hash Join
◦
하위 오퍼레이션 두 개에 대해 Hash 방식으로 조인 처리
◦
Hash 방식 조인에 사용한 조건 컬럼은 t1.shop_id와 t2.shop_id(4번 라인)
•
5번, 6번 라인: Index Scan
◦
tr_ord 테이블에서 tr_ord_x01 인덱스를 사용해 필요한 데이터를 가져온다.
◦
데이터를 가져오는 조건 컬럼은 ord_dtm이다.(6번 라인)
•
7번 라인: Hash
◦
하위 오퍼레이션(8번 라인)을 Hash 처리했음을 나타낸다.
•
8번 라인: Seq Scan
◦
ms_shop 테이블에서 Seq Scan 방식으로 데이터를 가져온다.
위의 설명에서 HashAggregate, Hash join, Index Scan과 같은 용어는 지금 몰라도 괜찮다. 이 책에서 하나씩 배우게 될 것이다. 오퍼레이션을 정리해 보니, 하나의 오퍼레이션이 두 줄로 표시되기도 하고, 한 줄로 끝나기도 한다. 상황에 따라 세 줄 이상으로 표현되기도 한다. PostgreSQL의 실행계획에서 각각의 오퍼레이션은 ‘->’ 표시로 구분된다. 따라서 ‘->’ 표시 아래에 들여쓰기 된 내용은 새로운 오퍼레이션이 아니라, 바로 위 오퍼레이션의 추가 정보임을 기억하자.

실행계획 Tree

실행계획은 트리(Tree) 형태로 표현된다. 대부분의 DBMS가 이와 같은 트리 형태로 실행계획을 보여준다. MySQL 역시 최신 버젼에서는 트리 형태의 실행계획을 지원한다.
트리를 구성하는 각 노드(오페레이션)는 다른 노드와의 상대적 위치에 따라 부모, 자식, 형제로 구분할 수 있다. 앞에서 살펴본 실행계획을 아래 그림으로 살펴보자. 아래 그림에는 각 오퍼레이션 별로 번호를 부여하고 있다. 이 번호는 설명의 편의를 위해 부여한 번호다. 실제 SQL이 내부적으로 처리되는 순서와는 무관하다.
위쪽 오퍼레이션보다 오른쪽으로 들여쓰기된 오퍼레이션은 위쪽 오퍼레이션의 자식 오퍼레이션이 된다. 당연히 위쪽 오퍼레이션은 부모가 된다. 부모가 같으면서 같은 수준의 오른쪽 들여쓰기를 가진 오퍼레이션은 형제 오퍼레이션이 된다. 이러한 기준을 적용해 각 오퍼레이션별로 상황을 정리해보면 다음과 같다.
•
1번 오퍼레이션
◦
바로 아래에 들여쓰기된 2번 오퍼레이션이 있으므로, 2번 오퍼레이션의 부모다.
•
2번 오퍼레이션
◦
바로 위에 1번보다 들여쓰기 되어 있으므로, 2번은 1번의 자식(Child) 오퍼레이션이다.
◦
바로 아래에는 같은 수준으로 들여쓰기 된, 3번과 4번 오퍼레이션이 있다.
▪
2번은 3번과 4번의 부모 오퍼레이션이다.
•
3번 오퍼레이션
◦
바로 위에 2번보다 들여쓰기 되어 있으므로, 3번은 1번의 자식이다.
◦
바로 아래, 4번이 같은 수준의 들여쓰기가 되어 있으므로 3번과 4번은 형제다.
•
4번 오퍼레이션
◦
바로 위에 3번이 같은 수준의 들여쓰기가 되어 있으므로 4번은 3번과 형제다.
◦
4번보다 위쪽에, 4번보다 왼쪽에 2번 오퍼레이션이 위치해 있다.
▪
4번은 2번의 자식 오퍼레이션이다.
◦
4번 오퍼레이션 아래에, 들여쓰기 된 5번 오퍼레이션이 있다.
▪
4번은 5번의 부모 오퍼레이션이다.
•
5번 오퍼레이션
◦
바로 위에 4번보다 들여쓰기 되어 있으므로 4번의 자식 오퍼레이션이다.
이처럼 각각의 오퍼레이션은 다른 오퍼레이션과의 상대적인 위치에 따라 부모가 될 수도 있으며 자식이 될 수도 있다.

Child First

실행계획을 보면, 데이터의 처리 순서나 처리 방식을 이해할 수 있다. 다시 그림으로 실행계획을 살펴보자.
일반적으로 부모는 자식의 오퍼레이션 결과를 받아서 처리한다. 위 실행계획을 예로 들면, 다음과 같다.
1.
HashAggrgate
a.
자식인 2번 오퍼레이션의 결과를 HashAggregate 처리한다.
2.
Hash Join
a.
자식인 3번과 4번의 결과를 Hash Join 처리한다.
3.
Index Scan tr_ord
a.
tr_ord 테이블을 Index Scan 방식으로 접근해 필요한 데이터를 가져온다.
4.
Hash
a.
자식인 5번의 결과를 Hash 처리한다.
5.
Seq Scan ms_shop
a.
ms_shop 테이블을 Seq Scan 방식으로 접근해 필요한 데이터를 가져온다.
부모는 자식의 오퍼레이션 결과를 받아서 처리하지만, 그렇다고 해서 무조건 자식의 오퍼레이션이 완료된 후에 부모 오퍼레이션이 처리되는 것은 아니다. 경우에 따라서는 자식과 부모 오퍼레이션을 교대로 오가면서 처리될 수도 있다. 예를 들어, 4번 오퍼레이션은 5번이 완료된 후에 처리될 수도 있지만, 상황에 따라서는 4번과 5번, 서로를 오가면서 동시에 처리될 수도 있다. 특히, 2번 오퍼레이션인 Hash Join은 자식 오퍼레이션이 모두 완료된 후에 조인이 처리된다고 하는 것보다는, 3번과 4번의 결과를 조인 처리한다고 해석하는 것이 더 자연스럽다. 실제로도, 3번의 처리 과정과 2번의 처리 과정은 동시에 처리된다. 실제 처리 순서를 100% 이해하려면 해당 오퍼레이션에 해당하는 PostgreSQL의 소스를 살펴봐야 할 것이다. DBMS를 새로 개발할 것이 아니라면 그 정도로 이해할 필요는 없다. 실행계획을 통해 실제 처리 순서를 완벽하게 읽거나 해석할 수는 없지만, 자식 오퍼레이션의 결과를 받아 부모 오퍼레이션이 받아서 처리한다는 개념을 갖고 있기 바란다.
준비한 내용은 여기까지 입니다.
데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.
•
SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터
•
SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!
•
그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서