にゃーん。
今日は自分でデータ型や演算子を作って、かつpostgres_fdwも使いたいという拡張機能大好き人間向けのネタを書いてみた。

概要

postgres_fdw経由でユーザ定義型を使っている表にアクセスする場合、CREATE SERVERコマンドのOPTIONとして、extensionオプションをつけると捗る。
extensions指定しないと条件処理やソート処理をローカル側で実行することになる。リモートからローカル間へのデータ転送量が非常に多くなるし、単純に性能が悪くなる。
extensionsを指定定しておくと、リモート側テーブルにあるユーザ定義型への比較演算や、ソートやLIMIT処理がリモート側で実行されて捗る。

検証モデル

ちょっと前に分数を管理するpg_fractionというユーザ定義型を作成したので、そのユーザ定義型を使って、postgres_fdw経由で検索してみた。

複数のマシンやデータベースクラスタを作るのが面倒だったので、今回は同一データベースクラスタ内にlocalというデータベースとremoteというデータベースを作成する。

そしてremoteデータベース上に、自作のデータ型の拡張pg_fractionを登録し、分数型(fraction)の列をもつ、testという名前のテーブルを作成して、テキトーに10万件ほどデータを挿入しておく。


CREATE EXTENSION pg_fraction ;
CREATE UNLOGGED TABLE test (id int, data fraction, dummy text);

INSERT INTO test VALUES ( generate_series(1,100000), ((random() * 100)::int::text || '/' || ((random() * 100)::int + 100)::text)::fraction, repeat('#', 30));

これで、こんなレコードが格納されているはずだ。

local=# TABLE test LIMIT 5;
 id |  data  |             dummy
----+--------+--------------------------------
  1 | 3/68   | ##############################
  2 | 39/101 | ##############################
  3 | 1/4    | ##############################
  4 | 82/185 | ##############################
  5 | 86/111 | ##############################
(5 rows)

次にremnoteデータベース上に、CREATE EXTENSIONコマンドで、postgres_fdw拡張と、pg_fraction拡張をインストールしておく。

そして、localデータベースから、remoteというデータベースに接続する外部サーバremote_serverを作成する。
さらにIMPORT SCHEMAコマンドで、remoteデータベース上にあるtestテーブルを外部表としてlocalデータベースにインポートする。
（手動でCREATE FOREIGN TABLEで生成しても可）

この状態で、以下の3つのクエリ

INTEGR型のidに対する比較演算
fraction型のdataに対する比較演算
fraction型のdataに対するソート処理＋LIMIT処理

をEXPLAIN ANALYZE VERBOSEで実行して、remoteデータベース内でどんな処理が行われているか(Remote SQL)、また全体の実行時間を見てみる。

extensionsオプションがない場合


CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'remote');
CREATE USER MAPPING FOR postgres SERVER remote_server OPTIONS (user 'postgres');
IMPORT FOREIGN SCHEMA public FROM SERVER remote_server INTO public;

この状態で3つのクエリを実行する。

local=# EXPLAIN ANALYZE VERBOSE SELECT id, data FROM test WHERE id < 1000;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.test  (cost=100.00..145.66 rows=620 width=20) (actual time=0.714..17.445 rows=999 loops=1)
   Output: id, data
   Remote SQL: SELECT id, data FROM public.test WHERE ((id < 1000))
 Planning time: 0.133 ms
 Execution time: 18.322 ms
(5 rows)

local=# EXPLAIN ANALYZE VERBOSE SELECT id, data FROM test WHERE data < '1/100';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.test  (cost=100.00..170.46 rows=620 width=20) (actual time=1.199..411.884 rows=1513 loops=1)
   Output: id, data
   Filter: (test.data < '1/100'::fraction)
   Rows Removed by Filter: 98487
   Remote SQL: SELECT id, data FROM public.test
 Planning time: 0.110 ms
 Execution time: 412.711 ms
(7 rows)

