PostgreSQL: Optimizer(Planner)

※ PostgreSQL: Optimizer(Planner).
※ Version: PostgreSQL 16.
 
안녕하세요. 듀스트림입니다.
 
요즘 계속 튜닝의 연속인 날들 보내고 있습니다.
 
그래서 작성하는 이번 포스팅은 PostgreSQL의 옵티마이저에 관한 내용입니다.

---

1. 옵티마이저 단계

[그림 1] 옵티마이저 흐름도

① SQL 입력

• 클라이언트가 입력한 SQL 문이 PostgreSQL에 전달됩니다.

② 파서(Parser)

• 구문 분석: 입력된 SQL 문을 구문 분석하여 파스 트리(Parse Tree)를 생성합니다.
• 문법 검사: SQL 문이 문법적으로 올바른지 확인하며, 오류가 있을 경우 이 단계에서 에러를 반환합니다.

③ 애널라이저(Analyzer)

• 의미 분석: 재작성된 파스 트리를 기반으로 테이블, 칼럼, 데이터 타입 등의 정보를 시스템 카탈로그에서 조회하여 쿼리 트리(Query Tree)를 생성합니다.
• 이름 해석: 테이블과 칼럼 이름을 실제 객체로 해석합니다.

④ 리라이터(Rewriter)

• 규칙 적용: Query Tree에 정의된 뷰(VIEW)나 규칙(RULE)을 찾아 적용하고 서브쿼리 및 RULE 변형을 수행합니다. 그 후 변경된 트리를 반환합니다.
  (예. 뷰에 대한 쿼리는 기본 테이블을 참조하는 쿼리로 변환됩니다.)

⑤ 플래닝(Planner)

• 실행 계획 생성: 쿼리 트리를 기반으로 다양한 실행 계획을 생성합니다.
• 비용 계산: 각 실행 계획에 대해 I/O, CPU 등의 비용을 추정합니다.
• 최적 계획 선택: 추정된 비용을 비교하여 가장 낮은 비용의 실행 계획을 선택합니다.

⑥ 실행기(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 선택도(Selectivity) - 통계 정보(Statistics)와 Extended Statistics

• pg_statistic 테이블에 저장된 히스토그램, MCV(Most Common Values), NDV(중복도), 상관관계(correlation) 등을 활용합니다.

 

• 선택도(Selectivity)추정 방법

→ 동등 조건(Equality Conditions): 가장 일반적인 값(Most Common Values, MCV)을 기반으로 선택도를 추정합니다.

→ 범위 조건(Range Conditions): 히스토그램을 활용하여 조건에 해당하는 값의 비율을 추정합니다.

 

• 비용 계산에의 영향

→ 낮은 선택도: 조건을 만족하는 행이 적을 경우, 인덱스 스캔이 유리하다고 판단됩니다.

→ 높은 선택도: 조건을 만족하는 행이 많을 경우, 순차 스캔이 유리하다고 판단됩니다.

• 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) 가능.

Total Cost = (seq_page_cost × 디스크 페이지 수) + ((cpu_tuple_cost + cpu_operator_cost) × 튜플 수)

• seq_page_cost: 순차 페이지 읽기 비용 (기본값: 1.0)
• cpu_tuple_cost: 튜플 처리 비용 (기본값: 0.01)
• cpu_operator_cost: 연산자 평가 비용 (기본값: 0.0025)

 

• Index Scan
→ 인덱스를 통해 조건에 맞는 튜플의 위치(TID(페이지+오프셋))를 찾고, 해당 튜플을 테이블에서 랜덤 접근하여 읽음.
→ 선택도가 낮고, 인덱스 컬럼에 잘 맞는 조건(=, <, >, BETWEEN 등)이 있을 때 유리.
→ 랜덤 I/O 비중이 높아 seq_page_cost, random_page_cost 비율에 민감.

Total Cost = indexStartupCost + indexTotalCost + (cpu_tuple_cost × 반환 튜플 수)

