ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQL における WITH 句と実行計画への影響の考察

データベース プログラミング アーキテクチャ

概要

PostgreSQL では WITH 句（Common Table Expressions: CTE）を利用できます。WITH 句を使用すると、クエリ内で一時的なテーブルを定義し、それを後続のクエリで参照できます。これは複雑なクエリの構造を整理するのに役立ちます。

本記事では、WITH 句が実行計画にどのような影響を与えるのかを、シンプルな例を用いて検証します。

検証環境

OS Red Hat Enterprise Linux release 9.5
DB PostgreSQL 17.2

PostgreSQL はソースコードからインストールし、デフォルト設定のまま使用しています。

検証内容

事前準備として、以下のスクリプトを実行しました。

CREATE TABLE tab1 (id integer);
CREATE TABLE tab2 (id integer);
CREATE TABLE tab3 (id integer);

INSERT INTO tab1 SELECT generate_series(1, 1000);
INSERT INTO tab2 SELECT generate_series(1, 2000);
INSERT INTO tab3 SELECT generate_series(1, 3000);

VACUUM ANALYZE;

以下のクエリを基準とします。

SELECT tab1.id FROM tab1 INNER JOIN tab2 ON tab1.id = tab2.id INNER JOIN tab3 ON tab2.id = tab3.id;

このクエリを WITH 句を用いて書き換えたものが以下のクエリです。

WITH tab_tmp AS (SELECT tab2.id FROM tab2 INNER JOIN tab3 ON tab2.id = tab3.id) SELECT tab1.id FROM tab1 INNER JOIN tab_tmp ON tab1.id = tab_tmp.id;

さらに、WITH 句に MATERIALIZED および NOT MATERIALIZED を指定した場合の違いも比較します。

検証結果

それぞれのクエリについて EXPLAIN を実行し、実行計画を比較しました。

testdb=# EXPLAIN SELECT tab1.id FROM tab1 INNER JOIN tab2 ON tab1.id = tab2.id INNER JOIN tab3 ON tab2.id = tab3.id;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=86.50..151.75 rows=1000 width=4)
   Hash Cond: (tab3.id = tab1.id)
   ->  Seq Scan on tab3  (cost=0.00..44.00 rows=3000 width=4)
   ->  Hash  (cost=74.00..74.00 rows=1000 width=8)
         ->  Hash Join  (cost=27.50..74.00 rows=1000 width=8)
               Hash Cond: (tab2.id = tab1.id)
               ->  Seq Scan on tab2  (cost=0.00..29.00 rows=2000 width=4)
               ->  Hash  (cost=15.00..15.00 rows=1000 width=4)
                     ->  Seq Scan on tab1  (cost=0.00..15.00 rows=1000 width=4)
(9 rows)
testdb=# EXPLAIN WITH tab_tmp AS (SELECT tab2.id FROM tab2 INNER JOIN tab3 ON tab2.id = tab3.id) SELECT tab1.id FROM tab1 INNER JOIN tab_tmp ON tab1.id = tab_tmp.id;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=105.25..148.42 rows=1000 width=4)
   Hash Cond: (tab2.id = tab3.id)
   ->  Seq Scan on tab2  (cost=0.00..29.00 rows=2000 width=4)
   ->  Hash  (cost=92.75..92.75 rows=1000 width=8)
         ->  Hash Join  (cost=27.50..92.75 rows=1000 width=8)
               Hash Cond: (tab3.id = tab1.id)
               ->  Seq Scan on tab3  (cost=0.00..44.00 rows=3000 width=4)
               ->  Hash  (cost=15.00..15.00 rows=1000 width=4)
                     ->  Seq Scan on tab1  (cost=0.00..15.00 rows=1000 width=4)
(9 rows)
testdb=# EXPLAIN WITH tab_tmp AS MATERIALIZED (SELECT tab2.id FROM tab2 INNER JOIN tab3 ON tab2.id = tab3.id) SELECT tab1.id FROM tab1 INNER JOIN tab_tmp ON tab1.id = tab_tmp.id;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Join  (cost=156.75..214.25 rows=1000 width=4)
   Hash Cond: (tab_tmp.id = tab1.id)
   CTE tab_tmp
     ->  Hash Join  (cost=54.00..129.25 rows=2000 width=4)
           Hash Cond: (tab3.id = tab2.id)
           ->  Seq Scan on tab3  (cost=0.00..44.00 rows=3000 width=4)
           ->  Hash  (cost=29.00..29.00 rows=2000 width=4)
                 ->  Seq Scan on tab2  (cost=0.00..29.00 rows=2000 width=4)
   ->  CTE Scan on tab_tmp  (cost=0.00..40.00 rows=2000 width=4)
   ->  Hash  (cost=15.00..15.00 rows=1000 width=4)
         ->  Seq Scan on tab1  (cost=0.00..15.00 rows=1000 width=4)
