PostgreSQLのハッシュ結合と `work_mem`：その「境界線」をどう見極めるか
PostgreSQLでクエリのパフォーマンスが突然崩壊する瞬間――。DBAであれば、誰もが一度は経験する悪夢のようなシナリオです。特に巨大なテーブル同士の結合（Hash Join）で、実行計画がディスクI/Oの嵐に飲み込まれていく様子を `EXPLAIN ANALYZE` で眺めるのは、精神衛生上、あまりいいものではありません。
今日は、PostgreSQLのクエリ最適化における最も重要なパラメータの一つ、`work_mem` とハッシュ結合の深い関係について、少し掘り下げてみたいと思います。
なぜハッシュテーブルは「溢れる」のか
まず前提として、PostgreSQLのハッシュ結合は、結合する側の小さい方のテーブル（Innerテーブル）をメモリ上にハッシュテーブルとして構築することで成り立っています。
ここで重要なのが、この構築に許されるメモリ上限が `work_mem` であるという点です。もし、構築しようとしているハッシュテーブルが `work_mem` に収まりきらなくなるとどうなるか。PostgreSQLは「Spill to disk」という挙動に出ます。
具体的には、ハッシュテーブルをいくつかの「バケット」に分割し、メモリに入り切らない分を一時ファイル（`base/pgsql_tmp/` 配下）に書き出しながら、段階的に処理を進めることになります。これが始まると、パフォーマンスは劇的に低下します。メモリ上のランダムアクセスが、ディスク上の逐次I/Oと多大なシーク時間に取って代わられるわけですから。
`work_mem` の設定は「魔法の杖」ではない
よくある失敗は、「遅いから `work_mem` を極端に大きくする」という短絡的なアプローチです。
SET work_mem = ‘1GB’; — こうすれば速くなるはず…？
しかし、注意してください。`work_mem` は「セッションごと」かつ「オペレーションごと」に消費されます。もし複雑なクエリで複数のハッシュ結合やソートが同時に走れば、`work_mem 接続数 オペレーション数` のメモリが平気で消費されます。これを無策に広げれば、待っているのはOOM KillerによるPostgreSQLプロセスの強制終了です。
では、どうやって「最適値」を見つけるべきか。私はいつも、勘ではなくデータで判断するようにしています。
現場で役立つ「ディスク溢れ」の検知術
クエリがディスクに溢れているかどうかは、`EXPLAIN (ANALYZE, BUFFERS)` を実行すれば一目瞭然です。
Hash Join (cost=… rows=… width=…)
…
Batches: 9 Memory Usage: 4096kB Disk Usage: 32768kB
ここで注目すべきは `Batches` と `Disk Usage` です。
- Batches: 1であれば、メモリ内に収まっています。2以上であればディスクへ溢れています。
- Disk Usage: ここに値が出ているということは、確実にディスクI/Oが発生しています。
もし `Batches` が非常に大きい場合、`work_mem` を増やすことで劇的な改善が見込めます。しかし、逆に `Batches` が2〜4程度であれば、`work_mem` を増やすことによるオーバーヘッドやリスクを考えると、インデックスの再考やパーティショニング、あるいは統計情報の更新（`ANALYZE`）の方が本質的な解決策になることが多いです。
チューニングの哲学：メモリとIOのトレードオフ
私が現場でよく行うチューニングのステップを共有します。
1. 統計情報の確認: まず `pg_stats` を見て、行数の見積もりが実態と乖離していないかを確認します。オプティマイザがテーブルサイズを過小評価していれば、そもそもハッシュ結合が選ばれない、あるいは不適切なメモリ割り当てが行われます。
2. 個別の最適化: 特定の重いクエリに対してのみ、トランザクション内で `SET LOCAL work_mem = ’64MB’;` のように限定的に適用します。グローバルな `work_mem` を触るのは最後の手段です。
3. 実行計画の観察: `work_mem` を調整した後、再び `EXPLAIN ANALYZE` を回し、`Batches` が1に収まっているかを確認します。
最後に：完璧な設定値は存在しない
PostgreSQLのアーキテクチャにおいて、`work_mem` は「どれだけメモリを贅沢に使ってもいいか」というエンジニアからの提示です。しかし、ワークロードは常に変動します。
私たちがやるべきことは、メモリを最大まで積むことではなく、「クエリがどのようなコストでデータを処理しているか」を可視化し、適切なリソースを割り当てる判断力を磨くことです。
ディスク溢れは、システムからの「今のやり方では少し無理があるよ」というサインです。そのサインを見逃さず、クエリの構造を紐解いていく。そんな地道な作業こそが、最も確実なチューニングへの近道だと私は信じています。
皆さんのデータベースが、今日も軽快にクエリを捌ききれますように。
コメント