PostgreSQL 튜닝 기술(상) - 1,2장

test_ddl.sql

📚 학습 범위: 『PostgreSQL 튜닝 기술 상』 83페이지까지

---

1. Gather 노드 당 병렬 실행 수란?

• PostgreSQL의 병렬 쿼리에서 Gather 노드는 병렬 워커들의 결과를 모으는 역할을 한다.
• max_parallel_workers_per_gather는 한 Gather 노드가 사용할 수 있는 병렬 워커의 수를 의미함.
• OLTP 환경에서는 병렬 실행의 오버헤드가 더 클 수 있어 0으로 설정하면 throughput이 좋아질 수 있음.

---

2. random_page_cost는 왜 SSD에서 낮춰야 하나?

• random_page_cost는 랜덤 블록 접근 비용을 추정하는 파라미터로, 기본값은 4.0 (HDD 기준).
• PostgreSQL 옵티마이저는 이 값을 기반으로 Index Scan 사용 여부를 결정함.
• SSD는 랜덤 I/O 속도가 빠르기 때문에 이 값을 1.1 ~ 1.5 정도로 낮추는 것이 적절.
• 값이 낮을수록 Index Scan이 더 자주 선택됨.

---

3. Index Scan이 랜덤 I/O인 이유

• Index Scan은 인덱스를 통해 얻은 TID를 이용해 테이블(heap)의 각 블록을 개별적으로 읽는다.
• 이 블록들이 물리적으로 흩어져 있어 랜덤 I/O가 발생함.
• 반면, Seq Scan은 테이블을 순차적으로 읽기 때문에 순차 I/O 처리.

---

4. HDD vs SSD: 랜덤 I/O 성능 차이

항목	HDD	SSD
구조	회전판 + 물리 헤드	플래시 메모리
랜덤 I/O 성능	느림 (seek + rotation)	빠름 (직접 접근)
순차 I/O 성능	빠름	빠름
PostgreSQL에서의 적용	random_page_cost = 4.0	random_page_cost = 1.1 권고

• HDD는 물리적 이동이 필요해 랜덤 I/O가 느림.
• SSD는 메모리 기반 구조라 랜덤/순차 모두 빠름.

---

5. work_mem의 적용 범위

• work_mem은 쿼리 전체가 아닌, 각 정렬/해시 노드마다 개별 적용되는 메모리 제한값.
• 예: work_mem = 100MB, Sort 노드 10개 → 최대 1GB 사용 가능.
• 병렬 쿼리인 경우 워커마다 work_mem 별도 적용됨.
• 너무 크게 설정 시 다수의 쿼리 동시 실행 시 메모리 부족 가능성 있음.

---

6. EXISTS 서브쿼리에서는 왜 startup cost가 더 중요할까?

• EXISTS는 서브쿼리 결과가 존재하는지만 확인하고, row 하나만 찾으면 바로 종료됨.
• 그래서 전체 비용(total cost)보다 첫 결과를 얻기까지 드는 비용(startup cost)이 훨씬 더 중요함.
• 옵티마이저는 startup cost가 낮은 쿼리 계획을 선호하여 빠르게 True/False를 판단하려 함.

---

7. loops의 의미는?

• loops는 해당 쿼리 계획 노드가 실제 실행된 횟수를 나타냄.
• loops = 1: 한 번만 실행됨 (예: 전체 테이블 1회 스캔)
• loops = N: 외부 쿼리로 인해 N번 반복 실행됨 (예: Nested Loop Join에서 내부 테이블)
• 실제 총 row 수는 loops × rows로 계산됨.

예:

Index Scan on orders (actual time=0.02..0.05 rows=3 loops=10)
→ 총 3 × 10 = 30 rows 반환

---

8. EXPLAIN에서 generic plan과 바인드 변수의 관계

• 바인드 변수는 SQL문을 재사용하면서 값만 나중에 전달하는 방식.
• PostgreSQL은 바인드 변수가 있을 경우, 처음엔 custom plan을 만들고, 일정 횟수 이후 generic plan으로 전환할지 판단함.
• Custom Plan: 바인드 값에 따라 실행 계획이 매번 달라짐
• Generic Plan: 바인드 값과 관계없이 하나의 고정된 계획을 반복 사용
• 특정 바인드 값에 따라 성능 차이가 클 경우, generic plan이 비효율적일 수 있음.

---

✅ 참고 파라미터 정리

파라미터	설명
max_parallel_workers_per_gather	Gather 노드 당 병렬 워커 수
random_page_cost	랜덤 I/O 비용 추정치
seq_page_cost	순차 I/O 비용 추정치
work_mem	노드별 임시 작업 메모리 (정렬, 해시 등)