EXISTS記念日なので他のコードと実行計画を比較してみた

　皆様どうもこんにちは。会計ソフトに起票するよりSQLばかり書いている経理財務部のみおつくし0800です。
　ところで。今日は私が生まれて始めてSQLのEXISTS句でエラーも出ず求めるデータセットを出力できた日となりました。
　もう嬉しくて興奮冷めやらぬ間に書き上げてしまおうと、このチラシの裏にすらもったいないほど下らないEXISTS句の書き方、そして実行計画をさらっと眺めた結果をまとめておきます。

※特に断りがない限り、すべてPostgreSQLで実行・検証しています。

取り急ぎ、どうやって書けば良いの？

SELECT col1
FROM tab1
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);

　EXISTS句の中で最も重要なのはサブクエリ内のWHERE句です。EXISTS句は存在すればTRUEを返す、つまりWHERE句がTRUEと評価されるためデータセットに現れます。

　そのため、EXISTS句を有効に活用するにはメインクエリとサブクエリを紐付けるような条件が必要です。私はこの部分をすっかり見落としており、まるでIN句のように使おうとして泥沼にハマっていたのです。ANSI結合でもこういった表記しましたよね？

他にも書けるIN句とINNER JOIN

　EXISTS句は存在を判定する、とよく表記されますがそれならばIN句やINNER JOINでも書けるのではないか？と思考が進むかと思います。上記のクエリはそれぞれ以下のようにも表現できます。

select col1
from tab1
where col2 in (select col2 from tab2);

select col1
from tab1
inner join tab2
using(col2);

　上記は同じデータセットを出力するはずですが、パフォーマンスが異なるようです。

EXISTS句の真価は NOT EXISTS かもしれない

　実は私が今回生まれて始めて成功したのもNOT EXISTS句でありました。
　もしかすると、EXISTS句よりもNOT EXISTS句の方がイメージしやすいのかもしれません。せっかく引用したので上図の左側の上から2番目のクエリをNOT EXISTS句を用いて表現したいと思います。

SELECT <select list>
FROM TableA A
WHERE NOT EXISTS
    (SELECT 1
     FROM TableB B
     WHERE A.Key = B.Key);

自己満で終わらせないために実行計画で検証してみる

　NOT EXISTS句においても実行計画をもとに検証してみます。
　今回はちょうど手元に170万行ぐらいのテーブルがあったので、そこから以下の条件で抽出してみます。

デーブル：deposit
カラム　：拠点名(brand_name::text)、入金日(deposit_date::date)
求めるデータセット：今年度(> 22-03-31)になって初めて入金のあった拠点におけるその年月
条　件：前年度以前に取引がなかった拠点における入金日の最小値から年月を取り出す

○NOT EXISTSを用いたクエリ

select
    brand_name,
    extract(year from min(deposit_date)) as deposit_year,
    extract(month from min(deposit_date)) as deposit_month
from deposit as a
where a.deposit_date > '2022-03-31'
      and not exists(
          select 1 from deposit as b
          where b.deposit_date < '2022-04-01'
                and a.brand_name = b.brand_name)
group by brand_name
order by extract(year from min(deposit_date)),
         extract(month from min(deposit_date));

○LEFT OUTER JOINとWHERE句を用いたクエリ

select
    a.brand_name,
    extract(year from min(a.deposit_date)) as deposit_year,
    extract(month from min(a.deposit_date)) as deposit_month
from deposit as a
left outer join deposit as b
on a.deposit_date > '2022-03-31'
   and b.deposit_date < '2022-04-01'
   and a.brand_name = b.brand_name
where b.brand_name is null
group by a.brand_name
order by extract(year from min(a.deposit_date)),
      extract(month from min(a.deposit_date));

○EXPLAINの結果

・NOT EXISTS

Sort (cost=230432.11..230432.11 rows=1 width=98)

・LEFT OURT JOIN

Sort (cost=242222.35..242222.63 rows=111 width=98)

・（比較用）SELECT * FROM deposit

Seq Scan on deposit (cost=0.00..118605.26 rows=1709626 width=226)

　このページによると、今回は主にcostとrowsの比較をすれば良さそう。
　結果は火を見るより明らかですが、NOT EXISTSに関してはすぐに結果表示されたのに対してLEFT OUTER JOINは5分ほど経っても応答がなく正しいかどうかの検証もままならぬまま。。。
　クエリの文法が正しいと仮定すれば、自己結合のコスト高を改めて学べたのかもしれません。