ANALYZE 개요
ANALYZE 란?
collect statistics about a database
- PostgreSQL 공식 문서 –
- 데이터베이스에 대한 통계를 수집하고, 시스템 카탈로그에 결과를 저장하는 PostgreSQL의 내장 함수 입니다.
ANALYZE 목
- 시간의 지남에 따라 테이블의 데이터가 변경될 수 있으며 쿼리 플래너에서 사용하는 통계 정보가 최신이 아닐 수 있습니다. 이로 인해 쿼리 실행 플랜이 최적화 되지 않아 쿼리 성능이 저하될 수 있습니다.
- ANALYZE는 쿼리 플래너가 테이블의 데이터에 대한 최신의 통계 정보를 가지고 있는지 확인하여 가장 효율적이고 효과적인 방법으로 쿼리를 실행하는데 도움을 주기 위해 사용합니다.
ANALYZE 문법
ANALYZE [ ( option [, …] ) ] [ table_and_columns [, …] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, …] ]where option can be one of:
VERBOSE [ boolean ] SKIP_LOCKED [ boolean ]and table_and_columns is:
table_name [ ( column_name [, ...] ) ]
- 기본 구조는 ANALYZE 와 ANALYZE 할 대상을 입력합니다.
- table_and_columns 을 사용하면 해당 테이블 또는 열에 대한 통계만 수집됩니다.
- table_and_columns 을 넣지 않고 그냥 ANALYZE 만 작성하면 현재 사용자에게 분석 권한이 있는 현재 데이터베이스의 모든 테이블 및 구체화된 뷰의 통계정보를 수집합니다.
ANALYZE 옵션
| 옵션 명 | 설명 |
|---|---|
| VERBOSE | 진행 메세지를 표시합니다. |
| SKIP_LOCKED | relation에 대한 ANALYZE 작업을 시작할 때 충돌하는 LOCK이 해제될 때까지 기다리지 않도록 지정합니다. |
| boolean | 선택할 옵션을 켜거나 끌지 여부를 지정합니다. 키려면 TRUE, ON, 1 을 사용하고 끄려면 FALSE, OFF, 0 을 사용합니다. |
| table_name | 분석할 특정 table의 이름을 지정합니다. 생략하면 현재 데이터베이스의 모든 일반 테이블, 파티션 테이블 및 구체화된 뷰가 분석됩니다. |
| column_name | 분석할 특정 column의 이름을 지정합니다. |
ANALYZE 특징
- 데이터베이스 소유자는 공유 카탈로그를 제외한 데이터베이스의 모든 테이블을 분석할 수 있고,테이블을 분석하려면 테이블의 소유자 또는 수퍼 유저 여야 합니다.
- 공유 카탈로그에 대한 분석은 수퍼 유저만 할 수 있습니다.
- 분석 권한이 없는 테이블은 모두 건너뛰고 분석합니다.
- ANALYZE는 해당 테이블에 대한 READ LOCK만 필요하므로 병렬로 다른 활동과 실행할 수 있습니다.
- 큰 테이블의 ANALYZE의 경우 모든 행을 검사하는 대신 랜덤 샘플을 추출하여 ANALYZE를 실행합니다. 이를 통해 매우 큰 테이블도 짧은 시간 내에 분석할 수 있습니다.
- 통계는 근사치에 불과하며, 실제 테이블 내용이 변경되지 않았더라도 ANALYZE가 실행될 때마다 약간씩 변경될 수 있습니다. 이로 인해 ANALYZE 이후 쿼리 플랜이 변경될 수 있습니다.
default_statistics_target파라미터를 변경하여 ANALYZE에 의해 수집되는 통계의 양을 늘려 이 문제를 방지할 수 있습니다.
default_statistics_target
- 기본 수집되는 목표 통계 양의 값을 설정하는 파라미터 입니다. 1에서 10000사이의 정수로 설정할 수 있으며 기본값은 100으로 설정됩니다.
- 값이 높을수록 쿼리 플래너가 히스토그램을 만들 때 더 많은 값을 사용하여 더 정확한 쿼리 계획을 생성할 수 있음을 의미합니다.
- 이것은 또한 히스토그램을 저장하는데 더 많은 디스크 공간이 사용되고 히스토그램을 만들고 업데이트 하는 데 더 오래 걸린다는 것을 의미합니다.
- 값이 낮을 수록 히스토그램을 만들 때 더 적은 고유 값을 사용하므로 디스크 공간 사용량이 줄어들고 히스토그램 생성 및 업데이트가 빨라집니다. 그러나 쿼리 플랜이 덜 정확할 수도 있습니다.
- 그렇기 때문에 정확도와 효율성 간의 올바른 균형을 찾아 값을 설정하는 것이 중요합니다.
ANALYZE의 실행 빈도
- ANALYZE를 실행하는 빈도는 데이터베이스의 크기, 데이터 변경 속도 및 시스템 부하를 비롯한 여러 요인에 따라 달라집니다.
- 일반적으로는 다음 시점에 ANALYZE를 실행하는 것을 권장합니다.
- 대량 데이터를 로드 한 후 : 최근에 대량의 데이터를 로드 한 경우 ANALYZE를 실행하여 통계를 업데이트하는 것이 좋습니다.
- 데이터 변경 후 : 데이터베이스의 데이터를 자주 변경하는 경우 통계를 최신 상태로 유지하기 위해 주기적으로 ANALYZE를 실행하는 것이 좋습니다
- 스키마 변경 후 : 컬럼 추가, 제거 또는 수정과 같이 데이터베이스의 스키마를 변경하는 경우 ANALYZE를 실행하여 영향을 받는 테이블의 통계를 업데이트 해야합니다.
- 쿼리 성능이 느린 경우 : 쿼리 성능이 느린 경우 ANALYZE를 실행하여 통계를 업데이트 하면 성능이 개선될 수도 있습니다.
ANALYZE 명령어
ANALYZE 사용법
- 데이터베이스, 테이블, 컬럼 레벨로 통계 정보 생성 가능합니다.
- 데이터베이스 ANALYZE
ANALYZE - 단일 테이블 ANALYZE ANALYZE [table_name]
- 여러 테이블 ANALYZE ANALYZE [table_name]
- 컬럼 레벨 ANALYZE ANALYZE [column_name]
ANALYZE 예시
- 테스트 테이블 생성
CREATE TABLE test1 AS
SELECT
gs as idx,
‘TEST STRING ‘ || gs AS test_string,
md5(random()::text) AS random_string
FROM
generate_series(1,10) AS gs ;CREATE TABLE test2 AS
SELECT
gs as idx,
‘TEST STRING ‘ || gs AS test_string,
md5(random()::text) AS random_string
FROM
generate_series(1,10) AS gs ;
- ANALYZE 실행
데이터베이스 ANALYZE
postgres=# ANALYZE;
단일 테이블 ANALYZE
postgres=# ANALYZE test1;
상세 정보 출력 하며 여러 테이블 ANALYZE
postgres=# ANALYZE VERBOSE test1, test2;
INFO: analyzing “public.test1”
INFO: “test1”: scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
INFO: analyzing “public.test2”
INFO: “test2”: scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
ANALYZE
칼럼 레벨 ANALYZE
postgres=# ANALYZE test1(test_string);
통계 정보 확인
- test1, test2 테이블에 ANALYZE를 실행합니다.
postgres=# ANALYZE VERBOSE test1, test2;
- test1의 idx 컬럼에 대한 통계를 확인합니다.
postgres=# x
postgres=# SELECT * FROM pg_stats WHERE tablename = ‘test1’ AND attname = ‘idx’;
-[ RECORD 1 ]———-+———————–
schemaname | public
tablename | test1
attname | idx
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,2,3,4,5,6,7,8,9,10}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
- test1 테이블의 random_string 컬럼에 대한 통계 확인
postgres=# SELECT * FROM pg_stats WHERE tablename = ‘test2’ AND attname = ‘random_string’;
-[ RECORD 1 ]———-+————————————————————————————————————schemaname | public
tablename | test2
attname | random_string
inherited | f
null_frac | 0
avg_width | 33
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1886c7bf46c351367668274a27740dc0,341aebd65cd88a2be497c3fc760d6ccb,3eb870c84f91791cee42533d08ed29c6,4acfbcf420e6eef6e002e09a642bcdd4,66007e6d88e6c0831115927cdd207454,6ddee86e88ff50246cfd5d3b64c84ba2,7b479393939d94811c00117bfa1293a8,848d05aca5efbafd4f4f843c97c8b482,9b1d29b91d05a493ddf9f606450c9de3,d9d307203327a1f93f6eb02b17705b7b}
correlation | -0.16363636
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
- test1 테이블의 random_string 컬럼에 대한 히스토그램을 확인합니다.
postgres=# SELECT histogram_bounds FROM pg_stats WHERE tablename = ‘test1’ AND attname = ‘test_string’;
-[ RECORD 1 ]—-+——————————————————————————————————————————————————————-
histogram_bounds | {“TEST STRING 1″,”TEST STRING 10″,”TEST STRING 2″,”TEST STRING 3″,”TEST STRING 4″,”TEST STRING 5″,”TEST STRING 6″,”TEST STRING 7″,”TEST STRING 8″,”TEST STRING 9”}
지금까지 PostgreSQL의 ANALYZE에 관해 알아보았습니다
‘PostgreSQL의 bucardo’를 바로 이어서 확인해보세요!
