PostgreSQL EXPLAIN ANALYZE – 테오의 저장소

왜 EXPLAIN ANALYZE를 제대로 읽어야 하는가

PostgreSQL 쿼리 튜닝의 출발점은 실행 계획(Execution Plan)을 정확히 읽는 것이다. ORM이 생성한 SQL이 느릴 때, 인덱스를 추가했는데 여전히 Seq Scan이 뜰 때, 조인 순서가 예상과 다를 때—모두 EXPLAIN ANALYZE 출력을 해석해야 원인을 찾을 수 있다.

이 글은 PostgreSQL 16–18 공식 문서(14.1 Using EXPLAIN, SQL EXPLAIN)를 근거로, 실행 계획의 트리 구조·비용 모델·노드별 해석법·실무 판독 체크리스트를 정리한다.

1. EXPLAIN 옵션 정리

PostgreSQL은 다양한 EXPLAIN 옵션을 제공한다. 실무에서 가장 많이 쓰는 조합은 다음과 같다.

옵션	설명	기본값
`ANALYZE`	실제 실행 후 actual time·rows 표시	OFF
`BUFFERS`	shared/local/temp 블록 hit·read·dirtied·written	PG 18부터 ANALYZE 시 자동 ON
`VERBOSE`	출력 컬럼 목록, 스키마 한정 이름	OFF
`TIMING`	노드별 실행 시간(ms). 오버헤드 있으므로 row count만 볼 때 OFF	ON (ANALYZE 시)
`SETTINGS`	기본값과 다른 GUC 설정 표시	OFF
`WAL`	WAL 레코드 수·FPI·바이트 수 (DML 분석용)	OFF
`MEMORY`	플래너 메모리 사용량 (PG 17+)	OFF
`FORMAT`	TEXT / JSON / XML / YAML	TEXT

실무 권장 조합:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

DML을 분석할 때는 반드시 트랜잭션으로 감싸서 롤백한다:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

2. 실행 계획의 트리 구조와 비용 모델

EXPLAIN 출력은 트리(tree) 형태다. 최하위 노드(scan node)가 테이블에서 행을 읽고, 상위 노드가 조인·정렬·집계 등을 수행한다. 각 노드에 표시되는 숫자 네 가지를 정확히 이해해야 한다.

Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

항목	의미
`cost=0.00` (startup)	첫 번째 행을 반환하기까지 소요되는 추정 비용. Sort 노드에서 높게 나타난다.
`..445.00` (total)	모든 행을 반환할 때까지의 총 추정 비용. 상위 노드 비용은 하위 노드를 포함한다.
`rows=10000`	이 노드가 출력하는 추정 행 수 (스캔 행 수가 아님).
`width=244`	출력 행의 평균 바이트 폭.

비용 단위는 seq_page_cost = 1.0을 기준으로 한 상대값이다. 위 예시의 비용은 (345페이지 × 1.0) + (10000행 × 0.01) = 445로 산출된다.

ANALYZE를 붙이면 실측값이 추가된다:

Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)
                   (actual time=0.015..2.34 rows=10000 loops=1)

actual time은 밀리초 단위이며, loops가 1보다 크면(Nested Loop 내부 등) 시간과 행 수를 loops로 곱해야 실제 총량이 된다.

3. 스캔 노드 종류와 전환 기준

3-1. Seq Scan (순차 스캔)

테이블 전체를 처음부터 끝까지 읽는다. WHERE 조건이 있어도 모든 행을 방문한 뒤 필터링한다.

Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
  Filter: (unique1 < 7000)

선택되는 경우: 테이블이 작거나, 선택도(selectivity)가 높아서(대부분의 행을 읽어야 해서) 인덱스 사용 비용이 더 클 때.

3-2. Index Scan

인덱스를 탐색하고, 인덱스 순서대로 테이블 힙(heap)에서 행을 가져온다.

Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
  Index Cond: (unique1 = 42)

선택되는 경우: 소수의 행을 조회하거나, ORDER BY가 인덱스 순서와 일치할 때. 랜덤 I/O가 발생하므로 행 수가 늘면 비용이 급증한다.

3-3. Index Only Scan

인덱스만으로 쿼리를 충족할 수 있을 때(Covering Index) 힙 접근 없이 반환한다.

Index Only Scan using idx_orders_status on orders  (cost=0.29..4.30 rows=1 width=4)
  Index Cond: (status = 'active')
  Heap Fetches: 0

Heap Fetches가 높으면 Visibility Map이 갱신되지 않은 것이므로 VACUUM을 실행한다.

3-4. Bitmap Index Scan → Bitmap Heap Scan