--인덱스 스캔의 비용을 추정하기 위해 amcostestimate 함수를 사용하며, 이 함수는 인덱스 접근 방법에 따라 비용을 계산

• indexStartupCost: 인덱스 스캔을 시작하는 데 필요한 비용
• indexTotalCost: 인덱스 스캔 전체에 대한 비용
• cpu_tuple_cost: 튜플 처리 비용

• Index Only Scan
→ 인덱스 자체만으로 필요한 컬럼을 모두 충족시킬 수 있는 경우(커버링 인덱스).
→ Visibility map에서 모든 튜플이 MVCC 관점에서 모두 유효함(all-visible)을 빠르게 확인 가능할 때 테이블 접근 생략 → I/O 횟수 대폭 감소.
 
• Bitmap Index Scan
→ 인덱스를 스캔하여 조건에 맞는 튜플의 위치(TID)를 비트맵으로 수집하고, 이를 정렬하여 테이블을 Batch I/O로 읽어 무작위 접근 비용을 줄임.

→ 비트맵 생성: 조건에 맞는 행의 위치를 표시하기 위해 비트맵을 생성합니다. 각 행에 대해 인덱스를 스캔하면서 해당 행이 조건을 만족하는지 여부를 비트로 표시

→ Heap 참조: 비트맵에서 확인된 위치를 기반으로 테이블에서 실제 데이터를 참조. 이 과정에서 인덱스가 직접적으로 테이블 데이터로 가는 것이 아니라, 비트맵을 통해 해당 행들의 위치를 알아내고 테이블 자체에서 데이터를 추출.

→ 선택도가 중간 정도일 때 또는 여러 인덱스를 결합하여 조건을 만족시킬 때 사용.
→ 대량의 레코드를 인덱스로 찾을 때 Index Scan보다 유리.
 

• Bitmap Heap Scan

→ Bitmap Heap Scan은 Bitmap Index Scan과 달리 테이블 데이터에 직접적으로 접근하는 방법.

→ 비트맵 검색 결과 사용: Bitmap Heap Scan은 Bitmap Index Scan으로 생성된 비트맵을 기반으로 테이블에서 데이터를 찾음.

→ 테이블 데이터 접근: 실제로 데이터를 찾기 위해 테이블의 힙(heap)을 스캔하고, 비트맵에 표시된 위치에 해당하는 행들을 추출.

→ 힙 스캔 및 필터링: 힙 스캔 과정에서 실제 데이터를 읽으며, 필요한 조건을 만족하는 행을 찾아냅니다. 이때 필터링 조건이 추가적으로 적용될 수 있음.

→ 비트맵을 사용하여 실제 데이터에 접근하므로 I/O 비용이 크게 줄어듦.

→ 큰 범위의 데이터를 검색할 때 유용.

→ 병렬화 시 Parallel Bitmap Heap Scan(비트맵 공유) 가능.

• TID Scan
→ 특정 TID를 직접 지정해 액세스할 때 사용. (특정 튜플의 TID(페이지 번호와 오프셋)를 직접 지정하여 해당 튜플을 읽음)
→ 잘 쓰이지는 않으나, UPDATE/DELETE의 내부 처리에서 특정 레코드를 직접 참조해야 할 때 제한적으로 활용. (ctid를 명시적으로 지정하는 쿼리나 내부적으로 특정 튜플을 직접 참조해야 하는 경우에 사용)

---

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 확보가 필요함)

 

조인 방식	요약 설명	특징
Nested Loop Join	왼쪽 테이블의 각 행마다 오른쪽 테이블을 반복 탐색	소규모 테이블 또는 고선택도 필터일 때 유리
Merge Join	정렬된 양쪽 테이블을 병합	조인 키가 정렬되어 있거나 인덱스를 활용 가능한 경우
Hash Join	한 테이블을 메모리에 해시 테이블로 만들고 다른 테이블을 스캔하며 조인	큰 테이블 간 조인, 중간 수준의 선택도 이상일 때 사용

---

