옵티마이저가 쓰는 통계

옵티마이저가 쓰는 통계 (Statistics Used by the Planner)

쿼리 플래너가 좋은 실행 계획을 고르려면 "이 질의는 대충 몇 행을 돌려줄 것인가"를 추정할 수 있어야 해요. 그 추정의 밑바탕이 되는 게 PostgreSQL이 수집해 두는 통계 인데요, 이 통계는 크게 각 테이블·인덱스의 크기 정보와, 각 열 값의 분포 정보로 나뉘어요. 통계를 제대로 이해하면 "왜 플래너가 이상한 계획을 골랐지?" 하는 문제를 진단하는 출발점이 돼요. 이번에는 플래너가 어떤 통계를 어떻게 쓰는지, 그리고 다중 열 상관관계를 잡는 확장 통계까지 살펴볼게요.

출처: PostgreSQL 공식 문서 — 14.2. Statistics Used by the Planner

단일 열 통계

통계의 한 부분은 각 테이블과 인덱스의 항목 수, 그리고 각 테이블·인덱스가 차지하는 디스크 블록 수예요. 이 정보는 pg_class 표의 reltuples와 relpages 열에 보관돼요. 이렇게 조회해서 볼 수 있어요.

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%';

이 쿼리 결과를 보면 tenk1이 그 인덱스들과 마찬가지로 10000행을 담고 있지만, 인덱스들이 테이블보다 훨씬 작다는 걸 알 수 있어요.

효율성 때문에 reltuples와 relpages는 실시간으로 갱신되지 않아서 보통 다소 오래된 값을 담고 있어요. 이 값들은 VACUUM, ANALYZE, 그리고 CREATE INDEX 같은 일부 DDL 명령에 의해 갱신돼요. 테이블 전체를 훑지 않는 VACUUM·ANALYZE(흔한 경우)는 훑은 부분을 기준으로 reltuples를 근사적으로 갱신해요. 어쨌든 플래너는 pg_class의 값을 현재 물리적 테이블 크기에 맞게 조정해서 더 가까운 근사치를 얻어요.

대부분의 질의는 WHERE 절 덕에 테이블의 일부 행만 가져와요. 그래서 플래너는 WHERE 절의 선택도(selectivity) 를 추정해야 하는데, 선택도는 각 조건에 맞는 행의 비율이에요. 이 작업에 쓰는 정보는 pg_statistic 시스템 카탈로그에 저장돼요. pg_statistic의 항목은 ANALYZE와 VACUUM ANALYZE 명령으로 갱신되며, 갓 갱신됐어도 항상 근사치예요.

pg_statistic을 직접 보기보다는 그 뷰인 pg_stats를 보는 게 나아요. pg_stats는 읽기 쉽게 설계됐고, 무엇보다 모든 사용자가 읽을 수 있어요. 반면 pg_statistic은 수퍼유저만 읽을 수 있죠. (이렇게 해서 권한 없는 사용자가 남의 테이블 내용을 통계로부터 알아내는 걸 막아요. pg_stats 뷰는 현재 사용자가 읽을 수 있는 테이블의 행만 보여줘요.) 예를 들면 이런 식으로 조회해요.

SELECT attname, inherited, n_distinct, array_to_string(most_common_vals, E'\n') as most_common_vals FROM pg_stats WHERE tablename = 'road';

같은 열에 대해 두 행이 보이는 걸 볼 수 있는데, 하나는 road 테이블에서 시작하는 상속 계층 전체(inherited = t)이고, 다른 하나는 road 테이블 자체만(inherited = f)이에요.

