PostgreSQL テーブル差集合のパフォーマンス
お題
次の記事を参照後、興味があってPostgreSQL におけるテーブル差集合のパフォーマンスを測定しました。
今回は灰色部分を求めます。
結論
測定結果は次のようになりました
not exists >>> [Big Wall] >>> left join / is NULL >= not in > except all
[not in]
Planning Time: 0.047 ms
Execution Time: 475.355 ms
[not exists]
Planning Time: 0.024 ms
Execution Time: 3.766 ms
[left join / is NULL]
Planning Time: 0.033 ms
Execution Time: 349.660 ms
[except all]
Planning Time: 0.017 ms
Execution Time: 718.943 ms
差集合を得たい場合は exists を使えば、パフォーマンス問題に悩まされないようです。
参考
データに応じて測定結果が変わりますが、データ量を増減させた時のパフォーマンス特性も掲載しておきます。
t2 の行数を変化させながら測定しています。
PostgreSQLの9.2以降はnot in も not existsと遜色ないという記事もあるが、そんなことはありませんでした。
実験
[環境]
- Ubuntu 22.04
- PostgreSQL 14
psql Cli から DB 作成後にアクセス
root@masami-L ~# createdb -U postgres sample
root@masami-L /# psql -U postgres -d sample
ベンチマーク用関数用意
sample=# CREATE FUNCTION f_test(ct int, sql text)
RETURNS void
LANGUAGE plpgsql AS
$func$
DECLARE
i int;
BEGIN
FOR i IN 1 .. $1 LOOP
EXECUTE sql;
END LOOP;
END
$func$;
CREATE FUNCTION
データ準備
sample=# create table t1 (
id serial,
val int default random()*1000);
CREATE TABLE
sample=# create table t2 (
id serial,
val int default random()*1000);
CREATE TABLE
sample=# insert into t1
select from generate_series(1,10000);
INSERT 0 10000
sample=# insert into t2
select from generate_series(1,10000);
INSERT 0 10000
■not in
実行計画の確認
sample=# explain analyze select * from t1 where (val) not in (select val from t2);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=172.12..344.25 rows=5085 width=8) (actual time=16.781..16.783 rows=0 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 10000
SubPlan 1
-> Seq Scan on t2 (cost=0.00..146.70 rows=10170 width=4) (actual time=0.037..4.310 rows=10000 loops=1)
Planning Time: 1.118 ms
Execution Time: 17.252 ms
(7 rows)
パフォーマンスの確認
sample=# EXPLAIN ANALYZE
SELECT f_test(100, 'select * from t1 where (val) not in (select val from t2);');
QUERY PLAN
----------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=4) (actual time=475.229..475.230 rows=1 loops=1)
Planning Time: 0.047 ms
Execution Time: 475.355 ms
(3 rows)
■not exists
実行計画の確認
sample=# explain analyze select * from t1 where not exists (select val from t2);
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Result (cost=0.01..145.01 rows=10000 width=8) (actual time=0.028..0.030 rows=0 loops=1)
One-Time Filter: (NOT $0)
InitPlan 1 (returns $0)
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=0) (actual time=0.023..0.023 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.01..145.01 rows=10000 width=8) (never executed)
Planning Time: 0.159 ms
Execution Time: 0.081 ms
(7 rows)
パフォーマンスの確認
sample=# EXPLAIN ANALYZE
SELECT f_test(100, 'select * from t1 where not exists (select val from t2);');
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=4) (actual time=3.752..3.752 rows=1 loops=1)
Planning Time: 0.024 ms
Execution Time: 3.766 ms
(3 rows)
■left join / is NUL
実行計画の確認
sample=# explain analyze select * from t1 left join t2 using (val) where t2.val is NULL;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=270.00..490.00 rows=1 width=12) (actual time=6.997..6.999 rows=0 loops=1)
Hash Cond: (t1.val = t2.val)
-> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) (actual time=0.013..1.048 rows=10000 loops=1)
-> Hash (cost=145.00..145.00 rows=10000 width=8) (actual time=3.554..3.555 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=8) (actual time=0.008..1.291 rows=10000 loops=1)
Planning Time: 1.331 ms
Execution Time: 7.039 ms
(8 rows)
パフォーマンスの確認
sample=# EXPLAIN ANALYZE
SELECT f_test(100, 'select * from t1 left join t2 using (val) where t2.val is NULL;');
QUERY PLAN
----------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=4) (actual time=349.641..349.642 rows=1 loops=1)
Planning Time: 0.033 ms
Execution Time: 349.660 ms
(3 rows)
■except all
実行計画確認
sample=# explain analyze select * from t1
except all select * from t2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
HashSetOp Except All (cost=0.00..690.00 rows=10000 width=12) (actual time=8.851..9.891 rows=9986 loops=1)
-> Append (cost=0.00..590.00 rows=20000 width=12) (actual time=0.008..5.381 rows=20000 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..245.00 rows=10000 width=12) (actual time=0.008..2.070 rows=10000 loops=1)
-> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) (actual time=0.005..0.759 rows=10000 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..245.00 rows=10000 width=12) (actual time=0.007..2.002 rows=10000 loops=1)
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=8) (actual time=0.006..0.747 rows=10000 loops=1)
Planning Time: 0.055 ms
Execution Time: 10.738 ms
(8 rows)
パフォーマンス測定
sample=# EXPLAIN ANALYZE
SELECT f_test(100, 'select * from t1
except all select * from t2;');
QUERY PLAN
----------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=4) (actual time=718.931..718.932 rows=1 loops=1)
Planning Time: 0.017 ms
Execution Time: 718.943 ms
(3 rows)