소프트 파싱을 지원하기 위해 실행 계획이 저장되는 메모리 영역은 임시 계획 캐시(Plan Cache) 또는 SQL 캐시라고 부릅니다.
PostgreSQL은 주로 다이나믹 파라미터화된 쿼리를 사용하여 실행 계획을 캐싱합니다.
통계 수집 명령 :
EXPLAIN은 쿼리의 실행 계획을 보여줍니다. 실제로 쿼리를 실행하지 않고, 실행 계획만을 반환합니다.
EXPLAIN SELECT * FROM table_name;
EXPLAIN ANALYZE는 실제로 쿼리를 실행하고, 실행 계획과 함께 실행 시간 및 실제 작업 통계를 반환합니다.
EXPLAIN ANALYZE SELECT * FROM table_name;
ANALYZE table_name;
ANALYZE table_name (column_name);
PostgreSQL에서는 자동으로 통계를 수집하는 자동 VACUUM 및 자동 ANALYZE 기능이 활성화되어 있습니다.
데이터가 변경될 때 자동으로 통계를 업데이트할 수 있도록 설정되어 있으며, 이를 조정하려면 설정 파일(postgresql.conf)에서 관련 파라미터를 수정할 수 있습니다.
autovacuum: 자동으로 VACUUM 및 ANALYZE를 수행하는 기능
autovacuum_analyze_threshold: 테이블에 대해 자동 분석이 실행되기 위한 최소 행 변경 수
autovacuum_analyze_scale_factor: 행 변경에 기반한 통계 수집 비율
pg_stat_all_tables 뷰를 사용하여 테이블별 통계 정보를 확인할 수 있습니다.
SELECT * FROM pg_stat_all_tables WHERE schemaname = 'your_schema' AND relname = 'your_table';
PostgreSQL의 옵티마이저는 비용 기반(Cost-Based)으로 작동합니다.
Access Method :
Sequential Scan : 테이블의 모든 행을 순차적으로 읽는 방법입니다. 작은 테이블이나 조건이 거의 없는 쿼리에 적합합니다.
Index Scan : 인덱스를 사용하여 특정 조건에 맞는 데이터만 조회하는 방법입니다. 큰 테이블에 유효한 인덱스가 있을 때 사용됩니다.
Bitmap Index Scan : 여러 인덱스나 큰 테이블에서의 검색 성능을 개선하기 위해 사용되는 기법입니다. 조건이 여러 개일 때 유용합니다.
Nested Loop Join : 작은 테이블과 큰 테이블 간의 조인을 수행할 때 사용됩니다. 작은 테이블에서 하나씩 읽으며 큰 테이블에서 검색합니다.
Hash Join : 해시 테이블을 사용하여 조인을 처리하는 방식입니다. 특히 큰 테이블 간의 조인에서 사용됩니다.
Merge Join : 정렬된 두 테이블을 병합하여 조인하는 방법으로, 특정 상황에서 효율적입니다.