前提

DB：postgres9.4くらい

概要

SQL抽出要件でとあるテーブルには存在して、とあるテーブルには存在しないデータがあり、
その差分を抽出する要件があった。

プライマリーキーはID。
とあるテーブルにはおよそ50万件ほどデータが存在。

結論

EXIST句は早かった。

まずIN句で差分を抽出

30分以上かかり中断。

IN句で書く

-- すべてデータ存在するテーブル
WITH all_data AS (
SELECT ID1,ID2 FROM all_data
)

## データ一部欠損してるテーブル
, missing_data AS (
SELECT ID1,ID2 FROM missing_data 
)

-- 差分検索
SELECT * FROM all_data WHERE (ID1,ID2) NOT IN (SELECT ID1,ID2 FROM missing_data );

EXIST句で差分を抽出

数秒で抽出。

EXIST句で書く

-- すべてデータ存在するテーブル
WITH all_data AS (
SELECT ID1,ID2 FROM all_data
)

-- データ一部欠損してるテーブル
, missing_data AS (
SELECT ID1,ID2 FROM missing_data 
)

-- 差分検索
SELECT * FROM all_data WHERE NOT EXISTS (
  SELECT * FROM missing_data WHERE ID1 = all_data.ID1 AND ID2 = all_data.ID2
);

なぜIN句は遅いのか

以下サイトや他サイトによると、「IN」または「NOT IN」を入れると、クエリエンジンはすべての行を調べて条件が満たされているかどうかを一々確認する「テーブルフルスキャン」を行うから遅いとのこと。
https://itnews.org/news_contents/sql-201-10-ways-to-tweak-slow-running-queries-3pkb

補足

未検証だがPostgres以外だと上記通りIN句が遅いとは限らないとのこと。

以上