실행계획은 우리가 실행한 SQL이 내부적으로 어떻게 처리되는지 알려줍니다. SQL 성능 개선을 위해서는 실행계획을 읽고 해석할 수 있어야 합니다. PG는 실행계획을 트리(Tree) 형태로 표현해 줍니다. 많은 DBMS가 이와 같은 트리 형태 실행계획을 제공합니다. MySQL 역시 8버젼 이상부터는 트리 형태로 실행계획을 제공합니다.

아래와 같이 SQL을 실행해보기 바랍니다.

EXPLAIN
/*+ NestLoop(t1 t2) */
SELECT  t1.* ,t2.ord_amt
FROM    ms_member t1 LEFT OUTER JOIN (
          SELECT  a.member_id, sum(a.ord_amt) ord_amt
          FROM    tr_ord a
          GROUP BY a.member_id
          ) t2
          ON (t2.member_id = t1.member_id)
WHERE   t1.join_dtm = TO_DATE('20190324','YYYYMMDD');

위 SQL을 실행하면 다음과 같은 실행계획이 출력됩니다.

• 각자의 환경(테이블 구조, 데이터 구성, DBMS 설정)에 따라 실행계획이 다르게 나올 수 있습니다.

1. Nested Loop Left Join  (cost=13510.51..14625.29 rows=17 width=99)
2.   Join Filter: ((t2.member_id)::text = (t1.member_id)::text)
3.   ->  Seq Scan on ms_member t1  (cost=0.00..271.99 rows=17 width=67)
4.         Filter: (join_dtm = to_date('20190324'::text, 'YYYYMMDD'::text))
5.   ->  Materialize  (cost=13510.51..13593.28 rows=3010 width=38)
6.         ->  Subquery Scan on t2  (cost=13510.51..13578.23 rows=3010 width=38)
7.               ->  HashAggregate  (cost=13510.51..13548.13 rows=3010 width=38)
8.                     Group Key: a.member_id
9.                     ->  Seq Scan on tr_ord a  (cost=0.00..10948.34 rows=512434 width=11)

설명 편의를 위해 실행계획의 각 라인 별로 번호를 표시했습니다. 트리 형태의 실행계획을 구성하는 각 단계 간에는 선후 관계의 순서가 존재합니다. 다만, 이러한 순서를 100% 정확히 해석하기는 어렵습니다. 또한 SQL 성능 개선을 위해 실행계획의 각 단계별 처리 순서를 100% 정확히 파악해야 할 필요도 없습니다. 실행계획의 중요 부분에 대한 처리 흐름을 이해하고 비효율을 찾아 낼 수 있으면 충분히 성능 개선을 할 수 있습니다.

트리 형태를 구성하는 하나의 단계는 자신을 기준으로 부모나 자식, 또는 형제를 가질 수 있습니다. 자신보다 상위 단계가 부모가 되며, 자신의 하위 단계가 자식이 됩니다. 그리고 자신과 같은 레벨이며, 부모가 같은 단계가 형제 단계가 됩니다. 형제 단계의 경우는 자신보다 위쪽에 표시되면 형이 되고 아래쪽이 표시되면 동생이 됩니다. 이와 같은 기준으로 실행계획의 몇 가지 단계를 정리해보면 다음과 같습니다. 위의 실행계획과 아래 설명을 같이 살펴보기 바랍니다.

• 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(ms_member) 테이블을 검색한 결과입니다.
• 5번 단계는 t2(tr_ord) 테이블을 검색해 GROUP BY 처리한 결과입니다.
• 1번 단계인 NL 조인은 두 개의 데이터 집합(3번과 5번)을 조인 처리합니다.
  • 이때, 실행계획 위쪽에 표시된 데이터 집합(NL의 자식 중 형)에 먼저 접근하고,
  • 아래쪽에 표시된 데이터 집합(NL의 자식 중 동생)을 나중에 접근합니다.
  • 그러므로 3번을 먼저 접근한 후에 5번에 접근하는 방식으로 NL 조인을 처리합니다.