Hatena::ブログ(Diary)

日々の記録 別館

2016-06-13

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のソースって実表だけなんだっけ?と疑問に思ったので、ソースとしてビュー、マテリアライズド・ビュー、そして外部表を試してみることにした。
構成はこんな感じ。
f:id:nuko_yokohama:20160613212358p:image

(´-`).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されてますね。

知ってる人には当たり前かもしれないけど、自分で確認して納得できたので良かった。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/nuko_yokohama/20160613/1465820662
Connection: close