5.3 통계와 ANALYZE

옵티마이저의 모든 결정은 통계에 의존합니다. 통계는 ANALYZE(수동) 또는 autovacuum(자동)이 수집해 pg_class, pg_statistic, pg_statistic_ext_data에 저장합니다. PostgreSQL이 어떤 통계를 보는지, 어떻게 갱신되는지, 무엇이 자주 잘못되는지를 정리합니다.

통계가 저장되는 곳

pg_statistic은 보안상 직접 SELECT 불가능합니다. 대신 pg_stats 뷰로 보면 됩니다.

SELECT attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds
  FROM pg_stats
 WHERE schemaname = 'public' AND tablename = 'orders'
 ORDER BY attname;

통계의 종류

1. MCV — Most Common Values

가장 자주 등장하는 값과 빈도수를 저장합니다. default_statistics_target = 100 기준으로 상위 100개.

most_common_vals    | {paid, shipped, pending}
most_common_freqs   | {0.6, 0.3, 0.05}

이 컬럼은 paid가 60%, shipped가 30%, pending이 5% — 나머지 5%는 다른 값들. WHERE status = 'paid'의 selectivity는 MCV에서 직접 0.6으로 잡힙니다.

2. Histogram

MCV에 안 들어간 값들의 분포를 경계값 배열로 보관. 기본 101개 bucket.

histogram_bounds | {1, 1003, 2010, ..., 99987}

WHERE id BETWEEN 5000 AND 10000 같은 범위 쿼리 selectivity는 histogram에서 추정합니다.

3. n_distinct

서로 다른 값의 개수. 양수면 추정값, 음수면 row 수 대비 비율.

추정이 어려운 컬럼에는 직접 강제 가능:

ALTER TABLE big_table ALTER COLUMN some_col SET (n_distinct = 1000000);
ANALYZE big_table;

4. correlation

물리적 저장 순서와 값 순서의 상관계수 (−1 ~ +1). 인덱스 스캔의 random I/O 비용 추정에 씁니다.

• +1: 정렬된 채로 저장 (CLUSTER 직후)
• 0: 무작위
• −1: 역순

ANALYZE 동작

ANALYZE는 다음을 수행한다:

1. 테이블에서 샘플 row 수집 (default_statistics_target × 300 정도)
2. 각 컬럼의 MCV, histogram, n_distinct 계산
3. pg_class.reltuples, relpages 갱신
4. pg_statistic 갱신

-- 한 테이블
ANALYZE orders;

-- 컬럼 단위 (PG 9.5+)
ANALYZE orders (status, created_at);

-- 데이터베이스 전체
ANALYZE;

옵션:

ANALYZE는 read lock(SHARE UPDATE EXCLUSIVE)만 잡아 운영 중 안전하게 실행 가능합니다. VACUUM과 같은 락 모드라 동시에는 못 돌지만, SELECT/INSERT는 막지 않습니다.

autovacuum이 trigger하는 ANALYZE

기본 임계값:

autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

즉, 마지막 ANALYZE 이후 50 + (0.1 × row 수) 개 이상 변경되면 자동 트리거.

대용량 테이블(10M+)에서는 0.1이 너무 보수적이라 통계가 늦게 갱신됩니다. 권장:

ALTER TABLE big_orders SET (autovacuum_analyze_scale_factor = 0.02);

통계 신선도 점검

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       n_mod_since_analyze,
       last_analyze, last_autoanalyze
  FROM pg_stat_user_tables
 ORDER BY n_mod_since_analyze DESC
 LIMIT 10;

n_mod_since_analyze가 큰 테이블은 통계가 오래된 상태. plan 미스의 1순위 원인입니다.

extended statistics — 컬럼 간 종속

단일 컬럼 통계로는 컬럼 사이 상관관계를 잡지 못합니다.

WHERE city = 'Seoul' AND country = 'KR'
-- 옵티마이저는 두 조건을 독립 사건으로 가정해 selectivity를 곱함
-- 0.3 × 0.4 = 0.12 → 12%로 추정
-- 실제로는 Seoul은 거의 모두 KR이라 30%

PG 10+의 CREATE STATISTICS로 해결:

CREATE STATISTICS orders_geo (dependencies, ndistinct, mcv)
  ON city, country
  FROM orders;

ANALYZE orders;

세 가지 종류:

대부분 운영에서는 dependencies + ndistinct 정도면 충분합니다.

통계 미스의 흔한 증상

운영 표준

1. ANALYZE를 매 ETL/대용량 INSERT 직후 명시적으로 실행
2. autovacuum의 analyze scale_factor를 큰 테이블에서 0.02~0.05로 낮춤
3. 평소 plan이 흔들리는 컬럼은 SET STATISTICS 500~1000
4. JOIN 키 조합에 CREATE STATISTICS (dependencies) 검토
5. pg_stats 직접 조회로 분포 점검 — selectivity 추정의 출발점

정리

• 통계는 pg_class(행·페이지 수)와 pg_statistic(컬럼 분포)에 저장
• 종류: MCV, histogram, n_distinct, correlation
• ANALYZE는 SHARE UPDATE EXCLUSIVE — 운영 중 안전
• autovacuum의 analyze 트리거 = threshold + scale × rows — 큰 테이블은 scale 낮춤
• 컬럼 간 종속은 단일 통계로 못 잡음 → CREATE STATISTICS
• plan 미스의 1순위 원인은 통계 오래됨 — n_mod_since_analyze 점검 습관

다음 절(5.4)에서는 옵티마이저가 만든 plan을 사람이 읽는 도구 — EXPLAIN — 을 봅니다.