ANALYZE가 pg_statistic에 저장하는 정보의 양, 특히 각 열의 most_common_vals와 histogram_bounds 배열의 최대 항목 수는 ALTER TABLE SET STATISTICS 명령으로 열별로, 또는 default_statistics_target 설정 변수로 전역적으로 정할 수 있어요. 현재 기본 한도는 항목 100개예요. 한도를 높이면 특히 데이터 분포가 불규칙한 열에서 더 정확한 계획 추정이 가능해지지만, 그 대가로 pg_statistic 공간을 더 쓰고 추정 계산 시간이 약간 늘어나요. 분포가 단순한 열이라면 오히려 낮은 한도로 충분할 수 있어요.

확장 통계 (다중 열 통계)

느린 질의가 나쁜 실행 계획을 타는 흔한 이유 중 하나는, 질의 절에 쓰인 여러 열이 서로 상관관계를 가질 때예요. 플래너는 평소에 여러 조건이 서로 독립적이라고 가정하는데, 열 값이 상관관계를 가지면 이 가정이 성립하지 않죠. 개별 열 단위로만 보는 일반 통계로는 열 사이의 상관관계를 잡을 수 없어요. 하지만 PostgreSQL은 이런 정보를 담을 수 있는 다변량 통계(multivariate statistics) 를 계산할 수 있어요.

가능한 열 조합의 수가 너무 많아서 다변량 통계를 자동으로 계산하는 건 비현실적이에요. 대신 확장 통계 객체(statistics object) 를 만들어, 관심 있는 열 집합에 대한 통계를 얻도록 서버에 지시해요. 확장 통계 객체는 CREATE STATISTICS 명령으로 만들어요. 객체를 만드는 건 그 통계에 관심이 있다는 카탈로그 항목을 만들 뿐이고, 실제 데이터 수집은 ANALYZE(수동 또는 백그라운드 자동 분석)가 수행해요. 수집된 값은 pg_statistic_ext_data 카탈로그에서 확인할 수 있어요.

ANALYZE는 일반 단일 열 통계를 계산할 때 뽑는 것과 같은 표본으로 확장 통계도 계산해요. 표본 크기를 늘리려면 통계 대상(statistics target)을 키우면 되니, 대상이 클수록 확장 통계도 보통 더 정확해지지만 계산 시간도 늘어나요.

함수 종속 (Functional Dependencies)

가장 단순한 확장 통계는 함수 종속(functional dependencies) 을 추적해요. 이 개념은 데이터베이스 정규형 정의에서 쓰는 것이죠. 열 a의 값을 알면 열 b의 값이 결정될 때, 열 b는 열 a에 함수 종속이라고 해요. 즉 a 값은 같은데 b 값이 다른 두 행이 없다는 뜻이에요. 완전히 정규화된 DB에서는 함수 종속이 기본 키와 슈퍼키에만 존재해야 하지만, 실제로는 다양한 이유로(대표적으로 성능을 위한 의도적 비정규화) 완전히 정규화되지 않은 데이터가 흔해요.

함수 종속의 존재는 특정 질의의 추정 정확도에 직접 영향을 줘요. 질의가 독립 열과 종속 열 모두에 조건을 가진다면, 종속 열의 조건은 결과 크기를 더 줄이지 않아요. 그런데 함수 종속을 모르는 플래너는 조건들이 독립이라고 가정해서 결과 크기를 과소 추정하게 돼요.

플래너가 함수 종속을 알게 하려면, ANALYZE가 열 간 의존도를 측정할 수 있어요. 모든 열 집합의 의존도를 평가하는 건 비용이 너무 커서, dependencies 옵션으로 정의한 통계 객체에 함께 나타나는 열 그룹으로만 수집을 제한해요. 강하게 상관된 열 그룹에 대해서만 dependencies 통계를 만드는 게 좋아요. 함수 종속 통계 수집 예시를 볼게요.

CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxddependencies FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts';

결과에서 열 1(우편번호)이 열 5(도시)를 완전히 결정하므로 계수가 1.0인 걸 볼 수 있고, 반대로 도시는 우편번호를 약 42%만 결정한다는 뜻은 여러 우편번호를 가진 도시가 꽤 많다(58%)는 걸 나타내요.

