【実務・中級編】サブクエリの平坦化 (Subquery Flattening) – PostgreSQL

「またサブクエリでハマってるの？」

コードレビューをしていて、後輩の書いたSQLに深いネストのサブクエリを見つけるたび、僕はついこう声をかけたくなります。もちろん、PostgreSQLのクエリオプティマイザは優秀です。でも、彼らが魔法を使えるわけじゃない。

今日は、PostgreSQLが裏でこっそりやっている「サブクエリの平坦化（Subquery Flattening）」と、それがなぜ時々「魔法を失う」のか、現場の視点から紐解いていきましょう。

—

1. クエリオプティマイザの「お節介」：サブクエリの平坦化

PostgreSQLのオプティマイザは、賢いお節介焼きです。人間が書いた「入れ子構造のSQL」を、そのまま実行するのは非効率だと知っています。

例えば、こんなSQLがあったとします。

SELECT FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

人間は「ユーザーの中から、1000円以上の注文をした人を探す」という手続きで書きますよね。でも、PostgreSQLはこれを実行前に内部で `JOIN` に書き換えます。

SELECT DISTINCT users.
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE orders.amount > 1000;

これがサブクエリの平坦化（Subquery Flattening）です。JOINに変換することで、PostgreSQLは「Hash Join」を使うか「Nested Loop」を使うかといった、より高度な選択肢をテーブル全体に対して適用できるようになるわけです。

—

2. なぜ魔法が解けてしまうのか？

ところが、この最適化が働かないケースがあります。特に複雑な分析クエリを書いていると、「あれ、なんでこんなに遅いの？」という事態に陥る。その原因の多くは、オプティマイザが「安全に平坦化できない」と判断した時です。

よくある「平坦化を阻害する代表選手」は以下の3つです。

`LIMIT` や `OFFSET` がサブクエリ内にある場合
「先頭の5件」という条件は、JOINの順序を強制的に固定してしまうため、オプティマイザは迂闊に手を出しません。
`DISTINCT` や `GROUP BY` を含む場合
これらが入ると、サブクエリの結果セットが一意に確定するまでJOINできないため、最適化が制限されます。
`UNION` や `EXCEPT` などの集合演算
これも計算の順序が確定してしまうので、基本的には独立して実行されます。

—

3. 現場で「あ、これダメなやつだ」と気づく瞬間

例えば、こんなSQLを見てみてください。

— 注文数が一番多い上位10人のユーザー名を取りたい
SELECT name FROM users
WHERE id IN (
SELECT user_id FROM orders
GROUP BY user_id
ORDER BY count() DESC
LIMIT 10
);

一見良さそうですが、これだとサブクエリ内で `LIMIT 10` が使われているため、PostgreSQLは「まずサブクエリを完遂して10個のIDを確定させてから、usersテーブルを引く」という手順を強制されます。

もし users テーブルが数千万件規模で、インデックスが効きにくい状況だったら？悲惨なパフォーマンスになりますよね。

—

4. 僕が現場で推奨する「最適化のコツ」

もしサブクエリのせいでパフォーマンスが出ないなら、以下の手順を試してみてください。

1. EXPLAIN ANALYZE を必ず見る
「Subquery Scan」という単語が出てきたら、それが「平坦化できていない証拠」だと思っていい。
2. CTE（WITH句）で書き換えてみる
昔のPostgreSQLではCTEは最適化の境界でしたが、最新のバージョン（PostgreSQL 12以降）ではインライン化されるケースも増えました。可読性が段違いに良くなります。
3. いっそJOINに書き直す
結局、オプティマイザを信じすぎず、最初から「自分がプランナになったつもりで」JOINで書くのが一番の近道です。

—

最後に：完璧なSQLなんてない

データベースエンジニアとして一つだけ言っておきたいのは、「常に平坦化が正義ではない」ということです。

時として、あえてサブクエリを残して、計算を先に絞り込ませるほうが速いケースもあります。でも、それは「平坦化ができなかった結果」ではなく「意図してそう書いた」場合です。

まずは `EXPLAIN` を眺めて、PostgreSQLがあなたのクエリをどう解釈しているか、対話してみてください。機械が何を考えているか想像できるようになると、SQLを書くのがぐっと楽しくなりますよ。

さて、次はどのクエリを最適化しましょうか？困ったことがあればまたいつでも聞いてくださいね。