local=# EXPLAIN ANALYZE VERBOSE SELECT id, data FROM test ORDER BY data LIMIT 1000;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=266.90..269.40 rows=1000 width=20) (actual time=456.499..456.877 rows=1000 loops=1)
   Output: id, data
   ->  Sort  (cost=266.90..271.55 rows=1861 width=20) (actual time=456.496..456.641 rows=1000 loops=1)
         Output: id, data
         Sort Key: test.data
         Sort Method: top-N heapsort  Memory: 182kB
         ->  Foreign Scan on public.test  (cost=100.00..165.83 rows=1861 width=20) (actual time=0.385..425.836 rows=100000 loops=1)
               Output: id, data
               Remote SQL: SELECT id, data FROM public.test
 Planning time: 0.069 ms
 Execution time: 457.695 ms
(11 rows)

id列は組み込みデータ型なので、自動的に検索条件をリモート側で実行してくれる。
data列はユーザ定義型なので、検索条件はリモート側で評価されず、全てのレコードをローカル側に転送し、その後で評価される。
data列に対するソート処理も同様に、全てのレコードローカル側に転送した後、ローカル側でソート処理を行う。
要するにdata型を条件にした検索や、ソートするのはフルスキャン＋転送しないといけないので遅い。

extensionsオプションがある場合

今度はextentionsオプションをつけて、CREATE SERVERを実行する。
extensions 'pg_fraction'という記述が追加されているのに注目。


CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'remote', extensions 'pg_fraction');
CREATE USER MAPPING FOR postgres SERVER remote_server OPTIONS (user 'postgres');
IMPORT FOREIGN SCHEMA public FROM SERVER remote_server INTO public;

この状態で、先程と同じクエリを実行してみる。


local=# EXPLAIN ANALYZE VERBOSE SELECT id, data FROM test WHERE id < 1000;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.test  (cost=100.00..145.66 rows=620 width=20) (actual time=1.286..27.608 rows=999 loops=1)
   Output: id, data
   Remote SQL: SELECT id, data FROM public.test WHERE ((id < 1000))
 Planning time: 0.146 ms
 Execution time: 28.377 ms
(5 rows)

local=# EXPLAIN ANALYZE VERBOSE SELECT id, data FROM test WHERE data < '1/100';
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.test  (cost=100.00..145.66 rows=620 width=20) (actual time=1.306..20.998 rows=1513 loops=1)
   Output: id, data
   Remote SQL: SELECT id, data FROM public.test WHERE ((data OPERATOR(public.<) '1/100'::public.fraction))
 Planning time: 0.153 ms
 Execution time: 21.617 ms
(5 rows)

local=# EXPLAIN ANALYZE VERBOSE SELECT id, data FROM test ORDER BY data LIMIT 1000;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=100.00..138.45 rows=1000 width=20) (actual time=241.777..244.521 rows=1000 loops=1)
   Output: id, data
   ->  Foreign Scan on public.test  (cost=100.00..171.55 rows=1861 width=20) (actual time=241.775..244.378 rows=1000 loops=1)
         Output: id, data
         Remote SQL: SELECT id, data FROM public.test ORDER BY data ASC NULLS LAST
 Planning time: 0.261 ms
 Execution time: 246.282 ms
(7 rows)

extensions指定なしのときと比べると、以下のような違いがある。

data列に対する比較条件はリモート側で評価されている。このため、ローカル側に転送されるレコード数は10万件ではなく、1513件に減少している。
data列に対するソートもリモート側で実施されている。
Remote SQLを見ると、LIMIT句は展開はされていないが、リモート側でLIMIT相当の処理は実施しているようであり、リモートからローカルへ転送される件数は1000件になっている。

おわりに

ユーザ定義型とpostgres_fdwを組み合わせて使う機会はそうそうないとは思うけど、組み合わせるときには、extentionsオプションのことを覚えておくと、postgres_fdwを使ったときの性能問題の解決になるかもしれない。

postgres_fdw経由で自作のユーザ定義型を使っている表を検索する

概要

検証モデル

extensionsオプションがない場合

extensionsオプションがある場合

おわりに