(11 rows)
testdb=# EXPLAIN WITH tab_tmp AS NOT MATERIALIZED (SELECT tab2.id FROM tab2 INNER JOIN tab3 ON tab2.id = tab3.id) SELECT tab1.id FROM tab1 INNER JOIN tab_tmp ON tab1.id = tab_tmp.id;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=105.25..148.42 rows=1000 width=4)
   Hash Cond: (tab2.id = tab3.id)
   ->  Seq Scan on tab2  (cost=0.00..29.00 rows=2000 width=4)
   ->  Hash  (cost=92.75..92.75 rows=1000 width=8)
         ->  Hash Join  (cost=27.50..92.75 rows=1000 width=8)
               Hash Cond: (tab3.id = tab1.id)
               ->  Seq Scan on tab3  (cost=0.00..44.00 rows=3000 width=4)
               ->  Hash  (cost=15.00..15.00 rows=1000 width=4)
                     ->  Seq Scan on tab1  (cost=0.00..15.00 rows=1000 width=4)
(9 rows)

WITH 句の有無や MATERIALIZED 指定の有無によって、結合順が変化しました。以下の表にその結果を示します。

クエリ 結合順
WITH 句なし tab1 → tab2 → tab3
WITH 句あり tab1 → tab3 → tab2
WITH 句あり + MATERIALIZED tab2 → tab3 → tab1
WITH 句あり + NOT MATERIALIZED tab1 → tab3 → tab2

注目すべき点は、「WITH 句あり」と「WITH 句あり + NOT MATERIALIZED」の実行計画が全く同じになったことです。これは、PostgreSQL の最適化によって WITH 句のクエリが親クエリに組み込まれたためと考えられます。

この最適化の詳細については、PostgreSQL 16 の公式ドキュメントにも記載されています（検証は PostgreSQL 17 で実施しましたが、現時点で公開されている日本語版マニュアルの最新バージョンを参照しています）。

WITH問い合わせが非再帰で副作用がない（つまり、揮発性（volatile）の関数を含まないSELECTである）場合は、親問い合わせに組み込むことができ、2つの問い合わせレベルを同時に最適化できます。デフォルトでは、親問い合わせがWITH問い合わせを1回だけ参照する場合にこれが発生しますが、WITH問い合わせを2回以上参照する場合には発生しません。この決定を上書きするには、MATERIALIZEDを指定してWITH問い合わせの個別の計算を強制するか、NOT MATERIALIZEDを指定して親問い合わせにマージするようにします。後者を選択すると、WITH問い合わせの計算が重複する危険性がありますが、WITH問い合わせを使用するたびにWITH問い合わせのごく一部しか必要としない場合は、全体の節約になります。

7.8. WITH問い合わせ（共通テーブル式）

WITH 句の有無で実行計画のコストを比較すると、WITH 句を使用したクエリのコストがわずかながら低くなっています。通常、WITH 句なしのほうが最適化しやすいように思えますが、WITH 句がクエリの構造を整理することで、プランナがより低コストな実行計画を導き出せた可能性があります。

一方で、WITH 句あり + MATERIALIZED の場合は、WITH 句内の tab2 と tab3 の結合が先に実行されるため、コストが上昇しました。これは、MATERIALIZED 指定により一時テーブルが作成され、その結果が参照されるためです。その結果、最適とは言えない実行計画になりました。

しかし、この挙動を利用すれば、結合順を意図的に変更する手段として WITH 句を活用できる可能性が示唆されます。例えば、PostgreSQL のプランナが意図しない結合順を選択した場合、MATERIALIZED を利用することで結合順を制御することができるかもしれません。

まとめ

実行計画の最適化を試みる際、WITH 句の適用を検討する価値があることが分かりました。クエリの可読性向上と合わせて、実行計画のチューニングの手段として活用を検討してみてはいかがでしょうか。