함수 종속이 적용되는 한계도 알아두면 좋아요. 현재는 열을 상수 값과 비교하는 단순 등호 조건과 상수 값 IN 절에서만 적용돼요. 두 열을 비교하는 등호 조건이나 열을 표현식과 비교하는 경우, 범위 절, LIKE나 다른 조건 유형의 추정 개선에는 쓰이지 않아요. 또 추정할 때 관련 열의 조건이 호환되어 중복이라고 가정하는데, 만약 호환되지 않는다면 올바른 추정은 0행이지만 그 가능성은 고려되지 않아요.

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

이런 질의에서는 city 절이 선택도를 바꾸지 않는다고 무시하는 게 맞아요. 하지만 다음 질의처럼 실제로 만족하는 행이 0개인 경우에도 같은 가정을 해요.

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

실제로는 0행이지만 함수 종속 통계만으로는 그렇게 결론 내릴 충분한 정보가 없어요. 많은 실제 상황에서는 호환되는 값만 질의에 쓰도록 보장되니(예: 호환되는 도시·우편번호만 선택하게 하는 GUI) 이 가정이 잘 맞지만, 그렇지 않다면 함수 종속이 적절한 선택이 아닐 수 있어요.

다변량 N-Distinct

단일 열 통계는 각 열의 고유 값 수를 저장해요. 여러 열을 합칠 때(예: GROUP BY a, b)의 고유 값 수 추정은 단일 열 통계만 있으면 자주 틀려서 나쁜 계획을 만들곤 해요. 이런 추정을 개선하기 위해 ANALYZE는 열 그룹의 n-distinct 통계를 수집할 수 있어요. 역시 모든 조합을 다 계산하는 건 비현실적이므로, ndistinct 옵션으로 정의한 통계 객체에 함께 나타나는 열 그룹에 대해서만 수집해요. 나열된 열에서 두 개 이상 열의 각 조합에 대해 데이터를 수집해요.

CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxdndistinct AS nd FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts2';

이 결과는 우편번호와 주, 우편번호와 도시, 우편번호·도시·주 조합이 모두 33178개의 고유 값을 가진다는 걸 보여줘요(이 표에서 우편번호 단독으로도 유일하므로 셋이 같게 나오는 건 예상대로예요). 반면 도시와 주 조합은 27435개의 고유 값만 가져요. ndistinct 통계 객체는 실제로 그룹화에 쓰이고 그룹 수 오추정이 나쁜 계획을 만드는 열 조합에 대해서만 만드는 게 좋아요. 그렇지 않으면 ANALYZE 주기가 그냥 낭비되거든요.

다변량 MCV 목록

각 열에 저장되는 또 다른 통계 유형은 최빈값 목록(most-common value lists) 이에요. 개별 열에 대해서는 아주 정확한 추정이 가능하지만, 여러 열에 조건이 있는 질의에서는 큰 오추정이 날 수 있어요. 이를 개선하기 위해 ANALYZE는 열 조합에 대한 MCV 목록을 수집할 수 있어요. MCV 목록은(함수 종속이나 n-distinct와 달리) 흔한 열 값을 실제로 저장하므로, 모든 조합에 하기는 더더욱 비현실적이에요. 그래서 mcv 옵션으로 정의한 통계 객체에 함께 나타나는 열 그룹에 대해서만 수집해요.

CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;
ANALYZE zipcodes;
SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

이 결과에서 도시와 주의 가장 흔한 조합은 Washington, DC이고 표본에서 실제 빈도가 약 0.35%인 걸 알 수 있어요. 개별 열 빈도로 계산한 기본 빈도(base frequency)는 0.0027%에 불과해서, 두 자릿수 과소 추정을 하게 되죠. MCV 통계 객체도 실제로 함께 조건에 쓰이고 오추정이 나쁜 계획을 만드는 열 조합에 대해서만 만드는 게 좋아요.

더 알아보기