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

# 経緯
システム開発時に
**操作対象のテーブルにレコードが存在していなければ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回ずつ行ってテストを実施。
1. **ワークテーブル全レコードをマスタにINSERT**
2. **ワークテーブル全レコードでマスタをUPDATE**
3. **ワークテーブルの半分はINSERT、半分はUPDATE**

### 各テスト前の準備
1. テストデータ投入

2. 統計情報を以下のコマンドで収集
    `ANALYZE` 

3. キャッシュクリアのため、テスト実施前にPostgresqlを毎回再起動 & 下記の記事を参考にOSファイルキャッシュもクリア
    [【PostgreSQL】初回クエリーが遅い](https://qiita.com/yaju/items/e0f8f56e4d8d04ea22b6)
 


## テスト対象のテーブル
※てきとーにネットで「テーブル サンプル」でググったらでてきたテーブルなので特にこだわりはない。
```sql
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)
);
```

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

```
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)
```
</details>

# 使用するSQL
<details><summary>CONFLICT句を使ったINSERT文</summary>

```sql
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
;
```
</details>

<details><summary>WITH句を使ったINSERT文</summary>

```sql
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
;
```
</details>


# テスト実施
## パターン1（ワークテーブル全レコードをマスタにINSERT）
空のマスタと200万件のレコードを持つワークテーブルを用意。
<details><summary>テストデータ準備</summary>

```sql
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 --テストデータ投入にこんな便利関数があるとは、、、
;
```
</details>

### 実行計画
#### 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万件です。。。）`
<details><summary>テストデータ準備</summary>

```SQL
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
;
```
</details>

### 実行計画
#### 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)
```
</detailS>

## パターン3（ワークテーブルの半分はINSERT、半分はUPDATE）
<details><summary>テストデータ準備</summary>

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

```SQL
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
;
```
</details>

### 実行計画
#### 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もこんな制約あっただろうか…？