PostgreSQL 9.6 パラレルクエリに関する小ネタ

よく考えたら当たり前なんだけど、いざ気付くと「えーっ！」となった衝撃的な話。

「パラレルクエリは、マテリアライズド・ビューのリフレッシュに使えない」疑惑

パラレルクエリの制限事項って結構あるんですね。そのうちの1つを9.6.1時点のマニュアルから抜粋すると 

クエリがデータを書き込むか、データベースの行をロックする場合。 クエリがデータ更新操作をトップレベルあるいはCTE内で含むと、そのクエリに対するパラレルプランは生成されません。 これは現在の実装の制限で、将来のリリースでは解除される可能性があります。 

要はDMLはダメと言っているように見える。じゃあマテビュー作るのもダメじゃね？という当然の話ではあるのですが。夜間に巨大なテーブルの加工や集計（そんな処理を数十分とか、数時間かけて）した結果をマテビューにして、なんとか日中はサクサク動くように頑張るわけじゃないですか。
で、パラレルクエリが入ったときに、「くそ重いロックをとるマテビューのリフレッシュが、2時間かかってたやつが1分になったら（キラキラ）」などと夢をいだいたものです。ところがそれが現状のパラレルクエリでは何も変わらないのです。がびーん。

そんなわけで、実際に試してみることにします。 わざわざ実行計画を見るのはめんどいマテビューでは内部で実行されたクエリの実行計画は覆い隠されてしまうので、単に所要時間で（psqlの\timingで）パラったか判断します。

今回使う表（今回、表の中身は重要ではない。件数と所要時間だけ注目）

demo=# \timing
demo=# SELECT count(*) FROM tab1;
  count
----------
 28864000
(1 行)
時間: 669.988 ms   --- パラレルの時は1秒以下、非パラレルだと4秒強かかることを確認済み。

/* 実行計画はこんな感じ */
demo=# EXPLAIN ANALYZE
demo-# SELECT count(*) FROM tab1;
                                                                 QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=333435.96..333435.97 rows=1 width=8) (actual time=1015.604..1015.604 rows=1 loops=1)
   ->  Gather  (cost=333435.33..333435.94 rows=6 width=8) (actual time=1015.510..1015.594 rows=7 loops=1)
         Workers Planned: 6
         Workers Launched: 6
         ->  Partial Aggregate  (cost=332435.33..332435.34 rows=1 width=8) (actual time=1008.983..1008.983 rows=1 loops=7)
               ->  Parallel Seq Scan on tab1  (cost=0.00..320408.67 rows=4810667 width=0) (actual time=0.043..622.811 rows=4123429 loops=7)
 Planning time: 0.126 ms
 Execution time: 1017.730 ms

/* テーブルサイズはこれぐらい */
demo=# SELECT pg_size_pretty(pg_relation_size('tab1'));
 pg_size_pretty
----------------
 2127 MB

マテリアライズド･ビューを作成してリフレッシュ

demo=# CREATE MATERIALIZED VIEW mv1 AS
demo-# SELECT count(*) FROM tab1;
SELECT 1
時間: 4510.077 ms   --- む、この時間は・・・。

demo=# REFRESH MATERIALIZED VIEW mv1;
REFRESH MATERIALIZED VIEW
時間: 4451.020 ms   --- む、この時間は・・・。

うーん、パラられない。残念でならない！
というわけで、何かそれに代わる策がないか試してみます。

VIEWの場合

demo=# CREATE VIEW v2 AS
SELECT count(*) FROM tab1;
CREATE VIEW
時間: 2.151 ms
demo=# SELECT * FROM v2;
  count
----------
 28864000
(1 行)

時間: 682.303 ms --- viewならOKだよねうん。

まあ、これは大丈夫か。SELECTだけだし。でも結果を保存して再利用できない。

普通の表をつくってINSERT SEELCT

demo=# CREATE TABLE t3 (count numeric);
CREATE TABLE

demo=# INSERT INTO t3
demo-# SELECT count(*) FROM tab1;
INSERT 0 1
時間: 4506.088 ms --- だめ

だめ。デスヨネー。

外部テーブルを介してMビュー。

外部テーブルで問合せた先は、リモートのサーバー側でよしなに実行計画を作ってくれるはずなので淡い期待を込めてやってみるが・・・

demo=# CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
demo-# OPTIONS (dbname 'demo',host 'localhost',port '5432'); -- 自分自身を見に行くFDW
CREATE SERVER
時間: 1.331 ms
demo=# CREATE USER MAPPING FOR public SERVER loopback
demo-# OPTIONS (user 'postgres',password 'postgres');
CREATE USER MAPPING
時間: 0.914 ms
demo=# IMPORT FOREIGN SCHEMA public LIMIT TO (tab1)
demo-# FROM SERVER loopback
demo-# INTO loopback;
IMPORT FOREIGN SCHEMA
時間: 7.039 ms
demo=# EXPLAIN ANALYZE
demo-# SELECT * FROM loopback.tab1 WHERE sales_id = 0;　-- 一応ループバックしてFOREIGN SCANしてることを確認
                                                   QUERY PLAN

----------------------------------------------------------------------------------------------------------------
 Foreign Scan on tab1  (cost=100.00..117.59 rows=3 width=112) (actual time=396.211..4527.848 rows=1152 loops=1)
 Planning time: 0.286 ms
 Execution time: 4528.583 ms
(3 行)

時間: 4529.705 ms

demo=# CREATE MATERIALIZED VIEW mv2 AS
demo-# SELECT count(*) FROM loopback.tab1;
SELECT 1
時間: 71687.426 ms  ---パラレルかどうかよりaggrigate push downが効いてないせいかな。

demo=# REFRESH MATERIALIZED VIEW mv2;
REFRESH MATERIALIZED VIEW
時間: 68418.958 ms

ダメ。むしろダメ。Aggrigateのpush downはまだ実装されてないんだった。全件もってきて集計してるんだとしたら、仮にリモートでパラレルで動いてくれても、ネットワークが重すぎる。

\copy と COPY

demo=# \copy (SELECT count(*) FROM tab1) TO demo.copy;
COPY 1
時間: 4518.860 ms --ダメ

demo=# COPY (SELECT count(*) FROM tab1) TO '/home/enterprisedb/demo.copy';
COPY 1
時間: 4524.612 ms --ダメ

だめ。うーん、これぐらいはイケると思ったんだが。

\oでファイルに出力

demo=# \o demo2.copy
demo=# SELECT count(*) FROM tab1; -- OK
時間: 699.871 ms

OK！うれしい！

psqlの引数としてクエリを投げ、結果をファイルに出力

[postgres@demo ~]$ time psql -U postgres demo -At -c 'SELECT count(*) FROM tab1' -o demo3.copy

real    0m0.725s -- OK
user    0m0.003s
sys     0m0.009s

[postgres@demo ~]$ psql -U postgres demo -At -c "COPY t3 FROM '/home/enterprisedb/demo3.copy'"
　-- これで格納もできる。（t3は上部検証で作成したやつ）
  -- もちろん、集計ずみの1行だけ格納するので一瞬で終わる。

OK！うれしい！これだ感！これができればシェルとかプログラムで簡単にマテビューチックなことができる、はず。