こんにちは！データベースの世界にどっぷり浸かっているエンジニアです。
今日は、PostgreSQLを使っていると一度は耳にする（そして、たまに悩まされる）「CTE（WITH句）」のお話です。
「WITH句って、クエリを整理して読みやすくしてくれる魔法の杖でしょ？」そう思っている時期が、私にもありました。でも実は、この子、裏側でちょっとした「性格のムラ」を見せることがあるんです。
今日は、この「CTEのマテリアライゼーション（一時保存）」という少し難しそうな挙動を、私たちの日常に置き換えて優しく紐解いてみましょう。
—
カフェの注文で例えてみよう
想像してみてください。あなたは今、大人気のカフェの店員さんです。そこへお客さんから「このメニューにある『特製ブレンド』を3つ作って！」と注文が入ったとします。
この時、店員さんの動き方は大きく分けて2パターンあります。
パターンA：インライン展開（その都度作る）
注文を受けるたびに、豆を挽き、お湯を沸かし、一杯ずつ丁寧に淹れるスタイルです。
- メリット： いつでも淹れたて。豆の鮮度も最高です。
- デメリット： 3杯作るのに3回同じ工程を繰り返すので、時間がかかります。
パターンB：マテリアライゼーション（作り置きする）
最初に「特製ブレンド」を大きなポットにまとめて3杯分作り、それをカップに注いで提供するスタイルです。
- メリット： 提供スピードが爆速です。
- デメリット： ポットに置いている間に少し冷めてしまうかもしれませんし、そもそも「1杯だけ」の注文だったなら、余計な手間がかかったことになります。
—
データベースの世界でも同じことが起きている
PostgreSQLの「WITH句」も、まさにこの判断を裏側で行っています。
- インライン展開： WITH句の中身を、メインのクエリの中に「直接組み込んで」実行する。
- マテリアライゼーション： WITH句の結果を、一旦「作業用の一時テーブル」に書き出して、そこから結果を取り出す。
PostgreSQLは非常に賢いので、基本的には「どっちが効率いいかな？」と自動で判断してくれます。でも、たまに「いや、そこは作り置きしなくていいから！」とか「いやいや、一度計算したなら使い回してよ！」と、人間側から教えてあげたくなる時があるんです。
なぜ「マテリアライゼーション」が問題になるの？
「作り置き」であるマテリアライゼーションは、データ量が多いと、その「一時保存」という作業自体が重荷になってしまうことがあります。
例えば、数百万件のデータがあるのに、それをわざわざ別の一時テーブルに書き出してから結合しようとすると、ディスクI/Oがボトルネックになって、クエリがなかなか終わらない……なんて経験、データベースエンジニアなら一度は通る道です。
—
どう付き合っていけばいいの？
もし、あなたが書いたクエリが「なんだか最近遅いな？」と感じたら、まずはこの「WITH句の作り置き問題」を疑ってみてください。
PostgreSQL 12以降であれば、`MATERIALIZED` または `NOT MATERIALIZED` というキーワードを添えることで、私たち人間が明示的に挙動を指定できるようになりました。
— 作り置きしてほしいとき
WITH summary AS MATERIALIZED (
SELECT …
) SELECT …
— その都度計算してほしいとき
WITH summary AS NOT MATERIALIZED (
SELECT …
) SELECT …
「データベースにお任せ」もいいけれど、時には私たちが「ここはこうしてほしい」と指示を出してあげる。まるで、料理のレシピを調整するように、クエリを最適化していくのは本当に楽しい作業ですよ。
—
最後に
「WITH句はただの整理整頓ツール」と侮るなかれ。その裏側では、データベースが一生懸命「作り置きするか、その都度淹れるか」を計算しています。
もしクエリのパフォーマンスでお困りなら、ぜひこの「カフェの店員さん」の例えを思い出してみてください。きっと、どこをどう調整すれば速くなるのか、ヒントが見えてくるはずです。
それでは、また次回の技術ブログでお会いしましょう！Happy Querying!
コメント