5.2 옵티마이저와 비용 모델

PostgreSQL의 옵티마이저는 비용 기반(cost-based)입니다. 같은 SQL을 답할 수 있는 여러 실행 계획 후보를 만들고, 각각의 비용을 추정해 가장 싼 계획을 고릅니다. 비용은 단위 없는 추상 숫자지만, 그 안의 가중치들은 “디스크에서 페이지 한 장 읽는 비용 = 1.0"을 기준으로 정해집니다.

핵심 비용 파라미터

random_page_cost 조정

HDD 시대에는 기본 4.0이 적당했습니다. SSD/NVMe 환경에서는 랜덤·시퀀셜 차이가 작아서 4.0이 너무 보수적입니다. 흔한 권장:

random_page_cost = 1.1   # SSD
random_page_cost = 1.05  # NVMe

이걸 줄이면 옵티마이저가 인덱스 스캔을 더 선호하게 됩니다.

비용 계산 예시

SELECT * FROM orders WHERE id = 42 같은 PK 룩업의 비용:

Index Scan using orders_pkey on orders
  cost = (index_search) + (cpu_index_tuple_cost × 1) + (random_page_cost × 1) + (cpu_tuple_cost × 1)
       ≈ 4.0 + 0.005 + 0.01 ≈ 4.01

seq scan과 비교:

Seq Scan on orders
  cost = (seq_page_cost × N_pages) + (cpu_tuple_cost × N_rows) + (filter_cost × N_rows)

테이블이 클수록 seq scan 비용이 폭증해 옵티마이저가 인덱스를 고릅니다. 작으면 seq가 더 쌉니다.

통계 의존

비용 추정의 핵심 입력은 통계다. ANALYZE (또는 autovacuum의 일부) 가 갱신합니다.

통계가 부실하면 —

• 행 수 추정이 틀려 잘못된 조인 순서 선택
• “selectivity"가 어긋나 인덱스 vs seq scan 잘못 선택
• nested loop을 hash join으로 잘못 선택 (또는 반대)

옵티마이저의 정확도는 통계의 신선도에 좌우됩니다. autovacuum이 잘 돌고 default_statistics_target이 적절해야(기본 100).

EXPLAIN의 비용 읽기

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using orders_user_id_idx on orders  (cost=0.42..23.51 rows=12 width=64)
   Index Cond: (user_id = 42)

cost=A..B의 의미:

• A = 첫 row를 반환하는 시점의 누적 비용
• B = 마지막 row를 반환하는 시점의 누적 비용

A와 B의 차이가 크면 row가 많다는 뜻. LIMIT 1에는 A가 더 중요하고, 전체를 다 보는 SELECT에는 B가 중요합니다. 옵티마이저는 쿼리의 요구되는 row 수에 따라 다른 plan을 고르기도 합니다.

조인 순서 탐색

여러 테이블을 join할 때 후보 계획이 폭발합니다. n개 테이블이면 join 순서만 n! 가지.

geqo_threshold를 늘리면 더 많은 후보를 정확히 검사하지만 계획 시간이 길어집니다. n ≥ 20 정도에서 DP가 분 단위로 길어지므로 GEQO가 의미를 갖습니다.

from_collapse_limit, join_collapse_limit

from_collapse_limit = 8   # FROM 절 평탄화 한도
join_collapse_limit = 8   # JOIN 절 평탄화 한도

이 값 안에서는 옵티마이저가 순서를 자유롭게 정합니다. 넘어가면 SQL에 적힌 순서를 그대로 유지합니다. 매우 큰 join 쿼리에서 내가 적은 순서가 정답이라면 일부러 낮춰서 옵티마이저를 강제 가이드하는 패턴도 있습니다.

힌트가 없는 이유

PostgreSQL은 공식적으로 쿼리 힌트를 제공하지 않는다. 옵티마이저 통계를 더 정확하게 만드는 방향으로 가야 한다는 철학.

우회:

운영에서는 pg_hint_plan + 통계 개선이 가장 흔하다(자세한 내용은 5.7).

extended statistics — 컬럼 간 상관관계

단일 컬럼 통계로는 WHERE city='Seoul' AND country='KR' 같은 강한 상관관계를 잡지 못합니다. PG 10+의 CREATE STATISTICS가 답입니다.

CREATE STATISTICS orders_geo
  ON city, country
  FROM orders;

ANALYZE orders;

세 가지 종류 (ndistinct, dependencies, mcv) 중 필요한 걸 선택 가능합니다. row 수 추정이 크게 개선될 수 있습니다.

운영 진단

-- 행 수 추정과 실제 차이 — 큰 차이가 나면 plan이 틀렸을 가능성
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
;

-- 통계 신선도
SELECT relname, n_live_tup, n_dead_tup,
       last_analyze, last_autoanalyze
  FROM pg_stat_user_tables
 ORDER BY last_analyze NULLS FIRST
 LIMIT 10;

rows=X, actual rows=Y 차이가 10배 이상이면 통계나 selectivity 추정에 문제. 우선 ANALYZE 또는 statistics target 상향.

default_statistics_target

default_statistics_target = 100   # 기본

100~1000 사이. 컬럼별로 따로 설정도 가능합니다.

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

분포가 매우 skew된 컬럼은 200~500이 효과적. 너무 높이면 ANALYZE 자체가 느려집니다.

정리

• PostgreSQL은 비용 기반 옵티마이저 — seq_page_cost = 1.0을 기준으로 한 추상 비용
• SSD/NVMe에서는 random_page_cost를 1.1~1.05로 낮추는 게 표준
• effective_cache_size는 옵티마이저 힌트(실제 할당 아님). RAM 50~75%
• 조인 순서 탐색은 DP, n > 12면 GEQO
• 공식 힌트 없습니다. pg_hint_plan·enable_*·extended statistics로 우회
• 행 수 추정 오차가 모든 plan 오류의 근원 — ANALYZE와 default_statistics_target이 핵심

다음 절(5.3)에서는 옵티마이저의 입력인 통계와 ANALYZE를 깊게 봅니다.