2단계 스캔이다. 먼저 인덱스에서 조건에 맞는 행 위치를 비트맵으로 수집하고, 이를 물리 순서로 정렬한 뒤 힙에서 읽는다. 여러 인덱스를 BitmapAnd/BitmapOr로 결합할 수도 있다.

Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244)
  Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
  -> BitmapAnd  (cost=25.07..25.07 rows=10 width=0)
       -> Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
            Index Cond: (unique1 < 100)
       -> Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
            Index Cond: (unique2 > 9000)

선택되는 경우: 중간 선택도—Index Scan으로는 랜덤 I/O가 많고, Seq Scan으로는 불필요한 행이 너무 많을 때.

스캔 전환 요약

선택도	주로 선택되는 스캔	이유
매우 낮음 (<1–5%)	Index Scan / Index Only Scan	소수 행만 접근, 랜덤 I/O 적음
중간 (5–20%)	Bitmap Index + Heap Scan	비트맵으로 물리 정렬 후 순차 읽기로 랜덤 I/O 줄임
높음 (>20%)	Seq Scan + Filter	대부분 행을 읽으므로 순차 I/O가 효율적

⚠️ 이 비율은 절대적이지 않다. random_page_cost, effective_cache_size, 테이블 크기, 통계 정확도에 따라 달라진다.

4. 조인 노드: Nested Loop · Hash Join · Merge Join

4-1. Nested Loop

외부(outer) 테이블의 각 행마다 내부(inner) 테이블을 스캔한다. 내부 테이블에 인덱스가 있을 때 효율적이다.

Nested Loop  (cost=4.65..118.50 rows=10 width=488)
  -> Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
       Recheck Cond: (unique1 < 10)
       -> Bitmap Index Scan on tenk1_unique1  ...
  -> Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
       Index Cond: (unique2 = t1.unique2)

특징: 외부 행 수 × 내부 스캔 비용. 외부가 적을 때 빠르고, 많을 때 급격히 느려진다.

4-2. Hash Join

작은 쪽 테이블로 해시 테이블을 빌드하고, 큰 쪽을 스캔하면서 프로빙한다.

Hash Join  (cost=230.00..713.98 rows=101 width=488)
  Hash Cond: (t2.unique2 = t1.unique2)
  -> Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
  -> Hash  (cost=229.00..229.00 rows=101 width=244)
       -> Seq Scan on tenk1 t1  ...

특징: 빌드 비용(startup cost)이 크지만 프로빙은 O(1). work_mem에 해시 테이블이 들어가야 한다. 넘치면 디스크로 스필(spill)되어 급격히 느려진다.

4-3. Merge Join

양쪽 입력이 조인 키로 정렬되어 있을 때, 동시에 순회하며 매칭한다.

Merge Join  (cost=0.56..734.21 rows=10000 width=488)
  Merge Cond: (t1.unique1 = t2.unique1)
  -> Index Scan using tenk1_unique1 on tenk1 t1  ...
  -> Index Scan using tenk2_unique1 on tenk2 t2  ...

특징: 양쪽이 이미 정렬되어 있으면(인덱스 등) 매우 효율적. 정렬이 필요하면 Sort 노드가 추가되며 startup cost가 커진다.

조인 전략 선택 요약

조인	최적 상황	주의점
Nested Loop	외부 행 수가 적고 내부에 인덱스 있음	외부 행 증가 시 비용 선형 증가
Hash Join	한쪽이 work_mem에 들어갈 크기	메모리 부족 시 디스크 스필
Merge Join	양쪽이 이미 정렬(인덱스 순서 등)	정렬 비용이 크면 Hash Join에 밀림

5. 자주 등장하는 상위 노드

Sort / Incremental Sort

Sort는 전체 입력을 정렬한 뒤 출력한다. Sort Method: quicksort Memory: 25kB이면 메모리 내 정렬, external merge Disk: 10240kB이면 디스크 스필이다. PG 13+의 Incremental Sort는 선행 키가 이미 정렬된 경우 청크 단위로 정렬하여 LIMIT와 조합 시 효과적이다.

HashAggregate / GroupAggregate

HashAggregate는 해시 테이블로 그룹핑, GroupAggregate는 정렬된 입력 위에서 그룹핑한다. 그룹 수가 적으면 Hash가, 입력이 이미 정렬되어 있으면 Group이 유리하다.

Materialize

내부 노드 결과를 메모리(또는 디스크)에 캐싱하여 반복 접근 시 재스캔을 피한다. Nested Loop의 inner로 자주 나타난다.

Limit

상위에 Limit이 있으면 하위 노드의 total cost가 (원래 비용 × LIMIT/전체행)으로 보간된다. 이 때문에 Seq Scan + Limit이 Index Scan보다 저렴하게 평가되기도 한다.

6. BUFFERS 출력 해석

BUFFERS는 I/O 병목을 찾는 핵심 도구다.

Bitmap Heap Scan on orders  (cost=4.36..39.38 rows=10 width=244)
                             (actual time=0.12..0.15 rows=10 loops=1)
  Buffers: shared hit=12 read=3

