LoginSignup
1
3

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.

@mimitaro

PostgreSQL12の新機能 COPY FROMのWHERE句を検証してみた

Last updated at Posted at 2019-04-18

はじめに

早いもので、PostgreSQL 12の新機能がもう色々と検証されています。
その中で自分的に気になった「COPY FROM」に追加された「WHERE句」を検証してみました。

そもそも「COPY」は外部ファイル(csvファイル)とテーブルのデータをやり取りする機能です。
テーブルを外部ファイルへ吐き出す(COPY TO) or 外部ファイルをテーブルへ取り入れる(COPY FROM)ことができます。

INSERTやfdwによるやり取りより高速に実行できることが「COPY」の利点です。
「COPY TO」は従来のバージョンでもWHERE句が使用できます。
「COPY FROM」でWHERE句絞り込みができるのはPostgreSQL 12からです。

実際に使ってみよう

今回、検証にはCentOS 7のPostgreSQL 12develを使用しました。
検証のために取り込み先のテーブルと、WHERE句で絞り込まれるcsvファイルを作成します。

テストテーブル作成
=# CREATE TABLE sample1 (id INTEGER, name VARCHAR);
csvファイル作成
$ cat << EOF > sample1.csv
1,TARO,2003-10-30 11:23:40
2,HANAKO,2012-3-5 9:55:20
3,JON,1999-11-2 15:32:00
4,PETER,1990-7-27 22:31:05
5,MARY,2000-2-16 21:03:45
EOF

$ cat sample1.csv 
1,TARO,2003-10-30 11:23:40
2,HANAKO,2012-3-5 9:55:20
3,JON,1999-11-2 15:32:00
4,PETER,1990-7-27 22:31:05
5,MARY,2000-2-16 21:03:45

csvファイルには番号、名前、そして日付データが入っています。
今回は日付データに対してWHERE句による絞り込みが可能か確かめます。

csvファイルの3列目には日付データが入っています。
絞り込み条件として2000年以降のデータのみをCOPYによりテーブルへロードします。
ここで3列目を指定する場合、ロード先のテーブルの列名を使用します。
sample1テーブルの3列目はuptimeなため、WHERE句は以下のようにuptimeを用いて絞り込みをします。

WHERE句による絞り込みデータのロード
=# COPY sample1 FROM '/var/lib/pgsql/sample1.csv' WITH (FORMAT csv)
   WHERE uptime >= '2000-1-1';
COPY 3


=# SELECT * FROM sample1;
 id |  name  |       uptime
----+--------+---------------------
  1 | TARO   | 2003-10-30 11:23:40
  2 | HANAKO | 2012-03-05 09:55:20
  5 | MARY   | 2000-02-16 21:03:45
(3 行)

上記の通り、uptime列が2000/1/1以降の行のみがロードされました。

性能を検証してみた

続いては性能検証です。WHERE句による絞り込みでデータのロードが高速化することを確かめます。
大量行のcsvファイルから特定の行のみをWHERE句でロードする場合と、一括でロードした場合の性能を比較します。

検証準備

検証のためにテストテーブルと大量行のcsvファイルを作成します。

性能検証テストテーブル作成
=# CREATE TABLE sample2 (id INTEGER, name VARCHAR);
性能検証大量行csvファイル作成
$ i=5000000
$ paste -d, <(seq $i) <(cat /dev/urandom | tr -dc '[:alnum:]' | fold -w 42 | head -n $i) > sample2.csv

$ cat sample2.csv | head -n 5
1,FzNptDY0VAPR4LQ3cg0QEOmWxg2erBE9Qkx2heAcUn
2,i2KgSLlKw2aQi7MPwBs5xIQ0Ml4Kxh6eNivizLyo7J
3,weNBBXtCwS65xnxyCU7Bb7kNZJT3uR61WDRtQCJOio
4,EYovtxRsqspbaQV78Hyt9KtIaQdImvMT7npeqxhi3G
5,KJ21bDKO2V8544fsWWXcW871eZT0z1DM7T5c7zIPwm

※上記では先頭の5行しか呼び出していませんが、sample2.csvは500万行持つ243MBのcsvファイルです。
※大量行csvファイルの生成方法については補足で説明しています。

検証方法

今回は検証として複数回COPYを実行します。
実行時間を計測するた\timingを使用します。

計測時間測定SQL文
=# \timing

またテーブルの状態を常に初期状態とするため次のようにDROP TABLE→CREATE TABLEした後に検証用COPY文を実行します。

検証の流れ
=# DROP TABLE sample2;
=# CREATE TABLE sample2 (id INTEGER, name VARCHAR);
=# <検証用コピー文>

検証用COPY文

今回は以下の4つのパターンで検証・比較しました。

Ⅰ.一括ロード

WHERE句を付けずにCOPY文を実行します。

Ⅰ.一括ロード
=# COPY sample2 FROM '/var/lib/pgsql/sample2.csv' WITH (FORMAT csv);

Ⅱ.WHEREあり一括ロード

WHERE句による負荷が存在するか確かめるため次のWHERE句が全て正となるCOPY文を実行します。

Ⅱ.WHEREあり一括ロード
=# COPY sample2 FROM '/var/lib/pgsql/sample2.csv' WITH (FORMAT csv)
   WHERE 1 = 1;

Ⅲ.WHEREによる約半分絞り込み

