postgres_fdwのjoinが1300倍高速化！22時間→1分にしたチューニング手法

はじめに

こんにちは、エンジニアの長谷です。

フォルシアではPostgreSQL拡張の一つであるpostgres_fdwを使ってDB間の連携をとっているのですが、最近その処理が非常に遅くなっていることがわかりました。
ボトルネックとなっている部分は非常にシンプルなSQLながらちょっとした実装の違いでパフォーマンスに大きな差が出る興味深いものであり、実際チューニングすると22時間かかっていた処理を1分にできました。
本記事では簡単な例を用いて、得られる結果は同じだが表現の異なる4つのSQLでパフォーマンス比較をし、fdwでのjoin高速化についてご紹介します。

fdwとは

• fdw（foreign data wrapper）とは、外部にあるデータにアクセスできる仕組みのことです。PostgreSQLにおいては、PostgreSQL拡張としてpostgres_fdwがあります。
• 異なるDB間でSQLを実行でき、データの連携ができます（DB以外も連携できたりします）
• ドキュメント：https://www.postgresql.jp/document/17/html/postgres-fdw.html

処理の状況

外部DB（remote）から処理が行われるDB（local）にテーブルを取り込む際に、localのテーブルとjoinしながら取り込むような処理があったのですが、今回はそこがボトルネックとなっていました。約100テーブルほどに対して取り込みが行われるのですが、その中でもレコード数が非常に多いテーブル（1000万程度）で時間がかかっていました。そのようなテーブルでも、localとの絞り込みで最終的にはかなり絞り込まれることが想定されるので、通常はインデックスが効いていれば十分高速なクエリでした。

実験

実験に使用するテーブル

• 上記の処理を本記事で簡単に再現し解説するために、以下のようなシンプルなテーブルを作成し、いくつかのクエリで実験してみます
• 今回やりたいことは、test1テーブルのidに対応するtest2テーブルのvalueをlocal側で取得することです

※ PostgreSQLは17.6を使用しています

test1テーブル（local）（id=1,10,100,1000,10000,100000,1000000の7レコード）

カラム名	型	PRIMARY KEY制約
id	bigint	あり

test2テーブル（remote）（id=1,2,...10000000の1000万レコード）

カラム名	型	PRIMARY KEY制約
id	bigint	あり
value	int	なし

実験に使用するクエリ

1. inner joinでの絞り込み（チューニング前の処理としてはこれが実装されているイメージです）

クエリ1

select
    value
from
    test1
    inner join remote.test2 using(id)
;

2. where句での固定値指定

クエリ2

select
    value
from
    remote.test2
where
    id in (1, 10, 100, 1000, 10000, 100000, 1000000)
;

3. サブクエリをin句に指定

クエリ3

select
    value
from
    remote.test2
where
    id in (select id from test1)
;

4. 配列化してanyで指定

クエリ4

select
    value
from
    remote.test2
where
    id = any(array(select id from test1))
;

実験結果

remoteでどのようなSQLが実行されているかを確認するためにexplain analyze verboseをクエリの先頭につけて実行した結果です。

1の結果

実行結果1

explain analyze verbose
select
    value
from
    test1
    inner join remote.test2 using(id)
;

                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=160.85..677.33 rows=2275 width=4) (actual time=1.010..25207.315 rows=7 loops=1)
   Output: test2.value
   Inner Unique: true
   Hash Cond: (test2.id = test1.id)
   ->  Foreign Scan on remote.test2  (cost=100.00..610.50 rows=2275 width=12) (actual time=0.977..24583.470 rows=10000000 loops=1)
         Output: test2.id, test2.value
         Remote SQL: SELECT id, value FROM public.test2
   ->  Hash  (cost=32.60..32.60 rows=2260 width=8) (actual time=0.021..0.022 rows=7 loops=1)
         Output: test1.id
         Buckets: 4096  Batches: 1  Memory Usage: 33kB
         ->  Seq Scan on public.test1  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.015..0.016 rows=7 loops=1)
               Output: test1.id
 Planning Time: 0.107 ms
 Execution Time: 25208.333 ms
(14 rows)

25秒かかりました。特に注目したいのがRemote SQL: SELECT id, value FROM public.test2の部分です。これはremote側でまずtest2テーブルの全量を取得していることを表しており、1000万レコードが絞り込まれることなく全てlocalに転送されてしまっています。実業務で処理が遅くなっていた部分もこのようにremoteでの絞り込みが効かず、全てlocalに転送したうえでSQLが実行されているのが原因でした。

2の結果

実行結果2

explain analyze verbose
select
    value
from
    remote.test2
where
    id in (1, 10, 100, 1000, 10000, 100000, 1000000)
;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on remote.test2  (cost=100.00..186.26 rows=102 width=4) (actual time=4.199..4.200 rows=7 loops=1)
   Output: value
   Remote SQL: SELECT value FROM public.test2 WHERE ((id = ANY ('{1,10,100,1000,10000,100000,1000000}'::bigint[])))
 Planning Time: 0.380 ms
 Execution Time: 5.100 ms
