【PostgreSQL】IN vs EXISTS vs JOIN、結局どれが速いの？

こんにちは、新卒1年目のtakaramです。まもなく入社して丸一年となり、ほとんど経験のなかったSQLの力もついてきたと思っています。

しかし、パフォーマンス面も考慮したSQLとなると、まだまだ知識が足りないと感じています。

特に、一対多の関連テーブルの一方を使って他方を絞り込む、といったSQLは、ネットを見てもEXISTSが速いという記事があったり相関サブクエリだから遅いという情報があったり……何が本当かよくわかりません。そこで、今回自分で調べてみることにしました。

なお、今回検証に用いたのはPostgreSQL 11.4です。

検証

テスト用データ

テスト用テーブルとして、従業員テーブルemployeesと部署テーブルdepartmentsを用意し、それぞれ5万件、1000件のレコードを挿入しました*1。各テーブルはIDと名前のほか、従業員テーブルは所属部署IDと入社年を入れています。

これを使って2種類のデータを抽出するSQLを、それぞれIN, EXISTS, JOINの3パターンで作成し、EXPLAIN ANALYZEで実行時間・コストを比較しました。

1：2020年入社の社員がいる部署名を抽出

まず、各パターンのクエリはこんな感じです。

-- INパターン
SELECT name FROM departments
WHERE id IN (
  SELECT department_id FROM employees WHERE join_year = 2020
);

-- EXISTSパターン
SELECT name FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e
  WHERE e.department_id = d.id AND e.join_year = 2020
);

-- JOINパターン
SELECT d.name FROM departments d
INNER JOIN (
  SELECT DISTINCT department_id FROM employees
  WHERE join_year = 2020
) tmp ON tmp.department_id = d.id;

それぞれの実行計画を出してみます。

• IN

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=963.88..994.63 rows=1000 width=12) (actual time=5.690..6.153 rows=720 loops=1)
   Hash Cond: (departments.id = employees.department_id)
   ->  Seq Scan on departments  (cost=0.00..17.00 rows=1000 width=16) (actual time=0.023..0.178 rows=1000 loops=1)
   ->  Hash  (cost=954.66..954.66 rows=737 width=4) (actual time=5.651..5.651 rows=720 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 34kB
         ->  HashAggregate  (cost=947.29..954.66 rows=737 width=4) (actual time=5.376..5.500 rows=720 loops=1)
               Group Key: employees.department_id
               ->  Seq Scan on employees  (cost=0.00..944.00 rows=1318 width=4) (actual time=0.014..5.045 rows=1253 loops=1)
                     Filter: (join_year = 2020)
                     Rows Removed by Filter: 48747
 Planning Time: 0.614 ms
 Execution Time: 6.336 ms

• EXISTS

                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=963.88..994.63 rows=1000 width=12) (actual time=5.262..5.723 rows=720 loops=1)
   Hash Cond: (d.id = e.department_id)
   ->  Seq Scan on departments d  (cost=0.00..17.00 rows=1000 width=16) (actual time=0.006..0.171 rows=1000 loops=1)
   ->  Hash  (cost=954.66..954.66 rows=737 width=4) (actual time=5.237..5.237 rows=720 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 34kB
         ->  HashAggregate  (cost=947.29..954.66 rows=737 width=4) (actual time=4.957..5.084 rows=720 loops=1)
               Group Key: e.department_id
               ->  Seq Scan on employees e  (cost=0.00..944.00 rows=1318 width=4) (actual time=0.010..4.628 rows=1253 loops=1)
                     Filter: (join_year = 2020)
                     Rows Removed by Filter: 48747
 Planning Time: 0.570 ms
 Execution Time: 5.902 ms

• JOIN

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=971.25..990.88 rows=737 width=12) (actual time=7.757..8.241 rows=720 loops=1)
   Hash Cond: (d.id = employees.department_id)
   ->  Seq Scan on departments d  (cost=0.00..17.00 rows=1000 width=16) (actual time=0.009..0.174 rows=1000 loops=1)
   ->  Hash  (cost=962.03..962.03 rows=737 width=4) (actual time=7.739..7.740 rows=720 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 34kB
         ->  HashAggregate  (cost=947.29..954.66 rows=737 width=4) (actual time=4.406..7.568 rows=720 loops=1)
               Group Key: employees.department_id
               ->  Seq Scan on employees  (cost=0.00..944.00 rows=1318 width=4) (actual time=0.007..4.041 rows=1253 loops=1)
                     Filter: (join_year = 2020)
                     Rows Removed by Filter: 48747
 Planning Time: 0.127 ms
 Execution Time: 8.359 ms

予想外だったのですが、この3パターン、ほとんど同じ実行計画になりました！INとEXISTSは全く同じ、JOINもコストの数値が若干異なるだけです。

2：開発1課～9課の従業員を抽出

こちらのSQLは以下のようにしました。

-- INパターン
SELECT name FROM employees
WHERE department_id IN (
  SELECT id FROM departments
  WHERE name LIKE '開発_課'
);

-- EXISTSパターン
SELECT name FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE e.department_id = d.id AND name LIKE '開発_課'
);

