Dissecting an Execution Plan

목표

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번에서 표만 만들고 통계 수집을 빠뜨리면 플래너가 행 수를 모릅니다.

실행하지 않고 계획만 보기

orders 를 조회하는 아무 쿼리에 EXPLAIN 만 붙여 실행하고 출력을 /root/exp/plan_basic.txt 에 저장합니다. 실측치(actual time)가 없어야 합니다.

옵션 없이 키워드 하나만 붙이면 쿼리를 실행하지 않고 추정치만 보여 줍니다.

실측치가 붙은 계획 보기

같은 쿼리를 실제로 실행하는 옵션까지 붙여 /root/exp/plan_analyze.txt 에 저장합니다. actual time 과 Execution Time 이 있어야 합니다.

실제로 실행해 측정하는 옵션이 있습니다. 쓰기 쿼리에 쓸 때는 트랜잭션으로 감싸세요.

블록 읽기 통계 켜기

블록 읽기 통계까지 포함한 조인 쿼리의 계획을 /root/exp/plan_buffers.txt 에 저장합니다. Buffers: shared ... 줄이 있어야 합니다.

캐시 적중과 디스크 읽기를 구분해 주는 옵션이 있습니다. 괄호 안에 여러 옵션을 쉼표로 나열할 수 있습니다.

값이 치우친 표 만들고 통계 수집하기

skewed_events 라는 표를 만듭니다. 컬럼은 id, kind 이고 10만 행 이상이며, kind 가 rare 인 행은 전체의 1퍼센트 이하여야 합니다. 만든 뒤 통계를 수집해 플래너가 행 수를 알게 합니다.

generate_series 로 10만 행 이상을 만들고, 특정 값이 1퍼센트 이하로 아주 드물게 나오게 합니다. 만든 뒤 통계를 수집해야 플래너가 압니다.

반복이 많은 중첩 루프 관찰하기

다른 조인 방식을 잠시 끈 상태에서 조인 쿼리를 실행해 계획을 /root/exp/plan_loops.txt 에 저장합니다. Nested Loop 와 세 자리 이상의 loops= 가 있어야 합니다.

다른 조인 방식을 잠시 막으면 중첩 루프가 선택됩니다. 계획에서 loops 값을 확인하세요.

해시 조인의 배치 수 확인하기

해시 조인이 선택되는 쿼리를 실측 실행해 /root/exp/plan_hash.txt 에 저장합니다. Hash Join 과 Batches: 가 있어야 합니다.

해시 노드의 실측 정보는 실제로 실행해야 나옵니다. Batches 가 1인지 그 이상인지 보세요.

강제 전후를 실측으로 비교하기

같은 쿼리를 기본 상태와 순차 스캔을 막은 상태로 각각 실측 실행해 두 결과를 /root/exp/plan_forced.txt 에 이어 붙입니다. Seq Scan, 인덱스를 쓰는 노드, 그리고 Execution Time 이 두 번 이상 있어야 합니다.

같은 쿼리를 기본 상태와 순차 스캔을 막은 상태로 각각 실행해 한 파일에 이어 붙입니다.

Filter 를 Index Cond 로 옮기기

조건이 Index Cond 로 처리되고 Rows Removed by Filter 가 없는 계획을 실측 실행해 /root/exp/plan_fixed.txt 에 저장합니다.

읽고 버리는 대신 처음부터 읽지 않게 만드는 것이 목표입니다. 계획에서 Rows Removed by Filter 줄이 사라져야 합니다.