postgres_fdwで異バージョン接続/外部表のカスケード
PostgreSQL 9.6/9.5のpostgres_fdwの差異を調べていて、ふと気になったことを調べてみた。
異バージョン間でFDW
PostgreSQL 9.6/9.5のpostgres_fdwの差異を調べていて、ふと気になったのだけど、異なるバージョン間をpostgres_fdwで接続ってできたっけ。
ということでやってみた。
ローカルサーバが9.6、リモートサーバが9.5の場合。
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(a.data1), AVG(a.data2)
FROM table_a a JOIN table_b b ON (a.id = b.id)
WHERE a.data1 = 10000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=342.59..342.60 rows=1 width=64) (actual time=2.176..2.176 rows=1 loops=1)
Output: avg(a.data1), avg(a.data2)
-> Foreign Scan (cost=100.00..341.78 rows=161 width=8) (actual time=2.151..2.153 rows=11 loops=1)
Output: a.data1, a.data2
Relations: (public.table_a a) INNER JOIN (public.table_b b)
Remote SQL: SELECT r1.data1, r1.data2 FROM (public.table_a r1 INNER JOIN public.table_b r2 ON (((r1.id = r2.id)) AND ((r1.data1 = 10000))))
Planning time: 1.714 ms
Execution time: 11.359 ms
(8 rows)おお、フツーに結合SQLを9.5のリモートサーバに投げているな。
ローカルサーバが9.5、リモートサーバが9.6
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(a.data1), AVG(a.data2)
FROM table_a a JOIN table_b b ON (a.id = b.id)
WHERE a.data1 = 10000 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=349.93..349.94 rows=1 width=8) (actual time=1152.246..1152.246 rows=1 loops=1)
Output: avg(a.data1), avg(a.data2)
-> Hash Join (cost=238.80..349.12 rows=161 width=8) (actual time=48.058..1152.208 rows=10 loops=1)
Output: a.data1, a.data2
Hash Cond: (b.id = a.id)
-> Foreign Scan on public.table_b b (cost=100.00..197.75 rows=2925 width=4) (actual time=0.349..1065.369 rows=1000000 loops=1)
Output: b.id, b.data1, b.data2
Remote SQL: SELECT id FROM public.table_b
-> Hash (cost=138.66..138.66 rows=11 width=12) (actual time=0.284..0.284 rows=10 loops=1)
Output: a.data1, a.data2, a.id
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan on public.table_a a (cost=100.00..138.66 rows=11 width=12) (actual time=0.274..0.277 rows=10 loops=1)
Output: a.data1, a.data2, a.id
Remote SQL: SELECT id, data1, data2 FROM public.table_a WHERE ((data1 = 10000))
Planning time: 0.128 ms
Execution time: 1152.649 ms
(16 rows)ローカル側は9.5なので、当然ながらJoin-pushdownの実行計画が存在しない。
なので、リモートの9.6に対して、結合のないクエリを複数回発行して、その結果をローカル側で結合する。
まあ、当たり前の結果ではあるけど、一応試してみたかったので。
postgres_fdw のカスケード
ていうか、postgres_fdwのソースって実表だけなんだっけ？と疑問に思ったので、ソースとしてビュー、マテリアライズド・ビュー、そして外部表を試してみることにした。
構成はこんな感じ。
(´-`).oO (IMPORT FOREIGN SCHEMAがサポートされて、こういう検証が本当に楽になったよ・・・)
さて、この構成でサーバ 96_l の外部テーブルに対してクエリを投げてみる。
詳細は省略するけど、table_a, table_b, table_a_v, table_a_v, table_c, table_d 全て外部テーブル経由で検索はできた。
もちろん、更新不可能なビューや、マテリアライズド・ビューをソースとする外部表を更新しようとするとエラーになる。
bench=# UPDATE table_a_v SET data1 = 50000; ERROR: cannot update view "table_a_v" DETAIL: Views containing TABLESAMPLE are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule. CONTEXT: Remote SQL command: UPDATE public.table_a_v SET data1 = 50000 STATEMENT: UPDATE table_a_v SET data1 = 50000;
bench=# UPDATE table_a_mv SET data1 = 50000; ERROR: cannot change materialized view "table_a_mv" CONTEXT: Remote SQL command: UPDATE public.table_a_mv SET data1 = 50000 STATEMENT: UPDATE table_a_mv SET data1 = 50000;
で、せっかく9.6を使っているので、Join pushdownが効くのかどうか試してみた。
table_a, table_bを結合したクエリは当然結合クエリが 96_r にpushdownされてます。
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(a.data1), AVG(a.data2)
FROM table_a_v a JOIN table_b b ON (a.id = b.id)
WHERE a.data1 = 1000 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=342.59..342.60 rows=1 width=64) (actual time=6.023..6.023 rows=1 loops=1)
Output: avg(a.data1), avg(a.data2)
-> Foreign Scan (cost=100.00..341.78 rows=161 width=8) (actual time=6.016..6.016 rows=0 loops=1)
Output: a.data1, a.data2
Relations: (public.table_a_v a) INNER JOIN (public.table_b b)
Remote SQL: SELECT r1.data1, r1.data2 FROM (public.table_a_v r1 INNER JOIN public.table_b r2 ON (((r1.id = r2.id)) AND ((r1.data1 = 1000))))
Planning time: 0.248 ms
Execution time: 7.353 ms
(8 rows)では、96_rr → 96_r → 96_l のように外部表をカスケードした場合はどうなるのか。
お、96_rr サーバまで結合クエリがきちんとpushdownされてますね。
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(c.data1), AVG(c.data2)
FROM table_c c JOIN table_d d ON (c.id = c.id)
WHERE c.data1 = 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=294.93..294.94 rows=1 width=64) (actual time=1.105..1.105 rows=1 loops=1)
Output: avg(c.data1), avg(c.data2)
-> Foreign Scan (cost=100.00..277.86 rows=3413 width=8) (actual time=1.101..1.101 rows=0 loops=1)
Output: c.data1, c.data2
Relations: (public.table_c c) INNER JOIN (public.table_d d)
Remote SQL: SELECT r1.data1, r1.data2 FROM (public.table_c r1 INNER JOIN public.table_d r2 ON (((r1.id = r1.id)) AND ((r1.data1 = 1000))))
Planning time: 0.140 ms
Execution time: 1.787 ms
(8 rows)96_r, 96_rr サーバのデータベースに contrib/auto_explain を入れて、実際にどんなSQLが発行されてみるか確認してみる。
96_r サーバで実行されたSQL
LOG: duration: 0.392 ms plan: Query Text: DECLARE c1 CURSOR FOR SELECT r1.data1, r1.data2 FROM (public.table_c r1 INNER JOIN public.table_d r2 ON (((r1.id = r1.id)) AND ((r1.data1 = 1000)))) Foreign Scan (cost=100.00..277.86 rows=3413 width=8) Relations: (public.table_c r1) INNER JOIN (public.table_d r2)
96_rr サーバで実行されたSQL
LOG: duration: 0.018 ms plan: Query Text: DECLARE c1 CURSOR FOR SELECT r1.data1, r1.data2 FROM (public.table_c r1 INNER JOIN public.table_d r2 ON (((r1.id = r1.id)) AND ((r1.data1 = 1000)))) Nested Loop (cost=0.29..2553.33 rows=100000 width=8) -> Index Scan using c_data1 on table_c r1 (cost=0.29..12.33 rows=1 width=8) Index Cond: (data1 = 1000) Filter: (id = id) -> Seq Scan on table_d r2 (cost=0.00..1541.00 rows=100000 width=0)
うん、きちんと結合条件がpushdownされてますね。
知ってる人には当たり前かもしれないけど、自分で確認して納得できたので良かった。
