More than 1 year has passed since last update.

僕のPostgresqlのUPSERT処理調査〜CONFLICT句 vs WITH句〜

Last updated at 2022-09-17Posted at 2022-08-29

経緯

システム開発時に
操作対象のテーブルにレコードが存在していなければINSERT、存在していればUPDATEを行う、いわゆるUPSERT処理を実装することとなり
これまでPostgresqlでの開発経験がなく（今まではほとんどOracleの案件ばかりでOracleならMERGEを利用していた）、調べたところ、どうやらPostgresqlではINSERT文にCONFLICT句を利用することで実装が可能らしいということがわかった。
ただ、自社での開発実績からいくとWITH句を使ってPostgresqlでUPSERT処理を実装してきたとのこと。（Postgresqlのバージョンの関係でCONFLICT句が使えなかったのかも。）

パッと見た感じCONFLICT句を利用したほうが実行速度が速そう（根拠はない）だったが気になったので、簡単なテーブルで両者を比較してみた。

筆者について

実行計画を見てSQLをカリッカリにチューニングしたりとか、「このSQLはこの辺に問題がありそうだ」みたいなことはしたことがないので、間違ってればコメントで教えてください。

テスト内容・テスト対象のテーブル

テスト内容

処理内容

今回はワークテーブルEMPLOYEE_WKからマスタEMPLOYEE_MASTERへのSELECT INSERTでUPSERT処理を行う。

テストパターン

今回はCONFLICT句・WITH句それぞれを使ったINSERT文を利用して以下の3パターンのUPSERT処理を各3回ずつ行ってテストを実施。

ワークテーブル全レコードをマスタにINSERT
ワークテーブル全レコードでマスタをUPDATE
ワークテーブルの半分はINSERT、半分はUPDATE

各テスト前の準備

テストデータ投入
統計情報を以下のコマンドで収集
ANALYZE
キャッシュクリアのため、テスト実施前にPostgresqlを毎回再起動 & 下記の記事を参考にOSファイルキャッシュもクリア
【PostgreSQL】初回クエリーが遅い

テスト対象のテーブル

※てきとーにネットで「テーブルサンプル」でググったらでてきたテーブルなので特にこだわりはない。

CREATE TABLE EMPLOYEE_MASTER(
	ID INT,
	NAME VARCHAR(20) NOT NULL,
	ROMAJI VARCHAR(20),
	CREATED_AT TIMESTAMP,
	UPDATED_AT TIMESTAMP,
	PRIMARY KEY(ID)
);

CREATE TABLE EMPLOYEE_WK(
	ID INT,
	NAME VARCHAR(20) NOT NULL,
	ROMAJI VARCHAR(20),
	CREATED_AT TIMESTAMP,
	UPDATED_AT TIMESTAMP,
	PRIMARY KEY(ID)
);

テーブルの定義内容を一応確認（ワークとマスタで定義は一緒ですね！）

postgres=# \d employee_master
                         テーブル"public.employee_master"
     列     |           タイプ            | 照合順序 | Null 値を許容 | デフォルト
------------+-----------------------------+----------+---------------+------------
 id         | integer                     |          | not null      |
 name       | character varying(20)       |          | not null      |
 romaji     | character varying(20)       |          |               |
 created_at | timestamp without time zone |          |               |
 updated_at | timestamp without time zone |          |               |
インデックス:
    "employee_master_pkey" PRIMARY KEY, btree (id)


postgres=# \d employee_wk
                           テーブル"public.employee_wk"
     列     |           タイプ            | 照合順序 | Null 値を許容 | デフォルト
------------+-----------------------------+----------+---------------+------------
 id         | integer                     |          | not null      |
 name       | character varying(20)       |          | not null      |
 romaji     | character varying(20)       |          |               |
 created_at | timestamp without time zone |          |               |
 updated_at | timestamp without time zone |          |               |
インデックス:
    "employee_wk_pkey" PRIMARY KEY, btree (id)

使用するSQL

CONFLICT句を使ったINSERT文

