PostgreSQLのハッシュ結合と仲良くなる：メモリの限界を知り、クエリを加速させる技術
やあ。最近、PostgreSQLの実行計画（EXPLAIN）と睨めっこする時間が増えていないかな？
特にデータ量が数百万行、数千万行と増えてくると、いつもの「ネステッドループ」では太刀打ちできなくなる瞬間が来る。そんな時、オプティマイザが好んで選択するのが「ハッシュ結合（Hash Join）」だ。
今日は、このハッシュ結合の「裏側」と、現場で遭遇する「メモリ不足（ディスクへの溢れ）」という現実的な問題について、少し深掘りしてみようと思う。
—
ハッシュ結合の仕組み：直感的な理解
ハッシュ結合を簡単に説明すると、「片方のテーブルをメモリ上に辞書（ハッシュテーブル）として作って、もう片方のテーブルを流し込みながら突き合わせる」という手法だ。
1. Buildフェーズ: 小さい方のテーブル（内部テーブル）をスキャンして、結合キーをハッシュ関数に通し、メモリ上にハッシュテーブルを作る。
2. Probeフェーズ: もう一方のテーブル（外部テーブル）をスキャンし、同じくハッシュ関数に通して、先ほど作ったテーブルから合致するものを探す。
シンプルだよね。ネステッドループが「総当たり」であるのに対し、ハッシュ結合は「辞書引き」だから、圧倒的に効率がいい。
—
`work_mem`：エンジニアの腕の見せ所
ここで必ず話題に上がるのが、PostgreSQLのパラメータ`work_mem`だ。
この設定値は、「ハッシュテーブルを構築するために、どれくらいのメモリを確保していいか」という制限値になる。勘違いしやすいんだけど、これは「接続全体」ではなく「一回の操作（ソートやハッシュ）」ごとに割り当てられるメモリだ。
ここが落とし穴！「ディスクへの溢れ（Spill to Disk）」
もし`work_mem`が足りないとどうなるか。ハッシュテーブルがメモリに収まりきらなくなると、PostgreSQLは泣く泣く一時ファイル（temp file）としてディスクへデータを書き出す。これを「Spill to Disk」と呼ぶ。
これが起きるとパフォーマンスは急激に悪化する。SSDとはいえ、メモリの数千倍から数万倍遅いからね。
- どうやって確認するか？
`EXPLAIN ANALYZE` を叩いてみてほしい。
Hash Join (cost=… rows=… width=…) (actual time=… rows=… loops=1)
Hash Cond: (t1.id = t2.id)
Batches: 1 Memory Usage: 1024kB Disk: 0kB
ここが「Disk: 0kB」なら優秀。「Disk: 50MB」なんて表示されていたら、そこがボトルネックの犯人だ。
—
具体的なチューニングのアプローチ
じゃあ、現場でどう立ち回るか。いくつか実践的なアドバイスを置いておくよ。
1. `work_mem` を闇雲に上げない
「メモリが足りないなら`work_mem`を1GBにしよう！」というのは危険だ。接続数（`max_connections`）が100あったら、理論上最大で100GBのメモリを食い潰す可能性がある。サーバーの物理メモリと相談しつつ、セッション単位で一時的に上げるのがプロのやり方だ。
— 特定の重いクエリだけ、トランザクション内で一時的に引き上げる
BEGIN;
SET LOCAL work_mem = ’64MB’;
SELECT FROM large_table t1 JOIN heavy_table t2 ON t1.id = t2.id;
COMMIT;
2. 統計情報の鮮度を疑う
オプティマイザが「このテーブルは小さいはずだ」と誤認してハッシュ結合を選び、実際には巨大なテーブルでメモリが溢れる、というケースはよくある。`ANALYZE`コマンドで統計情報を最新にしておくことは、チューニングの基本中の基本だ。
3. ハッシュキーの型を意識する
結合キーのデータ型が不一致だと、暗黙の型変換が発生してインデックスが効かないだけでなく、ハッシュ値の計算効率も落ちる。特に`text`型と`varchar`型の比較なんかは要注意だ。可能な限り型を合わせよう。
—
まとめ：魔法の杖はない
ハッシュ結合は、大規模データセットを扱う上で最強の武器の一つだ。しかし、メモリという物理的な制約からは逃げられない。
- EXPLAIN ANALYZEで「Disk」の文字を見逃さないこと。
- メモリの制限と、同時接続数のバランスを考えること。
- 統計情報を信じすぎず、定期的にケアすること。
エンジニアリングの世界に「これをやっておけば絶対に速くなる」という魔法の杖はないけれど、仕組みを理解して適切に設定してやれば、PostgreSQLは期待以上のパフォーマンスで応えてくれる。
もし今、君の書いたクエリが遅くて悩んでいるなら、一度`EXPLAIN ANALYZE`の結果をじっくり眺めてみてくれ。そこに必ず、DBからのメッセージが隠れているはずだから。
それじゃ、また現場で会おう。健闘を祈るよ！
コメント