PostgreSQL: 선택도 기반 실행 계획 수립 원리

※ PostgreSQL: The Principle of Execution Plan Generation Based on Selectivity.

※ Version: PostgreSQL 17.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 이전 포스팅과 이어지는 내용입니다.

PostgreSQL: 동등 조건과 범위 조건의 선택도 추정 방식 분석

 

아래 포스팅도 참고하시면 좋을 거 같습니다.

PostgreSQL: Optimizer(Planner)

 

이번 포스팅에서는 중요 내용을 다시 한번 짚어가면서, 선택도 기반의 실행 계획 수립 원리를 알아보겠습니다.

---

PostgreSQL에서 실행 계획은 단순히 쿼리 문장을 해석하는 것이 아니라, 각 조건의 선택도(Selectivity) 와 이를 기반으로 계산한 비용(Cost) 을 평가한 후, 가장 효율적인 실행 전략을 결정하는 과정입니다.

---

1. 선택도란?

• 특정 조건을 만족하는 행의 비율
• 0.0 (전혀 없음) ~ 1.0 (전체 포함) 사이의 값

selectivity = 조건을 만족하는 예상 행 수 / 전체 행 수

---

2. 선택도의 중요성

PostgreSQL 옵티마이저는 모든 실행 전략마다 비용(cost)을 계산하고, 그중 가장 비용이 낮은 실행 계획을 선택합니다.
이때 비용 계산의 핵심 입력값이 선택도입니다.

선택도 → 예상 결과 행 수 → 비용 계산 → 실행 계획 결정

즉, 선택도를 잘못 추정하면 실행 계획 전체가 틀어지고, 성능 저하로 직결됩니다.

---

3. 실행 계획 수립 순서

PostgreSQL 옵티마이저는 아래 순서로 실행 계획을 결정합니다.

1. 가능한 스캔 경로 생성: 각 테이블에 대해 가능한 스캔 방법(시퀀스 스캔, 인덱스 스캔 등)을 생성합니다.
2. 선택도 추정: 각 조건절에 대해 선택도를 추정합니다.
3. 비용 계산: 선택도와 통계 정보를 기반으로 각 경로의 비용을 계산합니다.
4. 최적 실행 계획 선택: 계산된 비용을 비교하여 가장 낮은 비용의 경로를 선택합니다.

50.5. Planner/Optimizer

---

4. 선택도 판단 기준

이 기준은 실전 튜닝 경험과 내부 cost 계산 로직에서 관찰된 값을 기반으로 작성하였습니다. (공식 문서에는 존재하지 않습니다.)

선택도 수치	의미	실행 전략 영향
0.0 ~ 0.01	매우 희소	Index Scan, Bitmap Scan 유리
0.01 ~ 0.1	희소	전략 유동적 (상황에 따라 다름)
0.1 이상	조건을 많이 만족	Seq Scan, Hash Join, Merge Join 유리

---

5. 비용 계산에 영향을 주는 핵심 파라미터들

PostgreSQL의 비용 모델은 다음 파라미터들에 의해 크게 좌우됩니다. (postgresql.conf 또는 세션 단위에서 조정 가능)

파라미터	기본값	설명
seq_page_cost	1	순차 디스크 페이지 접근 비용 (낮을수록 Seq Scan 선호)
random_page_cost	4	랜덤 디스크 페이지 접근 비용 (높을수록 Index Scan 불리)
cpu_tuple_cost	0.01	튜플(row) 하나를 처리하는 비용
cpu_index_tuple_cost	0.005	인덱스에서 튜플을 하나 읽을 때 비용
cpu_operator_cost	0.0025	연산자(비교 등) 하나를 평가할 때의 비용
parallel_tuple_cost	0.1	병렬 worker가 튜플을 처리할 때 비용
parallel_setup_cost	1000	병렬 쿼리를 준비하는 초기 고정 비용
effective_cache_size	4GB	OS 레벨 캐시를 통해 재사용 가능한 메모리 추정값

이 값들은 옵티마이저가 비용을 계산할 때 가중치로 곱해지는 값이며, 시스템 특성에 따라 적절히 조정해주면 실행 계획 품질이 크게 향상됩니다.

 

상세 내용은 아래 포스팅 참고 부탁드립니다.

PostgreSQL: Query Planning(Optimizer) 파라미터

---

6. 선택도에 따라 달라지는 실행 전략

전략 유형	선택도 낮을 때	선택도 높을
Index Scan / Bitmap Scan	✅ 효율적	과도한 I/O 발생
Seq Scan	전체 스캔 불필요	✅ 연속 I/O 유리
Nested Loop Join	✅ inner row 소수일 때 유리	반복 비용 높음
Hash Join	소규모에선 비효율	✅ 중대형 Join에 적합
Merge Join	✅ 정렬되어 있고 균형 잡힌 경우	작은 대상엔 정렬 비용 과함

---

7. 선택도 추정 공식 요약

7.1 동등 조건 (=)

- 조건값이 MCV에 있으면: selectivity = freq(value)
- 조건값이 MCV에 없으면:
  selectivity = (1 - sum(mcv_freqs)) / (n_distinct - mcv_count)

---

7.2 범위 조건 (<, >, BETWEEN)

- 히스토그램 기반 계산:
  selectivity = (i + f) / (n - 1)

  where:
  - n = histogram_bounds 개수
  - i = 조건을 만족하는 전체 구간 수
  - f = 마지막 구간에서의 부분 포함 비율

---

7.3 복합 조건 (AND 연결)

기본 추정: selectivity = sel(cond1) × sel(cond2)

컬럼 간 상관관계가 있는 경우 CREATE STATISTICS (dependencies)로 개선 필요

---

8. 선택도에 따른 실행 계획 예시

8.1 예제 1: 선택도가 낮은 경우

SELECT * FROM users WHERE city = 'Seoul';
-- 선택도 = 0.01 → 인덱스 스캔 유리

Bitmap Index Scan on idx_city
→ Bitmap Heap Scan on users

---

8.2 예제 2: 선택도가 높은 경우

SELECT * FROM users WHERE age > 0;
-- 선택도 = 0.95 → 전체 테이블 읽는 게 더 효율적

Seq Scan on users

---

9. 선택도 오판 시나리오

실제 선택도	옵티마이저 오판	결과
낮음 (0.01)	높게 추정	Seq Scan 발생 (풀스캔)
높음 (0.8)	낮게 추정	Nested Loop 발생 (반복 과다)

---

10. 요약 정리

개념	설명
선택도	조건을 만족하는 행의 비율 (0.0~1.0)
목적	실행 계획 비용 계산의 핵심 입력값
낮은 선택도	인덱스, Bitmap, Nested Loop 선호
높은 선택도	Seq Scan, Hash Join, Merge Join 선호
판단 기준	일반적으로 0.01 미만이면 낮음으로 간주
오판 시 문제	실행 계획 오류로 성능 저하 가능
비용 파라미터	random_page_cost, cpu_tuple_cost 등은 핵심 요소
보완 방법	ANALYZE, 통계 설정 조정, CREATE STATISTICS 활용

---

이번 포스팅은 여기까지입니다.

다음 포스팅에서는 EXPLAIN (ANALYZE)를 통해 선택도 추정과 실제 실행 결과를 비교하는 방법을 작성해 보겠습니다.