[PostgreSQL] 통계와 ANALYZE — 대량 적재 직후 플래너가 행 수를 5000으로 헛짚을 때

설치·접속: PostgreSQL 설치와 접속

부제: 방금 100만 행을 넣고 조회하니 계획이 이상하다 — 플래너 추정 rows가 실제와 딴판일 때

-- 100만 행 적재만 하고 통계는 안 모은 상태
CREATE TABLE m5_fresh (id int, user_id int, status text);
INSERT INTO m5_fresh SELECT g, 1+(g%50000), 'done' FROM generate_series(1,1000000) g;
CREATE INDEX ON m5_fresh(user_id);

EXPLAIN SELECT * FROM m5_fresh WHERE user_id = 7;   -- ANALYZE 전
ANALYZE m5_fresh;
EXPLAIN SELECT * FROM m5_fresh WHERE user_id = 7;   -- ANALYZE 후

-- ANALYZE 전: 컬럼 통계가 없어 rows=5000 으로 대충 찍음 (실제는 20)
 Bitmap Heap Scan on m5_fresh  (cost=59.17..5640.65 rows=5000 width=40)
-- ANALYZE 후: rows=20 으로 정확, 비용도 5640 → 81 로 급감
 Bitmap Heap Scan on m5_fresh  (cost=4.58..81.18 rows=20 width=13)

플래너는 실데이터를 세지 않는다 — pg_statistic에 저장된 통계 요약(고유값 수, 히스토그램, 최빈값)으로 "이 조건에 몇 행 걸리겠다"를 추정한다. 대량 INSERT 직후엔 이 통계가 비어 있어, 플래너는 컬럼 폭·페이지 수만 보고 rows=5000 같은 엉뚱한 기본값을 쓴다(실제 user_id=7은 20건). 추정이 250배 틀리면 조인 알고리즘·인덱스 선택이 통째로 어긋난다 — Hash가 붙을 자리에 Nested Loop이 들어서는 식. ANALYZE가 통계를 다시 수집하면 rows=20으로 바로잡히고 예상 비용도 5640→81로 내려간다. 평소엔 autovacuum이 알아서 ANALYZE를 돌리지만, 대량 적재·대량 삭제 직후엔 그 사이 창(window)이 위험하다. 그래서 배치 로드 스크립트 끝에는 ANALYZE 테이블;을 명시적으로 붙이는 게 정석이다.

이렇게도 쓴다

상관된 두 컬럼(region이 city를 결정)의 과소추정은 확장 통계로 잡는다. (조합: CREATE STATISTICS)

CREATE STATISTICS m5_geo_deps (dependencies) ON region, city FROM m5_geo;
ANALYZE m5_geo;   -- 독립 가정 rows=114 → 실제 반영 rows=10333 로 교정

 

특정 컬럼의 통계 해상도를 올려 히스토그램을 촘촘히 한다. (조합: SET STATISTICS)

ALTER TABLE m5_orders ALTER COLUMN amount SET STATISTICS 1000;
ANALYZE m5_orders;

 

플래너가 지금 보고 있는 통계값을 직접 들여다본다. (조합: pg_stats)

SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename='m5_fresh';

 

이 테이블이 마지막으로 ANALYZE된 시각을 확인한다(낡았는지 판단). (조합: pg_stat_user_tables)

SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname='m5_orders';

 

추정 rows와 실제 rows를 한 눈에 대조한다 — 어긋나면 통계 문제다. (조합: EXPLAIN ANALYZE)

EXPLAIN (ANALYZE) SELECT * FROM m5_fresh WHERE user_id=7;  -- (cost ... rows=20) (actual rows=20)

 

전체 DB 통계를 한 번에 갱신한다(마이그레이션·복원 직후). (조합: 전역 ANALYZE)

ANALYZE;