옵티마이저 (optimizer, 비용 기반, cost-based)

SQL을 어떻게 실행할지 결정하는 DB 구성요소. 현대 DB의 옵티마이저는 전부 비용 기반(cost-based) 이다.

비용 기반이란

① 가능한 실행 방법(후보 계획)을 여러 개 만든다
② 각각의 비용을 추정한다 — 읽어야 할 디스크 페이지 수 · CPU 연산량
③ 가장 싼 것을 고른다

비용은 절대 시간이 아니라 상대 단위다. PostgreSQL의 cost=0.29..8.31 같은 숫자는 밀리초가 아니라 "순차 페이지 하나 읽기"를 1로 잡은 상대값이다. 계획끼리 비교하는 데만 쓴다.

무엇을 근거로 추정하나 — 통계

옵티마이저는 미래를 모르므로 테이블 통계를 본다.

행 수 칼럼별 값 분포 · 카디널리티 히스토그램 (값이 어떻게 치우쳐 있나) NULL 비율

이걸로 "이 조건이면 대략 몇 행이 나올까" 를 추정하고, 그 추정이 모든 결정의 출발점이 된다.

"조건에 맞는 행이 전체의 0.1%" 로 추정 — → 인덱스로 몇 행만 집는 게 싸다
"50% 가 나온다" 로 추정 — → 어차피 절반이니 풀 스캔이 낫다

옵티마이저를 이기려 하지 말 것

초보가 흔히 하는 일이 힌트로 계획을 강제하는 것이다.

-- MySQL: 인덱스를 강제한다
SELECT * FROM orders FORCE INDEX (idx_created) WHERE ...;

대개 잘못된 처방이다. 옵티마이저가 인덱스를 안 쓴 데는 이유가 있다 — 카디널리티가 낮거나, 통계가 낡았거나, 조건이 인덱스를 무력화했다. 힌트는 그 원인을 덮을 뿐이고, 데이터가 늘면 다시 틀린 선택으로 굳는다.

순서는 이렇다.

① 통계를 갱신한다 (ANALYZE)
② 추정 vs 실제 행 수를 다시 본다
③ 그래도 틀리면 인덱스나 쿼리를 고친다
④ 힌트는 마지막 수단

면접 함정

❌ "옵티마이저가 최적 계획을 고른다" → 추정한 비용이 가장 싼 것을 고른다. 추정이 틀리면 계획도 틀린다.
❌ "SQL을 잘 쓰면 계획을 통제할 수 있다" → SQL은 선언형이라 직접 통제할 수 없다. 통계와 인덱스로 유도할 뿐이다.

왜 그 계획을 골랐는지 캐묻기

-- PostgreSQL: 특정 방식을 잠시 꺼서 대안 계획의 비용을 비교해 본다 (진단용, 운영 설정 아님)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PAID';
RESET enable_seqscan;

인덱스 스캔의 비용이 풀 스캔보다 정말 비싼지 눈으로 확인하는 방법이다. 껐을 때가 오히려 빠르다면 비용 파라미터나 통계를 의심한다.

-- SSD 라면 랜덤 접근 비용이 기본값(4.0)보다 낮다 — 인덱스를 더 자주 고르게 된다
SHOW random_page_cost;      -- 기본 4.0 → SSD 환경에서는 1.1 로 낮추는 게 일반적
SHOW effective_cache_size;  -- OS 캐시까지 포함한 가용 메모리 힌트

random_page_cost가 기본값 그대로면 옵티마이저는 아직 회전 디스크를 가정하고 있다. SSD에서 인덱스를 잘 안 타는 흔한 원인이다.

통계 갱신이 계획을 되돌리는지 확인하기

ANALYZE orders;
EXPLAIN ANALYZE SELECT ...;     -- 계획이 바뀌었나?