실행 계획은 DB가 SQL을 어떤 순서와 방식으로 실행하려는지 보여주는 지도이며, EXPLAIN ANALYZE는 실제 실행 결과와 추정의 차이를 확인하게 해준다.
Optimizer는 통계와 비용 모델을 바탕으로 Seq Scan, Index Scan, Join 방식, Sort, Aggregate 전략을 선택한다.
백엔드 개발자는 느린 API를 만났을 때 코드 감으로 추측하지 말고 실제 SQL, 바인딩 값, EXPLAIN, 읽은 행 수, 예상과 실제 차이를 확인해야 한다.
핵심 정리
같은 SQL도 데이터 양, 데이터 분포, 통계 최신성, 파라미터 값, 인덱스 상태에 따라 다른 계획을 선택할 수 있다.
PostgreSQL의 EXPLAIN은 계획을 보여주고, EXPLAIN ANALYZE는 실제 실행까지 하며 실제 시간과 행 수를 보여준다. 운영에서는 write 쿼리, 무거운 집계, lock 가능성이 있는 쿼리에 실행 부하가 생기므로 staging 재현, timeout, rollback, DBA 협의를 먼저 둔다.
MySQL의 EXPLAIN도 접근 타입, 사용 인덱스, 예상 rows, Extra 정보를 통해 스캔 범위와 정렬 여부를 확인하게 해준다.
느린 쿼리 분석은 “인덱스를 타는가” 하나로 끝나지 않는다. 얼마나 많은 행을 읽는지, 필터 후 얼마나 버리는지, 정렬과 임시 테이블이 있는지, 조인 순서가 맞는지 확인해야 한다.
Optimizer가 틀린 선택을 할 때는 통계가 오래됐거나, 데이터 분포가 치우쳤거나, 조건식이 인덱스를 활용하기 어렵거나, 쿼리 구조가 의도를 흐릴 수 있다.
실무에서는 실행 계획을 코드 리뷰 자료로 사용해야 한다. 중요한 목록 API와 배치 쿼리는 PR에 SQL, 대표 bind, 예상 row 수, 후보 인덱스, plan 캡처 시점을 함께 남긴다.
실행 계획은 트리로 읽는다. 위쪽 노드는 아래쪽 노드의 결과를 소비하므로, 병목은 leaf scan, join 중간 row, sort, aggregate 중 어디에서든 생길 수 있다.
cost는 실제 시간이 아니라 Optimizer의 상대 비용 추정이다. 실제 시간, buffer, rows 차이는 EXPLAIN ANALYZE, slow query, APM 지표와 함께 봐야 한다.
plan regression은 SQL이 바뀌지 않아도 발생할 수 있다. 데이터 분포, 통계 갱신, 파라미터 변화, 인덱스 변경, DB 버전 변경이 계획을 바꿀 수 있기 때문이다.
헷갈리는 지점
인덱스를 사용하면 항상 좋은 실행 계획이라고 생각하기 쉽다. Index Scan이라는 단어가 좋아 보이기 때문이다.
핵심은 인덱스로 많은 행을 랜덤 접근하면 전체 스캔보다 느릴 수 있다는 점이다.
실제 읽은 행 수와 비용, 버퍼 접근을 함께 봐야 한다.
EXPLAIN 결과의 cost를 실제 시간으로 오해하기 쉽다. 숫자가 시간처럼 보이기 때문이다.
핵심은 cost가 DB의 상대적 비용 단위라는 점이다.
실제 시간은 EXPLAIN ANALYZE나 모니터링 지표로 확인한다.
개발 환경 실행 계획을 운영 판단에 그대로 쓰기 쉽다. 테스트 데이터가 작고 균등하기 때문이다.
핵심은 운영 데이터 분포와 통계가 계획 선택을 바꾼다는 점이다.
운영과 비슷한 데이터 규모에서 계획을 보거나, 운영에서는 안전한 방식으로 계획을 수집해야 한다.
실행 계획은 락 대기까지 모두 알려준다고 오해하기 쉽다. 느린 SQL을 보면 항상 plan부터 떠올리기 때문이다.
핵심은 실행 계획은 주로 접근 경로와 연산 비용을 보여주며, lock wait나 pool wait는 별도 지표로 봐야 한다는 점이다.
같은 SQL이 계획상 빠르게 보여도 실제 요청은 lock, connection acquire, network 때문에 느릴 수 있다.
확인 질문
느린 API를 분석할 때 실행 계획 전에 먼저 확보해야 할 것은 무엇인가?
실제 실행된 SQL, 바인딩 값, 호출 빈도, 응답 시간 분포, 데이터 건수, 해당 시점의 DB 상태다.
Optimizer가 잘못된 계획을 고를 수 있는 이유는 무엇인가?
통계가 오래됐거나 데이터 분포가 치우쳤거나 조건식이 복잡하거나 비용 모델이 실제 환경을 완벽히 반영하지 못하기 때문이다.
EXPLAIN ANALYZE를 운영에서 조심해야 하는 이유는 무엇인가?
실제로 쿼리를 실행하므로 쓰기 쿼리는 부작용이 있고, 읽기 쿼리도 부하를 만들 수 있기 때문이다.
실행 계획과 함께 별도로 확인해야 하는 대기 지표는 무엇인가?
lock wait, connection pool pending/acquire time, active session, I/O wait, network latency, 애플리케이션 timeout 지표를 함께 확인해야 한다.