PostgreSQL HASH 집계 처리에 따른 성능 차이

GROUP BY가 포함된 SQL을 처리하기 위해서는 DB 내부적으로 데이터를 집계 처리해야 합니다. 데이터를 집계 처리하는 방식에는 Sort를 사용한 방식과 Hash 함수를 사용한 방식이 있습니다. PostgreSQL 역시 Hash 방식의 집계 처리가 가능합니다. 데이터를 집계 처리하는 과정에서 Sort 방식과 Hash 방식의 성능 차이를 한 번 느껴보기 바랍니다.

아래는 tr_ord_big을 member_id 별로 집계하는 SQL입니다. 일부러 힌트(Set(enable_hashagg off))를 사용해, Hash 집계하지 못하도록 처리했습니다. 실행계획을 보면, 총 49초가 걸렸습니다.

EXPLAIN (ANALYZE, COSTS OFF)
/*+ Set(enable_hashagg off) */
SELECT  t1.member_id ,COUNT(*) cnt
FROM    tr_ord_big t1
WHERE   t1.ord_dtm >= TO_DATE('20200101','YYYYMMDD')
AND     t1.ord_dtm <  TO_DATE('20240101','YYYYMMDD')
GROUP BY t1.member_id
ORDER BY cnt DESC;

QUERY PLAN                                                                                                                                        |
--------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (actual time=49934.606..49935.000 rows=9999 loops=1)                                                                                         |
  Sort Key: (count(*)) DESC                                                                                                                       |
  Sort Method: quicksort  Memory: 775kB                                                                                                           |
  ->  GroupAggregate (actual time=46222.164..49931.320 rows=9999 loops=1)                                                                         |
        Group Key: member_id                                                                                                                      |
        ->  Sort (actual time=46221.910..48440.370 rows=28496394 loops=1)                                                                         |
              Sort Key: member_id                                                                                                                 |
              Sort Method: external merge  Disk: 278920kB                                                                                         |
              ->  Seq Scan on tr_ord_big t1 (actual time=99.887..14073.773 rows=28496394 loops=1)                                                 |
                    Filter: ((ord_dtm >= to_date('20200101'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20240101'::text, 'YYYYMMDD'::text)))|
                    Rows Removed by Filter: 2762080                                                                                               |
Planning Time: 0.455 ms                                                                                                                           |
Execution Time: 49989.435 ms    
SQL
복사

위의 실행계획을 보면, Sort까지 48초가 걸렸습니다. 그 이전 단계인 Seq Scan(TABLE FULL SCAN)은 14초의 시간이 걸렸습니다. 그러므로 Seq Scan으로 데이터를 찾고, 정렬하는 과정에 48 - 14인 34초가 걸렸다고 할 수 있습니다. 2천8백만 건의 데이터를 정렬하는 과정에서 메모리 공간이 부족해 디스크 공간을 사용했기 때문에 오래 걸렸다고 볼 수 있습니다.(external merge Disk: 278920kB 를 통해 알 수 있습니다.)

이제, HASH 함수를 사용한 데이터 집계가 가능하도록 힌트를 제거하고 SQL을 실행해봅니다. 아래와 같습니다. 총 실행 시간이 17.9초로 개선되었습니다.

EXPLAIN (ANALYZE, COSTS OFF)
SELECT  t1.member_id ,COUNT(*) cnt
FROM    tr_ord_big t1
WHERE   t1.ord_dtm >= TO_DATE('20200101','YYYYMMDD')
AND     t1.ord_dtm <  TO_DATE('20240101','YYYYMMDD')
GROUP BY t1.member_id
ORDER BY cnt DESC;

QUERY PLAN                                                                                                                                  |
--------------------------------------------------------------------------------------------------------------------------------------------+
Sort (actual time=17939.048..17939.565 rows=9999 loops=1)                                                                                   |
  Sort Key: (count(*)) DESC                                                                                                                 |
  Sort Method: quicksort  Memory: 775kB                                                                                                     |
  ->  HashAggregate (actual time=17935.269..17936.417 rows=9999 loops=1)                                                                    |
        Group Key: member_id                                                                                                                |
        Batches: 1  Memory Usage: 1169kB                                                                                                    |
        ->  Seq Scan on tr_ord_big t1 (actual time=70.917..13631.339 rows=28496394 loops=1)                                                 |
              Filter: ((ord_dtm >= to_date('20200101'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20240101'::text, 'YYYYMMDD'::text)))|
              Rows Removed by Filter: 2762080                                                                                               |
Planning Time: 0.177 ms                                                                                                                     |
Execution Time: 17940.747 ms                                                             
SQL
복사

실행계획을 보면, SeqScan까지 13초, 데이터를 집계 처리한 HashAggregate까지 17초가 걸린 것을 알 수 있습니다. 대량의 데이터를 집계 처리해야 한다면 Sort 방식보다는 Hash 방식이 월등히 좋은 것을 알 수 있습니다.

여기서는 성능 차이를 살펴보기 위해 일부러 힌트로 hashagg 기능을 OFF해서 테스트를 수행했습니다. 기본적으로 hashagg가 ON 되어 있다면, PG가 적절한 방식을 잘 선택해서 사용할 것입니다. 아래 SQL로 hashagg 기능의 활성여부를 알 수 있습니다.

SELECT  *
FROM    pg_settings t1
WHERE   t1.name like '%hashagg%';
SQL
복사

이상입니다.