まえがき
エンジニアの吉田です。
このブログのタイトルを見て記事をご覧になった方は言うまでもなく常日頃よりexplainコマンドの結果とにらめっこする生活を送っていることと推察しますが、PostgreSQL(以下postgres)の実行計画について「完全に理解した」と言える人は果たしてどれくらいいるでしょうか。
かくいう私も「ここでSeq Scanが走っているんだな」「ここはNested Loop Joinになっているんだな」「ここはパラレルに実行されているんだな」程度の極めて浅い見方しかできていない、というのが実情になります。
本稿はそんな我々の実行計画に対する理解を深めるべくpostgresが扱うノードに着目し、どのようなノードがどのような条件で選択されるのかをまとめた記事となります。なる予定でした。
検証について
※本稿での検証は全てpostgres 16.3で実施したものとなります。
Dockerが使えるならば以下のようなワンライナーで同等の環境を再現できるかと思います。
docker run -itd --name postgres -e POSTGRES_PASSWORD=postgres postgres:16.3
また、クエリの例では以下手順で作ったテーブルを注釈なく使用します。
postgres=# create table hoge (id int primary key, val int);
postgres=# create table fuga (id int primary key, val int);
そもそもノードとは何か
postgresではクエリの実行前にプランナ/オプティマイザがクエリを解析し、実行計画を作成します。実行計画は木構造になっていて、計画それぞれの要素をノードと呼びます[1]。
ノードは行の集合を引数にとり、行の集合を返す演算であり、自分の子となるノードを再帰的に呼ぶことで実際のクエリが処理されます。
groupByやunionのような集合に変換をかける操作、またinsertやupdateのようなデータ変更を伴う操作にもそれぞれのノードが存在します。
postgres=# explain select count(*) from hoge inner join fuga using (val) group by hoge.id;
QUERY PLAN
----------------------------------------------------------------
HashAggregate (cost=0.02..0.03 rows=1 width=12)
Group Key: hoge.id
-> Nested Loop (cost=0.00..0.01 rows=1 width=4)
Join Filter: (hoge.val = fuga.val)
-> Seq Scan on hoge (cost=0.00..0.00 rows=1 width=8)
-> Seq Scan on fuga (cost=0.00..0.00 rows=1 width=4)
(6 rows)
上は2テーブルをvalで結合させ、hoge.idで集約してそれぞれの件数を返すクエリとその実行計画の例です。
実行計画に登場するHashAggregate, Nested Loop, Seq Scanがノードです。いずれの演算も行の集合を引数にとり、行の集合を返す演算であることがわかるかと思います。
今回の例ではHashAggregate, Nested Loop, Seq Scanが選択されていますが、これらの演算方法はテーブルの統計情報やインデックスの有無、計算機資源やパラメータ設定などに応じて異なるものが選択され、それに伴って実行計画ツリーの構造が変わることもあります。
知りたくば、喰らえ（ソースを）
ここで好奇心旺盛な読者の方々はこう思ったことでしょう。 「実行ノードの全種類を一覧で見たい」 と。
残念ながらノードの全量を出すようなシステムカタログなどは用意されておらず一覧参照ができないので、ソースコードを確認してみましょう。
我々がexplainコマンドの結果として確認できるノードの種類はexplain.cのExplainNode内で定義されているpnameに入りうる値、といえそうです。
ざっと見ても60以上の分岐があり、結合など複数方式があるものも含めるとより多岐にわたります。
参考までに、EDBが公開しているウェビナーで各ノードの紹介がされています。
本題：マイナーなノードを出現させてみよう！
ここで飽くなき探求心を持った読者の方々はこう思ったことでしょう。 「このノードは一体何者なんだ？」 と。
ExplainNode内には見たこともない、何をするのかすらわからない名前がたくさんあります。
何をするのかもわからないノードを実際に実行させてみる、というのはなかなか骨の折れる作業かと思いますので、本稿で再現手順とともにそれらのノードがどのような時に出現するのかをまとめてみることにします。レアノードハンターの皆さんに活用いただけると幸いです。
とはいえ、Seq ScanやNested Loopなどは皆さんも親の顔より眺めていることでしょうから、わざわざここで紹介したところで日頃の徒労を思い出すだけになってしまう懸念があります。
全てのノードを紹介するのも記事が長大になってしまいますし、本稿では Google検索で結果件数が少なかったノード10種類 に絞って紹介することにします。
検索結果について
結果は2024/5/21現在のものです。検索は例えばSeq Scanの場合postgresql "Seq Scan"のようなフォーマットでキーワード検索を実施しました。
joinに関しては方式によって異なるものを全てまとめたため、実際より多くヒットしているかと思います。
ちなみに最もヒット件数が多かったのはpostgresql "Group"で約27,200,000件でした。どう考えてもこの件数の全てがGroupノードに言及しているはずはありませんが、厳正に不人気ノードを決めることが趣旨ではないためご容赦ください。
またソースを見ると???のような、入り得ない分岐に定義されているpnameがありますが、これも除外しています。
当初は???を確認することは可能か？という趣旨で記事を書き始めたのですが、そんなことができたら記事なんて書いてないでパッチを投げるべきですし、当然実現できなかったため没となりました。
10位: LockRows (約 1,300 件)
いきなりノードらしからぬ異物がランクインしてワクワクしますね。これは名前の通り行ロックを獲得するノードで、簡単に確認できます。
postgres=# explain select * from hoge for update;
QUERY PLAN
-----------------------------------------------------------
LockRows (cost=0.00..0.01 rows=1 width=14)
-> Seq Scan on hoge (cost=0.00..0.00 rows=1 width=14)
(2 rows)
9位: Sample Scan (約 775 件)
早くも1000件を切りました。なかなかのマイナーノードと言えそうです。実際業務でDBを利用している中で出会うことは稀ではないでしょうか。
Sample Scanはその名の通りテーブルのサンプルを取得するscanで、特に非常に巨大なテーブルのサンプリングに利用されます。
取得したいテーブルの後に tablesample句を付与して、取得方法と割合を与えることでSample Scanが走ります。
postgres=# explain select * from hoge tablesample system(10);
QUERY PLAN
-------------------------------------------------------
Sample Scan on hoge (cost=0.00..4.01 rows=1 width=8)
Sampling: system ('10'::real)
(2 rows)
8位: MixedAggregate (約 414 件)
1000件を切ったと思いきや、すぐに500件も切ってしまいました。
これはmixedという名の通り、異なる手法による集約をまとめるような演算を行うノードです。
grouping setsなどを使って複数の集約キーを用意することで確認できます。
postgres=# explain select id from hoge group by grouping sets(id, ());
QUERY PLAN
----------------------------------------------------------
MixedAggregate (cost=0.00..0.02 rows=2 width=4)
Hash Key: id
Group Key: ()
-> Seq Scan on hoge (cost=0.00..0.00 rows=1 width=4)
(4 rows)
7位: HashSetOp (約 365 件)
SetOpは集合演算を実施するノードで、その中でもハッシュテーブルを用いる場合にHashSetOpが出現します。
except句やintersect句などを実行すれば確認できます。
似たような演算は結合でも可能ですが、両者のノードは異なるため演算方法も異なり、場合によってはクエリを書き換えれば高速化するケースもありそうですね。
postgres=# explain select id from hoge except select id from fuga;
QUERY PLAN
-----------------------------------------------------------------------------
HashSetOp Except (cost=0.00..0.03 rows=1 width=8)
-> Append (cost=0.00..0.03 rows=2 width=8)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..0.01 rows=1 width=8)
-> Seq Scan on hoge (cost=0.00..0.00 rows=1 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.01 rows=1 width=8)
-> Seq Scan on fuga (cost=0.00..0.00 rows=1 width=4)
(6 rows)
6位: Table Function Scan (約 349 件)
結論から言うとこれは実現がかなり難しく、ウェビナーをカンニングした結果xmltable関数によって出現を確認できました[2]。
postgresではテーブルそのものを返す関数を定義でき、こういった関数をtable functionと呼びます。
しかしtable functionを実行しても実行計画上ではFunction Scanとなり、Table Function Scanを見ることはできません。
postgres=# explain
select *
from xmltable(
'/rows/row'
passing
$$
<rows>
<row id="1"></row>
</rows>
$$
columns id int path '@id'
);
QUERY PLAN
-----------------------------------------------------------------------
Table Function Scan on "xmltable" (cost=0.00..1.00 rows=100 width=4)
(1 row)
5位: Foreign Update (約 327 件)
遂にワースト5に突入しましたが、なんとそのうち3つを外部データ関連の処理が占めてしまいました。これは興醒めですね。
Foreignと名の付くノードは文字通りFDWを介した操作のことで、FDWの内部処理は実行計画には表示されません。上位にランクインしたのはデータ更新を伴うFDWの操作群でした。
ただでさえinsertやupdateの実行計画を見る機会は乏しいうえ、FDWで隠蔽されて見る意義が極めて薄いのがマイナーな理由でしょうか。しかし今回は実際に実行されるところを見ることが目標のため、確認のために準備をしてみましょう。
FDWを簡単に試す方法として、今回は同一クラスタ内に別DBを立て、postgres_fdwを介して外部データを操作することにします。
-- 同一クラスタ内に別DBを立て、テーブルを作成
postgres=# create database foreign_db;
postgres=# \c foreign_db
foreign_db=# create table piyo (id int primary key, val int);
foreign_db=# \c postgres
-- 拡張を導入し、foreign serverとuser mappingを定義したのちテーブルをインポート
postgres=# create extension postgres_fdw;
postgres=# create server foreign_server foreign data wrapper postgres_fdw options (host 'localhost', port '5432', dbname 'foreign_db');
postgres=# create user mapping for postgres server foreign_server options (user 'postgres');
postgres=# import foreign schema public from server foreign_server into public;
postgres=# explain update piyo set id = 1;
QUERY PLAN
------------------------------------------------------------------------
Update on piyo (cost=100.00..149.63 rows=0 width=0)
-> Foreign Update on piyo (cost=100.00..149.63 rows=1321 width=42)
(2 rows)
無事にForeign Updateが確認できました。
4位: Foreign Delete (約 280 件)
postgres=# explain delete from piyo;
QUERY PLAN
-----------------------------------------------------------------------
Delete on piyo (cost=100.00..191.90 rows=0 width=0)
-> Foreign Delete on piyo (cost=100.00..191.90 rows=2730 width=6)
(2 rows)
同様にForeign Deleteも確認できました。
3位: Tid Range Scan (約 182 件)
これです！こういうのを待っていたんですよね。業務での利用シーンはかなり少なそうです。
tidというのはタプルの物理的な位置を示すもので、tidによる絞り込み（つまり物理的位置の直接指定）は通常の走査と比べて高速に実施できます。
Tid Range Scanはpostgres14で追加された比較的新しいノードで、従来はSeq Scanとなっていた不等式での評価でもtidによる絞り込み効率が向上しました。
今回検証したところある程度データ量がないとSeq Scanが走ってしまったので、適当なデータを投入してからselectすることで確認できました。
postgres=# insert into hoge select generate_series(1,10000);
postgres=# analyze hoge;
postgres=# explain select * from hoge where ctid < '(1,0)';
QUERY PLAN
----------------------------------------------------------
Tid Range Scan on hoge (cost=0.00..4.00 rows=1 width=8)
TID Cond: (ctid < '(1,0)'::tid)
(2 rows)
2位: Foreign Insert (約 64 件)
postgres=# explain insert into piyo values (1, 2);
QUERY PLAN
--------------------------------------------------
Insert on piyo (cost=0.00..0.01 rows=0 width=0)
-> Result (cost=0.00..0.01 rows=1 width=8)
(2 rows)
なんと postgres_fdwではForeign Insertが確認できませんでした…
update, deleteと比べても極端にヒット件数が少ないのは、そもそも選択されないノードだからなのでしょうか。
検索結果では過去にpostgres_fdwの実行計画に選択されている記述が一つだけ見つかりましたが、postgres 9.3（手元で簡単に作れる最も古いメジャーバージョンがこれでした）でも再現しなかったため、再現するためにはかなり昔に遡る必要があるかもしれません。あるいは、そもそもForeign Insertは特殊ケースでしか選択されないノードなのかもしれません。
しかしなにせ64件しか記事がヒットしないものですから、この検証はなかなかに難航しそうです。Future workとさせてください。
1位: Named Tuplestore Scan (約 56 件)
堂々のワースト1位を獲得したのはNamed Tuplestore Scanとなりました。
これに関しては日本語の記事が全くヒットしなかった上に上述のウェビナーでもスコープ外とされるなど、圧倒的なマイナーっぷりで納得の1位と言えるでしょう。
このノードは一時的に保持している行の集合(Tuplestore)に対する走査をする際に現れるものです。Tuplestoreが作られる処理のうち容易に再現できるものとしては遷移テーブルが挙げられます。
遷移テーブルとはトリガが参照する一時的なテーブルで、トリガ実行前に発生した差分がまとめられています[3]。
つまり遷移テーブルを作るようなトリガを作って実施すれば、Named Tuplestore Scanを確認できそうだ、ということです。
-- hogeにinsertした内容をそのままfugaにinsertするプロシージャ
postgres=# create function copy_inserted() returns trigger as $$
begin
if (tg_op = 'INSERT') then
execute 'insert into fuga select * from new_table';
end if;
return null;
end;
$$
language plpgsql;
-- hogeへのinsert後に発火するトリガ
postgres=# create trigger copy_hoge_insert_to_fuga
after insert on hoge
-- referencing句で遷移テーブルに名前を付ける（ここでは'new_table'）と、その名前でプロシージャから参照できる
referencing new table as new_table
for each statement execute procedure copy_inserted();
早速hogeにinsertした内容をそのままfugaにinsertするトリガを作ってみました。しかし残念ながら、explainではトリガの実行計画は表示されません。
トリガの実行計画を見るためにはauto_explainモジュールを使用する必要があります[4]。
auto_explainはその名の通りクエリの統計情報を自動的にログ出力するためのcontribモジュールです。log_nested_statementsという設定パラメータがあり、これをonにすることで通常は確認できない関数内から実行された文の実行計画も記録されるようになります。
postgres=# load 'auto_explain';
postgres=# set auto_explain.log_min_duration = 0;
postgres=# set auto_explain.log_nested_statements to on;
postgres=# insert into hoge values (12345, 12345);
上記のようにauto_explainをloadしてパラメータを変更し、実際にhogeに対してinsertしてみたのち、ログを見てみます。
LOG: duration: 0.011 ms plan:
Query Text: insert into fuga select * from new_table
Insert on fuga (cost=0.00..0.02 rows=0 width=0) (actual time=0.009..0.010 rows=0 loops=1)
-> Named Tuplestore Scan (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)
CONTEXT: SQL statement "insert into fuga select * from new_table"
PL/pgSQL function copy_inserted() line 4 at EXECUTE
LOG: duration: 0.170 ms plan:
Query Text: insert into hoge values (12345, 12345);
Insert on hoge (cost=0.00..0.01 rows=0 width=0) (actual time=0.033..0.034 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
無事にトリガ内のinsert文の実行計画が記録され、Named Tuplestore Scanを確認できました。ラスボスの座にふさわしい難易度でしたね。
まとめ
本稿では紹介しなかったものも多数ありますが、実行計画は近年のバージョンでも頻繁に改善が繰り返されており、今後も新種のノードがどんどん追加されていくことでしょう。
特殊なノードが用意されるということは、その用途において特殊な手段を講じることが有効だということを意味します。クエリチューニングではその場その場で最適な選択を繰り返すことになりますので、特殊なケースには特殊な手段が使えないか、というアンテナは常に張っておきたいですね。
この記事を書いた人
吉田 侑弥
2020年新卒入社
最近インドアサイクリングを日課にしているのですが、あまりにも虚無なため「1分経ったぞ！楽しい！」という精神状態で進められるようになりました。
-
これは一般的なノードという単語の意味するところと同じで、postgresの日本語マニュアルでは計画ノードと呼ばれることもあります。本稿ではそのままノードと呼称します。 ↩︎
-
ウェビナーでも "weirdest" と言われており、ごく限られた処理でしか実行されないレアキャラだったようです。 ↩︎
-
statement単位でのトリガを実装して遷移テーブルを参照するようにすることで、行単位でのトリガと比べてパフォーマンスが向上する場合があります。 ↩︎
-
https://www.postgresql.org/message-id/A737B7A37273E048B164557ADEF4A58B50F92C76%40ntex2010i.host.magwien.gv.at ↩︎
Discussion