실행 계획 해부하기

목표

EXPLAIN 출력을 처음부터 끝까지 읽고, 어느 숫자를 어떤 순서로 보아야 하는지 몸에 익힙니다.

왜 중요한가

실행 계획에서 노드 이름 자체는 좋고 나쁨을 말해 주지 않습니다. Seq Scan 이 인덱스 스캔보다 빠른 상황이 분명히 존재하고, Nested Loop 가 최선인 경우도 많습니다. 옵티마이저는 자기가 가진 통계 안에서는 거의 항상 합리적으로 판단합니다.

그래서 계획이 이상해 보일 때 던져야 할 질문은 "왜 옵티마이저가 멍청한가"가 아니라 "내가 옵티마이저에게 무엇을 잘못 알려 주었는가" 입니다. 예상 행 수와 실제 행 수의 괴리가 그 답을 알려 줍니다.

읽는 순서를 정리하면 이렇습니다. 먼저 Execution Time 과 Planning Time 을 봅니다. 다음으로 각 노드의 예상 rows 와 실제 rows 를 비교해 한 자릿수 배율 이상 벌어진 가장 안쪽 노드를 찾습니다. loops 가 1이 아닌 노드는 곱셈을 해서 실제 기여를 계산합니다. Rows Removed by Filter 가 큰 노드에서 인덱스 기회를 찾고, 마지막으로 Buffers 의 read 비율로 계획 문제인지 캐시 문제인지 가릅니다.

단계

모든 출력 파일은 /root/exp/ 아래에 저장합니다.

1. orders 를 조회하는 아무 쿼리에 EXPLAIN 만 붙여 실행하고 출력을 /root/exp/plan_basic.txt 에 저장합니다. 실측치(actual time)가 없어야 합니다.
2. 같은 쿼리를 실제로 실행하는 옵션까지 붙여 /root/exp/plan_analyze.txt 에 저장합니다. actual time 과 Execution Time 이 있어야 합니다.
3. 블록 읽기 통계까지 포함한 조인 쿼리의 계획을 /root/exp/plan_buffers.txt 에 저장합니다. Buffers: shared ... 줄이 있어야 합니다.
4. skewed_events 라는 표를 만듭니다. 컬럼은 id, kind 이고 10만 행 이상이며, kind 가 rare 인 행은 전체의 1퍼센트 이하여야 합니다. 만든 뒤 통계를 수집해 플래너가 행 수를 알게 합니다.
5. 다른 조인 방식을 잠시 끈 상태에서 조인 쿼리를 실행해 계획을 /root/exp/plan_loops.txt 에 저장합니다. Nested Loop 와 세 자리 이상의 loops= 가 있어야 합니다.
6. 해시 조인이 선택되는 쿼리를 실측 실행해 /root/exp/plan_hash.txt 에 저장합니다. Hash Join 과 Batches: 가 있어야 합니다.
7. 같은 쿼리를 기본 상태와 순차 스캔을 막은 상태로 각각 실측 실행해 두 결과를 /root/exp/plan_forced.txt 에 이어 붙입니다. Seq Scan, 인덱스를 쓰는 노드, 그리고 Execution Time 이 두 번 이상 있어야 합니다.
8. 조건이 Index Cond 로 처리되고 Rows Removed by Filter 가 없는 계획을 실측 실행해 /root/exp/plan_fixed.txt 에 저장합니다.

참고

• 유용한 조합: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
• 조인 방식 끄기: SET enable_hashjoin = off; SET enable_mergejoin = off;
• 순차 스캔 끄기: SET enable_seqscan = off; — 진단용입니다. 운영 설정으로 두지 마세요.
• 흔한 실수 1: 1번에 ANALYZE 를 붙이면 실측치가 들어가 채점에 실패합니다.
• 흔한 실수 2: 4번에서 표만 만들고 통계 수집을 빠뜨리면 플래너가 행 수를 모릅니다.

단계 8개

1. 실행하지 않고 계획만 보기
2. 실측치가 붙은 계획 보기
3. 블록 읽기 통계 켜기
4. 값이 치우친 표 만들고 통계 수집하기
5. 반복이 많은 중첩 루프 관찰하기
6. 해시 조인의 배치 수 확인하기
7. 강제 전후를 실측으로 비교하기
8. Filter 를 Index Cond 로 옮기기