CTEの「最適化」という罠：PostgreSQL 12以降の挙動を正しく制御する
PostgreSQLを使っているエンジニアなら、一度は`WITH`句（CTE）の恩恵に預かったことがあるはずだ。複雑なクエリを論理的なブロックに切り分け、コードの可読性を劇的に高めてくれるあの機能だ。
しかし、長年DBのクエリプランナと格闘してきた身から言わせれば、CTEは「諸刃の剣」以外の何物でもない。特に、PostgreSQL 12を境にCTEの最適化戦略は大きく変わった。今日は、この「CTEのマテリアライゼーション（実体化）」という、パフォーマンスの明暗を分ける深い淵について話そうと思う。
隠れたコスト：マテリアライゼーションの正体
PostgreSQL 12以前、CTEは「最適化の境界」だった。一度`WITH`句で定義されたデータセットは、物理的にメモリ（あるいはディスク）上のテンポラリテーブルとして実体化され、クエリの残りの部分からは「ただのテーブル」として参照されていた。
これには明確な副作用がある。プランナがCTE内部のフィルタ条件を外側のクエリから引き継げないのだ。つまり、数百万行のテーブルから特定の1行を抽出したいだけなのに、CTE側で全件スキャンを走らせてから、外側でようやく絞り込みを行うという「無駄の極み」が頻発していた。
PostgreSQL 12以降、デフォルトでは「可能であればインライン化（展開）」されるようになった。これにより、CTEの中身がメインのクエリと統合され、インデックスが有効に機能するようになった。これは多くの場合、素晴らしい改善だ。
しかし、現場でトラブルシューティングをしていると、逆に「あえてマテリアライズしてほしい」というケースに直面することがある。
いつ、なぜ「MATERIALIZED」を明示するのか
PostgreSQL 12からは `WITH … AS MATERIALIZED` または `AS NOT MATERIALIZED` というヒント句が使えるようになった。これがただの飾りではないことを、君たちも経験で知っているはずだ。
私がこの指定を検討するタイミングは、主に以下の2つのパターンだ。
1. 非常に重い計算を複数回呼び出すとき
CTEで算出する計算コストが膨大な場合、インライン化して毎回計算させるよりも、一度計算して結果を保持する方がトータルコストが下がる。
2. プランナが「誤った選択」をしているとき
プランナは完璧ではない。複雑な結合条件が絡むと、インライン化することでかえってコスト推定が狂い、Nested Loopのネストが深まりすぎて爆死することがある。そんな時、マテリアライズで計算を切り離すことは、プランナに「ここは一度ここで計算を止めてくれ」と指示する強力なブレーキになる。
トラブルシューティングの勘所
パフォーマンスが劣化しているCTEを見つけたとき、私はまず`EXPLAIN ANALYZE`を見る。そこで注目するのは「どこで時間がかかっているか」ではなく、「期待したインデックスが使われているか」だ。
もし、CTEの内側でインデックスが使われず、全件走査（Seq Scan）が行われているなら、まずはデフォルトのインライン化の挙動を疑うべきだ。
— 明示的にマテリアライズを制御する例
WITH high_cost_calculation AS MATERIALIZED (
SELECT … FROM huge_table WHERE …
)
SELECT FROM high_cost_calculation JOIN other_table ON …
逆に、メモリ不足（Work Memの枯渇）でテンポラリファイルへの書き出しが発生しているなら、`NOT MATERIALIZED`を明示して、インライン化を強制する（あるいはクエリ構造自体を見直す）のが定石だ。
最後に：エンジニアの直感と測定のバランス
結局のところ、データベースエンジニアリングにおいて最も信頼できるのは「公式ドキュメントの記述」と「実測値」だけだ。
CTEの挙動を制御することは、オプティマイザのブラックボックスに手を入れるようなものだ。安易にヒント句を使いすぎると、将来的なデータ分布の変化によって逆に足を引っ張ることになる。
まずはデフォルトの挙動に任せ、どうしても納得のいかない実行計画が出たときにだけ、この「 хирургия（外科手術）」を行うこと。これが、PostgreSQLと長く付き合うための、僕なりの美学だ。
君たちの書くクエリが、今日も効率的で美しいものであることを祈っている。
コメント