PostgreSQL 쿼리 플래너 비용 파라미터 조정

개요

쿼리 플래너는 실행 계획을 선택할 때 비용(cost)을 기준으로 판단한다. 이 비용은 실제 I/O와 CPU 사용을 추정한 값이다. 따라서 비용 모델의 파라미터를 잘 설정하면 더 현실적인 계획을 얻을 수 있다. 본문에서는 planner cost postgres 조정 관점에서 주요 파라미터와 검증 방법을 설명한다.

비용 모델의 핵심 파라미터

postgres 쿼리 비용 파라미터는 여러 항목으로 구성된다. 중요한 것부터 이해하면 조정이 수월하다.

주요 항목

• random_page_cost: 랜덤 페이지 접근 비용. 디스크가 느리거나 캐시가 작으면 값이 크다.
• seq_page_cost: 순차적 페이지 접근 비용. 일반적으로 1.0을 기준으로 사용한다.
• cpu_tuple_cost: 튜플(행) 처리에 필요한 CPU 비용. 튜플 필터링, 복사 비용 등을 포함한다.
• cpu_index_tuple_cost: 인덱스 튜플 처리를 위한 CPU 비용.
• cpu_operator_cost: 연산자(비교, 산술) 실행 비용.
• effective_cache_size: 오퍼레이팅 시스템과 PostgreSQL이 활용할 수 있는 캐시 크기 추정값.

측정과 검증의 원칙

조정은 추정이 아닌 측정 기반으로 진행해야 한다. 다음 절차를 권장한다.

• 기준값 수집: 변경 전 중요한 쿼리들에 대해 EXPLAIN ANALYZE로 실행 계획과 실제 시간을 기록한다.
• 파라미터 변경: 세션 단위로 먼저 설정하여 전체 시스템 영향 최소화.
• 재측정: 동일 워크로드로 다시 EXPLAIN ANALYZE를 수행해 차이를 비교.
• 반복 및 검증: 여러 케이스(대용량, 소용량, 인덱스 의존 쿼리 등)에서 반복한다.

설정 방법 예시

세션 단위로 실험할 때는 SET 명령을 사용한다. 파일 수정은 신중히 검토한 뒤 적용한다.

-- 세션 단위 변경 예시
SET random_page_cost = 2.0;
SET cpu_tuple_cost = 0.01;
SET effective_cache_size = '8GB';

-- EXPLAIN 분석
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;

cpu_tuple_cost 튜닝 전략

postgres cpu_tuple_cost 튜닝은 CPU 성능과 쿼리 특성에 따라 효과가 달라진다. 주로 튜플 필터링이 많은 쿼리에서 영향을 크게 받는다.

절차

• 현재 값 확인: SHOW cpu_tuple_cost;
• 기준 수집: 비용에 민감한 쿼리들을 EXPLAIN ANALYZE로 기록;
• 작게 변경: 예를 들어 0.01 → 0.005 또는 0.02로 단계적 변경;
• 비교 분석: 실행 시간, I/O, 계획 선택 변화를 확인;
• 환경 반영: 실서비스에서는 운영 시간대에 충분한 테스트 후 postgresql.conf 반영.

-- cpu_tuple_cost 실험 세션
SHOW cpu_tuple_cost;
SET cpu_tuple_cost = 0.005;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE col LIKE '%value%';

실무에서 주의할 점

몇 가지 주의사항을 기억해야 한다.

• 단일 파라미터만 변경하면 다른 파라미터와 상호작용으로 의도치 않은 결과가 발생할 수 있다.
• effective_cache_size는 시스템 전체 캐시 사용량을 추정한 값이다. 과도한 설정은 비현실적 추정을 초래한다.
• 하드웨어(특히 디스크, NVMe, 메모리) 변경 후에는 비용 파라미터 재검증이 필요하다.
• 복잡한 쿼리 집합에 대해 전체적인 벤치마크를 수행하고, 대표 쿼리에 중점적으로 검증한다.

예제: 랜덤 I/O 비용 조정으로 인덱스 사용 유도

디스크가 빠르면 random_page_cost를 낮추면 인덱스 스캔 선택 확률이 올라간다. 반대로 느리면 인덱스 회피가 바람직하다.

-- 인덱스 선호를 테스트
SET random_page_cost = 1.1; -- SSD 환경 가정
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE order_date > '2025-01-01';

SET random_page_cost = 4.0; -- 느린 디스크 환경 가정
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE order_date > '2025-01-01';

결론

planner cost postgres 조정은 단일 설정 변경이 아닌 측정 기반의 반복적 작업이다. postgres 쿼리 비용 파라미터들을 이해하고 단계적으로 조정하면 더 현실적인 계획을 얻을 수 있다. cpu_tuple_cost 튜닝은 CPU 바운드 쿼리에 특히 효과적이며, 변경 시에는 EXPLAIN ANALYZE로 반드시 검증해야 한다.