항목	의미
`shared hit`	shared_buffers 캐시에서 찾은 블록 수
`shared read`	디스크(또는 OS 캐시)에서 읽은 블록 수
`shared dirtied`	이 쿼리가 더티(dirty)한 블록 수
`shared written`	이 쿼리 도중 eviction으로 쓴 블록 수
`temp read/written`	work_mem 초과 시 임시 파일 I/O (정렬·해시 스필)

핵심 지표: shared read가 크면 캐시 미스가 많다는 뜻이다. temp read/written이 나타나면 work_mem 증가를 검토한다. track_io_timing = on을 설정하면 I/O 시간(ms)도 표시된다.

7. 추정(estimated)과 실측(actual)의 차이 진단

플래너의 추정이 실측과 크게 다르면 차선의 계획이 선택된다. 대표적 원인과 대응:

증상	원인	대응
rows 추정 ≪ actual	통계 부정확, 상관 컬럼	`ANALYZE` 실행, `default_statistics_target` 상향, PG 14+ `CREATE STATISTICS` (MCV/ndistinct)
rows 추정 ≫ actual	함수·식 selectivity 과대평가	WHERE 조건을 단순화하거나 부분 인덱스 활용
loops가 매우 큼	Nested Loop의 외부 행 과소추정	통계 갱신, 또는 `SET enable_nestloop = off`로 Hash Join 유도 후 비교
Bitmap → Lossy	work_mem 부족으로 비트맵이 page 단위로 퇴화	`work_mem` 증가

8. 실무 판독 체크리스트

최상위 actual time 확인 — 쿼리 전체 실행 시간.
rows 추정 vs actual 비교 — 10배 이상 차이 나는 노드를 찾는다.
가장 비싼 노드 식별 — actual time이 가장 긴 노드에 집중한다.
Filter rows removed 확인 — Rows Removed by Filter: 99000이면 인덱스 추가 검토.
Buffers shared read 비율 — hit 대비 read가 높으면 캐시 부족 또는 테이블 bloat.
Sort/Hash 디스크 스필 — Sort Method: external merge 또는 temp read 발생 시 work_mem 조정.
loops 곱하기 — 노드별 시간·행 수에 loops를 곱해 실제 총량 계산.
Seq Scan + 큰 테이블 — 인덱스 누락인지, 통계 문제인지, 실제로 대부분의 행이 필요한지 구분.

9. GUC 파라미터로 플래너 힌트 주기

PostgreSQL은 인덱스 힌트를 지원하지 않지만, 비용 파라미터를 세션 단위로 조정하여 플래너를 유도할 수 있다.

파라미터	기본값	효과
`random_page_cost`	4.0	낮추면(예: 1.1~1.5) 인덱스 스캔 선호. SSD 환경에서 권장.
`effective_cache_size`	4GB	높이면 인덱스 스캔 비용을 낮게 평가. 실제 가용 메모리(shared_buffers + OS 캐시)로 설정.
`work_mem`	4MB	높이면 Hash Join·Sort가 메모리 내 수행. 커넥션 수 × work_mem이 RAM을 초과하지 않도록 주의.
`enable_nestloop` 등	on	진단 목적으로 특정 전략을 끄고 대안 계획 비교. 운영 환경에서 영구 설정 금지.

10. PostgreSQL 18 변경사항: BUFFERS 자동 포함

PostgreSQL 18(2025년 릴리즈)부터 EXPLAIN ANALYZE 실행 시 BUFFERS 옵션이 자동으로 활성화된다. 기존에는 EXPLAIN (ANALYZE, BUFFERS)로 명시해야 했지만, 18부터는 EXPLAIN ANALYZE만으로도 버퍼 정보를 얻을 수 있다. I/O 병목 진단이 기본 워크플로에 포함된 셈이다.

또한 MEMORY 옵션(PG 17에서 추가)으로 플래너 단계의 메모리 사용량도 확인할 수 있어, 복잡한 쿼리의 플래닝 비용까지 가시화할 수 있다.

마무리: EXPLAIN은 읽는 기술이다

EXPLAIN ANALYZE는 PostgreSQL이 제공하는 가장 강력한 진단 도구이지만, 출력을 정확히 해석하지 못하면 무용지물이다. 핵심을 정리하면:

cost는 상대적 단위이고, actual time이 실제 밀리초다.
rows 추정과 실측의 차이가 잘못된 계획의 근본 원인이다.
스캔 전환 기준은 선택도·random_page_cost·effective_cache_size의 함수다.
BUFFERS로 I/O 병목을, loops 곱하기로 Nested Loop 비용을 정확히 파악한다.
통계 갱신(ANALYZE)이 인덱스 추가보다 먼저다.

참고 문서: