ハッシュ結合の「深淵」：work_memを制する者がPostgreSQLを制する
PostgreSQLのクエリプランナーがハッシュ結合（Hash Join）を選択したとき、私たちは「さて、今回もメモリとの戦いだな」と直感します。
多くのエンジニアは「`work_mem`を増やせば速くなる」と教わります。しかし、現場で遭遇するパフォーマンス劣化の多くは、単にメモリが足りないからではなく、ハッシュテーブルがメモリの境界線で「あえいでいる」ときに発生します。
今日は、ハッシュ結合の内部構造に少しだけ深く潜り込み、なぜあなたのクエリがディスクI/Oの海に沈んでしまうのかを紐解いていきましょう。
—
ハッシュ結合の「影の立役者」：ハッシュテーブルの動態
PostgreSQLのハッシュ結合は、大きく分けて二つのフェーズで構成されます。
1. Buildフェーズ: 小さい方のテーブル（通常は結合の左側）をスキャンし、インメモリのハッシュテーブルを構築する。
2. Probeフェーズ: 大きい方のテーブルをスキャンし、ハッシュテーブルをルックアップしてマッチする行を探す。
ここで重要なのは、ハッシュテーブルがメモリ（`work_mem`）に収まらなくなった瞬間に、エンジンが「バケットの分割（Spilling）」という過酷な処理を開始することです。
1. メモリに収まる場合（理想郷）
全てが`work_mem`内に収まれば、ハッシュ結合はO(n+m)で終わる最高のパフォーマンスを発揮します。このとき、CPUキャッシュの局所性を活かした高速なルックアップが行われます。
2. メモリから溢れる場合（現実の罠）
`work_mem`を超過すると、PostgreSQLはハッシュテーブルを「バッチ」に分割し、一部を一時ファイル（`pg_tblspc`配下のディスク）へ書き出します。
ここで発生するのは単なるI/O負荷ではありません。「再帰的ハッシュ結合」と呼ばれるプロセスです。あふれたデータを再びハッシュ化し、ディスクとメモリの間で何度もスワップさせることになります。この時のパフォーマンス低下は、指数関数的と言っても過言ではありません。
—
トラブルシューティング：なぜ「あふれる」のか？
クエリの実行計画（`EXPLAIN ANALYZE`）を見たとき、以下のキーワードに戦慄したことはありませんか？
- `Batches: 8`
- `Disk Usage: 128MB`
これは、「メモリに収まりきらず、8回に分けてディスクを読み書きした」という警告です。特に、`Batches`の数が2の累乗で増えていくときは、メモリ設定とデータ量のミスマッチが深刻です。
現場でよくある「落とし穴」
- 統計情報の鮮度: `ANALYZE`を怠っていると、プランナーは「このテーブルは小さい」と誤認し、ハッシュ結合を選択します。しかし実際には巨大で、メモリは即座にパンクします。まずは`pg_stats`を疑いましょう。
- カーディナリティの過小評価: NULL値や重複の多い列で結合している場合、ハッシュテーブルのサイズを見積もるのが困難になります。
- 不適切な`work_mem`: 全てのセッションに巨大な`work_mem`を割り当てるのは自殺行為です。コネクション数×`work_mem`がサーバーの物理メモリを食いつぶし、OSレベルのOOM Killerを招くからです。
—
最適化へのアプローチ：設定値の「適正化」
`work_mem`の値を闇雲に増やす前に、まずは以下の手順でボトルネックを特定することを推奨します。
1. `auto_explain`の活用: 本番環境で遅いクエリを確実にキャッチするために、特定の閾値を超えたクエリをログに出力させます。
2. 実行計画の精査: `Hash Join`のコストと、実際の`Batches`数を見比べます。もし`Batches`が1以外なら、そのクエリだけに限定して`SET work_mem = ‘XXMB’`を適用し、パフォーマンスが劇的に改善するかを確認します。
3. インデックスの再考: ハッシュ結合を避けるために、あえて`Merge Join`や`Nested Loop`を選択させるインデックス戦略を練ることもあります。特に結合キーにインデックスがある場合、ソート順を利用したマージ結合の方が安定することもあります。
—
最後に：データベースエンジニアの矜持
ハッシュ結合のチューニングは、単なる数値合わせではありません。それはデータがメモリ上をどう流れ、どう配置されるかを設計する「彫刻」に近い作業です。
`work_mem`は魔法の杖ではありません。しかし、その背後にあるメカニズムを理解していれば、あなたはシステムが悲鳴を上げる前に、最適なルートを見つけ出すことができます。
次のチューニングで`Batches: 1`を見ることができたなら、そのときこそが、我々エンジニアが一番の快感を得る瞬間なのです。
—
何か特定の実行計画で詰まっていることがあれば、いつでもコメントで教えてください。一緒に読み解きましょう。
コメント