대량 적재 뒤 쿼리가 느려지면 — ANALYZE 와 낡은 통계

배치로 데이터를 대량 적재한 직후부터 쿼리가 느려졌어요. 스키마도 인덱스도 안 바뀌었고, 데이터만 늘었어요.

EXPLAIN 을 보면 계획이 이상해요. 몇 백만 행이 나오는 조건인데 추정은 몇 십 행이에요. 그 추정을 근거로 중첩 루프 조인을 골랐고, 결과적으로 수백만 번의 반복이 돌고 있어요.

이 상태는 시간이 지나면 저절로 풀리기도 해요. 자동 통계 수집이 돌면서요. 그래서 "아까는 느렸는데 지금은 괜찮네"로 넘어가고, 다음 적재 때 똑같이 반복됩니다.

저절로 낫는다는 게 이 문제의 가장 나쁜 성질이에요. 원인을 찾을 동기가 사라지거든요.

왜 그런가

옵티마이저는 실제 데이터를 보지 않아요. 통계를 봅니다.

통계에는 테이블의 대략적인 행 수, 각 컬럼의 값 분포, 자주 나오는 값 목록, 값의 분산 정도 같은 게 들어 있어요. 옵티마이저는 이걸로 "이 조건이면 몇 행이 나오겠다"를 추정하고, 그 추정 위에서 계획을 세워요.

그래서 추정이 틀리면 계획 전체가 틀어져요. 100행이 나올 거라 보고 중첩 루프를 골랐는데 실제로 100만 행이면, 100만 번의 반복이 돌아요. 반대로 100만 행을 예상하고 해시 조인을 골랐는데 실제로 10행이면 불필요한 해시 테이블을 만들고요.

통계는 자동으로 갱신되지만 즉시는 아니에요. 변경된 행 수가 일정 비율을 넘어야 수집이 시작돼요. 그래서 대량 적재 직후가 가장 위험한 구간이에요. 데이터는 이미 백만 행인데 통계는 아직 천 행 시절의 것을 들고 있는 상태죠.

그리고 통계는 표본이에요. 전체를 다 세는 게 아니라 일부를 뽑아 추정하니, 값이 심하게 치우친 컬럼에서는 표본이 커도 부정확할 수 있습니다.

코드로 보면

적재 뒤에는 통계를 갱신한다:

코드

· 대량 적재 직후 — 자동 수집을 기다리지 않는다
✓ COPY orders FROM '...';
✓ ANALYZE orders;

· 치우친 컬럼은 표본을 늘린다 (기본 100)
✓ ALTER TABLE orders
✓ ALTER COLUMN status SET STATISTICS 1000;
✓ ANALYZE orders;

· 컬럼끼리 상관이 있으면 확장 통계를 만든다
· city 와 zipcode 처럼 사실상 종속인 쌍
✓ CREATE STATISTICS s_city_zip (dependencies)
✓ ON city, zipcode FROM addresses;
✓ ANALYZE addresses;

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

추정이 틀어지는 대표적인 상황이 몇 가지 있어요.

컬럼 간 상관관계. 옵티마이저는 기본적으로 컬럼들이 서로 독립이라고 가정해요. city = '서울' 의 비율과 zipcode = '06000' 의 비율을 각각 구해 곱하죠. 그런데 이 둘은 사실상 종속이라 실제 결과는 곱한 값보다 훨씬 커요. 이런 경우 확장 통계를 만들어 상관을 알려 줘야 해요.

심한 치우침. 값 하나가 전체의 90% 를 차지하는 컬럼이면, 자주 나오는 값 목록에 그 값이 잡혀야 정확해요. 목록 크기가 작으면 놓치고, 그러면 그 값 조회의 추정이 크게 빗나가요. 이럴 때 통계 표본 크기를 올립니다.

파라미터 값에 따른 차이. 흔한 값을 넘길 때와 드문 값을 넘길 때 최적 계획이 다른데, 계획이 캐시되면 처음 실행의 값에 맞춰 굳어요. 그래서 어떤 사용자는 빠르고 어떤 사용자는 느린 현상이 생겨요.

그리고 표현식과 함수의 결과는 통계가 없어요. WHERE upper(name) = 'KIM' 같은 조건은 옵티마이저가 분포를 모르니 기본 추정치를 씁니다. 표현식 인덱스를 만들면 그 표현식에 대한 통계도 함께 생겨서 추정이 좋아져요.

마지막으로 임시 테이블이 자주 문제가 돼요. 만들고 바로 조인하면 통계가 없어서 옵티마이저가 아무것도 모르는 상태로 계획을 세워요. 큰 임시 테이블을 만들었으면 ANALYZE 를 명시적으로 돌려야 합니다.

처방

대량 적재·삭제 직후에는 ANALYZE 를 명시적으로 돌리세요. 자동 수집을 기다리지 마세요. 배치 스크립트의 마지막 단계로 넣어 두면 됩니다.
큰 임시 테이블을 만들어 조인한다면 ANALYZE 를 돌리세요. 통계 없는 테이블은 옵티마이저에게 깜깜이예요.
추정과 실제가 크게 다른 쿼리를 찾으세요. EXPLAIN ANALYZE 로 두 숫자를 비교하는 게 유일한 확인 방법이에요. 열 배 이상 차이 나면 조사 대상입니다.
치우친 컬럼은 통계 표본을 늘리세요. 전역으로 올리면 수집 비용이 커지니 문제 컬럼만 올리는 게 좋아요.
컬럼 간 상관이 있으면 확장 통계를 만드세요. 도시와 우편번호, 제품과 카테고리처럼 사실상 종속인 쌍이 대표적이에요.
autovacuum 의 통계 수집 임계값을 테이블별로 조정하세요. 크고 자주 바뀌는 테이블은 기본 비율이 너무 느슨해서 오래 낡은 통계로 돕니다.
계획이 갑자기 나빠지는 걸 감지할 수 있게 하세요. 같은 쿼리의 평균 실행 시간이 어느 날 갑자기 뛰면 계획이 바뀐 신호예요.

지금 확인해 보세요

추정이 얼마나 빗나가고 있나:

코드

· 추정 대 실제 — 이 한 줄이 진단의 전부다
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... ;
✕ rows=50 (추정) vs actual rows=1200000 → 통계 문제

· 통계 수집 시각과 변경량
SELECT relname, n_live_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 가 크면 통계가 낡았다

· 컬럼 분포가 실제로 어떤가
SELECT attname, n_distinct, most_common_vals
  FROM pg_stats
 WHERE tablename = 'orders' AND attname = 'status';

· 비용 파라미터가 하드웨어와 맞나 (SSD 면 낮춘다)
SHOW random_page_cost;

핵심 한 줄옵티마이저는 데이터가 아니라 통계를 본다. 통계가 낡으면 계획도 낡는다.

왜 그런가

코드로 보면

실전에서는

처방

지금 확인해 보세요

이어서 읽기