はじめに
ネストしたサブクエリ（副問合せ）はEXISTS条件でよく利用されます。ですが、このサブクエリのフィルタ条件次第ではパフォーマンス問題となることがあります。これを Oracle Database で改善する例がOracleの記事（以下、「記事」とだけ表記した場合はこの記事を指します）で取り上げられていました。
第52回 OR条件の続きとSQL計画ディレクティブの使用について
この記事で紹介された方法はPostgreSQLにも適用できます。PostgreSQLでの適用（SQLの書き換え）方法やパフォーマンス改善の仕組みを説明し、書き換えがどのようなケースでも有効かを検証します。
目次
SQLの書き換え
Oracle Database
記事の例を整形して引用します。
【書き換え前】
SELECT * FROM tab1 A1 WHERE EXISTS (SELECT 0 FROM tab2 A2 WHERE A2.c1 = A1.c1 AND (A2.c2 = 500 OR A1.c2 = 400));
【書き換え後】
SELECT * FROM tab1 A1 WHERE EXISTS (SELECT 0 FROM tab2 A2 WHERE A2.c1 = A1.c1 AND A2.c2 = 500) UNION ALL SELECT * FROM tab1 A1 WHERE EXISTS (SELECT 0 FROM tab2 A2 WHERE A2.c1 = A1.c1 AND A1.c2 = 400 AND LNNVL(A2.c2 = 500));
PostgreSQL
PostgreSQLの場合はLNNVL関数がありません。そこで、COALESCE と NOT を組み合わせて表現します。
参考：Oracle Database の LNNVL を PostgreSQL で実現する - ぱと隊長日誌
※書き換え前はOracleと同様のため省略します。
【書き換え後】
SELECT * FROM tab1 A1 WHERE EXISTS (SELECT 0 FROM tab2 A2 WHERE A2.c1 = A1.c1 AND A2.c2 = 500) UNION ALL SELECT * FROM tab1 A1 WHERE EXISTS (SELECT 0 FROM tab2 A2 WHERE A2.c1 = A1.c1 AND A1.c2 = 400 AND COALESCE(NOT(A2.c2 = 500), TRUE));
パフォーマンス改善の仕組み
記事でも書き換えの狙いについて解説していますが、本エントリで補足します。
なお、この改善の仕組みを深く理解するためにお勧めの本があります。
- 作者: エンコアコンサルティング
- 出版社/メーカー: 翔泳社
- 発売日: 2006/07/07
- メディア: 大型本
- 購入: 21人 クリック: 343回
- この商品を含むブログ (38件) を見る
EXISTSは「成功か、失敗か」を判定（確認）するブーリアン(Boolean)関数です。すなわち、EXISTSの条件に指定された集合の全ては必要なく、そのうちの1行でも該当すれば「成功」と判定されます。
EXISTSのサブクエリにはメインクエリの列が存在するため、サブクエリ単体で実行することはできません。そこで、メインクエリを先に実行し、その中間結果を受けてサブクエリを実行（EXISTS条件の判定）することになります。
このため、メインクエリで集合を十分小さく絞り込めないと、サブクエリを呼ばれる回数が増大することになります。
関連記事：SQLのWHERE句で用いられる相関サブクエリを理解する - ぱと隊長日誌
サブクエリ単体で実行することはできませんが、その条件次第では事前にサブクエリの結果である集合を絞り込むことができます。事前に集合を絞り込むことができれば、メインクエリの中間結果をEXISTSのサブクエリで確認する処理が効率よく行えることになります。
書き換え前のサブクエリの条件節を確認します。
A2.c1 = A1.c1 AND (A2.c2 = 500 OR A1.c2 = 400)
"A2.c1 = A1.c1"はメインクエリとサブクエリの列の両方が含まれるため、事前の絞り込みには利用できません。
"(A2.c2 = 500 OR A1.c2 = 400)"もメインクエリとサブクエリの列がOR条件でつながっているため、事前の絞り込みには利用できません。例えば、"A2.c2 = 500"に該当しないレコードでも、"A1.c2 = 400"の成り立つ可能性があるため、事前に除外することができません。
次に書き換え後のサブクエリの条件節を確認します（Oracle Database の例を利用します）。
UNION ALL 前段のSQLのサブクエリを確認します。
A2.c1 = A1.c1 AND A2.c2 = 500
"A2.c2 = 500"はメインクエリの結果に依存しておらず、事前の絞り込みに利用できます。
UNION ALL 後段のSQLのサブクエリを確認します。
A2.c1 = A1.c1 AND A1.c2 = 400 AND LNNVL(A2.c2 = 500)
"A1.c2 = 400"はサブクエリ内に記述されていますが、メインクエリの条件として扱うことができます。つまり、メインクエリの集合（中間結果）を小さく絞り込むことができます。
このように、書き換え前にはできなかった事前の絞り込みが書き換え後は有効となることがわかります。ただし、記事にもある通り、書き換えによってセミジョインが2回実行されることになるため、条件によっては狙ったような効果を得られないこともあることに注意が必要です。
PostgreSQLでの実測
測定環境
Hyper-V上に検証マシンを構築しました。
仮想プロセッサ：2個
メモリ：8192MB
CentOS 7
PostgreSQL 10.3
PostgreSQLはコードからビルドし、設定はデフォルトです。
テストテーブル・データ
テストデータの条件を変えるたびに以下のSQLを実行しました。
DROP TABLE tab1; DROP TABLE tab2; CREATE TABLE tab1( c1 integer NOT NULL DEFAULT 0, c2 integer NOT NULL DEFAULT 0 ); CREATE TABLE tab2( c1 integer NOT NULL DEFAULT 0, c2 integer NOT NULL DEFAULT 0 ); -- データの件数は条件に応じて設定する。 INSERT INTO tab1(c1, c2) SELECT i, i FROM generate_series(1, 10000000) as i ; INSERT INTO tab2(c1, c2) SELECT i, i FROM generate_series(1, 10000000) as i ; VACUUM ANALYZE;
測定方法
tab1, tab2のデータ件数の組み合わせ毎に、書き換え前後のSQLを EXPLAIN ANALYZE で実行しました。
キャッシュ状況による実行時間への影響を抑えるため、同じSQLを連続して実行し、3～5回目の Execution time の平均を実行時間としました。
掲載する実行計画は3回目以降に取得したものです。
結果
実行時間
| tab1[行数] | tab2[行数] | 書き換え前[ms] | 書き換え後[ms] |
|---|---|---|---|
| 10,000,000 | 10,000,000 | 99526.080 | 9412.461 |
| 10,000,000 | 1,000 | 4573.789 | 5391.029 |
| 1,000 | 10,000,000 | 10590.626 | 499.935 |
実行計画
(1)
tab1=10,000,000
tab2=10,000,000
書き換え前
Gather (cost=309310.55..477777.76 rows=2 width=8) (actual time=90064.670..98008.299 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Semi Join (cost=308310.55..476777.56 rows=1 width=8) (actual time=83112.371..93349.189 rows=1 loops=3)
Hash Cond: (a1.c1 = a2.c1)
Join Filter: ((a2.c2 = 500) OR (a1.c2 = 400))
Rows Removed by Join Filter: 3333333
-> Parallel Seq Scan on tab1 a1 (cost=0.00..85914.52 rows=4166652 width=8) (actual time=8.473..13931.876 rows=3333333 loops=3)
-> Hash (cost=144247.80..144247.80 rows=9999980 width=8) (actual time=31733.013..31733.013 rows=10000000 loops=3)
Buckets: 131072 Batches: 256 Memory Usage: 2553kB
-> Seq Scan on tab2 a2 (cost=0.00..144247.80 rows=9999980 width=8) (actual time=0.025..22586.940 rows=10000000 loops=3)(2)
tab1=10,000,000
tab2=10,000,000
書き換え後
Append (cost=97331.34..596907.79 rows=2 width=8) (actual time=504.257..9245.416 rows=2 loops=1)
-> Hash Semi Join (cost=97331.34..267828.90 rows=1 width=8) (actual time=504.256..8841.177 rows=1 loops=1)
Hash Cond: (a1.c1 = a2.c1)
-> Seq Scan on tab1 a1 (cost=0.00..144247.64 rows=9999964 width=8) (actual time=0.009..4121.556 rows=10000000 loops=1)
-> Hash (cost=97331.33..97331.33 rows=1 width=4) (actual time=503.765..503.765 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Gather (cost=1000.00..97331.33 rows=1 width=4) (actual time=0.360..503.761 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tab2 a2 (cost=0.00..96331.23 rows=1 width=4) (actual time=328.976..496.680 rows=0 loops=3)
Filter: (c2 = 500)
Rows Removed by Filter: 3333333
-> Nested Loop Semi Join (cost=1000.00..329078.87 rows=1 width=8) (actual time=0.705..404.234 rows=1 loops=1)
Join Filter: (a1_1.c1 = a2_1.c1)
Rows Removed by Join Filter: 399
-> Gather (cost=1000.00..97331.25 rows=1 width=8) (actual time=0.330..403.858 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tab1 a1_1 (cost=0.00..96331.15 rows=1 width=8) (actual time=261.607..395.839 rows=0 loops=3)
Filter: (c2 = 400)
Rows Removed by Filter: 3333333
-> Seq Scan on tab2 a2_1 (cost=0.00..169247.75 rows=4999990 width=4) (actual time=0.005..0.196 rows=400 loops=1)
Filter: COALESCE((c2 <> 500), true)(3)
tab1=10,000,000
tab2=1,000
書き換え前
Gather (cost=1027.50..97879.58 rows=1 width=8) (actual time=1.550..4486.569 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Semi Join (cost=27.50..96879.48 rows=1 width=8) (actual time=2981.615..4476.620 rows=1 loops=3)
Hash Cond: (a1.c1 = a2.c1)
Join Filter: ((a2.c2 = 500) OR (a1.c2 = 400))
Rows Removed by Join Filter: 333
-> Parallel Seq Scan on tab1 a1 (cost=0.00..85914.52 rows=4166652 width=8) (actual time=0.024..2202.727 rows=3333333 loops=3)
-> Hash (cost=15.00..15.00 rows=1000 width=8) (actual time=0.863..0.863 rows=1000 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 48kB
-> Seq Scan on tab2 a2 (cost=0.00..15.00 rows=1000 width=8) (actual time=0.007..0.402 rows=1000 loops=3)(4)
tab1=10,000,000
tab2=1,000
書き換え後
Append (cost=1017.51..195224.61 rows=2 width=8) (actual time=1.591..5790.684 rows=2 loops=1)
-> Gather (cost=1017.51..97869.59 rows=1 width=8) (actual time=1.590..5214.321 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Semi Join (cost=17.51..96869.49 rows=1 width=8) (actual time=3469.855..5206.497 rows=0 loops=3)
Hash Cond: (a1.c1 = a2.c1)
-> Parallel Seq Scan on tab1 a1 (cost=0.00..85914.52 rows=4166652 width=8) (actual time=0.246..2550.067 rows=3333333 loops=3)
-> Hash (cost=17.50..17.50 rows=1 width=4) (actual time=0.097..0.097 rows=1 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on tab2 a2 (cost=0.00..17.50 rows=1 width=4) (actual time=0.052..0.091 rows=1 loops=3)
Filter: (c2 = 500)
Rows Removed by Filter: 999
-> Nested Loop Semi Join (cost=1000.00..97355.00 rows=1 width=8) (actual time=1.847..576.357 rows=1 loops=1)
Join Filter: (a1_1.c1 = a2_1.c1)
Rows Removed by Join Filter: 399
-> Gather (cost=1000.00..97331.25 rows=1 width=8) (actual time=1.306..575.814 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tab1 a1_1 (cost=0.00..96331.15 rows=1 width=8) (actual time=377.590..569.091 rows=0 loops=3)
Filter: (c2 = 400)
Rows Removed by Filter: 3333333
-> Seq Scan on tab2 a2_1 (cost=0.00..17.50 rows=500 width=4) (actual time=0.007..0.313 rows=400 loops=1)
Filter: COALESCE((c2 <> 500), true)(5)
tab1=1,000
tab2=10,000,000
書き換え前
Hash Semi Join (cost=308310.66..347399.31 rows=1 width=8) (actual time=9539.860..10277.544 rows=2 loops=1)
Hash Cond: (a1.c1 = a2.c1)
Join Filter: ((a2.c2 = 500) OR (a1.c2 = 400))
Rows Removed by Join Filter: 998
-> Seq Scan on tab1 a1 (cost=0.00..15.00 rows=1000 width=8) (actual time=0.008..0.434 rows=1000 loops=1)
-> Hash (cost=144247.85..144247.85 rows=9999985 width=8) (actual time=9170.601..9170.601 rows=10000000 loops=1)
Buckets: 131072 Batches: 256 Memory Usage: 2553kB
-> Seq Scan on tab2 a2 (cost=0.00..144247.85 rows=9999985 width=8) (actual time=0.018..4096.851 rows=10000000 loops=1)(6)
tab1=1,000
tab2=10,000,000
書き換え後
Append (cost=1000.00..329126.59 rows=2 width=8) (actual time=496.239..498.212 rows=2 loops=1)
-> Nested Loop Semi Join (cost=1000.00..97361.36 rows=1 width=8) (actual time=496.237..497.723 rows=1 loops=1)
Join Filter: (a1.c1 = a2.c1)
Rows Removed by Join Filter: 999
-> Seq Scan on tab1 a1 (cost=0.00..15.00 rows=1000 width=8) (actual time=0.008..0.572 rows=1000 loops=1)
-> Materialize (cost=1000.00..97331.36 rows=1 width=4) (actual time=0.001..0.495 rows=1 loops=1000)
-> Gather (cost=1000.00..97331.36 rows=1 width=4) (actual time=0.349..494.411 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tab2 a2 (cost=0.00..96331.26 rows=1 width=4) (actual time=322.373..487.059 rows=0 loops=3)
Filter: (c2 = 500)
Rows Removed by Filter: 3333333
-> Nested Loop Semi Join (cost=0.00..231765.21 rows=1 width=8) (actual time=0.432..0.483 rows=1 loops=1)
Join Filter: (a1_1.c1 = a2_1.c1)
Rows Removed by Join Filter: 399
-> Seq Scan on tab1 a1_1 (cost=0.00..17.50 rows=1 width=8) (actual time=0.047..0.098 rows=1 loops=1)
Filter: (c2 = 400)
Rows Removed by Filter: 999
-> Seq Scan on tab2 a2_1 (cost=0.00..169247.81 rows=4999992 width=4) (actual time=0.006..0.200 rows=400 loops=1)
Filter: COALESCE((c2 <> 500), true)
実行結果の分析
実行時間の表をみると、条件（テーブルの行数）による効果の違いがわかります。書き換え後のほうが遅くなるケースもあり、書き換えを適用する際には注意が必要です。
実行計画(2)の例を確認します。
tab1=10,000,000
tab2=10,000,000
書き換え後
まず、UNION ALL 前段のSQLに相当する実行計画を確認します。
-> Hash Semi Join (cost=97331.34..267828.90 rows=1 width=8) (actual time=504.256..8841.177 rows=1 loops=1)
Hash Cond: (a1.c1 = a2.c1)
-> Seq Scan on tab1 a1 (cost=0.00..144247.64 rows=9999964 width=8) (actual time=0.009..4121.556 rows=10000000 loops=1)
-> Hash (cost=97331.33..97331.33 rows=1 width=4) (actual time=503.765..503.765 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Gather (cost=1000.00..97331.33 rows=1 width=4) (actual time=0.360..503.761 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tab2 a2 (cost=0.00..96331.23 rows=1 width=4) (actual time=328.976..496.680 rows=0 loops=3)
Filter: (c2 = 500)
Rows Removed by Filter: 3333333"A2.c2 = 500"を条件にサブクエリの集合を事前に十分絞り込んだことで、その後のメインクエリとの処理が効率よく行われています。
次に UNION ALL 後段のSQLに相当する実行計画を確認します。
-> Nested Loop Semi Join (cost=1000.00..329078.87 rows=1 width=8) (actual time=0.705..404.234 rows=1 loops=1)
Join Filter: (a1_1.c1 = a2_1.c1)
Rows Removed by Join Filter: 399
-> Gather (cost=1000.00..97331.25 rows=1 width=8) (actual time=0.330..403.858 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tab1 a1_1 (cost=0.00..96331.15 rows=1 width=8) (actual time=261.607..395.839 rows=0 loops=3)
Filter: (c2 = 400)
Rows Removed by Filter: 3333333
-> Seq Scan on tab2 a2_1 (cost=0.00..169247.75 rows=4999990 width=4) (actual time=0.005..0.196 rows=400 loops=1)
Filter: COALESCE((c2 <> 500), true)"A1.c2 = 400"を条件にメインクエリの集合が絞り込まれています。これにより、サブクエリとの処理件数を減らすことができました。
なお、tab2のシーケンシャルスキャンが rows=400 となっています。これは今回のテーブルのデータの特性によるものです。
今回のテーブルのデータは1始まりの連番かつ、tab1, tab2ともにc1=c2の関係にあります。
"A1.c2 = 400"かつ"A2.c1 = A1.c1"ということはtab2の先頭から400行目に該当レコードがあります。EXISTSは1行でも該当すれば「成功」と判定できるため、該当行を見つけた時点で処理を中断しました。これが rows=400 となった理由です。