3.3 기타 연산: Sort, Aggregate, WindowAgg 등

• Sort / Incremental Sort
→ 정렬 비용은 O(N log N)이 기본. Incremental Sort 최적화로 부분 정렬 기회가 있으면 전체 비용 절약.

Total Cost = startup_cost + run_cost

→ 정렬 비용은 디스크 I/O를 유발할 수 있기 때문에 work_mem이 중요한 변수로 작동.

→ 정렬에 필요한 메모리가 work_mem 초과 → 디스크 정렬 발생 → 비용 증가 (work_mem을 충분히 확보하면 run_cost를 크게 줄일 수 있음)
 
• Aggregate
→ HashAggregate(메모리 내 해시), GroupAggregate(정렬 기반), Parallel Aggregate 등.
 
• WindowAgg
→ OLAP 윈도우 함수 처리를 위한 노드. 쿼리 플랜에서 정렬이 중요.

---

4. 병렬 처리(Parallel Execution)

• Gather

→ 병렬 워커에서 생성된 튜플을 수집하여 최종 출력.

→ 워커 + 메인 프로세스가 함께 데이터를 처리.

→ 정렬 순서 보장 안 함.

 

• Gather Merge

→ ORDER BY, GROUP BY 등 정렬 순서를 보장해야 할 경우 사용.

→ 병렬 워커의 정렬된 결과를 병합(Merge Sort) 하여 출력.

→ 정렬이 중요한 쿼리에서 Gather 대신 사용됨.

---

4.1 Scan 계열

• Parallel Seq Scan

→ 테이블을 병렬로 나누어 Sequential Scan 수행.

→ 데이터 범위는 워커 간에 block 단위로 자동 분할.

 

• Parallel Index Scan

→ B-Tree 인덱스를 병렬로 탐색.

→ 병렬 탐색이 가능하도록 범위를 분할 가능할 때 적용.

 

• Parallel Bitmap Heap Scan

→ 공유 메모리로 구성한 Bitmap을 병렬 워커들이 참조하여 동시에 힙 페이지를 읽음.

→ 큰 테이블에서 Bitmap Index Scan이 병렬화되면 매우 빠를 수 있음.

---

4.2 Join 계열

• Parallel Hash Join

→ 병렬 워커들이 해시 테이블 빌드를 분담하거나, 빌드가 끝난 해시 테이블을 공유해 probing.

→ work_mem 크기, 병렬 워커 수 등에 따라 성능이 좌우됨.

 

• Parallel Nested Loop Join (간접적으로 가능)

→ PostgreSQL은 직접적으로는 "Parallel Nested Loop Join" 노드를 갖고 있지 않지만,

→ Nested Loop Join의 inner side에서 병렬 스캔이 가능하여 유사한 병렬 효과를 낼 수 있음.

• Outer Table(Driving Table): 병렬 스캔이 가능한 테이블이어야 하며, 이를 통해 병렬 처리가 가능.
• Inner Table(Driven Table): 항상 비병렬 계획으로 실행 (병렬 워커 내에서 실행) . 즉, Inner Table은 병렬로 처리되지 않으며, 모든 병렬 워커가 동일한 Inner Table 을 개별적으로 처리.

---

4.3 Aggregate 계열

• Partial Aggregate

→ 각 워커가 자신이 처리한 데이터에 대해 먼저 부분 집계를 수행함.

 

• Finalize Aggregate

→ Gather 또는 Gather Merge 이후, 각 워커의 부분 집계 결과를 합산하여 최종 결과 생성.

---

4.4 기타 노드

• Parallel Append

→ 여러 하위 노드를 병렬로 처리. (여러 파티션을 동시에 병렬 스캔)

 

• Parallel Foreign Scan

→ FDW(Foreign Data Wrapper)가 병렬 스캔을 지원할 경우 사용 가능 (postgres_fdw의 경우).

