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

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

# 概要

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

# 検証モデル

ちょっと前に分数を管理する[pg_fraction](https://github.com/nuko-yokohama/pg_fraction)というユーザ定義型を作成したので、そのユーザ定義型を使って、postgres_fdw経由で検索してみた。

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

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

```sql

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オプションがない場合

```sql

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つのクエリを実行する。

```sql
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'```という記述が追加されているのに注目。

```sql

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;
```

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

```sql

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を使ったときの性能問題の解決になるかもしれない。