【SQL】存在チェックにJOINとEXISTSを使った場合のパフォーマンスと実行計画の違い

2025年12月12日

本記事の内容は PostgreSQL 15.4で検証しています。

やりたいこと

以下のような注文テーブルにユーザー別、日別に注文額が保存されているとします。このテーブルから"特別商品"を注文したユーザーの当該月の注文データを検索するSQLを作成します。

以下の例ではuser1の2023年1月の注文データが検索にヒットします。

ユーザー名注文年月日商品名注文額
user12023-01-01一般商品8111.20
user12023-01-02特別商品5927.40
…………
user12023-01-31特別商品6127.40
user12023-02-01一般商品7511.20
…………
user12023-02-28一般商品4422.1
user22023-01-01一般商品2329.69
user22023-01-02一般商品5885.06
…………
注文テーブルのイメージ

実際のプロジェクトでこのような注文テーブルの設計はあり得ないと思いますが、特定のレコードが存在するとう条件で検索するSQLを作成するための架空のテーブルです。

テーブル定義

【SQL】LAG関数を使った別行との比較で作成たした注文テーブルに商品名のカラムを追加します。全データの商品名を一般商品を表す’normal_item’に更新した後、特別商品の購入データを何件か作成します。

# 商品名カラムを追加
ALTER TABLE  test_orders ADD item_name text;

# 全データの商品名を更新
UPDATE test_orders SET item_name = 'normal_item';

# 注文額を条件に指定して何件かの商品名を更新
UPDATE test_orders SET item_name = 'special_item' WHERE order_amount in (9628.22, 7839.75, 7369.28);

特別商品を注文したデータが3件作成されました。

 SELECT * FROM test_orders WHERE item_name = 'special_item';
 user_name | order_date | order_amount |  item_name
-----------+------------+--------------+--------------
 user5     | 2023-01-02 |      9628.22 | special_item
 user4     | 2023-01-05 |      7839.75 | special_item
 user2     | 2023-03-02 |      7369.28 | special_item
(3 rows)

これで、user4, user5の2023年1月とuser2の2023年2月の注文データが検索対象のデータとなります。

SQL作成

JOINを使ったSQL

以下のように考え、JOINを使ったSQLを作成しました。

  • 元テーブルから特別商品を注文したレコードのユーザー名と注文月から一時テーブルを作成する。
    • 最終的に当該月の全データを検索するために注文日を月で丸める。
    • ユーザー名と月の重複を削除する。
  • 元テーブルから注文日を月で丸めただけの別の一時テーブルを作成する。
  • 2つの一時テーブルをユーザー名と注文月をキーにJOINする。
 with tmp1 AS (
    SELECT 
        DISTINCT
        user_name,
        date_trunc('month', order_date) AS order_month
    FROM test_orders
    WHERE item_name = 'special_item'
    ORDER BY user_name, order_month

),
tmp2 AS (
    SELECT
        user_name,
        date_trunc('month', order_date) AS order_month,
        order_date,
        order_amount
    FROM test_orders
)
SELECT
    t2.user_name,
    t2.order_date,
    t2.order_amount
FROM tmp2 t2
INNER JOIN tmp1 tmp1
ON t2.user_name = tmp1.user_name
AND t2.order_month = tmp1.order_month
ORDER BY t2.user_name, t2.order_month, t2.order_date;

EXISTSを使ったSQL

各注文データに対して当該ユーザー、当該月に特別商品の注文データが存在するかの検証を素直にEXISTSを使ったSQLを作成しました。

SELECT
    t.user_name,
    t.order_date,
    item_name,
    t.order_amount
FROM test_orders t
WHERE EXISTS (
    SELECT 1
    FROM test_orders t2
    WHERE t2.user_name = t.user_name
      AND date_trunc('month', t2.order_date) = date_trunc('month', t.order_date)
      AND item_name = 'special_item'
)
ORDER BY
    t.user_name,
    date_trunc('month', t.order_date),
    t.order_date;

実行計画の確認

JOINを使ったSQLの実行計画