• async_capable 옵션
  • 이 옵션을 true로 설정하면, 외부 테이블을 비동기적으로 병렬 스캔할 수 있음. (기본값은 false)
  • 이 설정은 외부 테이블 또는 외부 서버 수준에서 지정할 수 있으며, 테이블 수준의 설정이 서버 수준의 설정을 재정의.

---

4.5 병렬 실행 계획 노드 표

범주	병렬 노드 이름	설명
수집	Gather, Gather Merge	병렬 워커 결과 수집 및 병합
스캔	Parallel Seq Scan	병렬 순차 스캔
	Parallel Index Scan	병렬 인덱스 스캔 (B-Tree 등)
	Parallel Bitmap Heap Scan	병렬 비트맵 힙 스캔
조인	Parallel Hash Join	병렬 해시 조인
집계	Partial Aggregate	병렬 부분 집계
	Finalize Aggregate	병렬 최종 집계
기타	Parallel Append	병렬로 하위 노드 실행 (파티션 등)
	Parallel Foreign Scan	병렬 FDW 스캔 (지원되는 경우)

 

• 주의사항

→ 병렬 실행 계획은 쿼리가 parallel_safe 조건을 만족해야 함.
→ parallel_setup_cost, parallel_tuple_cost 등 파라미터로 병렬 설정을 세밀하게 조정 가능.
→ 병렬화로 인해 CPU 사용량이 급증할 수 있고, OOM(Out Of Memory) 위험이나 context switching 비용도 발생.
→ DB 서버에 코어가 많고 쿼리 패턴이 대용량 스캔/조인 위주일 때 유리.

---

5. 형변환 처리

형변환은 주로 파서(Parser) 또는 플래너(Planner) 단계에서 처리됩니다.

transformAExprOp() → select_common_type() → coerce_type() 순으로 동작하며, 가능하면 컬럼 타입을 유지하고 상수 쪽을 바꾸려 시도합니다.

 

5.1 파서 단계 (Parsing + Parse Analysis)

항목	설명
관련 함수	transformExpr(), transformAExprOp(), select_common_type(), coerce_type()
주요 처리	- 쿼리에서 타입이 다른 비교식(예: id = '123')을 파악하고- 가능하면 컬럼 쪽은 그대로 두고, 리터럴에 형변환을 적용함 ('123'::int)
결과	이 변환된 구문 트리는 플래너에게 전달됨

 

예시:

SELECT * FROM users WHERE id = '123';

• id는 INTEGER, '123'은 TEXT
• 파서가 '123'::int로 캐스팅하고, 최종 표현은 id = 123이 됨.
• 이 결과는 transformExpr() → transformAExprOp()에서 coerce_to_target_type()로 호출됨.

---

5.2 리라이터 단계 (Rewrite Rule System)

항목	설명
처리 여부	❌ 형변환은 처리하지 않음
역할	RULE 기반으로 VIEW 확장, INSTEAD RULE 적용 등 구조 변경만 수행
대표 함수	fireRIRrules(), RewriteQuery()

---

5.3 플래너/옵티마이저 단계 (Planner/Optimizer)

항목	설명
처리 여부	✅ 형변환이 적용된 쿼리 트리를 기반으로 인덱스 여부 판단
주요 역할	- id = 123이면 인덱스 사용 가능 (Index Scan)- id::text = '123'이면 인덱스 불가능 (Seq Scan)
추가 처리	Predicate Pushdown, Expression Simplification 등 최적화 수행
관련 함수	create_indexscan_plan(), match_index_to_operand() 등

 

인덱스 활용 불가 예시:

-- 컬럼 쪽에 명시적 형변환
SELECT * FROM users WHERE id::text = '123';

• id::text는 표현식 → 인덱스가 이를 기반으로 만들어진 것이 아님.
• 옵티마이저는 idx_users_id가 (id)이므로 id::text에는 적용 못 함.
• 결론적으로 실행계획은 Seq Scan이 나올 수 있음.

---

5.4 실행기 (Executor)

