コールバック関数4つ × 周辺範囲5つ × 複数回テスト、を一つのクエリで

種類の多いテストを複数回実行して処理時間を測るため ↓ の無名コードブロックを作りました。最初の変数trialに任意の実行回数を入れます。後は自動的に今回のテストを実行し、各回の所要時間をファイルに書き込む仕組み。

do $$
declare
    trial int := 5;
    func_pres text[] := '{st, simple, plv8, sql}';
    func_pre text;
    ranges int[] := '{0, 1, 2, 3, 4}';
    range int;
    ts timestamp;
    sec float;
    outfile text := 'R:/range4ma_times.tsv';
begin
    -- output column header and initialize outfile
    execute format('copy (values(%L, %L, %L, %L)) to %L',
        'trial', 'func_pre', 'range', 'sec', outfile);

    -- trials
    for i in 1..trial loop
        for func_pre in select unnest(func_pres) loop
            for range in select unnest(ranges) loop
                ts := clock_timestamp();
                
                -- execute function
                perform ST_MapAlgebra(rast, 1, (func_pre ||
                    '_range4ma(float[][][], int[][], text[])') :: regprocedure,
                    '8BUI', 'FIRST', NULL, range, range) from adbadge_tall;
                
                -- get duration
                sec := extract(epoch from clock_timestamp() - ts);

                -- append result to file
                execute format('copy (values(%L, %L, %L, %L)) to program %L',
                    i, func_pre, range, sec, 'find /v "" >> "' || outfile || '"');

                -- show status
                raise info '%, %, %, %', i, func_pre, range, sec;
            end loop;
        end loop;
    end loop;
end;
$$;

前回までは画像処理の結果をPNGファイルに出力しましたが、今回はコールバック関数とピクセル範囲による処理時間を比較すればいいのでPNG出力は省きました。上記クエリの真ん中へんです（PERFORM文）。PERFORMは「結果を受け止らないSELECT」で、こういう場合に便利。

このPERFORM文の前後のタイムスタンプを取り、その差を処理時間としてます。で、コールバック関数名などと合わせて外部ファイルに書き込み。この際、普通にCOPY文を使うのでは追記書き込みできませんが、PostgreSQL 9.3で追加されたTO PROGRAMを使い、WindowsのFINDコマンドを経由することで追記書き込みしました。

FINDコマンドを使ったのは、これ以外にWindowsのコンソール（CMDシェル）で「標準入力をそのまま受け取って表示する」方法がなさそうだから。下記でこの方法を知らなかったら、諦めてました。

DOUDEMO EXE－標準入力の受け取り

今回は追記書き込みなので、FINDコマンドの表示先は「>>」。>にすれば常に新規書き込みになります。この部分の動作確認は ↓ こちら。2行目のCOPY文が追記書き込み。

copy (select 1, 2, 3) to program 'find /v "" > "R:/test.tsv"';
copy (select * from generate_series(2, 9)) to program 'find /v "" >> "R:/test.tsv"';

↓ テスト実行中の様子と、結果のファイル（COPY文のデフォルトのタブ区切り）。

テスト結果のTSVをPostgreSQLにインポート

20種類×5回＝計100行の短いテスト結果ですが、慣れているSQLで処理したいのでPostgreSQLにインポート。ヘッダ付のTSV（タブ区切りテキスト）をテーブルに入れるCOPY文は「CSVフォーマット、タブ区切り、ヘッダ有り」のオプション指定で ↓ できます。TSV本体はこちら（range4ma_times.tsv, 1.91KB）

create table result_range4ma (
    trial int, func_pre text, pixel_range int, sec float);
copy result_range4ma from 'R:/range4ma_times.tsv'
    (format csv, delimiter E'\t', header true);

-- Check
select * from result_range4ma;

PL/v8の方が、元からあるPL/pgSQLの数倍速い

今回は東芝R644（Core i7の法人向けモバイルノートPC）で実行しました。詳細は ↓ のとおり。CPUがモバイル用なので、Core i7と言っても余り速くありません。（その分バッテリーは良く持ちます）

CPU: Intel Core i7-4600U（2.1GHz）
Memory: 8 GB

前項で取り込んだテスト結果のうち、ピクセル範囲=4についてクエリで見やすく整理 ↓ してみました。関数名の表記で「st」はPostGISに元からあるPL/pgSQLのストアド、「simple」はそのうち条件分岐などを除いて配列処理に単純化したもの。PL/v8が14秒台後半、stが80～100秒台と、PL/v8の方が数倍速かったです。

select repeat(func_pre, 1 / trial) as func,
    repeat(to_char(min(sec) over w, dig), 1 / trial) as "min(sec)",
    repeat(to_char(max(sec) over w, dig), 1 / trial) as "max(sec)",
    trial, to_char(sec, dig) as sec
