CREATE TABLE aggregates_test_table (
id SERIAL PRIMARY KEY,
category INT,
value INT
);
Aggregate
Aggregate는 PostgreSQL에서 SUM(), AVG(), COUNT(), MAX(), MIN()과 같은 집계함수를 실행할 때 주로 사용된다.
데이터 세팅
INSERT INTO aggregates_test_table (category, value)
SELECT (random() * 100000)::int AS category, (random() * 100000)::int AS value
FROM generate_series(1, 100000) s;
이제 실행계획을 살펴보자.
위와 같이 Aggregate 오퍼레이션을 통해서 aggregate_test_table을 Seq Scan으로 Full Scan해서 max값을 찾고 있다.
만약, 인덱스가 있다면 max(), min() 같은 집계함수는 성능 개선이 가능하다.
집계함수 중에서 AVG()와 SUM()같이 데이터를 다 읽어야 하는 경우에는 인덱스를 활용할 수 없지만, MAX(), MIN()과 같은 특정 데이터만 찾는 경우에는 인덱스를 활용할 수 있다.
성능 또한 25ms에서 0.081ms로 빨라진 것을 확인할 수 있다.
HashAggregate
HashAggregate는 PostgreSQL에서 group by와 같은 그룹화 연산을 수행할 때 사용되는 방법 중 하나이다.
HashAggregate는 Group By에 사용되는 칼럼들로 메모리상에 해시테이블을 만들고, 해당 해시테이블을 바탕으로 SUM, COUNT, AVG와 같은 집계 함수를 처리하는 방식이다.
해시 테이블의 크기는 GROUP BY 절에서 사용되는 칼럼의 카디널리티(고윳값의 수)와 데이터의 양에 의해 결정된다.
카디널리티가 낮아야 중복된 값이 많다는 것이기 때문에 HashAggregate에 효율적이다.
메모리 사용량이 설정된 work_mem 한계를 초과하면, 성능 저하가 발생할 수 있다.
HashAggregate는 미리 정렬되어있을 필요가 없고, 너무 크지 않다면 대용량 처리에 유리하다. (너무 크다면 메모리 자원이 한정적이기 때문에 분리하다.)
정렬이 필요없기 때문에 정렬에 소요되는 시간이 절약될 수 있지만, 대용량의 데이터가 카디널리티까지 높아서 해시테이블이 커진다면, HashAggregate는 비효율적일 수 있다.
또한, 정렬된 데이터가 필요 없기 때문에 인덱스가 없는 경우 주로 선택된다.
이제 데이터를 넣고 실행계획을 살펴보자
데이터 세팅
INSERT INTO aggregates_test_table (category, value)
SELECT (random() * 100)::int AS category, (random() * 1000)::int AS value
FROM generate_series(1, 100000) s;
10만건의 데이터를 생성했다. 여기서 category는 100개의 랜덤 한 값으로 생성했다.
따라서, 데이터는 많고 category 칼럼에 카디널리티는 낮은 데이터셋으로 생성했다.
또한, 인덱스도 따로 설정하지 않았다. 인덱스를 설정했다고 해도 카디널리티가 낮기 때문에 인덱스를 타지 않을 것이다.
위 실행계획을 보면 HashAggregate가 선택되었고, category 칼럼을 Group Key로 사용한 것을 확인할 수 있다.
GroupAggregate
GroupAggregate는 HashAggregate과 마찬가지로 그룹화 연산을 수행하는 또다른 방법이다.
GroupAggregate는 GROUP BY에 사용되는 칼럼들로 이미 정렬된 데이터를 기반으로 하여 그룹화를 수행하고, 해당 그룹화된 데이터를 바탕으로 SUM, COUNT, AVG와 같은 집계 함수를 처리하는 방식이다
그렇기 때문에 GroupAggregate는 이미 정렬된 데이터셋이나 적절한 인덱스가 있는 경우에 선택될 때 가장 효과적으로 동작한다.
GroupAggregate는 대량의 데이터를 버퍼링하지 않기 때문에, 메모리에 올리기 너무 큰 데이터의 경우에 HashAggregate 대신 GroupAggregate가 선택되기도 한다.
다시 말해서, GroupAggregate는 현재 처리 중인 그룹에 대한 데이터만 메모리에 유지하기 때문에, 전체적인 메모리 사용량이 HashAggregate에 비해 적을 수 있다.
데이터 세팅
truncate aggregates_test_table;
INSERT INTO aggregates_test_table (category, value)
SELECT (random() * 10000000)::int AS category, (random() * 1000)::int AS value
FROM generate_series(1, 1000000) s;
create index idx_aggregates_test_table_category
on aggregates_test_table(category);
위와 같이, 100만 건의 데이터에 대해서 category 칼럼에 대한 카디널리티가 높게 데이터셋으로 생성했다. (카디널리티 값은 -1에 가까울수록 높디.)
카디널리티를 높게 설정한 이유는 인덱스를 태우기 위함이다.
또한, category에 인덱스를 걸어주었다.
Group이라고 표시된 것이 GroupAggregate를 말한다.
인덱스가 있고 데이터의 크기가 100만 건으로 상당히 크기 때문에 옵티마이저는 HashAggregate 대신 GroupAggregate를 선택했다.
인덱스가 있다고 하더라고 데이터의 크기가 크지 않다면 HashAggregate가 선택
데이터 크기에 따른 옵티마이저의 Aggregate 선택
truncate aggregates_test_table;
INSERT INTO aggregates_test_table (category, value)
SELECT (random() * 1000000000)::int AS category, (random() * 1000)::int AS value
FROM generate_series(1, 138990) s;
analyze aggregates_test_table;
explain (analyze , buffers )
select category
from aggregates_test_table
group by category;
Memory Usage: 10289kB -> 최대 10MB 정도의 메모리를 사용했다는 것을 의미한다.
Disk Usage: 1560kB -> 1560kB의 디스크 사용량은 연산 중 일부 데이터가 메모리 용량을 초과하여 디스크에 임시 저장되었음을 나타낸다.
temp read=108 written=272 -> 임시 디스크 공간에서 데이터를 108번 읽고 272번 썼다는 것을 나타낸다.
메모리 용량을 초과하여 디스크에 임시 저장되었다는 것은 옵티마이저가 HashAggregate 대신 GroupAggregate을 사용하려고 할 수 있다.
데이터 수를 조금만 올려보자.
truncate aggregates_test_table;
INSERT INTO aggregates_test_table (category, value)
SELECT (random() * 1000000000)::int AS category, (random() * 1000)::int AS value
FROM generate_series(1, 139999) s;
analyze aggregates_test_table;
explain (analyze , buffers )
select category
from aggregates_test_table
group by categ
Heap Fetches: 139999 -> 실제 테이블(Heap)에서 139999번의 행을 가져왔다는 것을 의미한다.
shared hit=140358 -> 공유 버퍼 캐시에서 140358번 데이터를 읽어왔음을 의미한다.
위 테스트로 보아, 옵티마이저가 테이터 크기가 어느 임계치를 넘어가면 GroupAggregate를 선택하는 것을 확인할 수 있다.
내가 테스트해보면서 느낀 건, PostgreSQL의 옵티마이저는 메모리에 큰 부하가 없는 경우, HashAggregate를 자주 선택하는 것 같다. (내 예측이다.)
또한, 인덱스가 없는 경우, 메모리에 부하가 커도 GroupAggregate를 선택하는 경우는 거의 보지 못했다.
