PostgreSQL에서 행 수 추정 오류 감소

I’m sorry, but I can’t access external websites to retrieve the article’s content. If you paste the text you’d like translated (excluding code blocks and URLs), I’ll be happy to translate it into Korean while preserving the original formatting.

Source: …

Introduction

PostgreSQL의 쿼리 플래너는 테이블 통계를 사용해 각 연산이 처리할 예상 행 수(estimated rows)를 추정하고, 이 추정값을 기반으로 최적의 실행 계획을 선택합니다. 예상 행 수가 실제 행 수와 크게 차이날 경우, 플래너는 최적이 아닌 계획을 선택하게 되어 쿼리 성능이 크게 저하될 수 있습니다.

이 글에서는 네 가지 접근법을 순서대로(가장 덜 침습적인 방법부터 가장 침습적인 방법까지) 소개합니다.
보안상의 이유로 실제 SQL 문이나 실행 계획은 공유하지 못합니다; 대신 진단 사고 과정과 적용한 기법에 초점을 맞춥니다.

이 접근법들은 최신 PostgreSQL 버전 모두에 적용 가능하며, 기본 메커니즘(autovacuum, pg_statistic, 확장 통계)은 버전 간에 크게 변하지 않았습니다.

1. 통계를 최신 상태로 유지

대상 테이블은 업데이트 빈도가 높았습니다. 가장 먼저 떠올린 가설은 통계가 단순히 오래됐다는 것이었습니다.

• PostgreSQL에서는 autovacuum 데몬이 자동으로 ANALYZE를 실행해 pg_statistic에 저장된 통계를 업데이트합니다.
• 쓰기 작업이 많은 테이블의 경우, 자동 ANALYZE가 따라가지 못해 통계가 실제와 어긋날 수 있습니다.

테이블 별 autovacuum‑ANALYZE 빈도 조정

postgresql.conf의 전역 설정을 바꾸는 대신, 특정 테이블에 대해 두 핵심 파라미터를 조정했습니다:

ALTER TABLE table_name SET (
    autovacuum_analyze_threshold = 0,
    autovacuum_analyze_scale_factor = 0.01
);

autovacuum_analyze_threshold를 0으로, 스케일 팩터를 0.01로 낮추면 테이블의 1 %만 변경돼도 자동‑ANALYZE가 실행됩니다.

검증 방법:

SELECT relname,
       last_autoanalyze,
       n_mod_since_analyze
FROM   pg_stat_user_tables
WHERE  relname = 'table_name';

• last_autoanalyze – 가장 최근 자동‑ANALYZE가 실행된 시점.
• n_mod_since_analyze – 마지막 ANALYZE 이후 변경된 행 수.

테이블 별 저장 파라미터 전체 목록은 PostgreSQL 문서의 storage parameters 섹션을 참고하십시오.

2. 샘플 크기 확대

통계가 최신임을 확인한 뒤, 두 번째 가설은 ANALYZE는 충분히 자주 실행되지만 샘플 크기가 너무 작아 정확한 통계를 만들지 못한다는 것이었습니다.

PostgreSQL의 ANALYZE는 각 컬럼에서 샘플을 수집하고 다음을 저장합니다:

• 가장 흔한 값(MCVs)
• 히스토그램

이 정보의 정밀도는 default_statistics_target(기본값 = 100)에 의해 결정되며, 히스토그램 버킷 수와 MCV 항목 수를 제어합니다.

컬럼 별 통계 목표값 증가

ALTER TABLE table_name
    ALTER COLUMN column_name SET STATISTICS 500;

• 가이드라인: WHERE 절에 자주 사용되는 컬럼은 500–1000 정도로 설정합니다.
• 트레이드‑오프: 값이 높을수록 ANALYZE 실행 시간이 늘어나고 pg_statistic이 커집니다.

Note: SET STATISTICS 로 통계 목표값을 변경한 뒤에는 새 설정이 적용되도록 ANALYZE를 직접 실행하거나 다음 자동‑ANALYZE를 기다려야 합니다.

3. 확장 통계로 컬럼 상관관계 포착

신선하고 정밀한 기본 통계를 갖추어도, 플래너의 추정 모델 구조적 한계 때문에 행 수 추정 오류가 계속 발생할 수 있습니다.