from result_range4ma, (values ('999.999')) as x(dig)
where pixel_range = 4
window w as (partition by func_pre)
order by min(sec) over w, trial
;
+--------+----------+----------+-------+----------+
|  func  | min(sec) | max(sec) | trial |   sec    |
+--------+----------+----------+-------+----------+
| plv8   |   14.742 |   14.898 |     1 |   14.789 |
|        |          |          |     2 |   14.836 |
|        |          |          |     3 |   14.773 |
|        |          |          |     4 |   14.742 |
|        |          |          |     5 |   14.898 |
| sql    |   28.439 |   28.766 |     1 |   28.595 |
|        |          |          |     2 |   28.766 |
|        |          |          |     3 |   28.439 |
|        |          |          |     4 |   28.766 |
|        |          |          |     5 |   28.657 |
| simple |   70.965 |   71.293 |     1 |   71.152 |
|        |          |          |     2 |   71.011 |
|        |          |          |     3 |   71.167 |
|        |          |          |     4 |   71.293 |
|        |          |          |     5 |   70.965 |
| st     |   87.937 |  108.919 |     1 |   88.126 |
|        |          |          |     2 |  108.919 |
|        |          |          |     3 |   87.937 |
|        |          |          |     4 |   88.374 |
|        |          |          |     5 |   88.453 |
+--------+----------+----------+-------+----------+
(20 rows)

ただし普通の画像処理アプリと比べると、PL/v8でも10数秒なわけで実用的とは言えず、ちょっと落胆。ラスタ1ピクセルごとにコールバック関数を呼び出す仕組み自体に限界があるような気がします。何か新機軸を考えたいなぁ。

ところで今回ならったPostgres Online Journalの記事には、同じピクセル範囲=4で次の結果が載ってました。全体的に自分の環境より2倍近く速いですが、関数間の差は同じ傾向。unnestと集約関数を使うSQLが、PL/v8程ではないけどPL/pgSQLより結構速いのは興味深いです。

PL/v8 : 8.241 sec
SQL : 17.701 sec
元からあるPL/pgSQL : 51.922 sec

自分のテストのうち、最も計算量が少ないピクセル範囲=0の結果を見ると ↓PL/v8に次いでPL/pgSQLを単純化したバージョンが多少速く、元からあるPL/pgSQLとSQLはほとんど同じ。先ほどのピクセル範囲=4とは関数間の優劣が違います。処理する配列の増減による変化が関数によって異なるみたいで、明日以降もう少し検討する予定。

select repeat(func_pre, 1 / trial) as func,
    repeat(to_char(min(sec) over w, dig), 1 / trial) as "min(sec)",
    repeat(to_char(max(sec) over w, dig), 1 / trial) as "max(sec)",
    trial, to_char(sec, dig) as sec
from result_range4ma, (values ('999.99')) as x(dig)
where pixel_range = 0
window w as (partition by func_pre)
order by min(sec) over w, trial
;
+--------+----------+----------+-------+---------+
|  func  | min(sec) | max(sec) | trial |   sec   |
+--------+----------+----------+-------+---------+
| plv8   |    2.18  |    2.25  |     1 |    2.22 |
|        |          |          |     2 |    2.25 |
|        |          |          |     3 |    2.18 |
|        |          |          |     4 |    2.22 |
|        |          |          |     5 |    2.23 |
| simple |    5.38  |    5.44  |     1 |    5.44 |
|        |          |          |     2 |    5.40 |
|        |          |          |     3 |    5.38 |
|        |          |          |     4 |    5.44 |
|        |          |          |     5 |    5.43 |
| st     |    7.41  |    7.58  |     1 |    7.53 |
|        |          |          |     2 |    7.43 |
|        |          |          |     3 |    7.58 |
|        |          |          |     4 |    7.41 |
|        |          |          |     5 |    7.53 |
| sql    |    7.43  |    7.69  |     1 |    7.49 |
|        |          |          |     2 |    7.53 |
|        |          |          |     3 |    7.69 |
|        |          |          |     4 |    7.43 |
|        |          |          |     5 |    7.52 |
+--------+----------+----------+-------+---------+
(20 rows)

あと、先ほど載せたピクセル範囲=4の実行5回のうち、関数st（元からある版）の所要時間が「88秒, 109秒, 88秒, 88秒, 88秒」と2回目だけ目立って遅く、何かPCに常駐してるアプリが一時的に動いて邪魔した感じ。でも確証はないわけで、もしかしたら関数自体が持つ幅かも知れません。本題とは離れますが、こういうのを「外れ値」として除外してよいか気になるので、データ処理一般のトピックとして明日以降あらためて書きます。