시리즈 목차

• 1부: 기본기
• 2부: 실전 최적화
• 3부: 고급 주제 (현재 문서)

참고: 예제 스키마는 1부를 참조하세요.

1) CBO(Cost Based Optimizer)가 Cost를 계산하는 기준

🔎 CBO 핵심 개념

Postgres는 쿼리 실행 전에:

1. 여러 실행계획 후보 생성
2. 각 플랜의 비용(Cost) 계산
3. 가장 저렴한 플랜 선택

⚠️ 중요: Cost는 절대 시간이 아님. 상대 비교용. “플랜 A가 B보다 몇 배 빠르다” 정도로만 사용.

1-1. Cost 판단 기준

(1) 통계 정보 (Statistics)

역할

• ANALYZE로 수집한 테이블/컬럼 통계
• 행 개수, 데이터 분포, null 비율, 상관관계 등을 추정

문제

• 통계가 오래되면 → 잘못된 행 수 추정 → 잘못된 실행계획 선택

실무 팁

• 대용량 insert/update 후에는 수동으로 ANALYZE 실행
• autovacuum만 믿지 말 것

(2) 플래너 비용 파라미터 (GUC 설정)

Postgres가 CPU와 I/O 비용을 계산하는 기준값:

파라미터	기본값	설명	실무 팁
seq_page_cost	1.0	순차 페이지 읽기 비용	보통 변경 안 함
random_page_cost	4.0	랜덤 페이지 읽기 비용	SSD면 1.1~1.5로 낮춤
cpu_tuple_cost	0.01	튜플 처리 비용	보통 변경 안 함
cpu_index_tuple_cost	0.005	인덱스 튜플 처리 비용	보통 변경 안 함
effective_cache_size	4GB	OS 캐시 메모리 추정	RAM의 50~75%로 설정

실무 팁

• SSD 환경: random_page_cost = 1.1~1.5 (인덱스 스캔을 더 선호)
• effective_cache_size: 실제 RAM의 50~75% (커야 인덱스 플랜 선호)

(3) 행 개수 추정 (Cardinality)

계산식

• 단일 조건: 조건 선택도 × 총 행 수 = 예상 행 수
• 조인: (외부 행 수 × 내부 행 수) × 선택도

문제

• 추정이 틀리면 → 잘못된 조인 방법 선택 (예: 해시 조인 대신 중첩 루프)

실무 팁

• EXPLAIN (ANALYZE)로 예상 vs 실제 행 수 비교
• 오차가 크면 → ANALYZE 또는 CREATE STATISTICS 실행

1-2. Cost 계산 과정

각 플랜 노드별로 다음을 합산:

(읽을 페이지 수 × 페이지 비용)
+ (처리할 튜플 수 × CPU 비용)
+ (정렬/해시/병렬 등 추가 비용)
= Total Cost

가장 낮은 Total Cost를 가진 플랜 선택

1-3. 실무 적용 체크리스트 ✅

1. 통계 최신화
   • 대규모 DML 후 ANALYZE 실행
   • 쿼리 성능 급변 방지
2. 환경 반영한 비용 조정
   • SSD: random_page_cost = 1.1~1.5
   • effective_cache_size: 실제 RAM의 50~75%
3. 실행계획 검증 루틴
   • EXPLAIN (ANALYZE, BUFFERS)로 예상 vs 실제 rows 비교
   • 오차 크면 → CREATE STATISTICS 고려 (다중 컬럼 상관관계 등)
4. Prepared Statement 주의
   • 반복 쿼리는 PREPARE로 성능 향상
   • 단, 조건 값에 따라 rows 편차가 크면 → plan_cache_mode=force_custom_plan 고려

🔑 CBO 요약

CBO는 다음 3가지를 바탕으로 Cost 계산:

1. 통계 정보: 행 수, 분포, 상관관계
2. 비용 파라미터: I/O, CPU 비용 설정값
3. 선택도: 조건에 맞는 행 비율

실무 필수 설정:

• SSD 환경: random_page_cost 낮추기
• effective_cache_size 실제 메모리에 맞게 조정

검증 방법:

• EXPLAIN (ANALYZE)로 예상 vs 실제 비교
• Cost는 상대값일 뿐, 실제 시간(EXPLAIN ANALYZE)이 진실

2) 도구 활용

pg_stat_statements

용도: 상위 느린/빈번 쿼리 식별

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

활용: 어떤 쿼리가 전체 시간을 많이 쓰는지 파악

auto_explain

용도: 느린 쿼리 자동 실행계획 로깅

-- postgresql.conf 설정
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '200ms'  -- 200ms 이상 쿼리만 로깅
auto_explain.log_analyze = on
auto_explain.log_buffers = on

활용: 운영 환경에서 느린 쿼리 자동 감지

시각화 도구

• EXPLAIN.depesz.com: 실행계획을 웹에서 시각화
• Dalibo PEV: PostgreSQL Explain Visualizer
• 복잡한 실행계획을 트리 형태로 보기 쉽게 표시

3) 마무리: 루틴화 팁

성능 개선 프로세스

1. 후보 찾기: pg_stat_statements로 느린 쿼리 식별
2. 병목 확인: EXPLAIN (ANALYZE, BUFFERS)로 병목 노드 찾기
3. 개선 실행: 인덱스 → 쿼리 수정 → 통계 갱신 순으로 시도
4. 결과 기록: 전/후 수치(시간, Buffers, Rows) 비교 및 공유

---

이전: 2부: 실전 최적화
시리즈 시작: 1부: 기본기