Go to Qiita Advent Calendar 2024 Top
LoginSignup
21
17

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@tnemotox

PostgreSQLでレコードの存在チェックを行う

Last updated at Posted at 2018-06-08

データベースにレコードが存在するかどうかをチェックする方法について、簡単にまとめてみる。

よくみかける、恐らく一番最初に思いつくであろう方法は、SQLでレコードを取得した後に、アプリケーションでチェックをするパターン。

SQL
SELECT COUNT(*) FROM exists_check WHERE id = 500000;
Java
int count = repository.exists(id);
// 取得したレコード数が1件以上であれば存在と判定
if (count >= 1) {
  // (snip)
}

しかし、これはイケてない点が3つある。

  1. 存在チェックを行うSQLを呼び出すメソッド名がexistsだが、発行するSQLはカウントである
  2. アプリケーション側で比較などする必要がある
  3. レコード数が多い場合、処理に時間がかかる場合がある

じゃあメソッド名をカウントにするかと言われるとそれもなんだかなぁという感じですし、存在チェックというからにはintではなくbooleanで返したいところ。
性能面は、レコード数やWHERE句で絞り込むカラムにindexが張られているかどうかで変わってきますが、いずれにせよよいSQLとは言えなさそうです。

ということで、COUNT句ではなくEXISTS句を使うようにしよう。

SQL
SELECT EXISTS (SELECT * FROM exists_check WHERE id = 500000);
Java
boolean isExists = repository.exists(id);
if (isExists) {
  // (snip)
}

これならば、上記で挙げたイケてない点は解消できています。
1.と2.についてはわかりやすいですね。
3.の性能面は、どれくらい違いがあるのかを実際に見てみましょう。

実行環境とPostgreSQLのバージョンは以下の通り。

実行環境
$ system_profiler SPHardwareDataType

Hardware:
    Hardware Overview:

      Model Name: MacBook Pro
      Model Identifier: MacBookPro12,1
      Processor Name: Intel Core i5
      Processor Speed: 2.7 GHz
      Number of Processors: 1
      Total Number of Cores: 2
      L2 Cache (per Core): 256 KB
      L3 Cache: 3 MB
      Memory: 8 GB
PostgreSQL
$ psql

psql (9.5.1)

まず、適当なテーブルを用意して、1,000,000件のレコードを挿入します。

準備
-- indexを張ったカラム"id"と張っていないカラム"num"を用意
CREATE TABLE exists_check(id SERIAL PRIMARY KEY, num INTEGER);

-- 1,000,000件のレコードを挿入
CREATE FUNCTION insert1000000() returns int as
'
DECLARE
i int;
BEGIN
  FOR i IN 1..1000000 loop
  INSERT INTO exists_check VALUES (i, i);
  END LOOP;
  RETURN 0;
END
'
LANGUAGE 'plpgsql';

SELECT insert1000000();

以下の3つの条件でEXPLAIN ANALYZEしてみましょう。

  1. indexを張ったカラムに対して500,000番目のレコードを絞り込む
  2. indexを張っていないカラムに対して10番目のレコードを絞り込む
  3. indexを張っていないカラムに対して500,000番目のレコードを絞り込む
indexを張ったカラムに対して500,000番目のレコードを絞り込む
# EXPLAIN ANALYZE SELECT EXISTS (SELECT * FROM exists_check WHERE id = 500000);

                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=8.44..8.45 rows=1 width=0) (actual time=0.027..0.027 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Only Scan using exists_check_pkey on exists_check  (cost=0.42..8.44 rows=1 width=0) (actual time=0.024..0.024 rows=1 loops=1)
           Index Cond: (id = 500000)
           Heap Fetches: 1
 Planning time: 0.127 ms
 Execution time: 0.065 ms
(7 rows)


# EXPLAIN ANALYZE SELECT COUNT(*) FROM exists_check WHERE id = 500000;

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8.45..8.46 rows=1 width=0) (actual time=0.030..0.031 rows=1 loops=1)
   ->  Index Only Scan using exists_check_pkey on exists_check  (cost=0.42..8.44 rows=1 width=0) (actual time=0.023..0.023 rows=1 loops=1)
         Index Cond: (id = 500000)
         Heap Fetches: 1
 Planning time: 0.108 ms
 Execution time: 0.105 ms
(6 rows)


# EXPLAIN ANALYZE SELECT COUNT(*) FROM exists_check WHERE id = 500000 LIMIT 1;

                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=8.45..8.46 rows=1 width=0) (actual time=0.031..0.032 rows=1 loops=1)
   ->  Aggregate  (cost=8.45..8.46 rows=1 width=0) (actual time=0.030..0.030 rows=1 loops=1)
         ->  Index Only Scan using exists_check_pkey on exists_check  (cost=0.42..8.44 rows=1 width=0) (actual time=0.023..0.024 rows=1 loops=1)
               Index Cond: (id = 500000)
               Heap Fetches: 1
 Planning time: 0.120 ms
 Execution time: 0.105 ms