기본적으로 PostgreSQL은 서로 다른 컬럼에 대한 조건이 독립적이라고 가정합니다. 이 가정이 깨지면 플래너는 선택도를 독립적으로 곱하게 되며, 실제보다 현저히 낮은 행 수를 추정하게 됩니다.

row count.

언제 이런 현상이 발생합니까?

• 테이블에 a1과 a2 두 개의 컬럼이 있습니다.
• 두 컬럼 사이에 함수 종속성이 존재합니다(예: a1이 a2를 결정).
• WHERE 절에 두 컬럼 모두에 대한 조건이 포함됩니다.

구체적인 예시: country와 city. 국가를 알면 해당 국가에서 가능한 도시 집합이 크게 결정됩니다. 플래너는 각 조건의 선택도를 독립적인 것으로 취급하여 실제보다 훨씬 낮은 추정치를 생성합니다.

확장 통계 만들기

CREATE STATISTICS stat_name ON a1, a2 FROM table_name;

CREATE STATISTICS는 세 가지 종류의 통계를 지원합니다:

• ndistinct – 다중 컬럼 고유값 개수
• dependencies – 함수 종속성
• mcv – 다중 컬럼 최빈값

KIND 절을 생략하면 세 가지 모두가 수집되며, 이것이 제가 시작점으로 사용한 방법입니다.

Note: CREATE STATISTICS는 통계 객체만 정의합니다. 실제 통계는 테이블에 ANALYZE가 실행될 때까지 채워지지 않습니다.

자세한 내용은 PostgreSQL 문서의 CREATE STATISTICS를 참고하십시오.

4. 플래너 힌트 사용 (pg_hint_plan) – 최후의 수단

통계 기반 접근법으로도 충분하지 않을 때, pg_hint_plan 확장은 SQL 주석 기반 힌트를 통해 플래너 동작을 직접 제어할 수 있는 방법을 제공합니다.

행 수 추정값 재정의

/*+ Rows(table_name #1000) */ SELECT ...

• # – 절대값(여기서는 1 000)으로 추정치를 설정합니다.
• +, -, *를 사용하여 플래너의 원래 추정치에 덧셈, 뺄셈, 곱셈을 적용할 수도 있습니다.

힌트 기반 접근법의 단점

권장 사항: 통계 기반 방법을 모두 시도한 후에만 힌트를 사용하거나, 보다 영구적인 해결책을 구현하는 동안 임시 방편으로만 사용하십시오.

Source:

근본 원인 조사

이 문서에서는 PostgreSQL에서 행 수 추정 오류를 줄이기 위한 네 가지 접근 방법을 침투 정도가 낮은 순서대로 다룹니다:

1. autovacuum 빈도 조정 – 통계가 오래되었나요?
2. 통계 대상값 증가 – 샘플 크기가 충분한가요?
3. 확장 통계 생성 – 플래너가 열 간 상관관계를 고려할 수 있나요?
4. 힌트 절 적용 – 통계만으로 문제를 해결할 수 없을 때 최후의 수단.

체계적인 문제 해결 워크플로우

행 추정 오류에 직면했을 때는 체계적인 접근이 가장 효과적입니다:

1. EXPLAIN ANALYZE 실행 – 플래너가 추정한 행 수와 실제 쿼리 결과 행 수를 비교합니다.
2. 통계 최신성 확인 – 통계가 오래되었다면 autovacuum 설정을 조정하거나 ANALYZE를 수동으로 실행합니다.
3. 정밀도 향상 – default_statistics_target(또는 열별 목표값)를 높여 플래너가 더 큰 샘플을 사용하도록 합니다.
4. 구조적 제한 해결 – CREATE STATISTICS를 사용해 플래너가 무시하는 다중 열 상관관계를 캡처합니다.
5. 힌트 절 적용 – 최후의 수단으로 pg_hint_plan과 같은 확장을 이용해 특정 조인 순서나 스캔 방법을 강제합니다.

이 글이 문제 해결 과정에서 유용한 참고 자료가 되길 바랍니다.

참고

• PostgreSQL 문서: Table storage parameters
• PostgreSQL 문서: CREATE STATISTICS
• pg_hint_plan (GitHub): https://github.com/ossc-db/pg_hint_plan