絞り込みの条件式は、"文字列に大文字「A」を含んでいる行"にします。

Ⅲ.WHEREによる約半分絞り込み
=# COPY sample2 FROM '/var/lib/pgsql/sample2.csv' WITH (FORMAT csv)
   WHERE name LIKE '%A%';

文字列の長さは42文字であり、ランダム文字列には大文字小文字のアルファベット(26×2=52文字)と数字(10文字)が使われています。
よって、ランダム文字列に大文字「A」が含まれる確率は以下の通り計算できます。

(1-(61/62)^42)*100 = 49.4871222172

つまり約50%の確率でランダム文字列は大文字「A」を含むため、250万行のデータが絞り込まれることが予想されます。
実際に今回の検証で大文字「A」が含まれる行は2476443行(=約250万行)でした。

Ⅳ.WHEREによる10行絞り込み

WHERE句による負荷が存在するか確かめるため次のWHERE句が全て正となるCOPY文を実行します。

Ⅳ.WHEREによる10行絞り込み
=# COPY sample2 FROM '/var/lib/pgsql/sample2.csv' WITH (FORMAT csv)
   WHERE id <= 10;

検証結果

検証用SQLを10回ずつ実行し結果を見てみます。

  • 表
実行時間(秒) 1回目 2回目 3回目 4回目 5回目 6回目 7回目 8回目 9回目 10回目 平均
Ⅰ.一括ロード 10.2 7.92 13.9 9.87 11.9 12.8 12.0 10.7 8.96 10.9 10.9
Ⅱ.WHEREあり一括ロード 12.2 14.8 10.5 10.2 9.72 17.8 11.2 9.87 11.2 9.98 11.7
Ⅲ.WHEREによる約半分絞り込み 8.32 5.78 8.64 5.74 7.86 7.05 8.70 7.34 7.14 6.25 7.28
Ⅳ.WHEREによる10行絞り込み 4.60 4.46 4.03 4.22 4.05 4.05 5.02 5.39 4.34 4.62 4.48
  • グラフ

copy_where_hikaku.png

WHERE句の絞り込みによってロードするデータ量が減るほどに、実行時間が少なくなります。
ただし絞り込むために前提として、csvデータを全てバッファ上にあげる必要があためか、データ量と実行時間は単純に比例している訳ではなさそうです。
またⅠとⅡの比較から、WHERE句の付与そのものが大きなボトルネックになるわけではなさそうです。
※ただし今回の検証では実施していませんが、WHERE句が複雑な条件式を持つ場合は影響するかもしれません。

今後、大量のcsvデータから特定の範囲のみをPostgreSQLへロードする時は、WHERE句を使用することで時間短縮ができそうです。

(補足)大量行csvファイルの作り方

ランダムcsvファイル作成に用いている/dev/urandomは、Linuxが所有するランダム文字列を生成する疑似デバイスです。

i行のランダムな42文字を表示
cat /dev/urandom | tr -dc '[:alnum:]' | fold -w 42 | head -n $i
  • /dev/urandom：ランダム文字列を延々に生成する疑似デバイス
  • tr -dc 'XXXX'：XXXX以外の文字列を削除
  • [:alnum:]はアルファベットと数字の正規表現
  • tr -dc '[:alnum:]'：アルファベットと英数字以外削除
  • fold -w X：X文字目で文字列を改行
  • head -n X：先頭からX行目までを表示する。

今回はこの/dev/urandomを使い複雑なコマンドで大量行のcsvファイルを作成しています。
複数行のデータを作るなら素直にforを使えばよいのでは? と感じる人もいるかもしれません。
実際に私も最初は単純に次のようなfor文によるcsvファイル作成をしていました。

for文による大量行のcsvファイル作成(非推奨)
for ((i=0; i<=5000000; i++));
do
  if [ $i -eq 0 ]; then
    : > sample2.csv
  else
    (echo -n $i,;cat /dev/urandom | tr -dc '[:alnum:]' | fold -w 42 | head -n 1) >> sample2.csv;
  fi
done

しかし上記の場合、10～30分経っても500万行のcsvファイルが生成されませんでした。
どうやらLinuxコマンドにおいて、for文は毎回指定されたコマンドを実行する関係上、非常に遅くなるそうです。
そのため文章ファイルの読み込み/書き込みにおいては、for文ではなく、grep等をうまく使い、一括でデータを操作することが高速化の道に繋がります。

その結果、下記のようにpasteやseqを用いて高速に大量行のcsvファイルを生成しました。
こちらはfor文と比較して数十秒で処理が完了しています。

性能検証大量行csvファイル作成
$ i=5000000
$ paste -d, <(seq $i) <(cat /dev/urandom | tr -dc '[:alnum:]' | fold -w 42 | head -n $i) > sample2.csv
  • paste -dX：複数のファイルを行単位で連結(連結文字は「X」が使われる)
  • seq X：1からXまで連番を1行ずつ表示

参考にしたサイト

COPY FROMのWHERE句検証


大量行csvファイルの作成方法


  • シェルスクリプトを何万倍も遅くしないためには —— ループせずフィルタしよう
    https://qiita.com/8x9/items/f1156503694d3683e78d
    テスト用csvファイルを作成するときの参考情報に使わせていただきました。
    リンク先に記載されている通り、シェルスクリプトでのfor文はパフォーマンス的によくないようです。

1
3
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
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?