PostgreSQLで多数のパーティションを持つテーブルに対してPrepared Statementを実行した際の性能劣化について調べてみた
CX事業本部@大阪の岩田です。
PostgreSQLで多数のパーティションを持つテーブルに対してPrepared Statementを実行するとクエリが遅くなる事象について調査する機会があったので、内容をご紹介します。
環境
今回検証に利用した環境は以下の通りです
- PostgreSQL: 15.3 (Debian 15.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
- postgres:15のDockerイメージを利用
- postgresql.conf
- 初期値から以下パラメータのみ変更
- plan_cache_mode... 検証パターンに応じて切り替え
- ログ関連のパラメータ
- 各種の実行時間がログに残るよう以下のように修正
- log_min_duration_statement: 0
- logging_collector: on
- node-postgres:8.11.0
パーティションテーブルに対してPrepared Statementを実行する際の考慮事項
こちらのブログで詳細に解説されている通り、パーティションテーブルに対してPrepared Statementを実行する際はパフォーマンス面での考慮が必要です。
上記ブログでは以下のようなことが解説されています
- Prepared Statementの実行計画には汎用プランとカスタムプランの2種類がある
- 汎用プランはパラメータにバインドされる値に依存しない汎用的な実行計画
- カスタムプランはパラメータにバインドされた値に最適化した実行計画
- パーティションテーブルに対して汎用プランを利用するのはパフォーマンス上の問題を引き起こす可能性がある
- 汎用プランが有効かどうか再検証するために全パーティションに読み取りロックをかけつつスキャンする
- これによりO(n)のオーバーヘッドが発生する ※nはパーティションの数
- パラメータのplan_cache_modeによって汎用プランもしくはカスタムプランの利用を強制できる
- デフォルトではplan_cache_modeはauto
- 汎用プランもしくはカスタムプランのいずれを利用するかは自動的に決定される
plan_cache_modeがautoの場合の振る舞いについて公式ドキュメントを調べたところ、PREPAREの解説で以下のように記載されていました。
he current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.
つまり、対象となるPrepared Statementの最初の5回分の実行についてはカスタムプランが利用され、6回目以後についてはMIN(AVG(実行されたカスタムプランのコスト), 汎用プランのコスト)を参照してコストの小さい方のプランを利用するようです。この辺りは以下のスライドも参考になりました。
検証してみる
前述のブログを読んで「なるほど」と感じたと同時に、追加で以下のような疑問が生まれました
- 拡張クエリプロトコルを使って実行するunnamedなPrepared Statementは「使い捨て」なので、いちいち汎用プランを作成しないのではないか？
- unnamedなPrepared Statementであればパーティションテーブルに対して実行してもパフォーマンス劣化を引き起こさないのではないか？
- Prepared Statementの利用をやめてリテラルを使ってSQLを記述すればパフォーマンスの問題は起きないのではないか？
ということで、実際に自分で環境を作って検証してみました。
検証環境の用意
検証としてpgbenchを使ってパーティションが1個だけのテーブル、パーティションが1000個のテーブルを作成して色々なパターンでクエリを実行してみます。まずは検証用のデータベースを作成します。
postgres=# create database "1000partitions"; CREATE DATABASE postgres=# create database "1partitions"; CREATE DATABASE
pgbenchでパーティションが1個だけのテーブルを作成します
pgbench -i --partitions 1 -U postgres 1partitions dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... creating 1 partitions... generating data (client-side)... 100000 of 100000 tuples (100%) done (elapsed 0.10 s, remaining 0.00 s) vacuuming... creating primary keys... done in 0.25 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.13 s, vacuum 0.07 s, primary keys 0.04 s).
同様にパーティションが1000個のテーブルを作成
pgbench -i --partitions 1000 -U postgres 1000partitions dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... creating 1000 partitions... generating data (client-side)... 100000 of 100000 tuples (100%) done (elapsed 0.19 s, remaining 0.00 s) vacuuming... creating primary keys... done in 7.00 s (drop tables 0.00 s, create tables 3.13 s, client-side generate 0.34 s, vacuum 1.09 s, primary keys 2.44 s).
psqlからテーブル定義を確認しておきます
1partitions=# \d pgbench_accounts
Partitioned table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Partition key: RANGE (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Number of partitions: 1 (Use \d+ to list them.)
1000partitions=# \d pgbench_accounts
Partitioned table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Partition key: RANGE (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Number of partitions: 1000 (Use \d+ to list them.)
期待通りテーブルが作成できました。
続いて検証用のコードを準備します。今回はNode.jsで以下のような簡単なプログラムを書きました。
const pg = require('pg')
const test = async ()=> {
const dbClient = new pg.Client({
user: 'postgres',
host: '127.0.0.1',
// database: '1000partitions',
database: '1partitions',
password: 'postgres',
});
await dbClient.connect()
// // 拡張問い合わせ(クエリ)プロトコルでPrepapredステートメントを実行
// const query = {
// name: 'test',
// text: 'select * from pgbench_accounts where aid = $1',
// values: [1],
// rowMode: 'array',
// }
// await dbClient.query(query)
// 拡張問い合わせ(クエリ)プロトコルでunnamedなPrepapredステートメントを実行
await dbClient.query('select * from pgbench_accounts where aid = $1',[1])
// // 簡易問い合わせ(クエリ)プロトコルでSQLを実行
// await dbClient.query('select * from pgbench_accounts where aid = 1')
await dbClient.end()
}
test().then(console.log('end'))
適宜コメントアウトとコメントアウトの解除を行い、様々なパターンでクエリが実行できるようにしています。
色んなパターンで1回ずつクエリを発行してみる
検証環境ができたので
- パーティション数
- plan_cache_mode
- クエリ種別
を変えながら1回ずつクエリを実行し、PostgreSQLのログから所要時間を確認してみました。
ログはこんな感じ
2023-05-25 08:04:57.915 UTC [230] LOG: duration: 0.406 ms parse <unnamed>: select * from pgbench_accounts where aid = $1 2023-05-25 08:04:57.921 UTC [230] LOG: duration: 6.272 ms bind <unnamed>: select * from pgbench_accounts where aid = $1 2023-05-25 08:04:57.921 UTC [230] DETAIL: parameters: $1 = '1' 2023-05-25 08:04:57.921 UTC [230] LOG: duration: 0.047 ms execute <unnamed>: select * from pgbench_accounts where aid = $1 2023-05-25 08:04:57.921 UTC [230] DETAIL: parameters: $1 = '1'
ログを整形しつつ結果をまとめると以下のようになりました
| パーティション数 | plan_cache_mode | クエリ種別 | parse | bind | execute | 合計 |
|---|---|---|---|---|---|---|
| 1 | force_generic_plan | named | 0.307 | 0.974 | 0.069 | 1.35 |
| 1 | force_generic_plan | unnamed | 0.323 | 0.499 | 0.086 | 0.908 |
| 1 | force_generic_plan | リテラル利用 | - | - | - | 1.46 |
| 1 | force_custom_plan | named | 0.723 | 0.947 | 0.066 | 1.736 |
| 1 | force_custom_plan | unnamed | 0.299 | 0.616 | 0.034 | 0.949 |
| 1 | force_custom_plan | リテラル利用 | - | - | - | 0.744 |
| 1000 | force_generic_plan | named | 0.336 | 302.675 | 1.141 | 304.152 |
| 1000 | force_generic_plan | unnamed | 0.351 | 59.825 | 0.076 | 60.252 |
| 1000 | force_generic_plan | リテラル利用 | - | - | - | 6.249 |
| 1000 | force_custom_plan | named | 0.456 | 6.391 | 0.051 | 6.898 |
| 1000 | force_custom_plan | unnamed | 0.488 | 5.998 | 0.064 | 6.55 |
| 1000 | force_custom_plan | リテラル利用 | - | - | - | 5.806 |
※実行時間の単位は全てmsです
パーティション数が1,000になると汎用プランの再検証で1000パーティションへのスキャンが発生するため、bindが遅くなっていることが分かります。気になっていたunnamedなPrepared Statementについては名前付きのPrepared Statementに比べて早いような気もするし、誤差のような気もするし...ここはもう少し計測回数を増やして詳しく見ていきたいところです。
WHERE句をリテラルで記述した非Prepared Statementの場合は1000パーティションかつforce_generic_planの場合でも6.2msで処理できており、汎用プラン再検証のオーバーヘッドが発生していないことが伺えます。
1000パーティションのテーブルに対して1000回クエリを発行してbindのオーバーヘッドを確認してみる
先程の検証結果から
- 1000パーティションの場合に1パーティションよりもオーバーヘッドが大きくなること
- 汎用プランを利用する場合はbindが遅くなること
が確認できたので、今度は1000パーティションに限定してもう少しちゃんと計測してみます。まずは各設定においてbindの所要時間がどのように変化するかを計測してみました。今度は各パターンについて1000回ずつ計測を行っています。結果は以下の通りです。
| plan_cache_mode | クエリ種別 | 平均 | 中央値 | 90%タイル | 95%タイル |
|---|---|---|---|---|---|
| force_generic_plan | named | 68.319204 | 67.049 | 74.1012 | 77.5862 |
| force_generic_plan | unnamed | 66.111728 | 64.907 | 71.2096 | 73.4793 |
| force_custom_plan | named | 5.702829 | 5.598 | 6.2726 | 6.60085 |
| force_custom_plan | unnamed | 5.627867925 | 5.504 | 6.1309 | 6.40005 |
この結果をみると名前付きのPrepared StatementもunnamedなPrepared Statementもbindのオーバーヘッドに差は無いように見えますね
1000パーティションのテーブルに対して1000回クエリを発行してトータルの実行時間を確認してみる
リテラルを利用した非Prepared StatementのSQLの速度も比較したいので、今度はbindの所要時間ではなくクエリのトータルの所要時間を比較してみました。結果は以下の通りです。
※Prepared Statementの場合はparse,bind,execute3つの所要時間合計を所要時間としています
| plan_cache_mode | クエリ種別 | 平均 | 中央値 | 90%タイル | 95%タイル |
|---|---|---|---|---|---|
| force_generic_plan | named | 68.819649 | 67.545 | 74.6537 | 78.09485 |
| force_generic_plan | unnamed | 66.587918 | 65.4405 | 71.7254 | 73.88435 |
| force_generic_plan | リテラル利用 | 6.134899 | 5.975 | 6.7919 | 7.2192 |
| force_custom_plan | named | 6.157199 | 6.06 | 6.767 | 7.11095 |
| force_custom_plan | unnamed | 6.060142558 | 5.9275 | 6.5994 | 6.87435 |
| force_custom_plan | リテラル利用 | 5.949285 | 5.779 | 6.5601 | 7.0531 |
plan_cache_modeの設定が何であれリテラル利用は安定して早いですね。
汎用プランの生成や再検証がそもそも発生しないため当然の結果といえるでしょう。plan_cache_modeの設定を変更する場合は影響範囲が多岐に渡ることになります。ある特定のSQLに関してだけパフォーマンス劣化の問題が発生していると特定できているのであればPrepared Statementの利用をやめるのは有効な選択肢になりそうです。※Prepared Statementの利用をやめるとSQLインジェクションのリスクが高まるので、外部から入力された値を利用してSQLを組み立てる場合はしっかりとバリデーションやサニタイズを実施しましょう。
まとめ
パーティションテーブルに対してPrepared Statementを実行する際の考慮事項について改めて検証してみました。個人的な見解としては汎用プランの再検証によってパフォーマンスが悪化するようなSQLはPrepared Statementの利用をやめるのが良さそうだと感じました。
もしRDS for PostgreSQLでLWLock:lock_managerが頻発している場合...それはPrepared Statementによって生成された汎用プランの再検証により全パーティションへのロックが発生しているのかもしれません。こういったケースへの対策としてplan_cache_modeの調整やPrepared Statementの利用をやめる といった選択肢も検討してみて下さい。
