PostgreSQL: EXPLAIN을 활용한 선택도 추정 정확도 분석

※ PostgreSQL: Analysis of Selectivity Estimation Accuracy Using EXPLAIN.

※ Version: PostgreSQL 17.

 

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

 

계속해서 이전 포스팅과 이어지는 내용입니다.

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

---

PostgreSQL 옵티마이저는 실행 계획 수립 시 조건절의 선택도(Selectivity)를 기반으로 예상 처리 행 수(rows), 비용(cost), 전략(Index Scan, Seq Scan 등)을 결정합니다.

이 추정값은 항상 정확하진 않으며, 통계의 신선도나 데이터 분포(skew)에 따라 실제 실행 결과와 오차가 발생할 수 있습니다.

---

1. EXPLAIN (ANALYZE)란?

• PostgreSQL이 쿼리를 어떻게 실행할지 계획(plan)을 보여주는 도구
• ANALYZE 옵션을 함께 주면 실제로 쿼리를 실행한 후, 예상과 실제 실행 결과를 함께 비교해 보여줍니다.

-- 예.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE city = 'Seoul';

 

• 주요 항목 설명

항목	설명
Plan	실제 사용된 실행 계획 노드 (Seq Scan, Index Scan 등)
Rows	옵티마이저가 예상한 결과 row 수
Actual Rows	실제 실행 결과에서 반환된 row 수
Loops	반복 횟수 (Nested Loop 등에서 중요)
Buffers	디스크 또는 캐시 페이지 접근 정보 (I/O 측정 가능)

---

2. 선택도 추정 정확도 평가 방법

• 옵티마이저의 선택도 추정이 정확한지 판단하려면 다음을 비교합니다.

예상 행 수 (rows) vs 실제 행 수 (actual rows)

 

• 기준 판단

기준	의미
예상값 ≒ 실제값	선택도 추정 정확
예상 ≪ 실제	선택도 과소 추정 (실제는 훨씬 많은 row 처리됨)
예상 ≫ 실제	선택도 과대 추정 (불필요하게 비싼 계획이 선택됐을 수 있음)

실행 전략 오류와 직결될 수 있으므로 반드시 주기적으로 점검해야 합니다.

---

3. 예제

3.1 테스트 데이터 생성

CREATE TABLE users (id SERIAL, city TEXT, age INT);

INSERT INTO users
SELECT i,
       CASE WHEN i % 100 = 0 THEN 'Seoul' ELSE 'Other' END,
       (RANDOM() * 100)::INT
FROM generate_series(1, 100000) i;

CREATE INDEX idx_users_city ON users(city);

ANALYZE users;

---

3.2 테스트 쿼리 실행

-- 1차 수행
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE city = 'Seoul';

--- QUERY PLAN ---
 Index Scan using idx_user_city on users  (cost=0.29..44.00 rows=950 width=14) (actual time=0.048..0.982 rows=1000 loops=1)
   Index Cond: (city = 'Seoul'::text)
   Buffers: shared hit=541 read=3
 Planning:
   Buffers: shared hit=15 read=1
 Planning Time: 0.332 ms
 Execution Time: 1.186 ms
(7 rows)
-- rows=950, actual rows=1000으로 거의 유사하므로, 선택도 추정 정확함
-- ±10% 이내면 높은 정확도


-- skew 데이터 추가
INSERT INTO users
SELECT 100001 + i, 'Seoul', (RANDOM() * 100)::INT
FROM generate_series(1, 10000) i;


-- 2차 수행
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE city = 'Seoul';

--- QUERY PLAN ---
 Index Scan using idx_user_city on users  (cost=0.30..151.05 rows=3307 width=14) (actual time=0.020..2.364 rows=13000 loops=1)
   Index Cond: (city = 'Seoul'::text)
   Buffers: shared hit=1154
 Planning Time: 0.066 ms
 Execution Time: 3.027 ms
(5 rows)
-- rows=3307, actual rows=13000으로 선택도 과소 추정
-- 실제보다 단순한 실행 계획이 선택되었을 수 있음


-- 통계 갱신
ANALYZE users;


-- 3차 수행
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE city = 'Seoul';

--- QUERY PLAN ---
 Index Scan using idx_user_city on users  (cost=0.30..348.40 rows=12328 width=14) (actual time=0.017..1.762 rows=13000 loops=1)
   Index Cond: (city = 'Seoul'::text)
   Buffers: shared hit=1154
 Planning Time: 0.057 ms
 Execution Time: 2.247 ms
(5 rows)
-- rows=12328, actual rows=13000으로 거의 유사

---

4. 선택도 오차 발생 원인

원인	설명
오래된 통계	데이터 변경 후 ANALYZE 미수행 시 발생
skew 분포	특정 값이 지나치게 많지만 히스토그램/MCV에 반영되지 않음
통계 수집 부족	default_statistics_target이 낮아 MCV/히스토그램 정밀도가 떨어짐
상관 컬럼 누락	다중 컬럼 상관관계가 CREATE STATISTICS로 반영되지 않음

---

5. 오차 보완 및 튜닝 방법

항목	조치
통계 갱신	ANALYZE, VACUUM ANALYZE 수행
통계 정밀도 향상	ALTER TABLE ... SET STATISTICS 500, default_statistics_target 증가
다중 컬럼 관계 반영	CREATE STATISTICS (dependencies) 활용
실행 계획 강제 적용	ENABLE_* 파라미터로 특정 계획 비활성화, pg_hint_plan 사용 (최후에 사용하시길 바랍니다.)

---

6. 요약 정리

개념	설명
EXPLAIN (ANALYZE)	실제 실행 결과와 옵티마이저 예상값을 비교 가능
핵심 비교 지표	예상 Rows vs Actual Rows
오차 의미	과소 추정 = 실행 계획 축소, 과대 추정 = 불필요한 계획 확장
원인 분석	오래된 통계, skew, 통계 목표값 낮음, 다중 컬럼 미반영 등
보완 방법	ANALYZE, 통계 설정 증가, CREATE STATISTICS 활용

---

14.1. Using EXPLAIN

 

오늘은 여기까지 ~