EXPLAIN ANALYZE 읽는 법 — 추정 행 수와 실제 행 수 비교하기

느린 쿼리를 잡으려고 EXPLAIN 을 봐요. 그런데 출력이 무슨 뜻인지 모르겠어요. 비용이라는 숫자가 나오는데 단위도 모르겠고, 이게 밀리초인지 아닌지도 헷갈려요.

그래서 대개 맨 위 줄만 보고 "순차 스캔이네" 정도로 넘어가요. 인덱스를 하나 만들고 다시 봐서 인덱스 스캔이 뜨면 해결됐다고 판단하고요.

그런데 인덱스 스캔인데도 여전히 느린 경우가 있어요. 그때는 EXPLAIN 이 아무 도움이 안 되는 것처럼 보여요. 사실은 잘못 읽고 있었던 건데요. 이 도구는 계획을 보라고 있는 게 아니라 추정이 얼마나 빗나갔는지 보라고 있습니다.

왜 그런가

EXPLAIN 만 쓰면 옵티마이저의 추정만 나와요. 실제로 실행하지 않으니까요. 비용 숫자는 임의 단위라서 밀리초가 아니고, 다른 계획과 비교할 때만 의미가 있어요.

ANALYZE 를 붙이면 실제로 실행하고 결과를 같이 보여 줘요. 여기서 핵심이 나옵니다. 각 단계마다 추정 행 수와 실제 행 수가 나란히 찍혀요.

이 두 숫자의 비율이 진단의 출발점이에요.

비슷하면 옵티마이저가 상황을 제대로 파악한 거예요. 그런데도 느리다면 계획 문제가 아니라 진짜로 일이 많은 거예요. 쿼리가 가져오는 데이터 자체를 줄여야 해요.

크게 다르면 옵티마이저가 잘못된 정보로 판단한 거예요. 50행 나올 줄 알고 중첩 루프를 골랐는데 실제로 100만 행이면, 100만 번의 반복이 돌아요. 이건 통계나 상관관계 문제고, 계획을 억지로 바꾸는 게 아니라 추정을 고쳐야 해요.

그래서 EXPLAIN 을 "어떤 계획인가"로 읽으면 절반만 보는 거예요. "추정이 맞았는가"로 읽어야 원인이 갈립니다.

코드로 보면

추정과 실제를 나란히 놓는다:

✕ EXPLAIN SELECT ...;
✕ -- 추정만 나온다. 비용 숫자는 밀리초가 아니다

✓ EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
✓ SELECT ...;

· 읽는 순서 — 안쪽(들여쓰기 깊은 곳)부터 바깥으로
· 각 줄에서 볼 것 세 가지
✓ rows=50 (추정) vs actual rows=1200000  ← 비율
✓ actual time=0.1..8500  ← 이 단계가 실제로 먹은 시간
✓ loops=1200000          ← 반복 횟수. 시간 × loops 가 총 비용

· BUFFERS 로 실제 읽은 양을 본다
✓ shared hit=12 read=480000  ← 캐시 적중 vs 디스크에서 읽음

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

읽을 때 실수하기 쉬운 지점이 몇 개 있어요.

첫째, loops 를 빠뜨리는 것. 중첩 루프의 안쪽 노드는 여러 번 실행돼요. actual time 은 1회 평균이라 총 비용은 그 값에 loops 를 곱해야 해요. 0.05ms 짜리가 100만 번 돌면 50초예요. 이 곱셈을 안 하면 "빠른 단계"로 오해합니다.

둘째, 시간이 누적값이라는 것. 부모 노드의 시간에는 자식의 시간이 포함돼 있어요. 그래서 어느 단계가 진짜 범인인지 보려면 부모에서 자식을 빼야 해요.

셋째, BUFFERS 를 안 켜는 것. 시간은 캐시 상태에 따라 크게 흔들려요. 두 번째 실행이 빠른 건 계획이 좋아서가 아니라 캐시에 올라와서일 수 있어요. 읽은 블록 수는 캐시와 무관한 안정적인 지표라, 계획을 비교할 때 시간보다 신뢰할 만합니다.

넷째, 필터로 버린 행을 안 보는 것. 어떤 단계가 100만 행을 읽어서 999,950 행을 버리고 50행을 냈다면, 읽는 단계에서 걸렀어야 할 일을 나중에 한 거예요. 인덱스나 조건 순서를 고칠 신호입니다.

다섯째, 실제 파라미터로 안 보는 것. 상수로 EXPLAIN 을 돌리면 옵티마이저가 그 값의 분포를 보고 계획을 세워요. 애플리케이션은 파라미터를 바인딩해 보내니 계획이 다를 수 있어요. 느린 요청의 실제 값으로 봐야 합니다.

그리고 ANALYZE 는 실제로 실행한다는 걸 기억하세요. UPDATE 나 DELETE 에 붙이면 정말로 바뀝니다. 트랜잭션으로 감싸고 롤백하는 습관이 필요해요.

처방

1. EXPLAIN 대신 EXPLAIN (ANALYZE, BUFFERS) 를 기본으로 쓰세요. 추정만 보는 건 진단의 절반도 안 됩니다.
2. 각 줄에서 추정 대비 실제 비율을 먼저 보세요. 열 배 이상 벌어진 첫 지점이 대개 원인이에요.
3. loops 를 곱해서 총 비용을 계산하세요. 안쪽 노드의 시간은 1회 평균입니다.
4. 필터로 버린 행 수를 보세요. 많이 읽어서 많이 버렸다면 조건을 앞으로 당길 여지가 있어요.
5. 느린 요청의 실제 파라미터로 실행하세요. 대표값으로 보면 문제가 재현되지 않습니다.
6. 쓰기 문장에 ANALYZE 를 붙일 때는 트랜잭션으로 감싸고 롤백하세요.
7. 계획을 비교할 때는 시간보다 읽은 블록 수를 보세요. 캐시 상태에 덜 흔들립니다.
8. 계획을 억지로 바꾸기 전에 추정을 고칠 수 있는지 보세요. 통계 갱신이나 확장 통계로 해결되면 그게 훨씬 오래갑니다.

지금 확인해 보세요

어디를 먼저 보는가:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

· 1) 추정 대비 실제 — 열 배 이상 벌어진 첫 지점
✓ rows=50  actual rows=1200000   ← 여기가 출발점

· 2) loops 를 곱했는가
✓ actual time=0.05 ... loops=1200000  → 실제 60초

· 3) 버린 행
✓ Rows Removed by Filter: 999950   ← 늦게 걸렀다

· 4) 디스크에서 읽었는가
✓ shared hit=12 read=480000        ← 캐시 밖이다

· 쓰기 문장을 볼 때는 반드시
✓ BEGIN; EXPLAIN (ANALYZE) UPDATE ...; ROLLBACK;

핵심 한 줄EXPLAIN 은 계획을 보는 게 아니라 추정이 빗나간 곳을 찾는 도구다.