질문에 대한 답변
PostgreSQL은 pg_statistic에 열별 히스토그램을 유지하고 다중 열 필터에 대한 선택성을 추정할 때 열 간의 통계적 독립성을 가정합니다. city와 state_code 또는 차량 데이터베이스에서 make와 model과 같이 강한 상관 관계가 있는 열에서 필터링할 때, 플래너는 개별 선택성을 곱하여 실제 행 수를 극단적으로 과소 추정합니다. 이 기수 오류는 종종 옵티마이저가 Nested Loop 조인을 Hash 또는 Merge 조인보다 선호하게 만들어 대규모 테이블에서 치명적인 성능 저하를 초래합니다.
이를 해결하기 위해 CREATE STATISTICS를 사용하여 확장 통계 객체를 생성할 수 있으며, 이는 다변량 상관 데이터 구축을 수행합니다. 구체적으로, dependencies 유형은 열 간의 함수적 종속성을 추적하여 플래너가 state_code = 'CA'로 필터링하면 이미 city가 캘리포니아 값으로 제한됨을 인식하여 곱셈 오류를 피할 수 있도록 합니다.
-- 상관된 열을 위한 확장 통계 생성 CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- 통계 채우기 ANALYZE vehicles;
실생활의 상황
물류 플랫폼은 customers와 조인된 5000만 행의 shipments 테이블에 대한 배송 추적 대시보드에서 어려움을 겪었습니다. 쿼리는 origin_state와 origin_city로 필터링되었고, 'Springfield'라는 도시의 95%의 행이 실제로 'IL'에 있었으나 플래너는 두 조건이 독립적으로 일치하는 배송은 2%뿐이라고 가정했습니다. 이는 500행으로 추정되어 Nested Loop 조인을 선택했으며, 수백만 개의 고객 레코드를 반복하다가 90초 후에 타임아웃되었습니다.
고려된 해결책 중 하나는 세션에서 SET enable_nestloop = off를 통해 Nested Loop 조인을 완전히 비활성화하는 것이었습니다. 이는 Hash Join을 강제하여 이 특정 쿼리에 대해 3초 만에 완료되었으나 심각한 위험이 따랐습니다: 전역 설정 변경은 연결 풀에 걸쳐 전파되어, 인덱스 조회가 잘 작동하는 작은 테이블에 대한 다른 합법적인 Nested Loop 계획을 후퇴시킬 수 있습니다. 또한 이 우회 방식은 쿼리 전에 매개변수를 설정하기 위해 애플리케이션 레벨 코드 변경을 요구했습니다.
또 다른 옵션은 (origin_city, origin_state)에 대한 복합 인덱스를 생성하는 것이었습니다. 인덱스 선택은 개선되었지만, 기수 과소 추정 문제는 해결되지 않았습니다; 플래너는 여전히 인덱스 스캔에서 몇 개의 행이 나올 것이라고 생각하고 Nested Loop 전략을 유지했으며, 단지 커버링 인덱스를 통해 더 빨리 실행될 뿐이었습니다. 또한, 넓은 복합 인덱스는 4GB의 추가 디스크 공간을 소모하고 빠른 흐름의 shipments 테이블에서 쓰기 작업을 느리게 했습니다.
팀은 궁극적으로 CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments를 실행한 후 ANALYZE하여 확장 통계를 배포했습니다. 이 접근 방식은 쿼리 재작성 없이 거의 무시할 수 있는 스토리지 오버헤드를 추가했습니다. 배포 후, 플래너는 45,000행을 정확히 추정하고 Hash Join을 선택하여 쿼리 지연 시간을 400 밀리초로 줄였으며 무관한 작업에 대한 최적의 계획을 유지했습니다.
후보자들이 자주 놓치는 점
ANALYZE 명령은 확장 통계를 어떻게 새로 고치며, 왜 통계 객체가 생성 직후에 사용되지 않는 것처럼 보일 수 있나요?**
ANALYZE는 타겟 테이블에서 명시적으로 호출되거나 통계 객체가 존재한 후 자동 진공이 테이블을 처리할 때만 확장 통계를 계산합니다. 많은 후보자들은 CREATE STATISTICS가 즉시 계획에 영향을 미친다고 가정하지만, 카탈로그 테이블 pg_statistic_ext와 pg_statistic_ext_data는 다음 분석 사이클까지 비어 있습니다. 결과적으로 플래너는 ANALYZE shipments;가 다변량 데이터를 채우기 전까지 단일 열 히스토그램과 독립성 가정을 계속 사용합니다. 비어 있지 않은 dependencies 또는 ndistinct 값을 위한 pg_stats_ext 보기를 확인하여 사용을 검증할 수 있습니다.
CREATE STATISTICS에서 dependencies와 ndistinct의 기능적인 차이는 무엇이며, 각 쿼리 패턴이 이점은 무엇인가요?
Dependencies는 한 열이 다른 열을 결정하는 함수적 관계를 캡처하여 WHERE 절 선택성 추정치를 직접 수정합니다. Ndistinct는 열 그룹의 고유 조합 수를 정확히 계산하여 GROUP BY 및 DISTINCT 추정치를 개선합니다. 많은 후보자들이 이를 혼동하여 느린 쿼리에 GROUP BY가 포함된 관계형 열에 대해 dependencies를 생성하거나 그 반대의 경우를 만들어냅니다. 최적의 결과를 위해 두 가지 유형을 모두 지정해야 합니다: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).
왜 확장 통계가 상관된 열 간에 OR 조건을 사용하는 쿼리에 도움이 되지 않을 수 있나요?
확장 통계는 현재 선택성 곱셈이 발생하는 AND 절에서만 도움을 줍니다. OR로 필터링할 때 (예: city = 'Springfield' OR state = 'IL'), PostgreSQL은 선택성을 P(A) + P(B) - P(A ∩ B) 공식을 사용하여 계산하며, 통계가 결합이 아닌 분리형의 조합에 대한 선택성을 추적하므로 교차항에 의존성 계수를 적용할 수 없습니다. 후보자들은 이 제한을 종종 간과하고 OR 기반의 기수 오류를 수정하기 위해 CREATE STATISTICS를 사용하려고 하며, 이는 쿼리 재작성(예: UNION ALL 분기로 나누기) 또는 부분 인덱스가 필요합니다.