PostgreSQLバージョン13新機能のディスクベース・ハッシュ集約で遅くなるケースについて

これは、PostgreSQL Advent Calendar 2020の7日目の記事です。

PostgreSQLバージョン13の新機能として、ディスクベース・ハッシュ集約がサポートされました¹。これにより、バージョン12以前では(メモリ不足などの理由でハッシュ集約を選択できず)グループ集約が選択されていたクエリについて、バージョン13以降ではハッシュ集約がプランとして選択される可能性が出てきました。今回は、このバージョン間のプラン選択の違いにより性能劣化するケースについて簡易検証します。

集約結果件数が多いときに性能劣化するケース

大量レコードに対して、重複の少ないキーを使ってGROUP BYすると集約結果件数も多くなり、ディスクベース・ハッシュ集約が選択されて、性能劣化するケースがあります。以下は、バージョン12と13で、キーが10件ずつ重複する合計1千万件のレコードに対してGROUP BYしたとき (集約結果件数は100万) のプランと実行時間です。

• テーブル作成

CREATE UNLOGGED TABLE test01000000 (aid BIGINT, bid BIGSERIAL) WITH (autovacuum_enabled = off);
INSERT INTO test01000000 (aid) SELECT num FROM generate_series(1, 1000000) num, generate_series(1, 10);
VACUUM (FREEZE on, ANALYZE on) test01000000;
CHECKPOINT;

• バージョン12でのクエリ実行

=# SET max_parallel_workers_per_gather TO 0;  -- パラレルクエリ無効化のため
=# SET work_mem TO '16MB';
=# EXPLAIN ANALYZE SELECT aid, count(*) FROM test01000000 GROUP BY aid;

                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1453477.19..1538505.55 rows=1002705 width=16) (actual time=2303.302..4441.925 rows=1000000 loops=1)
   Group Key: aid
   ->  Sort  (cost=1453477.19..1478477.63 rows=10000175 width=8) (actual time=2303.292..3497.982 rows=10000000 loops=1)
         Sort Key: aid
         Sort Method: external merge  Disk: 176368kB
         ->  Seq Scan on test01000000  (cost=0.00..154056.75 rows=10000175 width=8) (actual time=0.031..951.978 rows=10000000 loops=1)
 Planning Time: 0.033 ms
 Execution Time: 4495.617 ms
(8 rows)

• バージョン13でのクエリ実行

=# SET max_parallel_workers_per_gather TO 0;  -- パラレルクエリ無効化のため
=# SET work_mem TO '16MB';
=# EXPLAIN ANALYZE SELECT aid, count(*) FROM test01000000 GROUP BY aid;

                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=716545.73..804032.31 rows=936268 width=16) (actual time=4965.881..6569.734 rows=1000000 loops=1)
   Group Key: aid
   Planned Partitions: 8  Batches: 9  Memory Usage: 16465kB  Disk Usage: 244616kB
   ->  Seq Scan on test01000000  (cost=0.00..154053.60 rows=9999860 width=8) (actual time=0.031..987.545 rows=10000000 loops=1)
 Planning Time: 0.034 ms
 Execution Time: 6654.316 ms
(6 rows)

バージョン12では、グループ集約(GroupAggregate)がプランとして選択されて、実行時間は約4.5秒でした。一方、バージョン13では、ディスクベースのハッシュ集約(HashAggregate)がプランとして選択されて、実行時間は約6.7秒となり、バージョン12に比べて性能劣化しました。

集約処理時のディスク使用量が、バージョン12のグループ集約ではDisk: 176368kB、バージョン13のハッシュ集約ではDisk Usage: 244616kBと、ハッシュ集約の方が大きく、このあたりに性能劣化の要因があるのかもしれません。

そもそも大量レコードに対して重複の少ないキーを使ってGROUP BYするケースは少ないと思いますが、もしそのようなクエリがあり、バージョン13アップグレード時にディスクベース・ハッシュ集約により性能劣化を確認できる場合は、SET enable_hashagg TO offでハッシュ集約を一時的に無効化したり、work_memを増やしてメモリベースのハッシュ集約を実行するようにチューニングする必要があるかもしれません。

ちなみに、キーの重複を増やしてGROUP BYすると、バージョン12と13ともに以下のような(ディスクを使わない)ハッシュ集約が選択されます。この例では、キーが100件ずつ重複する合計1千万件のレコードに対してGROUP BYしています(集約結果件数は10万件)。

=# EXPLAIN ANALYZE SELECT aid, count(*) FROM test00100000 GROUP BY aid;

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=204057.62..205060.77 rows=100314 width=16) (actual time=3652.181..3682.538 rows=100000 loops=1)
   Group Key: aid
   Batches: 1  Memory Usage: 14353kB
   ->  Seq Scan on test00100000  (cost=0.00..154056.75 rows=10000175 width=8) (actual time=0.032..933.359 rows=10000000 loops=1)
 Planning Time: 0.036 ms
 Execution Time: 3689.205 ms
(6 rows)

1. ディスクベース・ハッシュ集約の概要については、PostgreSQL Conference Japan 2020の講演資料を参照 ↩