[PostgreSQL] 쿼리 최적화 - ANALYZE

ANALYZE 명령어는 PostgreSQL에서 통계 정보를 수집하여 쿼리 최적화에 필요한 정보를 갱신하는 데 사용됩니다. PostgreSQL의 쿼리 플래너는 쿼리를 최적화할 때 테이블과 열에 대한 통계 정보를 사용하므로, 정확한 통계 정보는 효율적인 쿼리 실행 계획을 수립하는 데 매우 중요합니다.

1. ANALYZE 명령어의 기능

• 테이블에 있는 각 열의 분포, 데이터 양, NULL 비율, 고유 값 수 등의 통계를 수집합니다.
• 수집된 통계는 pg_statistic 시스템 테이블에 저장되며, 쿼리 플래너가 이를 참조하여 최적의 실행 계획을 생성합니다.
• 테이블에 많은 데이터 변경(삽입, 업데이트, 삭제)이 발생한 후 ANALYZE를 실행하면 최신 통계 정보로 쿼리 성능을 개선할 수 있습니다.

2. 기본 사용법

전체 데이터베이스에 대해 실행

ANALYZE;

• 데이터베이스 내의 모든 테이블에 대해 통계를 수집합니다.

특정 테이블에 대해 실행

ANALYZE my_table;

• my_table에 대한 통계만 수집합니다.

특정 열에 대해 실행

ANALYZE my_table(my_column);

• my_table의 특정 열 my_column에 대한 통계만 수집합니다.

VERBOSE 옵션

ANALYZE VERBOSE;

• 실행 중인 테이블과 수집된 통계 정보를 자세히 출력합니다.

3. 자동 ANALYZE

PostgreSQL은 자동 ANALYZE 기능을 지원합니다. 자동 VACUUM과 함께 백그라운드 프로세스가 일정 주기마다 실행되며, 설정 파일(postgresql.conf)에서 다음 파라미터로 제어할 수 있습니다:

• autovacuum_analyze_threshold: 자동 ANALYZE가 실행되기 위한 최소 변경된 튜플 수.
• autovacuum_analyze_scale_factor: 테이블 크기에 비례하여 자동 ANALYZE가 실행되는 비율.

4. 예시

테이블 변경 후 통계 수집

CREATE TABLE orders (id SERIAL, customer_id INT, order_date DATE);
INSERT INTO orders (customer_id, order_date)
SELECT generate_series(1, 1000), now() + (random() * 100)::INT * '1 day'::INTERVAL;

-- ANALYZE 실행 전, 쿼리 플래너는 부정확한 실행 계획을 수립할 수 있음
ANALYZE orders;

-- ANALYZE 실행 후, 쿼리 플래너가 최신 통계 정보를 사용하여 최적의 실행 계획 수립

5. 주의사항

• ANALYZE는 데이터를 정렬하거나 복사하지 않기 때문에 빠르게 실행됩니다.
• 통계 수집은 쿼리 실행 속도에 직접적인 영향을 미치므로 주기적으로 실행하거나 자동 ANALYZE 설정을 적절하게 유지하는 것이 좋습니다.
• 매우 큰 테이블에서는 ANALYZE가 오래 걸릴 수 있으므로, 필요한 열만 지정하는 것이 유리할 수 있습니다.

6. 참고 자료

• PostgreSQL 공식 문서: ANALYZE 명령어