[PostgreSQL] 확장 통계(CREATE STATISTICS)로 상관 컬럼 추정 오차 잡기

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

부제: city='서울' AND zip='06...'처럼 서로 상관된 두 컬럼으로 걸었더니 플래너가 행 수를 100배 적게 잡아 엉뚱한 플랜을 탈 때

-- city와 zip이 1:1로 묶인 100만 행 (zip -> city 함수적 종속)
-- CREATE EXTENSION 불필요 (core 기능)

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY OFF)
SELECT * FROM geo WHERE city = 'city_42' AND zip = 'zip_42';

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..17628.00 rows=100 width=50) (actual rows=10000 loops=1)
   Workers Planned: 2
   ->  Parallel Seq Scan on geo  (cost=0.00..16618.00 rows=42 width=50) (actual rows=3333 loops=3)
         Filter: ((city = 'city_42'::text) AND (zip = 'zip_42'::text))
         Rows Removed by Filter: 330000
(6 rows)

플래너가 rows=100으로 추정했는데 실제는 rows=10000이다. 100배 과소추정이다. 원인은 플래너가 두 조건의 선택도를 독립이라고 가정하고 곱하기 때문이다. city도 100종, zip도 100종이니 각각 1/100 선택도, 곱하면 1/10000 → 100만 행 중 100행. 하지만 실제로는 zip이 정해지면 city가 자동으로 정해진다(함수적 종속). 두 번째 조건은 행을 전혀 걸러내지 못하므로 실제는 1/100, 즉 10000행이다. 이 오차 때문에 플래너가 조인 순서나 인덱스 선택을 잘못하기 쉽다.

확장 통계로 "이 두 컬럼은 종속 관계"라고 알려주고 다시 ANALYZE하자.

CREATE STATISTICS geo_stx (dependencies) ON city, zip FROM geo;
ANALYZE geo;

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY OFF)
SELECT * FROM geo WHERE city = 'city_42' AND zip = 'zip_42';

                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..18601.30 rows=9833 width=50) (actual rows=10000 loops=1)
   Workers Planned: 2
   ->  Parallel Seq Scan on geo  (cost=0.00..16618.00 rows=4097 width=50) (actual rows=3333 loops=3)
         Filter: ((city = 'city_42'::text) AND (zip = 'zip_42'::text))
         Rows Removed by Filter: 330000
(6 rows)

추정치가 rows=100에서 rows=9833으로 바뀌었다. 실제 10000행과 거의 일치한다. dependencies 종류의 확장 통계는 컬럼 간 함수적 종속의 정도(0~1)를 저장해두고, 여러 조건이 걸릴 때 선택도를 무작정 곱하지 않도록 보정한다. "인덱스도 있는데 플래너가 이상한 플랜을 고른다"는 미스터리의 흔한 원인이 바로 이 상관 컬럼 과소추정이다. 단일 컬럼 통계만으로는 컬럼 사이의 상관을 알 수 없기 때문에, 상관된 컬럼 조합으로 자주 필터링한다면 확장 통계를 명시적으로 만들어줘야 한다.

기록된 종속 정도는 카탈로그에서 직접 확인할 수 있다.

SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'geo_stx';

               dependencies
------------------------------------------
 {"2 => 3": 1.000000, "3 => 2": 1.000000}
(1 row)

2 => 3, 3 => 2 모두 정도 1.000000 — 두 컬럼(속성 번호 2번 city, 3번 zip)이 서로 완전히 결정한다는 뜻이다.

이렇게도 쓴다

서로 다른 값 조합의 개수(n-distinct)까지 보정한다 — GROUP BY 추정에 유용. (조합: ndistinct)

CREATE STATISTICS geo_nd (ndistinct) ON city, zip FROM geo;
ANALYZE geo;
-- (city, zip) 조합의 distinct 수를 곱셈 추정 대신 실측으로

 

특정 값 조합의 빈도(MCV, most-common-values)까지 저장해 편향된 분포를 잡는다.

CREATE STATISTICS geo_mcv (mcv) ON city, zip FROM geo;
ANALYZE geo;

 

종류를 지정하지 않으면 세 종류(dependencies, ndistinct, mcv)를 모두 만든다.

CREATE STATISTICS geo_all ON city, zip FROM geo;

 

표현식에도 통계를 건다 — 함수 결과에 대한 추정 보정.

CREATE STATISTICS geo_ex ON lower(city), zip FROM geo;

 

정의한 확장 통계와 수집된 값을 조회한다. (조합: pg_stats_ext)

SELECT statistics_name, kinds, n_distinct, dependencies
FROM pg_stats_ext WHERE tablename = 'geo';

언제 안 쓰나: 컬럼들이 실제로 독립이면 확장 통계는 이득이 없고 ANALYZE만 무거워진다. 필터 조합이 항상 단일 컬럼이거나, 오차가 있어도 플랜이 안 바뀌면 굳이 만들 필요 없다. 확장 통계는 "상관된 컬럼을 AND로 자주 거는데 플랜이 이상할 때" 꺼내는 도구다.