PostgreSQLのTRIGGERは滅多に使わない＞忘れてしまっていたので思い出すのを兼ねて備忘録とします。

今回はテーブルに対しての追加更新削除時のデータを、ログテーブルに丸ごと保存する例です。

参考情報は以下URL。
https://www.postgresql.jp/document/9.6/html/plpgsql-trigger.html

まずpostgres等のスーパーユーザでCREATE LANGUAGE。

CREATE LANGUAGE plpgsql;
SELECT * FROM pg_language WHERE lanname = 'plpgsql'; --確認

以降はスーパーユーザーの必要は無いので、データベースオーナーが実施。



テスト用テーブルの作成。



CREATE TABLE public.tb(
id serial NOT NULL,
note text,
create_time timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT tb_pkey PRIMARY KEY (id)
);


ログ保存用スキーマlogsを作成して、そこにログ用テーブルを追加。



CREATE SCHEMA logs;

CREATE TABLE logs.log_tb AS
SELECT 0::bigint AS log_id,''::text AS oper,null::timestamp without time zone AS copy_time,*
FROM public.tb WHERE id = 0;
CREATE SEQUENCE logs.log_tb_log_id_seq;
ALTER TABLE logs.log_tb ADD PRIMARY KEY (log_id);


トリガ関数を定義。



CREATE OR REPLACE FUNCTION public.tb_copy() RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO logs.log_tb SELECT nextval('logs.log_tb_log_id_seq'::regclass),TG_OP,now(),* FROM public.tb WHERE id = OLD.id;
RETURN OLD;
ELSE
INSERT INTO logs.log_tb SELECT nextval('logs.log_tb_log_id_seq'::regclass),TG_OP,now(),* FROM public.tb WHERE id = NEW.id;
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;


トリガをtbに仕掛ける。



CREATE TRIGGER tb_change AFTER INSERT OR UPDATE ON public.tb FOR EACH ROW EXECUTE PROCEDURE public.tb_copy();
CREATE TRIGGER tb_delete BEFORE DELETE ON public.tb FOR EACH ROW EXECUTE PROCEDURE public.tb_copy();

最後に、データを操作してログが取れるかを確認。



INSERT INTO public.tb(note) VALUES('TEST');
INSERT INTO public.tb(note) VALUES('TEST2');
UPDATE public.tb SET note = 'UPDATE TEST';
DELETE FROM public.tb;
SELECT * FROM logs.log_tb;

これでINSERT, UPDATE, DELETEのログがそれぞれ2件ずつ登録されている。

※log_idやidが増加してるのは、何度か試したので。

※時間が一緒なのは一括実行の結果なので、一行ずつ実行すれば異なる。