EXPLAIN ANALYZE --実行計画等を取得するためのコマンド
INSERT INTO EMPLOYEE_MASTER (
    ID,
    NAME,
    ROMAJI,
    CREATED_AT,
    UPDATED_AT
)
SELECT
    EMWK.ID,
    EMWK.NAME,
    EMWK.ROMAJI,
    EMWK.CREATED_AT,
    EMWK.UPDATED_AT
FROM
    EMPLOYEE_WK EMWK
ON CONFLICT (ID)
DO UPDATE SET
    NAME		=	EXCLUDED.NAME,
    ROMAJI		=	EXCLUDED.ROMAJI,
    CREATED_AT	=	EXCLUDED.CREATED_AT,
    UPDATED_AT	=	EXCLUDED.UPDATED_AT
;

WITH句を使ったINSERT文

EXPLAIN ANALYZE --実行計画等を取得するためのコマンド
WITH INPT AS (
    SELECT
        EMWK.ID,
        EMWK.NAME,
        EMWK.ROMAJI,
        EMWK.CREATED_AT,
        EMWK.UPDATED_AT
    FROM
        EMPLOYEE_WK EMWK
),UPDT AS (
    UPDATE EMPLOYEE_MASTER SET
        NAME        =   INPT.NAME,
        ROMAJI      =   INPT.ROMAJI,
        CREATED_AT  =   INPT.CREATED_AT,
        UPDATED_AT  =   INPT.UPDATED_AT
    FROM
        INPT
    WHERE
        EMPLOYEE_MASTER.ID = INPT.ID
    RETURNING 
        INPT.ID
)
INSERT INTO EMPLOYEE_MASTER (
    ID,
    NAME,
    ROMAJI,
    CREATED_AT,
    UPDATED_AT
)
SELECT
    SOUR.ID,
    SOUR.NAME,
    SOUR.ROMAJI,
    SOUR.CREATED_AT,
    SOUR.UPDATED_AT
FROM 
    INPT SOUR
WHERE
    ID NOT IN (SELECT UPDT.ID FROM UPDT) --NOT EXISTSにしたら実行速度が悪化したのでNOT IN
;

テスト実施

パターン1（ワークテーブル全レコードをマスタにINSERT）

空のマスタと200万件のレコードを持つワークテーブルを用意。

テストデータ準備

TRUNCATE TABLE EMPLOYEE_MASTER;
TRUNCATE TABLE EMPLOYEE_WK;
INSERT  INTO EMPLOYEE_WK( 
  ID,
  NAME,
  ROMAJI,
  CREATED_AT,
  UPDATED_AT
)
SELECT
  I,
  '佐々木尚子',
  'SASAKI NAOKO',
  CURRENT_TIMESTAMP,
  NULL
FROM
  GENERATE_SERIES(1, 2000000) AS I --テストデータ投入にこんな便利関数があるとは、、、
;

実行計画

CONFLICT句を用いたINSERT文

                                                            QUERY PLAN                                                  
