はじめに
PostgreSQLのstored function(ないしストアドプロシージャ)のパフォーマンスチューニングのため、実行計画を見ようとしましたが、シンプルにexplain句などでみることはできませんでした。
explain select some_example (
params := 'params'
);
-- 残念な出力
-- ProjectSet (cost=0.00..5.27 rows=1000 width=32)
-- -> Result (cost=0.00..0.01 rows=1 width=0)
残念ながら中でどれだけ複雑なロジックをもっていてもブラックボックス化されてしまっているようで、残念な出力しか得られません。
今回実行計画を見る方法を調べたので方法の共有のため記事にしました。
方法
auto_explain拡張機能を使います。
この拡張機能は本来は自動的に遅いクエリーの実行計画をログ記録するためのものです。
stored functionを使っている場合、普通に使う分には上の残念な出力がログに流れるだけになってしまいますが、拡張機能のパラメータにlog_nested_statementsなるものがあり、これをtrueにすることでstored functionの内部の実行計画がログに記録されるようになります。
explain の出力が上書きされたりはしません。
stored functionの実行計画は、ログに書きだされたものを見に行きます。
導入手順 RDS編
auto_explainはRDSでも使用可能な拡張機能です。
log_nested_statementsを設定するほかは、基本的にこれにならいます。
パラメータグループを新しくつくりそこで設定していくのがよいでしょう。
設定する項目
| 項目 | 値 |
|---|---|
| shared_preload_libraries | 空 もしくは auto_explainを追加する |
| auto_explain.log_nested_statements | 1 |
| auto_explain.log_min_duration | 0 |
auto_explain.log_min_duration を0にすることですべてのクエリの実行計画を見ることができますが、ログ出力するものが増えパフォーマンスの低下の恐れがあるので、パフォチューなどをしたら適切な値にしておきましょう。
重要: auto_explain.log_min_duration パラメータを 0 に設定すると、パフォーマンスが低下し、記憶域スペースが広範囲に消費されます。これにより、インスタンスに問題が発生する可能性があります。
(出典: https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/)
ちなみにこのパラメーターの意味は実行にかかった時間がそれ以上のクエリを出力するというものです。1にした場合は実行に1ms以上かかったクエリの実行計画が出力されるようになります。
これらのパラメータを設定しDBインスタンスを再起動することで、auto_explainが有効になります。RDSのログ出力先がcloud watchなら、cloud watchにこれらが流れていきます。重いクエリが流れていないか？のような監視につかうのもよいでしょう。n秒以上かかったクエリのシーケンシャルスキャンを見つけて通知するとかいいですね。
導入手順 docker編
ローカルでも使いたいなと思ったのでdockerのpostgresに導入します。
使うイメージは postgres:14.1です。
auto_explainは新たにインストールする必要はありませんでした。auto_explainはcontribの機能なのですが上記のイメージはcontribも含めてbuildしてくれているようです。
設定しないといけないことは、上記のパラメータの他にログの出力先を指定する必要があります。デフォルトでログは出さない設定になっているからです。
docker-compose.ymlでDBの起動コマンドを以下のようにしました
command: postgres -c log_destination=stderr -c auto_explain.log_nested_statements=true -c auto_explain.log_min_duration=0
こちらの
log_destination=stderr
でログ出力先を標準エラー出力にしています。
またデータの初期化のSQLの中で
LOAD 'auto_explain';
を実行させます。これはCREATE EXTENSIONの代わりのようなもので、これを実行することで拡張機能が有効になります。(auto_explainがSQLでアクセスできる関数を提供しないため、単に読み込ませるとドキュメントにあります。)
これでコンテナのエラー出力に、実行計画が出力されるようになります。
まとめ
auto_explain機能をつかうとログに実行計画が流せるよ！