実行計画は以下のようになりました。

 Sort  (cost=609.47..609.50 rows=11 width=24) (actual time=7.192..7.213 rows=310 loops=1)
   Sort Key: test_orders.user_name, (date_trunc('month'::text, (test_orders.order_date)::timestamp with time zone)), test_orders.order_date
   Sort Method: quicksort  Memory: 46kB
   ->  Hash Join  (cost=288.11..609.28 rows=11 width=24) (actual time=1.748..6.846 rows=310 loops=1)
         Hash Cond: ((test_orders.user_name = test_orders_1.user_name) AND (date_trunc('month'::text, (test_orders.order_date)::timestamp with time zone) = (date_trunc('month'::text, (test_orders_1.order_date)::timestamp with time zone))))
         ->  Seq Scan on test_orders  (cost=0.00..260.65 rows=10965 width=16) (actual time=0.025..0.956 rows=10965 loops=1)
         ->  Hash  (cost=288.10..288.10 rows=1 width=14) (actual time=1.704..1.708 rows=3 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Unique  (cost=288.08..288.09 rows=1 width=14) (actual time=1.689..1.694 rows=3 loops=1)
                     ->  Sort  (cost=288.08..288.08 rows=1 width=14) (actual time=1.686..1.688 rows=3 loops=1)
                           Sort Key: test_orders_1.user_name, (date_trunc('month'::text, (test_orders_1.order_date)::timestamp with time zone))
                           Sort Method: quicksort  Memory: 25kB
                           ->  Seq Scan on test_orders test_orders_1  (cost=0.00..288.07 rows=1 width=14) (actual time=0.020..1.646 rows=3 loops=1)
                                 Filter: (item_name = 'special_item'::text)
                                 Rows Removed by Filter: 10962
 Planning Time: 0.381 ms
 Execution Time: 7.310 ms
(17 rows)

2つの一時テーブル作成tmp1, tmp2のSeq Scanが実行されます。一時テーブルtmp1中のユーザー名と注文月の重複を削除するためにUniqueが実行されていることがわかります。

作成された2つの一時テーブルはHash Joinで結合されます。

EXISTSを使ったSQLの実行計画

実行計画は以下のようになりました。

                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=612.25..612.28 rows=12 width=36) (actual time=6.424..6.444 rows=310 loops=1)
   Sort Key: t.user_name, (date_trunc('month'::text, (t.order_date)::timestamp with time zone)), t.order_date
   Sort Method: quicksort  Memory: 49kB
   ->  Hash Semi Join  (cost=288.08..612.04 rows=12 width=36) (actual time=1.382..6.059 rows=310 loops=1)
         Hash Cond: ((t.user_name = t2.user_name) AND (date_trunc('month'::text, (t.order_date)::timestamp with time zone) = date_trunc('month'::text, (t2.order_date)::timestamp with time zone)))
         ->  Seq Scan on test_orders t  (cost=0.00..260.65 rows=10965 width=28) (actual time=0.011..0.833 rows=10965 loops=1)
         ->  Hash  (cost=288.06..288.06 rows=1 width=10) (actual time=1.357..1.358 rows=3 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on test_orders t2  (cost=0.00..288.06 rows=1 width=10) (actual time=0.005..1.345 rows=3 loops=1)
                     Filter: (item_name = 'special_item'::text)
                     Rows Removed by Filter: 10962
 Planning Time: 0.168 ms
 Execution Time: 6.513 ms
(13 rows)

EXISTSの該当するデータが1件でも存在すればという部分がHash Semi Joinとなっています。一時テーブルを作成しないためJOINを使ったSQLに比べて実行計画が全体的にシンプルになっています。

実行計画の比較

JOINとEXISTSを使ったSQLの実行時間の差を見ると、1ms程度でありほとんど差がでませんした。これは今回の検証ではデータ数が約1万件と少なかったことが主な原因と考えられます。データ数が数百万件になると実行時間の差が大きくなると予想されます。

EXISTSを使ったSQLの方が実装から意図を読み取りやすく、全体的にSQLもシンプルになるため特定のデータが存在するという条件のSQLを作成する場合はEXISTSの使用を第一候補にして問題無さそうです。

PostgreSQL,SQL

Posted by fanfanta