-- JOINパターン
SELECT name FROM employees e
INNER JOIN (
  SELECT DISTINCT id FROM departments
  WHERE name LIKE '開発_課'
) tmp ON e.department_id = tmp.id;

では実行計画を見てみます。

• IN

                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=19.51..970.32 rows=50 width=11) (actual time=0.283..16.909 rows=450 loops=1)
   Hash Cond: (employees.department_id = departments.id)
   ->  Seq Scan on employees  (cost=0.00..819.00 rows=50000 width=15) (actual time=0.007..7.910 rows=50000 loops=1)
   ->  Hash  (cost=19.50..19.50 rows=1 width=4) (actual time=0.104..0.105 rows=9 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on departments  (cost=0.00..19.50 rows=1 width=4) (actual time=0.051..0.100 rows=9 loops=1)
               Filter: (name ~~ '開発_課'::text)
               Rows Removed by Filter: 991
 Planning Time: 0.160 ms
 Execution Time: 16.985 ms

• EXISTS

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=19.51..970.32 rows=50 width=11) (actual time=0.276..16.525 rows=450 loops=1)
   Hash Cond: (e.department_id = d.id)
   ->  Seq Scan on employees e  (cost=0.00..819.00 rows=50000 width=15) (actual time=0.006..7.664 rows=50000 loops=1)
   ->  Hash  (cost=19.50..19.50 rows=1 width=4) (actual time=0.103..0.104 rows=9 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on departments d  (cost=0.00..19.50 rows=1 width=4) (actual time=0.050..0.099 rows=9 loops=1)
               Filter: (name ~~ '開発_課'::text)
               Rows Removed by Filter: 991
 Planning Time: 0.161 ms
 Execution Time: 16.608 ms

• JOIN

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=19.54..970.35 rows=50 width=11) (actual time=0.392..16.747 rows=450 loops=1)
   Hash Cond: (e.department_id = departments.id)
   ->  Seq Scan on employees e  (cost=0.00..819.00 rows=50000 width=15) (actual time=0.009..7.770 rows=50000 loops=1)
   ->  Hash  (cost=19.53..19.53 rows=1 width=4) (actual time=0.161..0.161 rows=9 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Unique  (cost=19.51..19.52 rows=1 width=4) (actual time=0.149..0.156 rows=9 loops=1)
               ->  Sort  (cost=19.51..19.52 rows=1 width=4) (actual time=0.148..0.150 rows=9 loops=1)
                     Sort Key: departments.id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Seq Scan on departments  (cost=0.00..19.50 rows=1 width=4) (actual time=0.054..0.115 rows=9 loops=1)
                           Filter: (name ~~ '開発_課'::text)
                           Rows Removed by Filter: 991
 Planning Time: 0.123 ms
 Execution Time: 16.836 ms

こちらもパターン1同様、INとEXISTSは全く同じ実行計画です。JOINの方はDISTINCTのUnique, Sortが入っていますが、コストはわずかです。

結論

単純なクエリではIN, EXISTS, JOINとも、おおむね同様の実行計画となることがわかりました。オプティマイザが思った以上に賢かったですね！

より複雑なクエリや、インデックスの有無によっては変わってくるかもしれませんが、この程度のシンプルなクエリならあまりパフォーマンスを気にする必要はないと考えていいでしょう。

---

• エンジニア中途採用サイト
  ラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております！
  ご興味ありましたら是非ご確認をお願いします。
  
  https://career-recruit.rakus.co.jp/career_engineer/

• カジュアル面談お申込みフォーム
  どの職種に応募すれば良いかわからないという方は、カジュアル面談も随時行っております。
  以下フォームよりお申込みください。
  rakus.hubspotpagebuilder.com

• ラクスDevelopers登録フォーム
  
  https://career-recruit.rakus.co.jp/career_engineer/form_rakusdev/

• イベント情報
  会社の雰囲気を知りたい方は、毎週開催しているイベントにご参加ください！

◆TECH PLAY
techplay.jp

◆connpass
rakus.connpass.com

*1:参考までに、利用したSQLはこちらです