PostgreSQL - 高速でINSERTしたい君へ

# 大量インサートの性能をあげよう

PostgresSQLに大量インサートする場合には、
複数レコードまとめてインサート用のユーティリティを使用しましょう。
あるいは、commit実行頻度を見直して、不要な処理コストを減らしましょう

複数パターンで実測して性能を比較します。
PythonでPostgreSQLにアクセスするライブラリのひとつpsycopg2では
複数件まとめてインサート用のユーティリティとして
[execute_values](http://initd.org/psycopg/docs/extras.html)という関数を使用します。
また、commitの実行単位を変更してcommitの実行間隔を変えて実測します。


# 確認準備

## テスト用テーブル

```sql:テスト用DDL
CREATE TABLE test01
(key serial PRIMARY KEY,
 text_val text);
```

## テスト用プログラム

簡単のため、実行件数やcommit回数は対話的な入力が行えるようにします。
できるだけ同じ範囲の時間で比較できるようにパラメータを作る部分を含めます。
実行前に都度、テーブルリセットするため、TRUNCATEとSEQUENCEオブジェクトの値初期化を行います。

execute_valuesは、デフォルト100行まで。それ以上を入力するときは、引数のpage_sizeに入力数以上の値を渡す必要があります。

```py:テスト用プログラム
import psycopg2
from psycopg2.extras import execute_values
from time import time

_text = 'test' * 300

conn = psycopg2.connect('dbname=dbname host=localhost user=username password=password')
cur = conn.cursor()

# テーブルリセット
reset_query_tbl = "TRUNCATE TABLE test01;"
cur.execute(reset_query_tbl)
reset_query_seq = "SELECT setval ('test01_key_seq', 1, false);"
cur.execute(reset_query_seq)
count_query = "SELECT count(key) FROM test01;"
cur.execute(count_query)
(count,) = cur.fetchone()
conn.commit()
print('■実行前状態')
print('　データ件数: ', count)
print()

# テスト設定
print('■INSERT方法')
print('INSERT方法を以下から選択してください(0/1/2/3)')
print('　0: 1件ずつINSERT&commit.')
print('　1: 1件ずつINSERT&複数まとめてcommit.')
print('　2: 1件ずつINSERT&全件まとめてcommit.')
print('　3: まとめてINSERT&commit.')
test_type = int(input('　INSERT方法 >'))
test_cnt  = int(input('　INSERT件数 >'))
if test_type == 1:
    commit_cnt  = int(input('　COMMIT間隔 >'))

# テスト実行＆結果
print()
print('■実行結果')
if test_type==0:

    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for _ in range(test_cnt):
        cur.execute(query)
        conn.commit()

    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

if test_type==1:
    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for i in range(test_cnt):
        cur.execute(query)
        if (i + 1) % commit_cnt == 0:
            conn.commit()

    conn.commit()

    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

if test_type==2:

    query = "INSERT INTO test01 (text_val) VALUES ('%s');" % _text
    st = time()
    for _ in range(test_cnt):
        cur.execute(query)

    conn.commit()

    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

if test_type==3:

    query = "INSERT INTO test01 (text_val) VALUES %s;"
    st = time()

    params = []
    for _ in range(test_cnt):
        params += [(_text,)]

    ret = execute_values(cur, query, params, page_size=test_cnt)
    conn.commit()
    elapsed_time = time() - st
    print('　経過時間(s): ', elapsed_time)

cur.execute(count_query)
(count,) = cur.fetchone()
print('　データ件数: ', count)

cur.close()
conn.close()
```


# 性能結果

結果は参考まで。当然、環境、マシンスペックに依存します。

- Windows10 64bit/論理プロセッサ数4/メモリ8GB
- PostgreSQL11
- PostgreSQLのコンフィグはデフォルト値。


| INSERT方法 | INSERT件数 | COMMIT回数| 処理時間(S) |
|:-------|---------:|---------:|---------:|
| 1件ずつINSERT(execute) & 1件ずつCOMMIT. | 100,000| 100,000|**40.9**|
| 1件ずつINSERT(execute) & 10件ずつCOMMIT. | 100,000| 10,000|**25.4**|
| 1件ずつINSERT(execute) & 100件ずつCOMMIT. | 100,000| 1,000|**24.1**|
| 1件ずつINSERT(execute) & 1,000件ずつCOMMIT. | 100,000| 100|**27.2**|
| 1件ずつINSERT(execute) & 10,000ずつCOMMIT. | 100,000| 10|**25.9**|
| 1件ずつINSERT(execute) & 全件まとめてCOMMIT. | 100,000| 1|**24.4**|
| まとめてINSERT(execute_values) & 全件まとめてCOMMIT. | 100,000| 1|**8.8**|


# 結論

execute_values、速い。
できれば使いましょう。