항목	설명
형변환 여부	✅ 실행 시점에 형변환이 필요한 경우 수행됨
예시	id = $1 쿼리에서 $1이 TEXT이고, id가 INTEGER라면, 실행 시점에 TEXT → INT 변환 발생
성능 영향	타입이 안 맞으면 실행 단계에서 CAST 연산 발생 → 성능 저하 가능

---

5.4 형변환이 인덱스 최적화에 미치는 영향

쿼리 형태	인덱스 사용	설명
id = 123	✅ 사용 가능	타입 일치
id = '123'	✅ 사용 가능	파서에서 '123'::int로 변환됨
id = '123'::int	✅ 사용 가능	명시적 캐스팅, 컬럼과 동일
id::text = '123'	❌ 불가능	컬럼 자체에 형변환 → 인덱스 미사용
CAST(id AS text) = '123'	❌ 불가능	동일한 이유로 인덱스 미사용

---

5.5 정리

단계	형변환 처리 여부	설명
파서	✅ 있음	transformExpr() 등에서 리터럴 캐스팅 우선 적용
리라이터	❌ 없음	쿼리 구조 변경 (RULE, VIEW 확장), 형변환 비관여
플래너/옵티마이저	✅ 있음	형변환 상태로 인덱스 조건 가능 여부 평가
실행기	✅ 있음	값 전달 시 타입 불일치 처리 (비용 높음)

 

postgres/src/backend/parser/parse_expr.c at master · postgres/postgres

---

6. 테이블 파티셔닝과 옵티마이저

PostgreSQL에서 테이블 파티셔닝은 Range, List, Hash 파티셔닝을 지원합니다.
파티션 테이블은 각각 독립된 물리 테이블로 취급되며, 옵티마이저는 Partition Pruning을 통"실제로 필요한 파티션만" 스캔하려고 시도합니다.
 

6.1 PostgreSQL의 파티션 구조

• PostgreSQL에서 파티션은 Declarative Partitioning (선언적 파티셔닝) 방식 사용
• 상위 테이블은 Partitioned Table (논리적 구조)
• 하위 테이블은 Child Partition (물리적 테이블)
• 상위 테이블 자체는 데이터를 가지지 않음 (empty shell)

---

6.2 파티션 인덱스 생성 동작

작업	동작 방식	설명
상위 테이블에 인덱스 생성	✅ 자동으로 하위 파티션에도 생성됨 (PostgreSQL 11부터)	동일한 속성으로 하위에 각각 독립 인덱스 생성 (글로벌 아님)
하위 파티션에 직접 인덱스 생성	✅ 가능	개별적으로 인덱스 전략 조정 가능
글로벌 인덱스 (Global Index)	❌ 미지원	파티션 전체를 아우르는 유니크 제약 불가능

• 상위 테이블에 인덱스를 생성하면 하위에도 생성되지만, 하위 파티션마다 별도 로컬 인덱스를 생성하는 것이지, 글로벌 인덱스는 아님.
• 파티션 키를 통한 효율적인 검색은 가능하나, 파티션 간 중복값에 대한 전역 유니크 인덱스 등은 구현이 어려움.

5.12. Table Partitioning

---

6.3 파티션 프루닝

구분	설명
정적 파티션 프루닝	플래너 단계에서 WHERE 조건이 상수면 미리 쓸 파티션 결정
동적 파티션 프루닝	실행 시점에 JOIN 키나 파라미터가 결정되면 런타임에 파티션 결정→ Parallel Query, PL/pgSQL 등에 유용
PostgreSQL 지원 시점	v11부터 정적, v12부터 동적 프루닝 정식 도입

 

+ enable_partition_pruning 파라미터는 파티션 프루닝 기능의 활성화 여부를 결정하며, 기본값은 on으로 설정되어 있음.

 

5.12. Table Partitioning

---

7. 파라미터 설정 및 통계 활용

• 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 옵티마이저의 성능을 최대한 끌어낼 수 있을 것입니다.
 
도움이 되셨길 바라며, 오늘 포스팅은 여기까지입니다.
다음 포스팅도 기대해주세요-!