【実務・中級編】マージ結合の最適化 – PostgreSQL

マージ結合の「魔法」を解く：ソートを回避して爆速クエリを手に入れる方法

やあ。最近、PostgreSQLの実行計画（`EXPLAIN`）とにらめっこして、夜を明かしてないか？

今日は、パフォーマンスチューニングの現場で避けては通れない、「マージ結合（Merge Join）」について深掘りしようと思う。特に、インデックスをうまく使って「ソート処理」をスキップさせるテクニックは、大規模なデータセットを扱うときには必須のスキルだ。

教科書には「マージ結合はソート済みデータに有効」なんてさらっと書いてあるけど、実際の現場では、なぜそのソートがボトルネックになるのか、どうやって回避するのがベストなのか、その「肌感覚」を共有しておくよ。

—

なぜマージ結合は「ソート」を嫌うのか

まず、マージ結合の基本動作を思い出してほしい。マージ結合は、2つのテーブルを結合キーでソートしてから、先頭から順番に突き合わせていくアルゴリズムだ。

問題は、「結合キーがソートされていない場合、PostgreSQLはメモリ（あるいはディスク）を使ってわざわざソート処理を行う」ということ。

データ量が数万件なら一瞬だけど、数百万、数千万件になったらどうなる？ `Sort` ノードが実行計画に現れた瞬間、クエリのレスポンスはガクンと落ちる。さらに、メモリの `work_mem` を超えればディスクへの書き出し（Temp file）が発生して、もう目も当てられない惨状になるんだ。

インデックスは「ただの検索用」じゃない

ここで腕の見せ所だ。もし、結合キーに適切なインデックスが貼られていれば、PostgreSQLは「お、既にソートされてるじゃん。じゃあソート処理はパスして、そのままマージしちゃおう」という判断（Index Scan / Index Only Scan）をしてくれる。

これが、いわゆる「ソート回避」だ。

具体例で見てみよう

例えば、注文データ（`orders`）と顧客データ（`customers`）を `customer_id` で結合するクエリを考えてみる。

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

もし、`customers` テーブルの `customer_id` に主キー（PK）があれば、PostgreSQLは自動的にB-treeインデックスを使ってソート済みとみなしてくれる。でも、`orders` テーブル側の `customer_id` にインデックスがなかったら？

PostgreSQLは `orders` テーブルを全スキャンして、結合のために巨大なソート処理を開始する。これを防ぐには、当然だけどインデックスを追加する。

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

たったこれだけで、実行計画から `Sort` ノードが消え、`Index Scan` が並ぶようになる。結果、CPU負荷もI/Oも劇的に軽くなるはずだ。

「落とし穴」にも注意してくれ

ただ、ここで一つ注意点がある。「インデックスを貼れば何でも速くなるわけじゃない」ということだ。

カーディナリティ（値の重複度）: 結合キーの重複が極端に多い場合や、逆にほとんど重複がない場合、オプティマイザは「マージ結合よりもハッシュ結合の方が速いな」と判断して、せっかくのインデックスを無視することがある。
カバリングインデックス: `SELECT` 句で取ってくるカラムまでインデックスに含めてしまえば（`INCLUDE` 句を使うなど）、テーブル本体へのアクセスすら発生しない「Index Only Scan」に持ち込める。これも余裕があれば狙っていきたいところだね。

実践的なチューニングのステップ

後輩のみんなにアドバイスしたいのは、以下のルーチンだ。

1. まずは `EXPLAIN (ANALYZE, BUFFERS)` を叩く:
「どのノードで時間がかかっているか」「`Sort` にどれだけメモリを使っているか」「ディスクに spill していないか」を凝視する。
2. インデックスを検討する:
結合キーにインデックスがないなら、まずは作る。ただし、書き込み速度への影響とのトレードオフは忘れないこと。
3. `work_mem` を疑う:
どうしてもソートが必要なクエリなら、セッション単位で `work_mem` を一時的に増やしてメモリ内でソートを完結させるのも手だ。ただし、安易に全体設定をいじるとメモリ不足でDBが死ぬから、あくまで「特定のクエリのチューニング」として使うのが鉄則だよ。

—

最後に

マージ結合を制するものは、大規模データのパフォーマンスを制する。

PostgreSQLは優秀なオプティマイザを持っているけど、彼らも万能じゃない。僕らエンジニアが「このデータは既にこういう順番で並んでいるんだから、わざわざ並べ直さなくていいよ」というヒント（インデックス）を渡してあげるだけで、クエリは驚くほど軽快に動いてくれる。

次に遅いクエリに出会ったら、まずは `EXPLAIN` を開いて、`Sort` という文字がないか探してみてくれ。それが、パフォーマンス改善の第一歩だ。

それじゃ、また現場で会おう。健闘を祈る！