postgres_fdw pushdown

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の20日目のエントリです。

今日のお題

今日のお題は、外部データラッパ(FDW)の中の一つ、postgresq_fdwに関する改善項目について。

FDWとはなんぞ？

PostgreSQLを使っている人なら、知っているとは思うけど念のため。FDW(Foreign Data Wrapper)というのは、ざっくり言うと別の場所にある任意の情報源(別にRDBMSでなくてもいい)を、あたかも仮想的なテーブル(外部テーブル)のように見せて、その外部テーブルに対してSQLでアクセスできるようにする機能だ。

FDWには現時点でも驚くほど多くの種類がある(Foreign data wrappers一覧参照)。
情報源があれば、FDWを作りたがる。これも PostgreSQLかいはつしゃの サガ か・・・。

FDWはPostgreSQLが提供する機能の中でも、自分がとても気に入っているものだ。自分も以前、グラフデータベースNeo4jを情報源とするFDW(neo4j_fdw)を作成したことがある。(最近メンテしていないので、9.5以降はビルドに失敗しちゃうが・・・)

postgres_fdw

FDWの中には、もちろんPostgreSQLに接続するFDWもある。それが、postgres_fdwだ。このモジュールはPostgreSQL 9.3からcontribモジュールとして提供されている。リモートサーバにあるPostgreSQLに接続する機能としては、以前からcontribモジュールのdblinkがあるが、postgres_fdwを用いると通常のテーブルと同様にSQL文でアクセスできるというメリットがある。

FDW/postgres_fdwの改善のあゆみ

リリースノートを元に、FDW基盤とpostgres_fdwの改善点のあらましを簡単にまとめてみた。

バージョン	FDW	postgres_fdw
8.4	SQL/MED用の基盤を追加(DDLのみ実装)	-
9.0	-	-
9.1	参照のための基盤整理 file_fdwのサポート	-
9.2	ALTER文の改善	-
9.3	外部テーブルへの書き込み機能 postgres_fdwのサポート	postgres_fdwの最初のリリース
9.4	-	-
9.5	IMPORT FOREIGN SCHEMAの追加	Importing Optionsの追加
9.6	-	結合、ソート、UPDATE、DELETEのpushdwon Extension Optionの追加(UDFのpushdown)
10	-	集約関数のpushddown 結合pushdownが可能なケースの追加 OID列の対応

PostgreSQL 10のpostgres_fdwの改善項目

上の表にも書いたけど、PostgreSQL 10のpostgres_fdwの改善項目は以下の3つっぽい。

• 集約関数のpushddown
• 結合pushdownが可能なケースの追加
• OID列の対応

今日はこのうち、集約関数のpushdownの例だけを示す。
(結合pushdownが9.6だと出来ず、10だと可能なモデルが作れなかった・・・。そしてOID列の対応については、きちんと理解できず・・・)

100万件のレコードをもつ外部テーブルremote_tableのinteger列に対してACG関数をを実行する例を、EXPLAIN ANALYZE VERBOSEをつけて実行してみる。

• PostgreSQL 9.6

local=# EXPLAIN ANALYZE VERBOSE SELECT AVG(data) FROM remote_table;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=205.06..205.07 rows=1 width=32) (actual time=1198.183..1198.183 rows=1 loops=1)
   Output: avg(data)
   ->  Foreign Scan on public.remote_table  (cost=100.00..197.75 rows=2925 width=4) (actual time=0.641..1106.070 rows=1000000 loops=1)
         Output: oid, id, data
         Remote SQL: SELECT data FROM public.remote_table
 Planning time: 0.855 ms
 Execution time: 1202.615 ms
(7 rows)

• PostgreSQL 10

local=# EXPLAIN ANALYZE VERBOSE SELECT AVG(data) FROM remote_table;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=2600.00..18006.03 rows=1 width=32) (actual time=135.276..135.278 rows=1 loops=1)
   Output: (avg(data))
   Relations: Aggregate on (public.remote_table)
   Remote SQL: SELECT avg(data) FROM public.remote_table
 Planning time: 0.108 ms
 Execution time: 136.650 ms
(6 rows)

実行計画のRemote SQLを見ると分かるように、PostgreSQL 10ではremote server側でAVG関数の処理を実施している。
また、PostgreSQL 9.6と比較すると、実行時間が大きく削減されているのがわかる。ただcostについては、実はPostgreSQL 10のほうが大きくなっている。これは何故なんだろう・・・？
（PostgreSQL 10のForeignScanの推定値が大きめなのかもしれないが）

おわりに

postgres_fdwの進化は、今後予定されている、PostgreSQL自体のシャーディング機能にも繋がっている。次回のリリースで、postgres_fdwも含めどういった進化を遂げるのか楽しみだ。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.12. Additional Modules

• In postgres_fdw, push aggregate functions to the remote server, when possible (Jeevan Chalke, Ashutosh Bapat)
• In postgres_fdw, push joins to the remote server in more cases (David Rowley, Ashutosh Bapat, Etsuro Fujita)
• Properly support OID columns in postgres_fdw tables (Etsuro Fujita)