데이터베이스 작업에서 GROUP BY 절의 성능은 대규모 데이터셋에서 중요한 영향을 미칩니다. PostgreSQL은 데이터를 그룹화하거나 정렬할 때, 정렬 순서에 따라 쿼리의 속도와 자원 소모량이 달라질 수 있습니다.
통계를 활용한 GROUP BY 절 순서 최적화
PostgreSQL 사용자는 종종 데이터를 다양한 기준으로 정렬하고 그룹화하는 분석 쿼리를 사용합니다. 이러한 연산자를 최적화하면 쿼리 실행 시간과 비용을 크게 줄일 수 있습니다. 이번 포스트에서는 GROUP BY 표현식에서 열의 순서를 선택하는 최적화 방법에 대해 다룹니다.
PostgreSQL은 이미 ORDER BY 조건에 따라 그룹화된 표현식 리스트를 재정렬하여 추가 정렬 작업을 제거하고 계산 리소스를 절약할 수 있습니다.
문제의 간단한 소개
DBMS는 일반적으로 한 개 이상의 열로 테이블 데이터를 그룹화하기 위해 해싱(HashAgg) 방법 또는 사전 정렬 후 순차적으로 집계하는 방법(SortAgg)을 사용합니다. 여러 열을 기준으로 데이터를 정렬할 때, PostgreSQL은 각 값 쌍마다 비교 연산자를 호출해야 합니다.
예를 들어, ('UserX1', 'Saturday', $100)라는 행과 ('UserX1', 'Monday', $10)라는 행을 비교한다고 가정해 봅시다. 첫 번째 값이 동일하면 두 번째 값을 비교하고, 두 번째 값이 다르면 세 번째 값을 비교할 필요가 없습니다.
제안된 SortAgg 최적화 메커니즘은 바로 이러한 원칙을 기반으로 합니다. 비교 시 중복값이 적은 열(예: UserID)을 먼저 비교하면 비교 연산자 호출 횟수가 줄어들어 성능이 향상됩니다.
PostgreSQL의 GROUP BY 동작 방식
PostgreSQL은 데이터를 그룹화할 때 다음 두 가지 주요 집계 방법을 사용합니다:
Hash Aggregate (해시 집계)
- 데이터 그룹별로 고유 키를 해시 테이블에 저장.
- 메모리 사용량은 많지만 데이터 정렬이 필요하지 않을 경우 유리.
Sort Aggregate (정렬 집계)
- 데이터를 정렬 후 그룹별로 순차 처리.
- 비교 연산자 호출 횟수에 따라 성능이 크게 좌우됨.
정렬 순서와 성능의 관계
PostgreSQL에서 데이터를 정렬하거나 그룹화할 때는 여러 열에 걸쳐 값을 비교합니다.
다음과 같은 순서를 따릅니다:
- 첫 번째 열의 값 비교.
- 첫 번째 열의 값이 같으면 두 번째 열 비교.
- 계속 같을 경우, 이후 열 순서대로 비교.
이 과정에서 고유 값(카디널리티, Cardinality)가 높은 열을 먼저 비교하면, 불필요한 비교 연산이 줄어들고 쿼리 속도가 개선됩니다.
카디널리티(Cardinality)란?
카디널리티는 한 열에 존재하는 서로 다른 고유 값의 개수를 의미합니다.
- CustomerId: 고유 값이 많음 → 높은 카디널리티.
- WeekDay: 고유 값이 7개(요일) → 낮은 카디널리티.
고유 값이 많을수록, 정렬이나 비교 과정에서 일찍 차이가 발생해 성능이 좋아집니다.
실험: 열 순서 변경으로 인한 성능 차이
CREATE TABLE shopping (
CustomerId bigint, CategoryId bigint, WeekDay text, Total money
);
INSERT INTO shopping (CustomerId, CategoryId, WeekDay, Total)
SELECT random()*1E6, random()*100,
'Day ' || (random()*7)::integer,
random()*1000::money
FROM generate_series(1, 1E6) AS gs;
VACUUM ANALYZE shopping;쿼리 1: 카디널리티가 낮은 열부터 정렬
WeekDay(고유 값 7개)부터 정렬.
- 쿼리
EXPLAIN (ANALYZE, TIMING OFF)
SELECT CustomerId, CategoryId, WeekDay, Total
FROM shopping
ORDER BY WeekDay, Total, CategoryId, CustomerId;- 결과
Sort (cost=124363.84..126863.84 rows=1000000 width=30) (actual rows=1000000 loops=1)
Sort Key: weekday, total, categoryid, customerid
Sort Method: quicksort Memory: 102702kB
-> Seq Scan on shopping (cost=0.00..24706.00 rows=1000000 width=30) (actual rows=1000000 loops=1)
Planning Time: 1.699 ms
Execution Time: 1772.805 ms쿼리 2: 카디널리티가 높은 열부터 정렬
CustomerId(고유 값 많음)부터 정렬.
- 쿼리
EXPLAIN (ANALYZE, TIMING OFF)
SELECT CustomerId, CategoryId, WeekDay, Total
FROM shopping
ORDER BY CustomerId, CategoryId, WeekDay, Total;
- 결과
Sort (cost=124363.84..126863.84 rows=1000000 width=30) (actual rows=1000000 loops=1)
Sort Key: customerid, categoryid, weekday, total
Sort Method: quicksort Memory: 102702kB
-> Seq Scan on shopping (cost=0.00..24706.00 rows=1000000 width=30) (actual rows=1000000 loops=1)
Planning Time: 0.197 ms
Execution Time: 466.041 ms비교 결과
- 카디널리티가 낮은 열 우선: 1.699 ms
- 카디널리티가 높은 열 우선: 0.197 ms
GROUP BY 절 최적화
기존 GROUP BY
CustomerId와 WeekDay의 순서가 고정되어 있음.
SELECT CustomerId, WeekDay, SUM(Total)
FROM shopping
GROUP BY CustomerId, WeekDay;최적화된 GROUP BY
카디널리티 높은 열 CustomerId를 먼저 그룹화.
결과적으로 더 적은 연산으로 그룹화 가능.
SELECT CustomerId, WeekDay, SUM(Total)
FROM shopping
GROUP BY WeekDay, CustomerId;PostgreSQL 통계 활용
PostgreSQL은 pg_statistic 테이블에 저장된 통계를 사용하여 최적화합니다:
- ndistinct: 열의 고유 값 개수를 나타냄.
- 히스토그램 분포 정보: 열 값의 분포도를 보여줌.
통계 확인
SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'shopping';결과 예시
| 컬럼명 | 값 |
|---|---|
| customerid | -0.511195 |
| categoryid | 101.0 |
| weekday | 8.0 |
| total | 92032.0 |
PostgreSQL의 미래: 동적 최적화
- PostgreSQL 17 버전에서는 GROUP BY 최적화를 자동화할 계획.
- 쿼리 실행 계획 단계에서 통계 기반으로 열 순서를 자동 재배열하여 최적화.
결론 및 실전 활용 팁
- 카디널리티 높은 열 우선
- GROUP BY나 ORDER BY에서 카디널리티 높은 열을 먼저 정렬하도록 쿼리 설계.
- PostgreSQL 실행 계획 분석
- EXPLAIN (ANALYZE) 명령으로 쿼리 성능을 정밀 분석.
- 정기적인 통계 업데이트
- ANALYZE 명령을 주기적으로 실행해 최신 통계 유지.
- 최신 PostgreSQL 기능 사용
- PostgreSQL 17 버전에서 제공될 자동 최적화 기능 활용.
효율적인 쿼리 최적화는 CPU 자원을 절약하고 대규모 데이터셋에서 놀라운 성능 향상을 가져옵니다. 여러분의 PostgreSQL 환경에서도 통계를 활용한 열 순서 최적화를 시도해 보세요!
