PostgreSQLで複雑なSQLを書く際に便利なCTE（Common Table Expression）は、クエリを分割して読みやすくできる一方で、実行性能に影響を与える場合があります。特にPostgreSQL 12以降で利用できるMATERIALIZEDとNOT MATERIALIZEDは、CTEをどのように処理するかを制御する重要な指定です。この記事では、CTEのマテリアライズ処理が実行計画やクエリ性能にどのような違いを生むのか、具体例を交えながら解説します。
PostgreSQLのCTEとは何か
CTE（Common Table Expression）は、WITH句を使って一時的な結果セットを定義できるSQL機能です。複雑なサブクエリを分離して記述できるため、SQLの可読性や保守性を高める目的で利用されます。
例えば、売上データを集計してから分析するような処理では、以下のように途中結果を名前付きのクエリとして定義できます。
<pre><code>WITH sales_summary AS (SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id) SELECT * FROM sales_summary WHERE total > 10000;</code></pre>
以前のPostgreSQLではCTEは基本的に必ず一度結果を作成する仕組みでしたが、PostgreSQL 12以降では条件によってインライン化されるようになり、MATERIALIZEDとNOT MATERIALIZEDによる制御が可能になりました。
MATERIALIZEDはCTEの結果を一時的に保存する
MATERIALIZEDを指定すると、CTEの結果を一度計算して保存し、その保存された結果を後続の処理で利用します。これはCTEの結果を再利用したい場合や、処理を分離したい場合に有効です。
例として、同じCTEを複数回参照するケースでは、MATERIALIZEDによって計算結果を一度だけ作成し、それを複数箇所で利用できます。
<pre><code>WITH filtered_data AS MATERIALIZED (SELECT * FROM orders WHERE status = ‘completed’) SELECT * FROM filtered_data WHERE amount > 1000;</code></pre>
この場合、PostgreSQLはfiltered_dataの結果を一度生成します。そのため、大量データを扱う場合でも同じ計算を何度も繰り返すことを防げる可能性があります。
NOT MATERIALIZEDはCTEをインライン展開する
NOT MATERIALIZEDを指定すると、CTEを通常のサブクエリのように展開し、オプティマイザが全体のSQLとして最適化できるようになります。
例えば、CTEの結果を一度しか利用しない場合、NOT MATERIALIZEDによってWHERE条件やJOIN条件を元のテーブルまで押し込むことができ、インデックスを利用した効率的な検索になる場合があります。
<pre><code>WITH recent_orders AS NOT MATERIALIZED (SELECT * FROM orders WHERE created_at > CURRENT_DATE – INTERVAL ’30 days’) SELECT * FROM recent_orders WHERE customer_id = 100;</code></pre>
この場合、PostgreSQLはCTEを展開して、元のordersテーブルに対してcustomer_idやcreated_atの条件を考慮した実行計画を作成できます。
MATERIALIZEDとNOT MATERIALIZEDによる実行計画の違い
両者の大きな違いは、クエリオプティマイザがCTE内部まで最適化できるかどうかです。
| 指定 | 処理方法 | 特徴 |
|---|---|---|
| MATERIALIZED | CTE結果を作成して保持 | 再利用に強いが最適化の自由度が下がる |
| NOT MATERIALIZED | CTEを展開して処理 | 全体最適化しやすいが計算量が増える場合がある |
MATERIALIZEDの場合、CTEが最適化の境界になるため、外側のWHERE条件を内部のテーブル検索へ反映できません。
例えば、100万件のデータから10件だけ取得したい場合でも、先に100万件分のCTE結果を作成してから絞り込む可能性があります。このようなケースではNOT MATERIALIZEDの方が高速になることがあります。
どのような場合にMATERIALIZEDを使うべきか
MATERIALIZEDは、CTEの計算結果を複数回利用する場合や、意図的に処理順序を固定したい場合に適しています。
例えば、複雑な集計処理を行ったCTEを複数のJOINで参照する場合、毎回同じ集計を実行するより、一度結果を作成して利用する方が効率的になるケースがあります。
また、巨大なクエリを分割してデバッグしたい場合にも、MATERIALIZEDによって処理を明確に分離できるメリットがあります。
どのような場合にNOT MATERIALIZEDを使うべきか
NOT MATERIALIZEDは、CTEを単純な整理目的で利用している場合や、オプティマイザによる高度な最適化を期待したい場合に向いています。
例えば、1つのテーブルから条件付きでデータを取得するだけのCTEでは、インライン化によってインデックス検索が利用されやすくなります。
ただし、NOT MATERIALIZEDではCTEが複数回評価される可能性があります。そのため、重い集計処理を何度も実行するようなSQLでは、逆に性能が低下する場合があります。
実際の性能確認ではEXPLAIN ANALYZEが重要
MATERIALIZEDとNOT MATERIALIZEDのどちらが高速になるかは、SQLの内容やデータ量、インデックス構成によって変化します。そのため、指定だけで性能を判断することはできません。
実際の環境ではEXPLAIN ANALYZEを利用して、実行計画や実際の処理時間を確認することが重要です。
<pre><code>EXPLAIN ANALYZE SELECT * FROM example;</code></pre>
実行計画を見ることで、CTE Scanが発生しているのか、Index Scanが利用されているのかなどを確認でき、適切な指定を判断できます。
まとめ｜CTEのMATERIALIZEDとNOT MATERIALIZEDは目的に応じて使い分ける
PostgreSQLのCTEでは、MATERIALIZEDは結果を一度保存して再利用する仕組み、NOT MATERIALIZEDはCTEを展開して全体最適化を行いやすくする仕組みです。
大量データの集計結果を複数回使う場合はMATERIALIZEDが有効になることがあり、単純な絞り込みやJOINではNOT MATERIALIZEDによって高速化できる可能性があります。
重要なのは、どちらか一方を常に選ぶのではなく、EXPLAIN ANALYZEで実際の実行計画を確認しながら、SQLの目的に合わせて選択することです。
コメント