PostgreSQL 쿼리 계획 노드별 해석

들어가며

쿼리 성능 문제를 해결하려면 실행계획을 읽을 줄 알아야 한다. 이 글은 postgres 쿼리 플랜 읽기와 explain 노드 해석 postgres, query plan 분석 postgres에 초점을 맞춰 초보자도 이해하기 쉽게 설명한다. 각 노드가 무엇을 의미하는지 차근차근 살펴본다.

EXPLAIN 출력의 기본 항목

EXPLAIN(ANALYZE, BUFFERS) 같은 명령으로 실제 실행 결과와 통계를 얻을 수 있다. 출력에서 자주 보이는 항목은 다음과 같다.

• cost: planner가 예측한 비용(시작 비용 .. 총 비용)
• rows: planner가 예측한 반환 행 수
• width: 한 행의 평균 바이트 크기
• actual time: 실제 실행에 걸린 시간(시작 .. 끝)
• loops: 노드가 반복 실행된 횟수

작동 원리 요약

플래너는 여러 계획을 비교해 비용이 최소인 것을 고른다. 실제 실행 결과는 예측과 다를 수 있다. 예측과 실제 차이를 통해 통계 문제나 인덱스 필요성을 파악한다.

주요 스캔 노드 해석

Seq Scan

테이블 전체를 순차적으로 읽는다. 작은 테이블이나 조건이 인덱스를 활용하기 어려울 때 선택된다. 비용 대비 실제 시간이 높다면 인덱스 생성이나 파티셔닝을 고려할 수 있다.

Index Scan

인덱스를 사용해 필요한 행만 접근한다. 선택도가 높을 때 유리하다. 실제 time과 loops를 확인해 인덱스 접근 비용을 판단한다.

Bitmap Heap Scan

비트맵 인덱스 접근 후 힙에서 한 번에 읽어오는 방식이다. 많은 행을 반환하지만 인덱스만으로는 비효율적일 때 쓰인다.

조인 노드 설명

Nested Loop

외부 루프의 각 행에 대해 내부 루프를 실행한다. 작은 외부 테이블과 인덱스가 있는 내부 테이블에서 효율적이다. 예상 rows와 actual rows 곱이 크면 비용이 급증한다.

Hash Join

작은 쪽 테이블을 해시 테이블로 만들어 큰 쪽을 탐색한다. 메모리 사용량과 해시 테이블 빌드 비용을 확인해야 한다.

Merge Join

양쪽 입력이 정렬되어 있을 때 효율적이다. 정렬 비용이 큰 경우에는 다른 조인 방식이 선택될 수 있다.

정렬과 집계 노드

SORT 노드는 종종 메모리 정렬(work_mem) 영향을 받는다. 집계(AGGREGATE)는 그룹 수와 집계 함수 특성에 따라 다른 전략을 쓴다. 정렬과 집계는 디스크 스필이 발생하면 성능 저하로 이어진다.

실전 예시

다음은 간단한 쿼리와 EXPLAIN ANALYZE 출력 예시이다.

EXPLAIN ANALYZE SELECT u.id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = true AND o.created_at > now() - interval '30 days';

예상 출력 일부는 다음과 같다.

Hash Join  (cost=... rows=... width=...) (actual time=... rows=... loops=...)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o
  ->  Hash  (cost=...)
        ->  Seq Scan on users u  (filter: active)

위에서 실제 time과 rows를 비교하면 planner 예측이 적절했는지 알 수 있다. 예측 rows가 실제보다 지나치게 작으면 통계 갱신이 필요하다.

읽는 순서와 점검 항목

실행계획을 해석할 때는 아래 순서를 권장한다.

• 최상위 노드에서 전체 비용과 실제 시간 확인
• 각 조인 노드의 rows와 loops 비교
• 예측 cost와 actual time의 차이 파악
• 인덱스 사용 여부와 정렬·스필 여부 점검

마무리

query plan 분석 postgres는 반복 학습이 필요하다. 다양한 쿼리를 EXPLAIN으로 확인하면서 노드별 특성을 체감하는 것이 중요하다. 통계 업데이트와 인덱스 설계는 성능 개선의 핵심이다.