백엔드 데이터 인프라 40편 — EXPLAIN 쿼리 계획 읽기

이 글은 백엔드 데이터 인프라 시리즈 70편 중 40편이에요. "이 쿼리 왜 느린가" 의 답이 EXPLAIN. PG 운영 깊이의 핵심 도구.

EXPLAIN 기본

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

결과:

                          QUERY PLAN
─────────────────────────────────────────────────────────
 Index Scan using idx_users_email on users
   (cost=0.42..8.44 rows=1 width=128)
   Index Cond: (email = 'alice@example.com'::text)

읽기: - Index Scan = 사용 방법 - cost=0.42..8.44 = 추정 비용 (시작 비용..총 비용) - rows=1 = 추정 결과 행 수 - width=128 = 행 평균 크기

EXPLAIN ANALYZE — 실제 실행

EXPLAIN ANALYZE SELECT ...;

ANALYZE = 실제 실행 + 실측 추가.

 Index Scan using idx_users_email on users
   (cost=0.42..8.44 rows=1 width=128)
   (actual time=0.025..0.026 rows=1 loops=1)
   Index Cond: (email = 'alice@example.com')
 Planning Time: 0.150 ms
 Execution Time: 0.045 ms

actual time = 실측. cost 와 비교 — 큰 차이 = 통계 부정확.

옵션 추가

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

옵션	의미
ANALYZE	실제 실행 + 실측
BUFFERS	디스크·메모리 읽기 통계
VERBOSE	상세 출력
COSTS (기본)	비용 표시
TIMING (기본 ANALYZE 시)	시간 측정
SETTINGS	영향 설정
FORMAT TEXT/JSON/YAML	출력 형식
WAL	WAL 생성 통계

운영 진단 표준 = EXPLAIN (ANALYZE, BUFFERS) ....

스캔 노드 4가지

Seq Scan          → 풀스캔 (인덱스 미사용)
Index Scan        → 인덱스 + 테이블 접근
Index Only Scan   → 인덱스만으로 (Heap Fetches: 0 일 때 최고)
Bitmap Index Scan → 여러 인덱스·범위

Seq Scan — 풀스캔

Seq Scan on users (cost=0.00..18334.00 rows=100 width=...)
  Filter: (email = '...')
  Rows Removed by Filter: 999900

Rows Removed 가 크면 — 인덱스 박을 곳.

Index Scan

Index Scan using idx_users_email on users
  Index Cond: (email = '...')

Index Cond = 인덱스에서 찾는 조건. Filter 가 추가로 있으면 = 인덱스 + 추가 필터.

Index Only Scan — 최고

Index Only Scan using idx_users_email on users
  Index Cond: (email = '...')
  Heap Fetches: 0

Heap Fetches: 0 = 테이블 안 봐도 답. INCLUDE 인덱스 또는 "필요한 컬럼 모두 인덱스에".

Bitmap Index Scan

Bitmap Heap Scan on users
  ->  BitmapOr
        ->  Bitmap Index Scan on idx_users_city
        ->  Bitmap Index Scan on idx_users_country

여러 인덱스 결합. "city=Seoul OR country=Korea" 같은 OR 검색.

JOIN 노드 3가지

Nested Loop  → 한쪽 행마다 다른 쪽 스캔 (작은 테이블)
Hash Join    → 해시 테이블 + 룩업 (중간)
Merge Join   → 양쪽 정렬 후 병합 (인덱스 정렬)

Nested Loop

Nested Loop  (cost=...)
  ->  Index Scan on users  (rows=10)
  ->  Index Scan on orders
        Index Cond: (user_id = users.id)

사용자 10명 × 각자 orders 인덱스 검색. 작은 외부 + 인덱스 내부 = 매우 빠름.

Hash Join

Hash Join
  Hash Cond: (users.id = orders.user_id)
  ->  Seq Scan on orders
  ->  Hash
        ->  Seq Scan on users

users 로 해시 테이블 만들고 — orders 스캔. 큰 테이블 + 작은 빌드 시 효율.

Merge Join

Merge Join
  Merge Cond: (users.id = orders.user_id)
  ->  Index Scan using idx_users_id
  ->  Index Scan using idx_orders_user_id

양쪽 인덱스 정렬 = 한 번에 병합. 매우 빠름.

PG 계획자가 자동 선택. "왜 Nested Loop 가 아닌 Hash 인가" 같은 의문 = 통계·비용 추정 차이.

정렬·집계 노드

Sort  (cost=... )
  Sort Key: created_at DESC
  Sort Method: quicksort  Memory: 25kB
  ->  ...

Sort Method = quicksort (메모리) · external merge (디스크). 디스크 정렬 = 느림. work_mem 늘리기.

HashAggregate  (cost=...)
  Group Key: category
  ->  ...

GROUP BY 의 해시 집계. 큰 그룹 = 메모리 부담.

Aggregate  (cost=...)
  ->  ...

집계 함수 (COUNT·SUM 등). 단순한 경우.

Limit 노드

Limit
  ->  Sort
        Sort Key: created_at DESC
        ->  Seq Scan

LIMIT 가 잘 박히면 — Limit ▸ Index Scan 형태로 정렬 X. 인덱스 ORDER BY 방향 활용.

