실행 계획 읽기

학습 목표

EXPLAIN 출력이 실행 전략이지 결과 집합이 아님을 설명할 수 있다.

Seq Scan·Index Scan·Index Only Scan 차이와 선택 조건을 설명할 수 있다.

Nested Loop·Hash Join·Merge Join의 동작과 적합한 입력 크기를 설명할 수 있다.

cost·rows·actual time 필드를 읽고 튜닝 방향을 잡을 수 있다.

문제 상황

• user_id 인덱스를 만들었는데 여전히 Seq Scan이다
  • 선택도·통계·조건식 때문에 옵티마이저가 풀 스캔을 고른다
• EXPLAIN에 cost=0.00..1234.56 rows=100 — 숫자만 보고 ms로 착각한다
  • cost는 상대 단위, 버전·설정마다 스케일이 다르다
• 작은 테이블 조인인데 Hash Join이 아니라 Nested Loop가 나온다
  • 행 수 추정·메모리·인덱스 유무에 따라 join 알고리즘이 바뀐다
• EXPLAIN과 실제 실행 시간이 반대다
  • EXPLAIN만으로는 I/O·캐시·락을 못 본다 → EXPLAIN ANALYZE 필요
• ORM SQL이 느린데 어느 노드가 병목인지 모른다
  • 플랜 트리를 위에서 아래로 읽어야 한다

앞서 쿼리 처리 파이프라인과 인덱스 구조를 봤다. 이번 편은 옵티마이저가 고른 실행 계획을 읽는다.

1. EXPLAIN이 보여 주는 것

EXPLAIN — SQL을 어떤 연산자 순서로 실행할지 텍스트(또는 JSON)로 출력.

	EXPLAIN	EXPLAIN ANALYZE
실행	하지 않음 (계획만)	실제 실행 후 측정
시간	추정 cost	actual time ms
부하	거의 없음	쿼리 실행 — prod 주의
용도	계획 비교·인덱스 설계	병목·추정 오차 확인

EXPLAIN SELECT o.id, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.email = 'a@example.com';

PostgreSQL은 트리 형태 — 가장 안쪽·아래가 데이터 접근, 루트가 최종 결과 조립.

키워드	의미
cost=시작..끝	옵티마이저 누적 비용 (임의 단위)
rows	해당 노드 출력 행 수 추정
width	행 평균 바이트 (PostgreSQL)
actual time	ANALYZE 시 실측 ms (첫 row..마지막 row)
loops	상위 노드가 하위를 반복 호출한 횟수

• cost는 상대 비교용 — 100 vs 10000이면 후자가 훨씬 비싸다고 보면 된다
• rows 추정이 틀리면 join·scan 선택도 틀어진다 → ANALYZE·통계 갱신

2. 접근 경로: Scan 종류

테이블·인덱스에서 행을 어떻게 가져오는지가 플랜의 leaf다.

연산자	동작	흔한 경우
Seq Scan	모든 페이지 순차 읽기	테이블 작음, 선택도 낮음, 인덱스 없음
Index Scan	B+ Tree 탐색 → 힙 TID fetch	WHERE user_id = ? 등 선택적 조건
Index Only Scan	인덱스 리프만 (커버링)	SELECT 열이 인덱스에 전부 포함
Bitmap Index Scan	인덱스 TID bitmap → 힙 순차	중간 선택도, PostgreSQL

-- Seq Scan (small table or low selectivity)
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING';

-- Index Scan
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Index Only Scan (covering index)
EXPLAIN SELECT user_id, total FROM orders WHERE user_id = 42;

Seq Scan이 이기는 경우	Index Scan이 이기는 경우
테이블 수백 행 이하	높은 선택도 equality
status처럼 흔한 값 (50%+)	PK·UK lookup
인덱스 없음	ORDER BY indexed_col + LIMIT

• 인덱스가 있어도 대부분 행을 읽으면 Seq Scan + random heap이 더 비쌀 수 있다
• EXPLAIN (ANALYZE, BUFFERS) — shared hit/read로 캐시 vs 디스크 I/O 확인

예시 SQL 전체는 code/explain-examples.sql 참고.

3. Join 알고리즘

조인은 두 입력 릴레이션을 결합하는 내부 노드다.

알고리즘	동작	적합
Nested Loop	외부 행마다 내부 인덱스/스캔	작은 외부 + 내부 인덱스
Hash Join	작은 쪽 해시 테이블 빌드 → probe	중·대형, 등치 join, 메모리 허용
Merge Join	양쪽 정렬된 입력 merge	이미 정렬·인덱스 순서 스캔

EXPLAIN ANALYZE
SELECT o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';

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

패턴	흔한 플랜
small_table JOIN big_table ON fk + 인덱스	Nested Loop + Index Scan
두 테이블 모두 크고 등치 join	Hash Join
ORDER BY join_key + sorted index	Merge Join

• Nested Loop + Seq Scan on inner — 내부에 인덱스 없으면 N×M에 가깝게 폭발
• Hash Join은 work_mem 초과 시 디스크 spill — sudden slowdown

4. 비용 추정 개요

옵티마이저는 통계로 rows·cost를 추정한다.

입력	출처
reltuples	테이블 대략적 행 수
ndistinct	컬럼 고유값 수
히스토그램	값 분포 (MCV 등)
seq_page_cost / random_page_cost	I/O 가중치 (설정)

증상	원인 추정
rows 추정 100 vs actual 100000	통계 stale, skew, correlated cols
Index Scan → Seq Scan 전환	데이터 증가 후 ANALYZE 안 함
잘못된 join 순서	다중 컬럼 상관 미반영

ANALYZE orders;
ANALYZE users;

SELECT relname, last_analyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'users');

• PostgreSQL extended statistics (CREATE STATISTICS) — correlated column
• MySQL도 EXPLAIN + rows·filtered — 문법은 DB별로 다름

5. 플랜 읽는 순서 (실무)

1. 가장 바깥(루트) — Sort, Aggregate, Limit 등 최종 연산
2. Join 노드 — Hash / Nested Loop / Merge, 어느 쪽이 driving
3. Filter — Filter: vs Index Cond: — 인덱스 후 필터인지
4. Leaf Scan — Seq vs Index, 어느 테이블이 풀 스캔인지
5. ANALYZE — actual rows vs rows, 가장 큰 actual time 노드

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...

red flag	조치
큰 테이블 Seq Scan + 높은 actual rows	인덱스·조건·통계
Nested Loop + inner Seq Scan	inner FK 인덱스
Hash Join Batches: N>1`	work_mem·쿼리 분할
Index Only Scan인데 Heap Fetches 많음	vacuum·visibility map

6. 정리

• EXPLAIN은 실행 트리 — leaf가 I/O, 상위가 join·sort·aggregate
• Seq Scan은 작거나 비선택적, Index Scan은 선택적 키, Index Only는 커버링
• Nested Loop·Hash Join·Merge Join은 입력 크기·정렬·인덱스에 따라 선택
• cost·rows는 추정 — EXPLAIN ANALYZE로 검증, ANALYZE로 통계 유지
• 튜닝은 가장 비싼 노드부터 — 인덱스만 추가하는 게 전부는 아니다

다음에 다룰 것

• 트랜잭션과 ACID
• COMMIT, ROLLBACK, 격리와 지속성

---

해당 내용은 Database System Concepts, 7/E (Avraham Silberschatz, Henry F. Korth, S. Sudarshan) 의 내용을 기반으로 합니다.