「なぜかクエリが遅い」を解決せよ。PostgreSQLの `work_mem` との付き合い方
現場でデータベースを触っていると、たまに「ローカルの検証環境では爆速だったのに、本番の大きなデータセットに入れた途端、クエリが止まらない」なんて悪夢に遭遇すること、あるよね。
その原因の筆頭候補が、今回取り上げる `work_mem` だ。
教科書には「ソートやハッシュ結合に使うメモリ領域」なんて書いてあるけれど、実務レベルでどう扱うべきか、現場の視点で少し深掘りしてみよう。
—
`work_mem` が引き起こす「ディスク書き出し」という悲劇
PostgreSQLは、メモリを賢く使おうとする。ORDER BYによるソートや、JOINの際のハッシュ処理を行うとき、まずは `work_mem` に指定されたサイズのメモリ内で完結させようと頑張るんだ。
でも、データ量がこのメモリを超えるとどうなるか。PostgreSQLは泣く泣く、「一時ファイル（Temporary File）」という形でディスクにデータを書き出し始める。
想像してみてほしい。メモリ上ならナノ秒で終わるはずの処理が、ディスクI/Oという「重い足かせ」をつけられた瞬間にどうなるか。性能は一気にガタ落ちだ。ログに `temporary file` の文字が並び始めたら、それが「もっとメモリを寄越せ」というクエリからの悲鳴だと思っていい。
どこまで増やせばいい？「魔法の数字」は存在しない
よくある間違いが、「とりあえず `work_mem` を1GBくらいにしておけば安心だろう」という雑な設定。これ、実は危険なんだ。
`work_mem` は「クエリ全体」に割り当てられるわけじゃない。「一つの操作（ソートやハッシュ）」ごとに消費されるんだ。
例えば、複雑なJOINが絡むクエリで、ハッシュ結合が3回、ソートが2回発生したら？
一つのクエリで `work_mem` × 5 のメモリが消費される可能性がある。もし最大コネクション数が100だったら……。計算しなくてもわかるよね、メモリ不足（OOM Killer）でサーバーが落ちる未来が待っている。
現場で使える「チューニングの作法」
じゃあ、どう調整するのが正解か。現場ではこんなステップで進めるのが鉄板だ。
1. まずは「現状把握」から
闇雲に設定を変える前に、まずは一時ファイルが発生しているかを確認しよう。
— ログ設定で一時ファイルの発生を記録する
log_temp_files = 0
これを入れておけば、PostgreSQLのログにどのクエリがどれだけのディスクI/Oを発生させたかが記録される。これで、特定のクエリが「悪さ」をしているのか、全体的にメモリが足りていないのかが見えてくる。
2. セッション単位でのチューニングが鍵
本番環境で、全てのクエリに対して `work_mem` を大きくするのはリスクが高すぎる。そこで、特定の重たい処理だけメモリを多めに割り当てるのがプロのやり方だ。
— 特定のバッチ処理など、重いクエリを実行する直前に叩く
SET work_mem = ’64MB’;
— ここで重いクエリを実行
SELECT FROM large_table ORDER BY created_at DESC;
— 終わったら戻す（またはセッション終了）
RESET work_mem;
こうすれば、サーバー全体のメモリ消費量を抑えつつ、必要なところだけにピンポイントでパワーを注入できる。
僕からのアドバイス：インデックスを忘れるな
最後に、一番大事なことを言っておくね。
`work_mem` をいじれば確かに速くなる。でも、「そもそもそのソートを回避できないか？」を考えるのが先決だ。
例えば、`ORDER BY` を使うクエリなら、インデックスが適切に効いていれば、そもそもメモリ上でソートする必要すらなくなるかもしれない。`EXPLAIN ANALYZE` を叩いて、`Sort Method: external merge` なんて文字が出ていないか確認してみてほしい。`external` が見えたら、それは「ディスクを使っている」というサインだ。
チューニングは、設定値を弄るだけが能じゃない。クエリの構造を見直して、メモリへの負担を減らすこと。それが、結局は一番の近道なんだ。
—
どうだい？ `work_mem` は、PostgreSQLのポテンシャルを引き出すための「諸刃の剣」みたいなもの。臆病になりすぎず、でも調子に乗って上げすぎず。この絶妙なバランス感覚を身につけるのが、優れたエンジニアへの第一歩だよ。
また何か詰まったら、いつでも聞きに来てくれ。
コメント