-----------------------------------------------------------------------------------------------------------------------------------
 Insert on employee_master  (cost=0.00..38692.00 rows=0 width=0) (actual time=14995.161..14995.169 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: employee_master_pkey
   Tuples Inserted: 2000000
   Conflicting Tuples: 0
   ->  Seq Scan on employee_wk emwk  (cost=0.00..38692.00 rows=2000000 width=49) (actual time=0.054..487.452 rows=2000000 loops=1)

WITH句を用いたINSERT文

                                                                      QUERY PLAN                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 Insert on employee_master  (cost=84278.26..129278.26 rows=0 width=0) (actual time=8245.926..8245.933 rows=0 loops=1)
   CTE inpt
     ->  Seq Scan on employee_wk emwk  (cost=0.00..38692.00 rows=2000000 width=49) (actual time=0.417..336.881 rows=2000000 loops=1)
   CTE updt
     ->  Update on employee_master employee_master_1  (cost=0.01..45361.26 rows=10000 width=302) (actual time=0.025..0.030 rows=0 loops=1)
           ->  Hash Join  (cost=0.01..45361.26 rows=10000 width=302) (actual time=0.023..0.027 rows=0 loops=1)
                 Hash Cond: (inpt.id = employee_master_1.id)
                 ->  CTE Scan on inpt  (cost=0.00..40000.00 rows=2000000 width=296) (never executed)
                 ->  Hash  (cost=0.00..0.00 rows=1 width=10) (actual time=0.012..0.014 rows=0 loops=1)
                       Buckets: 1024  Batches: 1  Memory Usage: 8kB
                       ->  Seq Scan on employee_master employee_master_1  (cost=0.00..0.00 rows=1 width=10) (actual time=0.010..0.011 rows=0 loops=1)
   ->  CTE Scan on inpt sour  (cost=225.00..45225.00 rows=1000000 width=136) (actual time=0.689..1155.457 rows=2000000 loops=1)
         Filter: (NOT (hashed SubPlan 3))
         SubPlan 3
           ->  CTE Scan on updt  (cost=0.00..200.00 rows=10000 width=4) (actual time=0.026..0.026 rows=0 loops=1)

パターン2（ワークテーブル全レコードでマスタをUPDATE）

マスタデータ2万件とそれを上書きする2万件のワークテーブルのデータを用意
（パターン1と同じ200万件だとWITH句を用いたINSERT文が返ってこなくなったのでここから2万件です。。。）

テストデータ準備

TRUNCATE TABLE EMPLOYEE_MASTER;
TRUNCATE TABLE EMPLOYEE_WK;
INSERT  INTO EMPLOYEE_WK( 
  ID,
  NAME,
  ROMAJI,
  CREATED_AT,
  UPDATED_AT
)
SELECT
  I,
  '佐々木尚子',
  'SASAKI NAOKO',
  CURRENT_TIMESTAMP,
  NULL
FROM
  GENERATE_SERIES(1, 20000) AS I
;

INSERT INTO EMPLOYEE_MASTER
SELECT * FROM EMPLOYEE_WK;

TRUNCATE TABLE EMPLOYEE_WK;
INSERT  INTO EMPLOYEE_WK( 
  ID,
  NAME,
  ROMAJI,
  CREATED_AT,
  UPDATED_AT
)
SELECT
  I,
  '山田尚子',
  'YAMADA NAOKO',
  CURRENT_TIMESTAMP + cast( '1 days' as INTERVAL )   ,
  NULL
FROM
  GENERATE_SERIES(1, 20000) AS I
;

実行計画

CONFLICT句を用いたINSERT文

                                                        QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------------
 Insert on employee_master  (cost=0.00..367.00 rows=0 width=0) (actual time=222.109..222.115 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: employee_master_pkey
   Tuples Inserted: 0
   Conflicting Tuples: 20000
   ->  Seq Scan on employee_wk emwk  (cost=0.00..367.00 rows=20000 width=46) (actual time=0.330..5.083 rows=20000 loops=1)

WITH句を用いたINSERT文

                                                                           QUERY PLAN                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Insert on employee_master  (cost=1906.51..2356.51 rows=0 width=0) (actual time=150.064..150.068 rows=0 loops=1)
   CTE inpt
     ->  Seq Scan on employee_wk emwk  (cost=0.00..367.00 rows=20000 width=46) (actual time=0.390..4.827 rows=20000 loops=1)
   CTE updt
     ->  Update on employee_master employee_master_1  (cost=637.00..1089.51 rows=20000 width=302) (actual time=11.686..132.898 rows=20000 loops=1)
           ->  Hash Join  (cost=637.00..1089.51 rows=20000 width=302) (actual time=8.540..30.911 rows=20000 loops=1)
                 Hash Cond: (inpt.id = employee_master_1.id)
                 ->  CTE Scan on inpt  (cost=0.00..400.00 rows=20000 width=296) (actual time=0.030..14.520 rows=20000 loops=1)
                 ->  Hash  (cost=387.00..387.00 rows=20000 width=10) (actual time=8.320..8.321 rows=20000 loops=1)
                       Buckets: 32768  Batches: 1  Memory Usage: 1116kB
                       ->  Seq Scan on employee_master employee_master_1  (cost=0.00..387.00 rows=20000 width=10) (actual time=0.252..4.832 rows=20000 loops=1)
   ->  CTE Scan on inpt sour  (cost=450.00..900.00 rows=10000 width=136) (actual time=150.063..150.063 rows=0 loops=1)
         Filter: (NOT (hashed SubPlan 3))
         Rows Removed by Filter: 20000
         SubPlan 3
           ->  CTE Scan on updt  (cost=0.00..400.00 rows=20000 width=4) (actual time=11.688..137.850 rows=20000 loops=1)

パターン3（ワークテーブルの半分はINSERT、半分はUPDATE）

テストデータ準備

マスタデータ1万件とそれを上書きする1万件と新たに追加する1万件をワークテーブルに用意。

TRUNCATE TABLE EMPLOYEE_MASTER;
TRUNCATE TABLE EMPLOYEE_WK;
INSERT  INTO EMPLOYEE_WK( 
  ID,
  NAME,
  ROMAJI,
  CREATED_AT,
  UPDATED_AT
)
SELECT
  I,
  '佐々木尚子',
  'SASAKI NAOKO',
  CURRENT_TIMESTAMP,
  NULL
FROM
  GENERATE_SERIES(1, 20000) AS I
;

INSERT INTO EMPLOYEE_MASTER
SELECT * FROM EMPLOYEE_WK;

TRUNCATE TABLE EMPLOYEE_WK;
INSERT  INTO EMPLOYEE_WK( 
  ID,
  NAME,
  ROMAJI,
  CREATED_AT,
  UPDATED_AT
)
SELECT
  I,
  '山田尚子',
  'YAMADA NAOKO',
  CURRENT_TIMESTAMP + cast( '1 days' as INTERVAL )   ,
  NULL
FROM
  GENERATE_SERIES(1, 20000) AS I
;

実行計画

CONFLICT句を用いたINSERT文

                                                        QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------------
 Insert on employee_master  (cost=0.00..367.00 rows=0 width=0) (actual time=177.076..177.081 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: employee_master_pkey
   Tuples Inserted: 10000
   Conflicting Tuples: 10000
   ->  Seq Scan on employee_wk emwk  (cost=0.00..367.00 rows=20000 width=46) (actual time=0.063..5.272 rows=20000 loops=1)

WITH句を用いたINSERT文

                                                                           QUERY PLAN                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Insert on employee_master  (cost=1681.52..2131.52 rows=0 width=0) (actual time=71.731..71.736 rows=0 loops=1)
   CTE inpt
     ->  Seq Scan on employee_wk emwk  (cost=0.00..367.00 rows=20000 width=46) (actual time=0.049..3.506 rows=20000 loops=1)
   CTE updt
     ->  Update on employee_master employee_master_1  (cost=412.00..864.52 rows=20000 width=302) (actual time=6.007..38.726 rows=10000 loops=1)
           ->  Hash Join  (cost=412.00..864.52 rows=20000 width=302) (actual time=5.433..22.055 rows=10000 loops=1)
                 Hash Cond: (inpt.id = employee_master_1.id)
                 ->  CTE Scan on inpt  (cost=0.00..400.00 rows=20000 width=296) (actual time=0.028..12.555 rows=20000 loops=1)
                 ->  Hash  (cost=287.00..287.00 rows=10000 width=10) (actual time=5.311..5.312 rows=10000 loops=1)
                       Buckets: 16384  Batches: 1  Memory Usage: 558kB
                       ->  Seq Scan on employee_master employee_master_1  (cost=0.00..287.00 rows=10000 width=10) (actual time=0.038..3.518 rows=10000 loops=1)
   ->  CTE Scan on inpt sour  (cost=450.00..900.00 rows=10000 width=136) (actual time=44.571..49.662 rows=10000 loops=1)         Filter: (NOT (hashed SubPlan 3))
         Rows Removed by Filter: 10000
         SubPlan 3
           ->  CTE Scan on updt  (cost=0.00..400.00 rows=20000 width=4) (actual time=6.008..40.966 rows=10000 loops=1)

それぞれの計画時間と実行時間

パターンと回数	Planning Time（CONFLICT）	Execution Time（CONFLICT）	Planning Time（WITH）	Execution Time（WITH）
パターン1（1）	2.649 ms	14995.267 ms	25.571 ms	8389.163 ms
パターン1（2）	12.438 ms	16455.882 ms	88.653 ms	37359.106 ms
パターン1（3）	4.345 ms	15037.061 ms	90.677 ms	37336.568 ms
パターン2（1）	12.495 ms	225.011 ms	25.933 ms	153.241 ms
パターン2（2）	12.207 ms	234.845 ms	24.809 ms	152.871 ms
パターン2（3）	12.323 ms	242.169 ms	26.286 ms	216.382 ms
パターン3（1）	10.966 ms	179.706 ms	10.842 ms	74.402 ms
パターン3（2）	12.524 ms	192.202 ms	26.395 ms	97.075 ms
パターン3（3）	6.857 ms	158.845 ms	25.132 ms	75.903 ms

まとめ

CONFLICT句を利用したINSERT文はCONFLICT特有っぽい実行計画はあるものの非常にシンプルなテーブルをフルスキャンするだけの実行計画となった。
WITH句を使用した場合は、当然のことながらWITH句（共通テーブル式）の実行計画も加わるので複雑な実行計画になっている。
実行速度だけみると、多くの場合、WITH句を使用したINSERT文の方が高速に実行されているが、WITHの実行計画のコストを見てわかる通り、WITHのINSERT文はコストが非常に大きいく、既に述べているようにパターン2,3についてはテストデータを200万件から2万件に減らしている。
200万件で実行した場合、WITH句やHash Joinがtemp落ちしたためか、クエリが返らなくなってしまったためだ。（15分程度待っていたが返ってこなかった）
なので一度でどのくらいのレコードを裁かなければならないかがCONFLICTとWITHの使い分けの判断基準の一つになるかなと。

別の観点でWITH句を用いたINSERT文は速度以外にも、CONFLICT句にあるユニークなキーをCONFLICT句のキーにする必要があるというような制約が無いため、ユニーク制約のないキーを基準にUPSERTを行う場合は必然的にWITH句のINSERT文を選択することになる。

反省

テスト前に統計情報を取得（ANALYZE）していたが、なぜ不要領域の回収（VACUUM）をしなかったのか、、、テストの結果に影響しそう、、、
EXPLAIN ANALYZEで取得した実行結果が見ずらい！（シンプルに見た目の話）FORMATオプションもつけた方がよかったかな

追記

CONFLICT文にまさか制約があり、それは
「同じレコードに対して2回操作できない」
という制約。
OracleのMergeもこんな制約あっただろうか…？

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

僕のPostgresqlのUPSERT処理調査 〜CONFLICT句 vs WITH句〜

経緯

筆者について

テスト内容・テスト対象のテーブル

テスト内容

処理内容

テストパターン

各テスト前の準備

テスト対象のテーブル

使用するSQL

テスト実施

パターン1（ワークテーブル全レコードをマスタにINSERT）

実行計画

CONFLICT句を用いたINSERT文

WITH句を用いたINSERT文

パターン2（ワークテーブル全レコードでマスタをUPDATE）

実行計画

CONFLICT句を用いたINSERT文

WITH句を用いたINSERT文

パターン3（ワークテーブルの半分はINSERT、半分はUPDATE）

実行計画

CONFLICT句を用いたINSERT文

WITH句を用いたINSERT文

それぞれの計画時間と実行時間

まとめ

反省

追記

僕のPostgresqlのUPSERT処理調査〜CONFLICT句 vs WITH句〜