PostgreSQL 기초 강좌 #5 EXPLAIN 읽는 법: 실행 계획으로 쿼리 진단하기

인프라 PostgreSQL 데이터베이스
4 분 소요

4편에서 “인덱스가 사용되는지, 사용되지 않으면 왜인지는 실행 계획으로 확인한다"고 미뤄 두었습니다. 이번 편이 그 도구입니다. EXPLAIN은 PostgreSQL이 쿼리를 어떻게 실행할지(또는 어떻게 실행했는지)를 보여 주는 명령이고, 쿼리 성능에 관한 거의 모든 논쟁을 추측에서 사실로 바꿔 줍니다.

EXPLAIN vs EXPLAIN ANALYZE #

EXPLAIN과 EXPLAIN ANALYZE 
-- 계획만 봅니다(실행 안 함)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- 실제로 실행하고 실측치를 같이 봅니다
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

EXPLAIN은 플래너의 예상만 보여 주고, EXPLAIN ANALYZE는 쿼리를 실제로 실행해서 예상과 실측을 나란히 보여 줍니다. 진단에는 거의 항상 ANALYZE가 필요합니다. 단, 실제 실행이므로 UPDATE·DELETE에 걸면 데이터가 진짜 바뀝니다. 쓰기 쿼리를 진단할 때는 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;으로 감싸는 습관이 안전합니다.

계획 트리: 안쪽(들여쓰기 깊은 곳)부터 읽습니다 #

실행 계획 예시 
Nested Loop  (cost=0.57..205.31 rows=50 width=52) (actual time=0.041..0.318 rows=48 loops=1)
  ->  Index Scan using users_pkey on users u  (cost=0.29..8.30 rows=1 width=24)
        (actual time=0.019..0.020 rows=1 loops=1)
        Index Cond: (id = 42)
  ->  Index Scan using idx_orders_user_id on orders o  (cost=0.29..196.51 rows=50 width=36)
        (actual time=0.018..0.284 rows=48 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.210 ms
Execution Time: 0.361 ms

실행 계획은 트리이고, 가장 들여쓰기 깊은 노드가 먼저 실행되어 바깥으로 결과를 올려 보냅니다. 위 예시라면 “users를 PK 인덱스로 1건 찾고, orders를 user_id 인덱스로 48건 찾아, Nested Loop로 조인했다"로 읽습니다. 각 노드의 괄호 두 쌍이 핵심입니다. 앞쪽 cost=..., rows=...는 플래너의 예상, 뒤쪽 actual time=..., rows=...는 실측입니다. cost는 밀리초가 아니라 상대적인 비용 단위라서 절대값보다는 “어느 노드가 전체 비용의 대부분을 먹는가"를 보는 용도입니다.

스캔 노드 네 가지 #

노드의미신호
Seq Scan테이블 전체를 순서대로 읽음큰 테이블 + 좁히는 조건인데 이것이면 의심(4편의 “사용되지 않는 이유” 점검)
Index Scan인덱스로 위치를 찾고 테이블에서 행을 가져옴소량 조회의 정상 패턴
Index Only Scan인덱스만으로 응답(테이블 접근 생략)가장 빠른 형태. 커버링 인덱스는 실전 편에서
Bitmap Heap Scan인덱스로 후보 위치를 모아 한꺼번에 테이블을 읽음중간 규모 결과의 정상 패턴(Index Scan과 Seq Scan의 절충)

Seq Scan 자체는 죄가 아닙니다. 4편에서 본 대로 조건이 행을 충분히 좁히지 못하면 Seq Scan이 최선입니다. 문제는 “수백만 행 중 수십 건을 찾는 쿼리인데 Seq Scan"인 경우이고, 그때 4편의 체크리스트(선행 컬럼, 컬럼 가공, 자료형 불일치)로 돌아갑니다.

가장 중요한 비교: 예상 rows vs 실측 rows #

실행 계획 읽기에서 단 하나만 고르라면 이것입니다. 예상 rows와 실측 rows가 자릿수 단위로 어긋나는 노드를 찾으세요. 플래너는 통계를 근거로 조인 방식과 스캔 방식을 고르는데, “1행 예상이 실제 10만 행"이라면 그 아래의 모든 선택이 잘못된 전제 위에 서 있는 것입니다. 처방은 우선 통계 갱신입니다.

통계 재수집 
ANALYZE orders;  -- 통계 재수집(보통은 autovacuum이 자동으로 하지만, 대량 적재 직후에는 수동으로)

대량 INSERT나 마이그레이션 직후의 갑작스러운 성능 저하는 이 통계 낙후가 원인인 경우가 많습니다. 그래도 어긋나면 상관관계가 강한 컬럼 조합 등 더 깊은 주제로 들어가는데, 그것은 실전 편의 성능 진단에서 잇겠습니다.

BUFFERS: 읽기량을 보는 습관 #

BUFFERS 옵션 
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

BUFFERS 옵션은 각 노드가 읽은 블록 수를 보여 줍니다. shared hit는 메모리(캐시)에서, read는 디스크에서 읽은 양입니다. 같은 쿼리가 어떤 날은 빠르고 어떤 날은 느리다면, 계획이 바뀐 것이 아니라 캐시 적중률이 달라진 것일 수 있고, 그 구분을 이 숫자가 해 줍니다. 시간과 함께 읽기량을 보는 습관을 들이면 “왜 느린가"의 해상도가 한 단계 올라갑니다.

실무에서는 이 진단을 쿼리 하나씩이 아니라 “무엇이 느린지 찾기 → EXPLAIN으로 파기"의 순서로 돌리는데, 그 앞 단계(pg_stat_statements)는 실전 편의 주제입니다.

정리 #

  • EXPLAIN은 예상, EXPLAIN ANALYZE는 실측입니다. 진단은 ANALYZE가 기본이고, 쓰기 쿼리는 BEGIN/ROLLBACK으로 감쌉니다.
  • 계획 트리는 들여쓰기 깊은 안쪽부터 읽습니다. cost는 상대 단위이므로 “어디가 대부분을 먹는가"로 봅니다.
  • Seq Scan은 판단의 결과일 수 있습니다. “좁히는 쿼리인데 Seq Scan"일 때만 4편의 체크리스트로 돌아갑니다.
  • 단 하나를 본다면 예상 rows와 실측 rows의 자릿수 차이입니다. 크게 어긋나면 우선 ANALYZE로 통계를 갱신합니다.
  • BUFFERS로 읽기량까지 보면 “캐시 덕에 빨랐던 것"과 “계획이 좋은 것"을 구분할 수 있습니다. 다음 편은 트랜잭션과 MVCC입니다.
X