(7 rows)

Index Scanが行われるのでどれも大きくは変わりません。それでも、若干EXISTS句を使っている方が早いですね。
では、続いてindexを張っていないカラムの場合を見てみましょう。

indexを張っていないカラムに対して10番目のレコードを絞り込む
# EXPLAIN ANALYZE SELECT EXISTS (SELECT * FROM exists_check WHERE num = 10);

                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Result  (cost=16925.00..16925.01 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on exists_check  (cost=0.00..16925.00 rows=1 width=0) (actual time=0.026..0.026 rows=1 loops=1)
           Filter: (num = 10)
           Rows Removed by Filter: 9
 Planning time: 0.111 ms
 Execution time: 0.064 ms
(7 rows)


# EXPLAIN ANALYZE SELECT COUNT(*) FROM exists_check WHERE num = 10;

                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16925.00..16925.01 rows=1 width=0) (actual time=172.878..172.878 rows=1 loops=1)
   ->  Seq Scan on exists_check  (cost=0.00..16925.00 rows=1 width=0) (actual time=0.025..172.873 rows=1 loops=1)
         Filter: (num = 10)
         Rows Removed by Filter: 999999
 Planning time: 0.088 ms
 Execution time: 172.927 ms
(6 rows)


# EXPLAIN ANALYZE SELECT COUNT(*) FROM exists_check WHERE num = 10 LIMIT 1;

                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=16925.00..16925.01 rows=1 width=0) (actual time=174.567..174.567 rows=1 loops=1)
   ->  Aggregate  (cost=16925.00..16925.01 rows=1 width=0) (actual time=174.567..174.567 rows=1 loops=1)
         ->  Seq Scan on exists_check  (cost=0.00..16925.00 rows=1 width=0) (actual time=0.015..174.561 rows=1 loops=1)
               Filter: (num = 10)
               Rows Removed by Filter: 999999
 Planning time: 0.067 ms
 Execution time: 174.602 ms
(7 rows)

こちらはSequential Scanになるので、明確に差が出ます。
EXISTS句を使った場合はレコードが見つかった時点で探索を終了しているのに対し、COUNT句の場合はLIMIT句を使おうが使わまいが、最初から最後まで探索をしていますね。
10番目ではなく500,000番目にレコードが見つかった場合を確認してみます。EXISTS句を使う場合は探索に時間がかかる分遅くなり、COUNT句の場合は変わらないはずです。

indexを張っていないカラムに対して500,000番目のレコードを絞り込む
# EXPLAIN ANALYZE SELECT EXISTS (SELECT * FROM exists_check WHERE num = 500000);

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Result  (cost=16925.00..16925.01 rows=1 width=0) (actual time=86.143..86.144 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on exists_check  (cost=0.00..16925.00 rows=1 width=0) (actual time=86.140..86.140 rows=1 loops=1)
           Filter: (num = 500000)
           Rows Removed by Filter: 499999
 Planning time: 0.154 ms
 Execution time: 87.248 ms
(7 rows)


# EXPLAIN ANALYZE SELECT COUNT(*) FROM exists_check WHERE num = 500000;

                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16925.00..16925.01 rows=1 width=0) (actual time=166.321..166.321 rows=1 loops=1)
   ->  Seq Scan on exists_check  (cost=0.00..16925.00 rows=1 width=0) (actual time=85.468..166.315 rows=1 loops=1)
         Filter: (num = 500000)
         Rows Removed by Filter: 999999
 Planning time: 0.087 ms
 Execution time: 166.425 ms
(6 rows)


# EXPLAIN ANALYZE SELECT COUNT(*) FROM exists_check WHERE num = 500000 LIMIT 1;

                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=16925.00..16925.01 rows=1 width=0) (actual time=169.626..169.626 rows=1 loops=1)
   ->  Aggregate  (cost=16925.00..16925.01 rows=1 width=0) (actual time=169.625..169.625 rows=1 loops=1)
         ->  Seq Scan on exists_check  (cost=0.00..16925.00 rows=1 width=0) (actual time=88.308..169.618 rows=1 loops=1)
               Filter: (num = 500000)
               Rows Removed by Filter: 999999
 Planning time: 0.101 ms
 Execution time: 169.678 ms
(7 rows)

予想通りの結果ですね。

いずれのパターンでも、EXISTS句を使った方が早いことは確認できました。
indexが張られているカラムであれば大差ないけど、その限りではないこともあるはず。
このような単純なテーブル、SQLを1回呼び出すだけで有意な差がついているので、実際のアプリケーションではもっと効いてくるでしょう。いずれにせよ、早くてわかりやすい方が良いですね。

結論：存在チェックはEXISTS句を使おう。

21
17
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
21
17

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?