CTE·서브쿼리

CTE Scan on cte_name
  ->  ... (CTE 정의)

PG 12+ 부터 CTE 가 "인라인 자동" (옵션). 옛 동작 (CTE materialize) 이 필요하면 WITH ... AS MATERIALIZED.

비용·실측 분석

비용 의미

cost=START..TOTAL  rows=N  width=W

• START = 첫 행 반환까지 비용 (LIMIT 에 유리)
• TOTAL = 모든 행 처리까지 비용
• rows = 추정
• width = 평균 바이트

실측 비교

actual time=START..TOTAL rows=N loops=L

• START..TOTAL = 실제 시간 (ms)
• rows = 실제 결과
• loops = 노드 실행 횟수 (Nested Loop 안 inner)

loops=1000 + actual time 10ms = 노드 자체는 10ms, 총 10초.

추정 vs 실측 어긋남

rows=100 (estimated) vs rows=10000 (actual)

100배 차이 = 통계 부정확. ANALYZE table; 실행.

BUFFERS — I/O 통계

Index Scan
  Buffers: shared hit=5 read=2

• hit = 메모리 캐시 (빠름)
• read = 디스크 (느림)

read 가 많으면 — 캐시 부족, 메모리 늘리기·자주 안 쓰는 데이터 분리.

시각화 도구

복잡한 EXPLAIN 출력은 — 도구 활용:

• explain.depesz.com — 가장 유명
• explain.dalibo.com — Dalibo 시각화
• pgMustard — 분석 + 권장

JSON 출력을 붙여넣어 시각화.

실전 — 느린 쿼리 진단

1. 느린 쿼리 찾기

-- pg_stat_statements 확장
CREATE EXTENSION pg_stat_statements;

SELECT
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

운영 환경의 "느린 쿼리 TOP".

2. EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) <slow_query>;

3. 진단

• Seq Scan + 큰 Rows Removed = 인덱스 박기
• Sort Method: external = work_mem 늘리기
• Nested Loop + 큰 loops = JOIN 인덱스
• 추정 vs 실측 어긋남 = ANALYZE

4. 적용 + 검증

인덱스 추가 → 다시 EXPLAIN → 확인.

함정 5가지

(1) EXPLAIN 만 (ANALYZE 없음)

추정만 — 실제 동작 모름. ANALYZE 무조건.

(2) BUFFERS 안 봄

캐시 hit 율 모름. BUFFERS 표준.

(3) PROD 에 EXPLAIN ANALYZE 무서워서 못 함

EXPLAIN ANALYZE = 실제 쿼리 실행. UPDATE·DELETE 에 ANALYZE 박으면 — 진짜 변경. BEGIN-ROLLBACK 으로 안전.

BEGIN;
EXPLAIN ANALYZE UPDATE users SET ...;
ROLLBACK;

(4) Cost vs Time 혼동

Cost = 추정 단위. Time = 실측 ms. 비교 X.

(5) 통계 갱신 잊음

대량 DML 후 ANALYZE 안 하면 — 계획자 잘못 판단. 주기적.

한 줄 정리 — EXPLAIN ANALYZE BUFFERS = 운영 진단 표준. 스캔 4종·JOIN 3종·정렬·집계 노드 의미. 추정 vs 실측 어긋남 = ANALYZE. UPDATE 진단 = BEGIN-ROLLBACK 안전. pg_stat_statements + 시각화 도구.

시험 직전 한 번 더 — EXPLAIN 입문자가 매번 헷갈리는 것

• EXPLAIN = 추정 계획만
• EXPLAIN ANALYZE = 실제 실행 + 실측
• BUFFERS = I/O 통계 (운영 필수)
• 스캔 = Seq·Index·Index Only·Bitmap
• Index Only + Heap Fetches: 0 = 최고
• Rows Removed by Filter = 인덱스 박을 곳
• JOIN = Nested Loop·Hash·Merge
• Nested Loop = 작은 외부 + 인덱스 내부
• Hash Join = 큰 + 작은 빌드
• Merge Join = 양쪽 정렬
• Sort Method: quicksort vs external merge
• work_mem 늘리면 = 메모리 정렬
• HashAggregate = GROUP BY
• cost = 추정 비용 단위
• actual time = 실측 ms
• loops = 노드 실행 횟수 (총 시간 = time × loops)
• 추정 vs 실측 차이 100배+ = ANALYZE
• shared hit·read = 캐시 vs 디스크
• pg_stat_statements = 느린 쿼리 통계
• UPDATE/DELETE 진단 = BEGIN-ROLLBACK
• explain.depesz.com 시각화
• 통계 부정확 = ANALYZE 수동
• 한국 백엔드 = EXPLAIN ANALYZE BUFFERS 표준
• 운영 진단 = 매주 느린 쿼리 TOP 점검

시리즈 다른 편

• Part 2 SQL Language 깊이: 37편 MVCC · 38편 격리 수준 · 39편 전문 검색 · 40편 (현재 글)

시리즈 다음 글

다음 글(41편)에서는 성능 팁 종합 — 메모리·디스크·쿼리·인덱스 운영.

공식 문서: PostgreSQL 18 — Using EXPLAIN에서 더 자세한 사양을 확인할 수 있어요.