(5 rows)

固定値で埋め込んだ結果、5msと非常に高速な処理に変わりました。同様にRemote SQLの部分を見てみると、test2での絞り込みが効いているのがわかります。このようにfdwではremoteで絞り込みが効くことで大きくパフォーマンスが向上します。

3の結果

実行結果3

explain analyze verbose
select
    value
from
    remote.test2
where
    id in (select id from test1)
;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=160.85..677.33 rows=2275 width=4) (actual time=0.783..25221.623 rows=7 loops=1)
   Output: test2.value
   Inner Unique: true
   Hash Cond: (test2.id = test1.id)
   ->  Foreign Scan on remote.test2  (cost=100.00..610.50 rows=2275 width=12) (actual time=0.753..24597.819 rows=10000000 loops=1)
         Output: test2.id, test2.value
         Remote SQL: SELECT id, value FROM public.test2
   ->  Hash  (cost=32.60..32.60 rows=2260 width=8) (actual time=0.017..0.018 rows=7 loops=1)
         Output: test1.id
         Buckets: 4096  Batches: 1  Memory Usage: 33kB
         ->  Seq Scan on public.test1  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.012..0.014 rows=7 loops=1)
               Output: test1.id
 Planning Time: 0.158 ms
 Execution Time: 25222.428 ms
(14 rows)

固定値ではなくサブクエリとしてin句に記載したところ、またremoteでの絞り込みが効かなくなってしまいました。絞り込みがremoteで有効になる基準についてはドキュメントなどに明記はされていなさそうで理由は不明ですが、サブクエリを書いただけでは、事前に結果を埋め込んでremoteのみで処理を行うといった判断は実行計画ではされなかったようです。

4の結果

実行結果4

explain analyze verbose
select
    value
from
    remote.test2
where
    id = any(array(select id from test1))
;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Foreign Scan on remote.test2  (cost=132.60..238.44 rows=143 width=4) (actual time=1.377..1.379 rows=7 loops=1)
   Output: test2.value
   Remote SQL: SELECT value FROM public.test2 WHERE ((id = ANY ($1::bigint[])))
   InitPlan 1
     ->  Seq Scan on public.test1  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.017..0.018 rows=7 loops=1)
           Output: test1.id
 Planning Time: 0.114 ms
 Execution Time: 2.341 ms
(8 rows)

3と似たような形ですが、in句ではなく配列化して判定してみたところこちらはremoteでの絞り込みが有効になっており、高速に処理されました。
こちらもなぜ有効になったのかははっきりわからないのですが、(id = ANY ($1::bigint[]))を見るとサブクエリがパラメータ化されているため、サブクエリのみを記述するのではなくパラメータ化されるようなSQLを書けるとうまくいくのかもしれません。

実験結果まとめ

手法	実行時間	remote側での絞り込み
1. inner joinでの絞り込み	25,208 ms (約25秒)	❌ 効かない
2. where句での固定値指定	5 ms	✅ 有効
3. サブクエリをin句に指定	25,222 ms (約25秒)	❌ 効かない
4. 配列化してanyで指定	2 ms	✅ 有効

• inner joinでの絞り込み: remote側で絞り込みが効かず、1000万レコード全てがlocalに転送される結果、25秒かかった
• where句での固定値指定: remote側で絞り込みが有効になり、5msと高速な処理となった
• サブクエリをin句に指定: 固定値と異なり、remote側での絞り込みが効かず、25秒かかった
• 配列化してanyで指定: サブクエリの結果を配列化してパラメータとして渡すことで、remote側での絞り込みが有効になり2msと高速な処理となった

まとめ

postgres_fdwを使用したlocalとremote間のjoin処理が遅い理由とその高速化について簡単な実験を行いました。
fdwのパフォーマンスチューニングにおいては以下を意識するのがよさそうだと思いました。

• localとremoteのテーブルでjoinすると、remoteのテーブルがすべてlocalに転送された上でSQLがlocalで実行されるため効率が悪い
• パフォーマンス調査の際にはexplain analyze verbose付きでSQLを実行し、remote側で実行されるSQLによって絞り込みが有効になっているかを確認する
• fdwはremote側でできる限り絞り込んでからlocalに転送（プッシュダウン）されるようなSQLとなるように実装を意識することが大切
  • localとremoteのテーブルはjoinしないようにする
  • joinをwhere句などの等価な表現で置き替える
  • 同じ結果を返すクエリでも、記述方法によってパフォーマンスが大きく変わる可能性があるため、複数のアプローチを試してみる

この記事を書いた人

長谷　洋斗
2021年新卒入社
最近iPhone17 Pro Maxにしたのですが、充電がとても長持ちするのと遠くの写真や動画がきれいに撮影できて感動してます