以前ElephantSQL(https://www.elephantsql.com/)がちょっとしたサンプルに使うのに便利そうだなと思ったのですが、思い出せなくてモヤモヤしたり社内Slackに書いたはず〜と検索したりしたのでメモっておきます。

Pricing(https://www.elephantsql.com/plans.html)を見ていただければわかりますが、Tiny Turtle というFREEプランがあるんですよ。データが最大20MBで5同時接続までですけど。

子亀ちゃん、作っていきましょう。

PlanはTiny Turtle(Free)にしてNameとTagsは適当に。
上の警告は有料プランなら支払い情報登録しなさいよってことですね。

次にリージョンとデータセンターを選びます。

AWS, GCP, Azure, IBM Cloudからたくさんの選択肢が提供されていますが、日本だとAWSにAP-NorthEast-1(Tokyo)があって、一番近そうです(いまのところ)

内容確認して Create instance したら

(test3の)できあがり

中身を見てみましょう。無料プランではDETAILS, BROWSER, STATS, BACKUPのメニューが使用できるようです。

DETAILSではデータベースへの接続情報等が表示されます。

pgAdmin 4 で接続してみましょう。接続についてはこちらに説明があります。
https://www.elephantsql.com/docs/pgadmin.html

接続すると同じサーバー上の他のユーザーのデータベースも一覧されるので、壮観です。

そして自分のをさがすのがとても面倒です。

次、順番でBROWSERを見てみましょう。
SQL Browser でクエリを実行して結果を表示することができます。

STATSはいくつかの統計情報などが表示されます。

PostgreSQLのバージョン、気になってたんですよね。11.5でした(2020-07-09現在)

最後にBACKUP。

リストアのためのコマンドが書いてありますが、ブラウザ上で完結もできるようです。

ところで、訓練されたPostgreSQLユーザーならBROWSERで実行されているSQLの結果でお気付きかと思いますがlc_collateとlc_ctypeがen_US.UTF-8なのが不穏です。変更したかったのですが、できないようです(おそらく)

文字列ソートの並びについては先人がHerokuで戦った記事がありました。

PostgreSQLで正しく文字列ソート出来ない時の解決方法(https://qiita.com/anoworl/items/af9f12f915b8969ea40d)

ありましたがpg_collectionにja_JP.utf8はないみたいです。

まぁ、お試し用と割り切ればアリかと思います。

少しだけ時間が出来たので、以前使おうか迷っていたPostgreSQLのWITH RECURSIVEを使って複数点間の移動経路と距離を求めるようなSQLを作ってみました。
※使おうと思ってた箇所は、計算Costが問題になりそうだったので、結局group化する列を追加して対応しましたが。

ある地点から別の地点へ行ける組合せを登録するテーブルを作ります。

CREATE TABLE public.pair(
p1 text, -- 開始地点
p2 text, -- 到達地点
distance integer -- 距離
);

以下のデータを作り、距離はランダムに入れました。

p1 p2 distance
A B 8
C K 91
D B 66
E D 78
F E 44
G F 36
H G 2
I H 23
J H 94
M K 28

今回は逆順でp2からp1へも同じ距離distanceで行く事を可としましたので、これをviewで対応します。

CREATE OR REPLACE VIEW public.pair2 AS
SELECT p1, p2, distance FROM pair
UNION
SELECT p2 AS p1,p1 AS p2, distance FROM pair
;

このpair2に対して再帰問い合わせを使ってみます。
・今回は数が多くないので繰り返し回数無制限でも良いんですが、一応10回未満とするためn列を追加します。
・また、途中の経路が分からないのも微妙だったので、route配列を追加します。

WITH RECURSIVE r AS(
SELECT p1,p2,distance
,1 as n
, array[p1, p2] AS route
FROM pair2
UNION
SELECT r.p1, p.p2, r.distance+p.distance as distance,n+1
, route||array[p.p2]
FROM pair2 p
JOIN r ON p.p1 = r.p2 -- 関連key
AND r.p1 != p.p2 -- 同じ値は不要
AND NOT(p.p2 = ANY(route)) -- 一度通った場所は通らない
WHERE n+1 < 10 -- 継続回数を指定
) SELECT * FROM r
WHERE p1 = 'A'
ORDER BY p1,p2,n,distance;

結果です。
distanceが加算されて行ってるのが分かります。
またAとC,K,Mは関連データが無いので、経路は出ていません。

p1 p2 distance n route
A B 8 1 {A,B}
A D 74 2 {A,B,D}
A E 152 3 {A,B,D,E}
A F 196 4 {A,B,D,E,F}
A G 232 5 {A,B,D,E,F,G}
A H 234 6 {A,B,D,E,F,G,H}
A I 257 7 {A,B,D,E,F,G,H,I}
A J 328 7 {A,B,D,E,F,G,H,J}

muraveです。LTSを使いたい人なのでLaravelのバージョンが5.1から5.5にジャンプアップしました。
その際、いままで使っていたデータベースバックアップのプラグインが使えなくなりました。

データベースdumpをお手軽にとったりもどしたりしたかっただけだったんだけど、重厚なバックアップ用パッケージが見つかった。これはこれで入れとくかぁ。
— murave (@murave) 2017年11月24日

ということで

https://github.com/spatie/laravel-backup

を導入。良い感じです。もしかして？と、この方のリポジトリを探してみたらありました。

https://github.com/spatie/laravel-db-snapshots

This package provides Artisan commands to quickly dump and load databases in a Laravel application.

そうそう、この記事の対象データベースはPostgreSQLです。

PostgreSQLの場合にレストア時にエラーでデータベースを飛ばしたので、その対処方法のメモだったりします。MySQL、SQLiteにも対応らしいですが試してません。導入や使い方はドキュメント見てくださいね。

ドキュメント通りにスナップショットをロード(restore)しようとしたらエラーでコケたのでした。

# artisan snapshot:load my-first-dump

[Illuminate\Database\QueryException]
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "\"
LINE 464: \.
^ (SQL: --

### 以下略

で、コケるとデータベース飛びます(掃除がリストアの前に別途行われるようです)。

バックアップ・リストアのテストしててバックアップオプションを変えてからデータのバックアップが取れないと思ってたら、リストアのテストの失敗でデータが消滅してたせいだった。
— murave (@murave) 2017年11月27日

こんあとき、ボクのように上書きで再度スナップショットを取ると空のデータベースのdumpで上書きという悲劇が起こるので気をつけましょう。

dumpファイルの内容を調べるとCOPY from stdinで.から読むようになってるところでコケてます。

「ははーん。pg_dump呼び出しに–insertsつければいいんだな」

pg_dumpに–insertsオプションをつけるとデータをCOPYではなくINSERTコマンドでdumpします。

「どうやってつけたらいいんだ？」

コードを追ったらdatabase.phpでdumpのオプション付けられるようになってました。
insertsオプションにも対応してます。サンプル載せときます。

'pgsql' => [
'driver' => 'pgsql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '5432'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'schema' => 'public',
'sslmode' => 'prefer',
//PDO絡みで環境によって型がばらつく可能性があるので割り切って全て文字列で取得。
'options' => [
PDO::ATTR_STRINGIFY_FETCHES => true,
],
'dump' => [
'useInserts',
],
],
‘dump’ => [‘useInserts’] の所です。無事snapshot:loadできるようになりました。

# artisan snapshot:create for-test-dump
# artisan --env=testing snapshot:load for-test-dump
これはデフォルトの環境でスナップショットを取ってtesting環境でロード(restore)してます。

PDO::ATTR_STRINGIFY_FETCHESについての余談

前記database.phpのサンプルのコメントにありますが、開発環境ではintで取れてたのに本番のサーバーにデプロイしたらstringで返ってくるなどデータ取得時に動作環境によって型がばらつくことがあります。

厳密比較の動作が異なって判定が変わったり、unit testがコケたりと困ったことになるので全て文字列で取得するオプションを設定するようにしています。

Model経由で値を扱う場合は

/**
* キャスト設定(文字列以外は要設定)
*
* @var array
*/
protected $casts = [
'id' => 'integer',
'updated_by' => 'integer',
'options' => 'array', //jsonb
];
のように$castsに書いておけばLaravelさんがキャストしてくれます。

PostgreSQLの全文検索でLudia, textsearch_sennaを使っていたのですが、9.2ではどちらも使えないので、代替となりそうなものを探した結果、pg_trgm, pg_bigm, textsearch_jaの三つが使えそうでした。
ただpg_trgmは日本語を使うにはソースからのコンパイルが必要で、しかも二文字以下は使えないのでパスしました。

textsearch_ja

textsearch_jaはtsvectorの拡張のようです。

とりあえずtextsearch_jaをインストールしてみます。
gcc-c++とかが必要らしいので事前にインストールしておきました。

まずはmecab。
http://taku910.github.io/mecab/#download
mecab本体とipa辞書をダウンロード。
PostgreSQLの文字コードはデフォルトUTF-8なのでconfigureオプションには、

--with-charset=utf8

をどちらも追加しないと駄目でした。
その後、/etc/ld.so.conf.d/mecab.confを作成して以下一行を追加。

/usr/local/lib

この後ldconfigを実施します。

次にtextsearch_ja本体
http://textsearch-ja.projects.pgfoundry.org/textsearch_ja.html

tar xzf textsearch_ja-9.0.0.tar.gz
cd textsearch_ja-9.0.0/
make USE_PGXS=1
make USE_PGXS=1 install

/usr/share/pgsql/contrib/textsearch_ja.sql はそのまま実行すると駄目で、’C’を小文字に直すか、クォートを外す必要が有りました。

psql [database] -f /usr/share/pgsql/contrib/textsearch_ja.sql
psql [database]
CREATE INDEX test_tb_content ON test_tb USING gin(to_tsvector('japanese', content));

実行結果

EXPLAIN SELECT * FROM test_tb WHERE to_tsvector('japanese', content) @@ to_tsquery('japanese', '猫');
QUERY PLAN
----------------------------------------------------------------------------------------
Bitmap Heap Scan on test_tb (cost=19.65..1470.66 rows=471 width=529)
Recheck Cond: (to_tsvector('japanese'::regconfig, content) @@ '''猫'''::tsquery)
-> Bitmap Index Scan on test_tb_content (cost=0.00..19.53 rows=471 width=0)
Index Cond: (to_tsvector('japanese'::regconfig, content) @@ '''猫'''::tsquery)
(4 行)

pg_bigmpg_bigmはpg_trgmの拡張のようです。

http://pgbigm.osdn.jp/pg_bigm-1-2.html#install

PostgreSQLについてはrpm版を使うので省略。
またインストールにはpostgresql-develが必要なので事前にインストールしておきます。

CentOS6用のrpmが有りましたが、CentOS7のデフォルトのpostgresqlでは使えなかったのでダウンロードしてmakeします。
pg_configはデフォルトパスが通っているので、オプションは入れずに実行可能です。

tar xzf pg_bigm-1.2-20161011.tar.gz
cd pg_bigm-1.2-20161011/
make USE_PGXS=1
make USE_PGXS=1 install

スーパーユーザーでデータベースごとにextensionを追加します。

su - postgres
psql [database]
CREATE EXTENSION pg_bigm;

インデックスを追加して実行してみます。

CREATE INDEX test_tb_content_bigm ON test_tb USING gin (content gin_bigm_ops);
EXPLAIN SELECT * FROM test_tb WHERE content LIKE '%猫%';
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on test_tb (cost=40.06..71.29 rows=8 width=529)
Recheck Cond: (content ~~ '%猫%'::text)
-> Bitmap Index Scan on test_tb_content_bigm (cost=0.00..40.06 rows=8 width=0)
Index Cond: (content ~~ '%猫%'::text)
(4 行)

単純な結果としては、pg_bigmの方が有利なのかなといった感じでした。

まえがき

Laravelでの開発時にデプロイ先はMySQLなのに手を抜いて手元の開発機ではSQLiteを使っていたら痛い目にあったりしました、muraveです。

SQLiteって結構ルーズにつかえてしまうので手元の開発でSQLite使っててMySQL運用のサーバーにデプロイするとエラーがバンバンというのが昨日から連続発生中。
— murave (@murave) 2016年7月14日

開発環境にあまり影響を与えずにサクッと開発用のRDB(Relational Database)を建てられると素敵ですね。Docker公式イメージを活用すると出来そうです。

RDBというデッカイ単語を使っていますが、自分がよく使うPostgreSQL、MySQL、MariaDBなどについて調べようと思います。MySQLとMariaDB自体はほぼ同じ扱い方ができるRDBですが、公式イメージでの扱いはどうなんでしょうね。

記事にまとめながら試していこうと思います。Docker for Macを使用しており、今回はPostgreSQLです。

PostgreSQL

公式イメージを調べる

OFFICIAL REPOSITORY postgres

2016年8月中旬現在、以下のバージョンがあるようです。

9.6(9.6-beta4) , 9.5(9.5.4), 9.4(9.4.9), 9.3(9.3.14), 9.2(9.2.18), 9.1(9.1.23)

流石に8系はないですね。使いたくないですよね。忘れましょう。客先には残ってますが。

起動する

試していきます。せっかくですから9.6でやってみようと思います。

$ docker run -d --name postgres96beta4 -e POSTGRES_PASSWORD=mysecretpassword -p 15432:5432 postgres:9.6
公式の例を参考にコンテナの5432を15432にポートフォワードなど追加しておきました。

接続する

psqlでの接続ですが、公式には別コンテナを立ち上げての接続例が載ってますね。

$ docker run -it --rm --link postgres96beta4:postgres postgres:9.6 psql -h postgres -U postgres
Password for user postgres:
psql (9.6beta4)
Type "help" for help.

postgres=# select version();
version
---------------------------------------------------------------------------------------------
PostgreSQL 9.6beta4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

postgres=# \q
–rm を付けてあるので抜けるとコンテナは消えます。儚い。

ポートフォワードしてあるのでMacからつなぐこともできますが、当然、psqlのバージョンはMac上のものになります。

$ psql -h localhost -p 15432 -U postgres
Password for user postgres:
psql (9.3.5, server 9.6beta4)
WARNING: psql major version 9.3, server major version 9.6.
Some psql features might not work.
Type "help" for help.

postgres=# \q
9.3用なので警告がでています。

愛しのpgAdmin3でつないでみましょう。pgAdmin3も9.3用なので警告されております。

接続はできました。

ほぼ使えましたがPostgreSQL9.6に対してツールのバージョンが9.3用と古いのでエラーがでたりもしますね。
psqlのみで作業するのはツライのでツールは対象に合わせたバージョンを用意しないとあかんなぁと思いました。

設定ファイルなど

コンテナのシェルを起動して探検。

$ docker exec -it postgres96beta4 bash
root@d2e991bd61a7:/# cd /var/lib/postgresql/data/
root@d2e991bd61a7:/var/lib/postgresql/data# more postgresql.conf
と、このようにpostgresql.confを眺めてみたり(lessもviもなかった)。

root@d2e991bd61a7:/var/lib/postgresql/data# more pg_hba.conf
pg_hba.confを眺めてみたり。pg_hba.confから抜粋しますと

# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

host all all 0.0.0.0/0 md5
ローカルからはtrustで他はパスワード認証ですかね。

拡張する

コンテナの /docker-entrypoint-initdb.d に拡張子がsql、shのファイルを置くとコンテナ起動時に実行してくれるそうです。
他、Dockerfileでdefault localeを設定するという例が公式のページに例として載っています。

やってみましょう。

日本語化されたイメージの作成

default localeを設定するDockerfileを作ります。

Dockerfile

FROM postgres:9.6

MAINTAINER murave

RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
ENV LANG ja_JP.utf8
日本語化されたイメージ、mypostgres:9.6を作ります。

$ docker build -t mypostgres:9.6 ./
確認

$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mypostgres 9.6 4b7ea9f892dd 24 seconds ago 269.2 MB
データベースの初期化操作しているディレクトリに/docker-entrypoint-initdb.d対応用にinitdbというディレクトリを作成しました。postgres/9.6/docker-entrypoint.sh を読んでみたところ

for f in /docker-entrypoint-initdb.d/*; do
case "$f" in
*.sh) echo "$0: running $f"; . "$f" ;;
*.sql) echo "$0: running $f"; "${psql[@]}" < "$f"; echo ;;
*.sql.gz) echo "$0: running $f"; gunzip -c "$f" | "${psql[@]}"; echo ;;
*) echo "$0: ignoring $f" ;;
esac
echo
done
で順次処理となっているのでファイル名順に実行されるようです。sqlをgzip圧縮したsql.gz拡張子のファイルにも対応しているようですね。

初期化時にdockerユーザーとdockerテーブルを作成するshを作成します。

initdb/01adduser.sh

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
CREATE USER docker;
ALTER USER docker WITH PASSWORD 'dockerpassword';
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL
次にsqlファイル内で\cでデータベースとユーザーをdockerに切り替えてテーブルを作ってみます。shでdb接続からしたほうが簡単な気がしますが、実験です。

initdb/02createtb.sql

\c docker docker

CREATE TABLE auth_group (
id integer NOT NULL,
name character varying(80) NOT NULL
);

COPY auth_group (id, name) FROM stdin;
1 Gyoumu
2 Master
3 System
\.
日本語化したmypostgres:9.6からMacのinitdbディレクトリを/docker-entrypoint-initdb.dにバインドしてコンテナを起動します。

$ docker run -d --name postgres96beta4 -e POSTGRES_PASSWORD=mysecretpassword -p 15432:5432 -v $(pwd)/initdb:/docker-entrypoint-initdb.d mypostgres:9.6
psqlで繋いで確認してみます(一応mypostgres:9.6イメージの方のものを使用)。

$ docker run -it --rm --link postgres96beta4:postgres mypostgres:9.6 psql -h postgres -U postgres
ユーザ postgres のパスワード:
psql (9.6beta4)
"help" でヘルプを表示します.

postgres=# \l
docker | postgres | UTF8 | ja_JP.utf8 | ja_JP.utf8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | docker=CTc/postgres
postgres | postgres | UTF8 | ja_JP.utf8 | ja_JP.utf8 |
template0 | postgres | UTF8 | ja_JP.utf8 | ja_JP.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.utf8 | ja_JP.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres

postgres=# \c docker docker
ユーザ docker のパスワード:
データベース "docker" にユーザ"docker"として接続しました。
docker=> \dt
public | auth_group | テーブル | docker

docker=> select * from auth_group;
1 | Gyoumu
2 | Master
3 | System

docker=> \q
日本語化されたイメージを元に、01adduser.shでdockerユーザーを作成してパスワードを設定、dockerデータベースを作り、02createtb.sqlでdockerデータベースdockerユーザーに切り替えてauth_groupテーブルを作成し、データを流し込めていることが確認できました。

Docker Compose で開発用の PostgreSQL を起動する

日本語化したイメージを使用し、initdbのスクリプトで初期化、データの永続化はbusyboxを使用する方針でdocker-compose.ymlを作成します。

docker-composeでmysql & postgreSQL をサクッと起動

を参考にさせていただきました。

docker-compose.yml

postgres-data:
image: busybox
volumes:
- /var/lib/postgresql/data
container_name: postgres-datastore

postgresql:
image: mypostgres:9.6
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: mysecretpassword
volumes:
- ./initdb/:/docker-entrypoint-initdb.d
ports:
- "15432:5432"
container_name: postgres-db
volumes_from:
- postgres-data
起動します。

$ docker-compose up -d
Creating postgres-datastore
Creating postgres-db
以降、使用するあいだはstop・startで停止・起動していく感じでしょうか。

psqlやpgAdmin3で動作確認。OKでしたのでこれからはコレをベースに調整すれば簡単にPostgreSQLを準備できます。

ディレクトリファイル構成は以下のようになりました。

.
├── Dockerfile
├── docker-compose.yml
└── initdb
    ├── 01adduser.sh
    └── 02createtb.sql

運用中の業務系システムがあるんですが、お客様から「遅い」とご指摘を頂いた機能を調べたところ台帳からデータを取得し、集計しているクエリが非常に遅い事が判明しました。

調査している環境のデータベースは PostgreSQL 9.3 です。こんな時は EXPLAIN ANALYZE。

ちなみにhaisou_daityouが台帳で現在2850万件程のデータがあり日々増え続けています。
syouhin_detailはマスタで380件程度です。

EXPLAIN ANALYZE

SELECT
haisou_daityou.syouhin_detail_id,
syouhin_detail.code as syouhin_detail_code,
haisou_daityou.haisou_bi,
date_part('day'::text, haisou_daityou.haisou_bi)::int as day,
sum(haisou_daityou.haisou_suu) AS haisou_sum

FROM haisou_daityou
LEFT OUTER JOIN syouhin_detail ON haisou_daityou.syouhin_detail_id = syouhin_detail.id

WHERE
"YearMonth"(haisou_daityou.haisou_bi) = "YearMonth"('2016-03-01'::date)

GROUP BY
haisou_daityou.syouhin_detail_id,
syouhin_detail.code,
haisou_daityou.haisou_bi

ORDER BY
syouhin_detail.code,
haisou_daityou.haisou_bi

結果

GroupAggregate (cost=1141834.38..1173281.22 rows=1048228 width=18) (actual time=30736.728..35349.575 rows=9393 loops=1)
-> Sort (cost=1141834.38..1144454.95 rows=1048228 width=18) (actual time=30735.447..34864.067 rows=1024085 loops=1)
Sort Key: syouhin_detail.code, haisou_daityou.haisou_bi, haisou_daityou.syouhin_detail_id
Sort Method: external merge Disk: 30048kB
-> Hash Left Join (cost=650914.54..994020.09 rows=1048228 width=18) (actual time=1812.137..5063.999 rows=1024085 loops=1)
Hash Cond: (haisou_daityou.syouhin_detail_id = syouhin_detail.id)
-> Bitmap Heap Scan on haisou_daityou (cost=650901.04..979593.45 rows=1048228 width=12) (actual time=1811.261..4238.580 rows=1024085 loops=1)
Recheck Cond: ((((date_part('year'::text, (haisou_bi)::timestamp without time zone))::integer * 100) + (date_part('month'::text, (haisou_bi)::timestamp without time zone))::integer) = 201603)
Rows Removed by Index Recheck: 191609
-> Bitmap Index Scan on haisou_daityou_tokuisaki_yearmonth_updated (cost=0.00..650638.98 rows=1048228 width=0) (actual time=1808.060..1808.060 rows=1024085 loops=1)
Index Cond: ((((date_part('year'::text, (haisou_bi)::timestamp without time zone))::integer * 100) + (date_part('month'::text, (haisou_bi)::timestamp without time zone))::integer) = 201603)
-> Hash (cost=8.78..8.78 rows=378 width=10) (actual time=0.847..0.847 rows=379 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 17kB
-> Seq Scan on syouhin_detail (cost=0.00..8.78 rows=378 width=10) (actual time=0.007..0.697 rows=379 loops=1)
Total runtime: 35426.470 ms

うわぁ。おーそーいー。開発環境なので本番と比べると非常に非力ではありますが…

特に

「Hash LEFT JOIN (cost=650914.54..994020.09 ROWS=1048228 width=18) (actual TIME=1812.137..5063.999 ROWS=1024085 loops=1)」

お客さんからの「初回が特に遅い」という話にも納得(初回はハッシュテーブルが作られますが以降はキャッシュされますので)。

ちなみに前記の結果は初回ではありません。やばい。

INDEXを追加してみたり、集計されている中間テーブルを使っての再実装を検討したり(実際にクエリを書いてみたり)、WINDOW関数での再実装を検討したりしましたが、結局、次のような書き換えとなりました。

EXPLAIN ANALYZE

SELECT syouhin_detail.code as syouhin_detail_code, sum_tmp.*
FROM
(
SELECT
haisou_daityou.syouhin_detail_id,
haisou_daityou.haisou_bi,
date_part('day'::text, haisou_daityou.haisou_bi)::int as day,
sum(haisou_daityou.haisou_suu) AS haisou_sum

FROM haisou_daityou

WHERE
haisou_daityou.haisou_bi BETWEEN '2016-03-01'::date AND '2016-03-31'::date

GROUP BY
haisou_daityou.syouhin_detail_id,
haisou_daityou.haisou_bi
) as sum_tmp

LEFT OUTER JOIN syouhin_detail ON sum_tmp.syouhin_detail_id = syouhin_detail.id

ORDER BY
syouhin_detail.code,
sum_tmp.haisou_bi

結果

Sort (cost=87961.52..87988.41 rows=10756 width=26) (actual time=1640.387..1641.442 rows=9393 loops=1)
Sort Key: syouhin_detail.code, haisou_daityou.haisou_bi
Sort Method: quicksort Memory: 971kB
-> Hash Left Join (cost=86797.56..87241.25 rows=10756 width=26) (actual time=1516.623..1529.725 rows=9393 loops=1)
Hash Cond: (haisou_daityou.syouhin_detail_id = syouhin_detail.id)
-> HashAggregate (cost=86784.06..86972.29 rows=10756 width=12) (actual time=1516.362..1524.763 rows=9393 loops=1)
-> Index Scan using haisou_daityou_haisou_bi on haisou_daityou (cost=0.44..78691.12 rows=1079058 width=12) (actual time=0.028..758.021 rows=1024085 loops=1)
Index Cond: ((haisou_bi >= '2016-03-01'::date) AND (haisou_bi <= '2016-03-31'::date))
-> Hash (cost=8.78..8.78 rows=378 width=10) (actual time=0.245..0.245 rows=379 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 17kB
-> Seq Scan on syouhin_detail (cost=0.00..8.78 rows=378 width=10) (actual time=0.005..0.135 rows=379 loops=1)
Total runtime: 1642.233 ms

集計した後にJOINすることで20倍くらい速くなりました。初回ではもっともっと差がでるものと思われます。

集計のためにお気楽にJOINした後にGROUP BYしたのが悪かった模様。

お気楽に書いたクエリがスロークエリ化して泣いたお話でした。「JOIN & GROUP編」とはしたが続編はない！(といいな)

実際は前の記事の前にやったんですけど、動かしたいソフトが使っていたのはpdo_pgsqlじゃなかったのでありました。

ということで、環境は前の記事と同じでPostgres.appを使用しててPostgreSQLのバージョンは9.3.5.0。対象のPHPのバージョンは5.5.14です。anyenvもからんでいるのでphpenv単独の場合とは少しディレクトリ構成が違う点にご注意ください。

以下の記事を参考に作業しました。

.phpenvで入れた場合のpeclモジュールの追加方法

まずはpeclのサイトからpdo_pgsqlを取ってきて解凍。

$ cd tmp
$ wget http://pecl.php.net/get/PDO_PGSQL-1.0.2.tgz
$ tar zxvf PDO_PGSQL-1.0.2.tgz
$ cd PDO_PGSQL-1.0.2

ぺちぱいず(読み方不明)だ！

$ phpize

こんふぃぎゅあしてめいくしましょう。

$ ./configure --with-pdo-pgsql=/Applications/Postgres.app/Contents/Versions/9.3/bin
$ make

インストール内容を確認してから

$ make test

インストール。

$ make install

有効化するためにpdo_pgsqのためのiniファイルを作成。

$ vim /Users/murave/.anyenv/envs/phpenv/versions/5.5.14/etc/conf.d/pdo_pgsql.ini

内容。

extension=pdo_pgsql.so

php.iniに直接追加してもいいけどphp.iniがリセットされて泣いたりするかもしれないから分けておくといいと思う(経験者)。

確認。

$ php -i | grep pdo_pgsql

開発環境のMacでPHPからPostgreSQLにアクセスできなくてアレレ〜となったので入れなおしました。その際のメモをまとめておきます。

Postgres.appを使用しており、PostgreSQLのバージョンは9.3.5.0です。

インストールしたPHPのバージョンは5.5.14。

anyenvもからんでいるのでphpenv単独の場合とは少しディレクトリ構成が違う点にご注意ください。

php_pgsqlを有効にするためにconfigure_optionを追加します。

$ vim /Users/murave/.anyenv/envs/phpenv/share/php-build/definitions/5.5.14

編集します。

configure_option "--with-pgsql" "/Applications/Postgres.app/Contents/Versions/9.3/bin"
install_package "http://php.net/distributions/php-5.5.14.tar.bz2"
install_pyrus
install_xdebug "2.2.5"
enable_builtin_opcache

configure_option “–with-pgsql” の行を追加しました。ここはPostgreSQLの導入状況にあわせる必要があります。

インストールします。

$ phpenv install 5.5.14

インストールしたPHPに切り替えて確認。

$ phpenv global 5.5.14
$ phpenv rehash
$ php -i | grep pgsql

OpenCOBOLからのファイル操作ですが内部での定義によって幾つか種類があります。

1. 固定長レコードのシーケンシャルファイル
2. 固定長レコードのISAM形式ファイル
3. 可変長レコードのシーケンシャルファイル
4. 他

これらはCOBOLソース内でのSELECT句での定義でファイル名を直接または環境変数を経由して間接的に指定することが可能です。OpenCOBOLの素の状態ですと

SELECT ADBF0320 ASSIGN TO "FILE0001"
ORGANIZATION SEQUENTIAL
ACCESS MODE SEQUENTIAL.
と記述されている場合は、FILE0001または環境変数 DD_FILE0001または dd_FILE0001に設定されているファイル名のファイルのOPENが可能です。弊社ではこの環境変数渡しの機能を活用してperlからOpenCOBOL側へJCL中で使用しているファイル名を渡しています。
さて、JCL中ではSYSINと呼ばれる形式でファイルを作らずにその場で渡したいデータを記述することがあります。

\INPUT ACCEPT1,TYPE=DATASSF,LIST=YES
4241122 登録データ１
4241122 登録データ１追加分
\ENDINPUT;

ADAM2200:
\STEP PROG2000 FILE=USL.CAT1 DUMP=DATA SUBLM=NORMAL;
\ASSIGN FILE0010 USR.F001 SHARE=ALL HOLDMODE=NO;
\ASSIGN FILE0110 USR.F011-T FILESTAT=TEMP PUBLIC NORMAL=PASS;
\ALLOCATE FILE0110 USR.F011-T SIZE=05;
\DEFINE FILE0110 RECSIZE=57 BLOCKSZ=10260 INCRSZ=01
RELSP RECFORM=FB;
\ASSIGN SIN ACCEPT1 FILESTAT=SYSIN;
\ENDSTEP;
上記ではJCL中で定義されたASSIGN1というSYSINの内容をSINというファイル識別名に割り当てています。これをperlに置き換える(この部分自動的に処理しています)と

INPUT "ACCEPT1,TYPE=DATASSF,LIST=YES",<<_EOT;
4241122 登録データ１
4241123 登録データ１追加分
_EOT
ENDINPUT;

ADAM2200:
STEP "PROG2000 FILE=USL.CAT1 DUMP=DATA SUBLM=NORMAL";
ASSIGN "FILE0010 USR.F001 SHARE=ALL HOLDMODE=NO";
ASSIGN "FILE0110 USR.F011-T FILESTAT=TEMP PUBLIC NORMAL=PASS";
ALLOCATE "FILE0110 USR.F011-T SIZE=05";
DEFINE "FILE0110 RECSIZE=57 BLOCKSZ=10260 INCRSZ=01",
"RELSP RECFORM=FB";
ASSIGN "SIN ACCEPT1 FILESTAT=SYSIN";
上記のように変換しています。さて、SYSINの内容ですがまず1レコードが何byteであるという情報がありません。そして1行毎に行の長さが異なっています。今回移植の対象となった対象機のCOBOLではこのような場合には「改行区切りで1レコード」とするようになっていました。つまり可変長レコードです。ところがOpenCOBOLで可変長レコードをファイルとして扱うためには：
レコード先頭1バイトまたは2バイトにレコード長＋1レコード分のデータ
レコード先頭1バイトまたは2バイトにレコード長＋1レコード分のデータ
・・・
という形式でデータを作成する必要があります（つまり1byte目がレコード長として正しくないと、メモリ上に過大な長さのデータが読み込まれて、あっという間にSegfault します）。OpenCOBOLの外側からファイルの形式について何らかの方法で指示を出す必要が在りましたので、弊社ではファイル名の先頭に「sysin://」という識別子を（URI的に）付けてファイル名を渡すようにしています。これをOpenCOBOL内のファイルハンドラに渡る前に処理し、改行区切りの可変長レコードとして処理しています。同じく、印刷用の中間データなど1行の長さが可変長となる場合について「sysout://」という識別子を付けて指定することができるようにしています。他、標準の固定長レコードのドライバと動作をちょっと変えたドライバを使いたい場合を考え「misam://」や「mseq://」さらにLinux他では/dev/nullに該当するものとして「nullfs://」という識別子を指定可能としています。
標準の固定長レコードのドライバと動作をちょっと変えたいというのは例えばレコード挿入、削除時の細かい振る舞い、二次キー指定時の動作、二次キーを持っているISAMファイルを主キーしか定義していないCOBOLソースから書き込みモードで開いた場合の動作（OpeCOBOLの標準の動作では、書き込みモードでISAMファイルを開くと、一旦削除されますので、最悪二次キーについての定義が欠落します）等々です。
また、「perlfs://CLASSNAME/param」という形式でファイル名を渡す事によりファイルハンドラとしてperlにて記述したものを呼び出すようにもしています。DBとCOBOL内の固定長レコードの編集用コードについてperlで記述できるため、大変柔軟にDBとの連携を図れるようになりました（つまり、DBD::PgやDBD:MySQL、Oracleなどとの連携も可能です。MySQLについては既に運用されていますし、KeyValue系のDBへの接続もそれほどの変更なしに実装できます）。
上記に加え、固定長レコードやキー定義などの情報を別ディレクトリ内の管理ファイルに登録しておくことで、ファイルオープン時に正しい形式のファイルを使用しているかどうかCOBOLプログラム内の定義と照らし合わせて動的にチェックできるようになり、また現在どのようなファイルがオープン状態であるか？を全てモニタできるようにしています。
これらの改造はOpenCOBOLがオープンソースとして配布されていたことで可能になりました。成果は随時コミュニティ等にフィードバックしていきたいと考えております。