ネットプリントの吟味
【PostgreSQL】インデックスの効果
前回までに70万件くらいを書き込んだテーブルを作ったのでちょっとした高速化を試してみます。
準備
インデックス付与
まずはテーブルにインデックスを付与します。
次のSQLでTestTableという名前のテーブルの列Timeに対してインデックスを付与します。
インデックスSQLサンプル
CREATE INDEX ON public."TestTable" ("Time")
インデックス付与確認
付与できていればpgAdminのツリーのindexesが増えているかと思います。
検証
検証用SQL
速度比較のため、実行速度に時間がかかるSQLを作成します。
前回作ったテーブル構成（列「Count」、「Time」が存在するテーブル）に10万件のデータがINSERTされている状態としています。
そのテーブルに下記SQLを実行してみます。
TestTableという名前の同じテーブルをa,bとみなして結合した合計件数を取得するだけのSQLです。
実行結果は100億件を返してくれます。
検証用SQLサンプル
SELECT Count(*) FROM "TestTable" a , "TestTable" b;
結果
| 条件 | SQL実行速度 |
|---|---|
| インデックスなし PostgreSQLデフォルト設定 |
5分27秒10 |
| インデックスあり PostgreSQLデフォルト設定 |
5分26秒98 |
| インデックスなし PostgreSQLの設定見直し ・「shared_buffers」2GB(メモリの25%) |
4分57秒99 |
| インデックスあり PostgreSQLの設定見直し ・「shared_buffers」2GB(メモリの25%) |
4分35秒17 |
| インデックスなし PostgreSQLの設定見直し ・「shared_buffers」2GB(メモリの25%) ・「work_mem」16MB |
4分40秒26 |
| インデックスあり PostgreSQLの設定見直し ・「shared_buffers」2GB(メモリの25%) ・「work_mem」16MB |
4分41秒98 |
まとめ
結果としては予想していたよりは高速化にならず・・・
単純結合するSQLだったのでインデックスが効いていないのかもしれないです。
PostgreSQLの設定見直しは多少ですが効果ありですね！
【PostgreSQL】INSERT、UPDATE速度の単純検証
前回の続きです。
前回、INSERTを繰り返すバッチ処理を作ったのでひたすら回してみました。
今回検証したのはこの2つです。
- INSERTを70万回繰り返した場合の1回毎の処理時間
- INSERTしたデータに対してUPDATEを70万回繰り返した場合の1回毎の処理時間
結果はめちゃくちゃ早すぎてあまり意味のないものに。。
検証に用いたパソコンはWindows10、第10世代Corei5、SSD、メモリ8GBです。
【INSERT】
最短：0.05秒
最長：26分5.47秒
※データ件数が多くなりバキュームなどDB最適化が走ったと思われる
平均：0.122秒
【UPDATE】
最短：0.06秒
最長：5.63秒
平均：0.104秒
【バッチ】PostgreSQLに行挿入するWindowsバッチ
PostgreSQLを学習中のこの頃です。
DBにデータって挿入しまくったら速度ってどうなるの！？と気になったので行挿入するWindowsバッチを作ってみました。
めちゃくちゃ雑ですがバッチファイルの最初にPostgreSQLの本体のあるフォルダやホスト名、DB名などを定義しています。
その情報でDB接続を行い、INSERTのSQLを実行しています。
DBに書き込む情報はタイムスタンプ情報でバッチは無限ループするので終わりたいときはコマンドプロンプトを終了してください。
バッチファイルと同じ場所に書き込んだ回数とSQL実行前後の日時を出力したログファイルも作成されます。
〜
@ECHO OFF
REM --------------------------------------------------
REM DB接続パラメータ
REM --------------------------------------------------
SET PGPATH="C:\Program Files\PostgreSQL\13\bin\"
SET HOSTNAME=localhost
SET PORTNUM=5432
SET DBNAME=postgres
SET USERNAME=postgres
SET PGPASSWORD=1234567890
REM --------------------------------------------------
REM 処理時間計測用のログファイル
REM --------------------------------------------------
SET LOGFILE=result.log
REM --------------------------------------------------
REM SQL実行
REM --------------------------------------------------
SET /a cnt=0
:LOOP
SET /a cnt=cnt+1
SET /P X=%cnt%,%DATE% %TIME%<NUL>>%LOGFILE%
REM %DATE:~0,4%-%DATE:~5,2%-%DATE:~8,2%でyyyy-MM-ddを生成
%PGPATH%psql -h %HOSTNAME% -p %PORTNUM% -d %DBNAME% -U %USERNAME% -c "INSERT INTO ""TestTable""(""Count"", ""Time"") VALUES(%cnt%, '%DATE:~0,4%-%DATE:~5,2%-%DATE:~8,2% %TIME%');"
ECHO ,%DATE% %TIME%>>%LOGFILE%
goto LOOP
〜
DBのテーブルは下記の構成です。
書き込み速度の比較は次回にでもやってみたいと思います！
更新：2021/7/27
【PostgreSQL】INSERT、UPDATE速度の単純検証 - ででりんブログ
