※ PostgreSQL: Optimizer(Planner).
※ Version: PostgreSQL 16.
안녕하세요. 듀스트림입니다.
요즘 계속 튜닝의 연속인 나날을 보내고 있습니다.
그래서 작성하는 이번 포스팅은 PostgreSQL의 옵티마이저에 관한 내용입니다.
1. 옵티마이저 단계
① SQL Query
→ 사용자가 입력한 SQL 쿼리(SELECT, INSERT, UPDATE, DELETE 등)가 PostgreSQL로 전달됩니다.
② Parser (문법 분석)
→ SQL 구문을 파싱하여 내부적으로 “파스 트리(Parse Tree)”를 생성합니다.
→ 문법에 맞지 않으면 여기서 오류가 발생합니다.
③ Rewrite / Analyzer (Query Rewriter, Analyzer)
→ 뷰(View)나 규칙(Rule) 등이 적용되어야 할 경우, 파스 트리를 재작성(Rewrite)합니다.
→ Analyzer 단계에서는 시스템 카탈로그에서 테이블, 칼럼 정보 등을 참조하여 "쿼리 트리(Query Tree)"를 보강합니다.
④ Planner / Optimizer (계획 수립 및 최적화)
→ 생성된 쿼리 트리를 기반으로 다양한 실행 계획(Plan)을 만들어봅니다.
→ 각 Plan에 대해 I/O 비용, CPU 비용 등을 계산하여 총비용을 추정합니다(Cost-Based Optimizer).
→ 가장 비용이 낮은(가장 빠를 것으로 예측되는) 실행 계획을 최종적으로 선택합니다.
⑤ Executor (실행기)
→ 옵티마이저가 선정한 최적(또는 준최적) 실행 계획을 토대로 실제 연산을 수행합니다.
→ 결과 집합을 생성하여 클라이언트에게 반환합니다.
2. PostgreSQL 옵티마이저의 동작 원리
PostgreSQL의 옵티마이저는 Cost-Based Optimizer로서, 가능한 모든(대부분의) 실행 계획 후보를 생성한 뒤 각 Plan의 비용을 추정하여 그 중 최소 비용을 가지는 계획을 선택합니다.
이 때 비용은 "CPU 비용, I/O 비용, 메모리 사용량, 네트워크 전송 비용(분산 환경 시) 등" 다양한 요소를 포괄적으로 추정한 결과입니다.
+ 옵티마이저의 성능은 결국 통계 정보의 품질과 비용 계산 로직에 달려 있습니다.
2.1. 내부 비용(cost) 모델
PostgreSQL 옵티마이저는 각 연산 노드(Seq Scan, Index Scan, Join, Sort, Aggregate 등)에 대해 다음과 같은 비용 파라미터를 기반으로 추정 비용을 계산합니다.
• seq_page_cost(기본값 1.0)
→ 순차적으로 테이블 블록을 읽을 때, 페이지 1개를 읽는 비용.
• random_page_cost(기본값 4.0)
→ 무작위(Random) I/O로 페이지 1개를 읽는 비용.
→ 일반적으로 HDD 환경에서 순차 I/O 대비 랜덤 I/O가 훨씬 비싸다고 가정.
• cpu_tuple_cost(기본값 0.01), cpu_index_tuple_cost(기본값 0.005), cpu_operator_cost(기본값 0.0025) 등
→ 튜플을 처리/연산하는 데 드는 CPU 비용을 추정하는 파라미터.
예를 들어 Index Scan을 수행할 경우, 옵티마이저는 "인덱스 탐색 비용 + 무작위 페이지 접근 비용 + CPU 비용"을 합산하여 최종 비용을 추정합니다.
이러한 추정은 get_index_path_cost(), cost_index() 등의 내부 함수에서 수행되며, (인덱스 선택도 × random_page_cost) + (반복되는 CPU 연산 수 × cpu_index_tuple_cost) 형태의 식으로 대략 계산됩니다.
2.2. 통계 정보(Statistics)와 Extended Statistics
• pg_statistic 테이블에 저장된 히스토그램, MCV(Most Common Values), NDV(중복도), 상관관계(correlation) 등을 활용합니다.
• PostgreSQL은 Extended Statistics(CREATE STATISTICS) 기능을 지원해, 다중 컬럼 간의 상관관계나 expression 통계 등을 등록할 수 있습니다. (10버전 이상 지원)
→ 예: (colA, colB)가 밀접하게 연관되어 있으면 일반적인 독립 가정보다 훨씬 정확한 selectivity 추정이 가능.
정확한 통계가 없으면 옵티마이저는 잘못된 selectivity(선택도)로 인해 비용을 오판하고, 비효율적 플랜을 선택할 수 있으므로, 주기적 ANALYZE와 Extended Statistics 활용이 매우 중요합니다.
2.3. 동적 프로그래밍(DP)과 GEQO(Genetic Query Optimizer)
• PostgreSQL은 12개 테이블 이하 조인 시, 동적 프로그래밍(DP) 알고리즘을 사용해 모든 조인 순서를 탐색(또는 상당 부분)하여 최적의 순서를 찾습니다.
• 12개 테이블 초과 시, GEQO(유전 알고리즘)로 전환하여, 무수히 많은 조인 순서를 전수 탐색하기보다는 확률적 방법으로 근사 해를 구합니다.
→ 이는 조인 개수가 많아질수록 조인 순서 경우의 수가 팽창(n! 수준)하기 때문입니다.
3. 옵티마이저가 선택할 수 있는 주요 쿼리 플랜과 선택 이유
PostgreSQL의 Planner는 아래와 같은 다양한 플랜 노드를 조합해 최적의 트리를 만듭니다.
각각 비용 산정 시 I/O, CPU, 통계 정보, 병렬화 가능성 등을 종합 고려합니다.
3.1. 스캔(Scan) 연산
• Sequential Scan
→ 테이블 전 블록을 순차적으로 스캔.
→ 행 반환율이 높거나(SELECTIVITY가 높음), 작은 테이블(테이블 자체가 작아 랜덤 I/O 비용 < 전체를 그냥 읽는 비용)인 경우 선호.
→ 병렬로 수행(Parallel Seq Scan) 가능.
• Index Scan
→ 인덱스로부터 TID(페이지+오프셋)를 얻어, 테이블 블록을 랜덤 접근.
→ 선택도가 낮고, 인덱스 컬럼에 잘 맞는 조건(=, <, >, BETWEEN 등)이 있을 때 유리.
→ 랜덤 I/O 비중이 높아 seq_page_cost, random_page_cost 비율에 민감.
• Index Only Scan
→ 인덱스 자체만으로 필요한 컬럼을 모두 충족시킬 수 있는 경우(커버링 인덱스).
→ Visibility map에서 “모든 튜플이 MVCC 관점에서 모두 유효”함을 빠르게 확인 가능할 때 테이블 접근 생략 → I/O 횟수 대폭 감소.
• Bitmap Index Scan + Bitmap Heap Scan
→ 여러 TID를 비트맵으로 모아 batch I/O로 테이블을 읽어 무작위 접근 비용을 줄임.
→ 대량의 레코드를 인덱스로 찾을 때 Index Scan보다 유리.
→ 병렬화 시 Parallel Bitmap Heap Scan(작업자들이 비트맵을 공유) 가능.
• TID Scan
→ 특정 TID를 직접 지정해 액세스할 때 사용.
→ 잘 쓰이지는 않으나, UPDATE/DELETE의 내부 처리에서 특정 레코드를 직접 참조해야 할 때 제한적으로 활용.
3.2. 조인(Join) 연산
• Nested Loop Join
→ 한쪽 테이블의 각 행(외부 루프)마다, 다른 테이블(내부 루프)을 반복적으로 검색.
→ 내부 루프에서 Index Scan을 잘 활용할 수 있으면, 소량의 결과에 매우 빠름.
→ 대규모 조인에선 오버헤드가 클 수 있어 부적합. (제한적 병렬화)
→ 소량 데이터 조인이나 인덱스 기반 조인에 적합. (OLTP 환경에서 주로 사용)
• Merge Join
→ 두 입력을 조인 키로 정렬해둔 상태에서 병합.
→ 범위 조인(ON a.col BETWEEN b.x AND b.y 등)이나 이미 정렬된 서브쿼리가 있을 때 강력.
→ 정렬 비용이 발생하나, 정렬되어 있으면 추가 비용이 거의 없음.
→ 병렬 Merge Join은 Gather Merge 노드와 결합해 특정 상황에서 가능.
• Hash Join
→ 한 입력(작은 쪽)을 해시 테이블로 만들고, 다른 입력을 스캔하며 매칭.
→ 대용량 조인에서 효율적이며, 정렬 없이 동작. (양쪽 테이블이 정렬되어 있을 때 유리, 정렬 비용이 낮은 경우 추천)
→ 병렬 Hash Join을 사용할 경우, 여러 워커가 해시 테이블 빌드를 분담하거나 공유 메모리에 해시 테이블을 구성.
→ 해시 테이블이 work_mem을 초과하면 디스크 스필(Spill) 발생 → 성능 저하. (충분한 work_mem 확보가 필요함)
3.3. 기타 연산: Sort, Aggregate, WindowAgg 등
• Sort / Incremental Sort
→ 정렬 비용은 O(N log N)이 기본. Incremental Sort 최적화로 부분 정렬 기회가 있으면 전체 비용 절약.
• Aggregate
→ HashAggregate(메모리 내 해시), GroupAggregate(정렬 기반), Parallel Aggregate 등.
• WindowAgg
→ OLAP 윈도우 함수 처리를 위한 노드. 쿼리 플랜에서 정렬이 중요.
4. 병렬 처리(Parallel Execution)
• Gather / Gather Merge 노드
→ 병렬 작업(Parallel Worker)에서 생성된 partial plan의 결과를 모아서(혹은 정렬 상태 유지해서) 최종 출력.
→ Merge가 필요한 경우(정렬 순서를 유지하거나 그룹화가 필요한 경우)에는 Gather Merge 사용.
• Parallel Bitmap Heap Scan
→ Shared memory 상에 비트맵 정보를 구성하고, 여러 워커가 동시에 테이블 블록을 읽어 필터링.
→ 큰 테이블에서 Bitmap Index Scan이 병렬화되면 매우 빠를 수 있음.
• Parallel Hash Join
→ 병렬 워커들이 해시 테이블 빌드를 분담하거나, 빌드가 끝난 해시 테이블을 공유해 probing.
→ work_mem 크기, 병렬 워커 수 등에 따라 성능이 좌우됨.
• 주의사항
→ parallel_setup_cost, parallel_tuple_cost 등 파라미터로 병렬 설정을 세밀하게 조정 가능.
→ 병렬화로 인해 CPU 사용량이 급증할 수 있고, OOM(Out Of Memory) 위험이나 context switching 비용도 발생.
→ DB 서버에 코어가 많고 쿼리 패턴이 대용량 스캔/조인 위주일 때 유리.
5. 테이블 파티셔닝과 옵티마이저
PostgreSQL에서 테이블 파티셔닝은 Range, List, Hash 파티셔닝을 지원합니다.
파티션 테이블은 각각 독립된 물리 테이블로 취급되며, 옵티마이저는 Partition Pruning을 통해 "실제로 필요한 파티션만" 스캔하려고 시도합니다.
• 동적 파티션 프루닝
→ 실행 시점(런타임)에 WHERE 절 파라미터가 결정되어, 불필요한 파티션을 건너뛰는 기능.
• 파티션 인덱스
→ 각 파티션에 인덱스를 따로 생성해야 하며, 글로벌 인덱스는 현재까지 공식 지원되지 않습니다. 이로 인해 파티션 키를 통한 효율적인 검색은 가능하나, 파티션 간 중복값에 대한 전역 유니크 인덱스 등은 구현이 어려움.
6. 파라미터 설정 및 통계 활용
• enable_seqscan, enable_indexscan, enable_bitmapscan, enable_hashjoin 등
→ 세션/시스템 레벨에서 특정 스캔/조인 방식을 강제로 비활성화해, 옵티마이저가 다른 플랜을 선택하도록 유도 가능(일시적 튜닝 또는 디버깅용).
(예: SET enable_seqscan = OFF; → 강제로 인덱스 스캔을 유도해 테스트.)
→ 실제 운영환경에서는 위험할 수 있으나, 특정 케이스에서 옵티마이저가 잘못된 선택을 하는지 확인할 때 유용.
• work_mem
→ 정렬/해시 조인 시 각 워커(쿼리/쓰레드)마다 소비할 수 있는 메모리 크기.
→ 너무 작으면 디스크 스필(Spill), 너무 크면 전체 시스템 메모리 부족(OOM) 가능.
• effective_cache_size
→ OS 파일시스템 캐시까지 고려한 시스템 메모리 추정치.
→ 높은 값이면 옵티마이저가 “메모리 캐시에 의해 I/O 비용이 더 낮을 수 있다”고 판단해 Index Scan, Hash Join 등을 적극 고려.
• CREATE STATISTICS
→ 다중 컬럼의 상관관계를 등록해 옵티마이저가 보다 정확한 선택도를 산정.
(예: CREATE STATISTICS s1 (dependencies) ON colA, colB FROM mytable; ANALYZE mytable;)
• auto_explain
→ 쿼리 실행 시점에 자동으로 실제 실행 계획을 로그로 남겨, 옵티마이저가 선택한 플랜과 실제 수행 시간이 얼마나 일치하는지 진단.
• pg_stat_statements
→ 쿼리 빈도, 평균 시간 등을 분석해, 자주 호출되지만 느린 쿼리를 선별하여 집중 튜닝할 수 있음.
작성하다 보니 길어졌네요.
쿼리의 특성과 데이터 분포를 면밀히 분석하여 1. 적절한 인덱스 설계, 2. 효과적인 파티셔닝, 3. 튜닝된 파라미터 설정, 4. 지속적인 모니터링 및 통계 관리를 실천하면, PostgreSQL 옵티마이저의 성능을 최대한 끌어낼 수 있을 것입니다.
도움이 되셨길 바라며, 오늘 포스팅은 여기까지입니다.
다음 포스팅도 기대해주세요-!
'PostgreSQL' 카테고리의 다른 글
| PostgreSQL: 인덱스 (0) | 2025.01.28 |
|---|---|
| PostgreSQL: VACUUM index_cleanup 옵션과 REINDEX의 차이 (0) | 2025.01.17 |
| PostgreSQL: 바인드 변수 실행 계획 최적화 (0) | 2025.01.10 |
| PostgreSQL: 락 체크 쿼리 (0) | 2025.01.07 |
| PostgreSQL: PG ↔ Oracle 이관 시 메타데이터 정합성 체크 쿼리 (0) | 2025.01.05 |