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 [, ...] ) ]
SQL
복사
•
기본 구조는 ANALYZE 와 ANALYZE 할 대상을 입력합니다.
◦
table_and_columns 을 사용하면 해당 테이블 또는 열에 대한 통계만 수집됩니다.
◦
table_and_columns 을 넣지 않고 그냥 ANALYZE 만 작성하면 현재 사용자에게 분석 권한이 있는 현재 데이터베이스의 모든 테이블 및 구체화된 뷰의 통계정보를 수집합니다.
ANALYZE 옵션
ANALYZE 특징
•
데이터베이스 소유자는 공유 카탈로그를 제외한 데이터베이스의 모든 테이블을 분석할 수 있고,테이블을 분석하려면 테이블의 소유자 또는 수퍼 유저 여야 합니다.
◦
공유 카탈로그에 대한 분석은 수퍼 유저만 할 수 있습니다.
◦
분석 권한이 없는 테이블은 모두 건너뛰고 분석합니다.
•
ANALYZE는 해당 테이블에 대한 READ LOCK만 필요하므로 병렬로 다른 활동과 실행할 수 있습니다.
•
큰 테이블의 ANALYZE의 경우 모든 행을 검사하는 대신 랜덤 샘플을 추출하여 ANALYZE를 실행합니다. 이를 통해 매우 큰 테이블도 짧은 시간 내에 분석할 수 있습니다.
◦
통계는 근사치에 불과하며, 실제 테이블 내용이 변경되지 않았더라도 ANALYZE가 실행될 때마다 약간씩 변경될 수 있습니다. 이로 인해 ANALYZE 이후 쿼리 플랜이 변경될 수 있습니다.
◦
default_statistics_target 파라미터를 변경하여 ANALYZE에 의해 수집되는 통계의 양을 늘려 이 문제를 방지할 수 있습니다.
default_statistics_target
•
기본 수집되는 목표 통계 양의 값을 설정하는 파라미터 입니다. 1에서 10000사이의 정수로 설정할 수 있으며 기본값은 100으로 설정됩니다.
•
값이 높을수록 쿼리 플래너가 히스토그램을 만들 때 더 많은 값을 사용하여 더 정확한 쿼리 계획을 생성할 수 있음을 의미합니다.
◦
이것은 또한 히스토그램을 저장하는데 더 많은 디스크 공간이 사용되고 히스토그램을 만들고 업데이트 하는 데 더 오래 걸린다는 것을 의미합니다.
•
값이 낮을 수록 히스토그램을 만들 때 더 적은 고유 값을 사용하므로 디스크 공간 사용량이 줄어들고 히스토그램 생성 및 업데이트가 빨라집니다. 그러나 쿼리 플랜이 덜 정확할 수도 있습니다.
•
그렇기 때문에 정확도와 효율성 간의 올바른 균형을 찾아 값을 설정하는 것이 중요합니다.
ANALYZE의 실행 빈도
•
ANALYZE를 실행하는 빈도는 데이터베이스의 크기, 데이터 변경 속도 및 시스템 부하를 비롯한 여러 요인에 따라 달라집니다.
•
일반적으로는 다음 시점에 ANALYZE를 실행하는 것을 권장합니다.
1.
대량 데이터를 로드 한 후 : 최근에 대량의 데이터를 로드 한 경우 ANALYZE를 실행하여 통계를 업데이트하는 것이 좋습니다.
2.
데이터 변경 후 : 데이터베이스의 데이터를 자주 변경하는 경우 통계를 최신 상태로 유지하기 위해 주기적으로 ANALYZE를 실행하는 것이 좋습니다
3.
스키마 변경 후 : 컬럼 추가, 제거 또는 수정과 같이 데이터베이스의 스키마를 변경하는 경우 ANALYZE를 실행하여 영향을 받는 테이블의 통계를 업데이트 해야합니다.
4.
쿼리 성능이 느린 경우 : 쿼리 성능이 느린 경우 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 ;
SQL
복사
•
ANALYZE 실행
◦
데이터베이스 ANALYZE
postgres=# ANALYZE;
SQL
복사
◦
단일 테이블 ANALYZE
postgres=# ANALYZE test1;
SQL
복사
◦
상세 정보 출력 하며 여러 테이블 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
SQL
복사
◦
칼럼 레벨 ANALYZE
postgres=# ANALYZE test1(test_string);
SQL
복사
통계 정보 확인
•
test1, test2 테이블에 ANALYZE를 실행합니다.
postgres=# ANALYZE VERBOSE test1, test2;
SQL
복사
•
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 |
SQL
복사
•
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 |
SQL
복사
•
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"}
SQL
복사
지금까지 PostgreSQL의 ANALYZE에 관해 알아보았습니다
‘PostgreSQL의 bucardo’를 바로 이어서 확인해보세요!
bucardo.png%26blockId%3D9499ff0e-5f0e-4d8e-8d63-cfda06d96237%26width%3D2400&blockId=5beeb276-9f1b-43a3-8c95-54310f27ca73&width=512)