EXPLAIN ANALYZE — What to Look At, and in What Order

한 줄 요약

실행 계획을 읽는다는 것은 노드 이름을 훑는 일이 아니라 옵티마이저의 예측과 실제 결과 사이의 괴리를 찾는 일이다.

왜 이게 필요했나

느린 쿼리에 EXPLAIN ANALYZE 를 붙였는데 화면을 가득 채운 괄호 속 숫자를 한참 보다가 "Seq Scan 이 보이니 인덱스를 만들자"로 건너뛰는 일이 흔하다. 그 결론이 맞을 때도 있지만, 계획이 알려 주려던 것은 대개 다른 이야기다.

괴리가 없다면 옵티마이저는 자기가 아는 정보 안에서 최선을 골랐다는 뜻이고 남은 병목은 물리적인 문제다. 괴리가 크다면 옵티마이저는 틀린 전제 위에서 정확하게 계산한 것이고, 손볼 대상은 쿼리가 아니라 통계다.

어떻게 동작하나

EXPLAIN 은 계획만 세우고 EXPLAIN ANALYZE 는 실제로 실행한다. 그래서 EXPLAIN ANALYZE UPDATE ... 는 진짜로 UPDATE 를 수행한다. 쓰기 쿼리를 분석할 때는 트랜잭션으로 감싸고 롤백해야 한다.

읽는 순서는 가장 깊이 들여쓰인 노드부터다. 들여쓰기가 깊을수록 먼저 실행되고, 부모 노드는 자식이 끝난 뒤에 완성된다. 그리고 부모의 actual time 은 자식의 시간을 포함한 누적값이다. 이 사실을 모르면 "조인이 131ms 나 걸린다"는 잘못된 결론에 도달한다.

cost=1842.00..24310.55 에서 앞은 첫 행까지, 뒤는 마지막 행까지의 비용이다. 단위는 밀리초가 아니라 순차 페이지 한 장 읽기를 1.0 으로 놓은 임의 단위다. 그래서 다른 쿼리의 cost 와 비교하는 것은 의미가 없고, "cost 가 얼마를 넘으면 위험" 같은 기준도 근거가 없다.

진짜 봐야 할 것은 같은 노드 안의 두 숫자다.

->  Index Scan using idx_orders_status on orders o
      (cost=0.42..8.44 rows=1 width=20)
      (actual time=0.031..214.882 rows=482913 loops=1)

예상 1행, 실제 48만 행. 4만 배 이상 틀렸다. 옵티마이저는 "1행만 나올 테니 중첩 루프로 붙이면 되겠다"고 판단했을 것이고, 그 전제가 무너지며 안쪽 노드를 48만 번 반복하게 된다. 이때 고칠 것은 조인 힌트가 아니라 통계다.

loops 는 반드시 곱해서 봐야 한다. 표시된 시간과 행 수는 1회 실행 기준 평균이다. actual time=0.011 rows=4 loops=52310 이면 총 시간은 약 575ms, 총 행은 약 20만 행이다. 계획 어디에도 575라는 숫자는 적혀 있지 않으므로 곱셈을 직접 해 보지 않으면 병목을 지나친다.

BUFFERS 옵션이 없는 EXPLAIN ANALYZE 는 반쪽이다. shared hit 은 버퍼 캐시에서 찾은 블록, shared read 는 캐시 밖에서 읽은 블록이다. 같은 쿼리가 어제는 20ms, 오늘은 900ms 인 이유는 대개 계획이 아니라 캐시에 있다.

현장에서 만나는 모습

Filter 와 Index Cond 의 차이가 중요하다. Filter 는 행을 읽은 뒤에 버리는 것이고 Index Cond 는 애초에 읽지 않는 것이다. Rows Removed by Filter 밑에 큰 숫자가 있으면 그 조건을 인덱스로 올릴 기회가 있다는 신호다.

Hash Join 에서 Batches 가 1이 아니면 해시 테이블이 work_mem 에 다 들어가지 못해 디스크로 쪼개졌다는 뜻이다. 이 경우 인덱스를 만드는 것보다 그 세션의 work_mem 을 올리는 편이 훨씬 효과적이다. 다만 전역 설정을 올리면 위험하다. work_mem 은 커넥션당이 아니라 쿼리 안의 정렬이나 해시 연산 하나당 할당되기 때문이다.

통계가 틀렸을 때 무엇을 하나

앞에서 본 4만 배의 괴리는 옵티마이저가 게을러서 생긴 것이 아니라 틀린 전제 위에서 정확하게 계산한 결과다. 그러면 전제를 고쳐야 한다.

먼저 통계가 낡았는지 본다. 대량으로 넣거나 지운 직후에는 통계가 실제와 크게 다르다. 자동으로 갱신되는 장치가 있지만 그것이 도는 문턱은 표 크기에 비례하므로, 큰 표에서는 상당한 변화가 쌓여야 돈다. 대량 작업 뒤에는 손으로 한 번 갱신해 주는 것이 정석이다.

표본이 모자란지 본다. 통계는 표본에서 만들어지고 기본 표본 크기는 크지 않다. 값이 아주 다양한 열이나 편중이 심한 열에서는 그 표본이 실제 분포를 담지 못한다. 열 단위로 표본 목표를 올릴 수 있고, 올린 뒤에는 통계를 다시 만들어야 반영된다.

열들이 서로 얽혀 있는지 본다. 이것이 가장 자주 놓치는 자리다. 옵티마이저는 기본적으로 조건들이 서로 독립이라고 가정하고 선택도를 곱한다. 그런데 city = '서울' 과 region = '수도권' 은 독립이 아니다. 곱하면 실제보다 훨씬 작은 수가 나오고, 그 작은 수를 믿고 중첩 루프를 고른다. 여러 열의 상관을 함께 재는 확장 통계를 만들어 두면 이 종류의 오추정이 사라진다.

표현식으로 걸러도 마찬가지다. lower(email) = ... 처럼 함수를 씌우면 그 표현식에 대한 통계가 없으므로 옵티마이저는 고정된 어림값을 쓴다. 표현식 인덱스를 만들면 인덱스를 탈 수 있게 되는 것과 함께 그 표현식의 통계도 생긴다.

그리고 고칠 수 없는 경우도 있다. 조건이 다른 표의 값에 따라 달라지거나, 같은 쿼리가 파라미터에 따라 전혀 다른 분포를 만나는 경우다. 그때는 계획을 강제하기 전에 쿼리를 둘로 나누는 것을 먼저 검토한다. 하나의 쿼리가 두 가지 전혀 다른 일을 하고 있다면, 옵티마이저에게 하나의 계획을 고르라고 요구하는 것 자체가 무리다.

다음 실습에서 할 것

EXPLAIN 과 EXPLAIN ANALYZE 의 차이, BUFFERS 의 효용, loops 의 곱셈, Hash 의 Batches, 그리고 강제로 인덱스를 태웠을 때 오히려 느려지는 경우를 직접 측정해 파일로 남긴다.