PostgreSQLのquery_id、plan_id、実行計画を記録する拡張機能pg_stat_plansについて / PostgreSQL's query_id, plan_id, and pg_stat_plans

「PostgreSQLのquery_id、plan_id、実行計画を記録する拡張機能pg_stat_plansについて」
第55回 PostgreSQLアンカンファレンス@オンライン https://pgunconf.connpass.com/event/379848/ 発表資料

Transcript

1. © NTT DOCOMO SOLUTIONS, Inc. 2026 PostgreSQLのquery_id、plan_id、 実行計画を記録する拡張機能 pg_stat_plansについて 2026年02月05日 NTTドコモソリューションズ株式会社 技術革新本部 システム技術部 オープンソーステクノロジセンタ 黒田 佳祐
2. © NTT DOCOMO SOLUTIONS, Inc. 2026 1 目次 自己紹介・背景 PostgreSQLのquery_idとplan_id パラメータcompute_query_id Jumbling plan_idの現状 拡張機能pg_stat_plans(v2.0) PostgreSQL 18で追加されたplan_id関連実装 これから
3. © NTT DOCOMO SOLUTIONS, Inc. 2026 2 自己紹介 NTTドコモソリューションズ 黒田 佳祐 2025年7月に社名が変わりました NTTコムウェア -> NTTドコモソリューションズ おもにPostgreSQLをふくむデータベースに関する技術検証・サポートに従事 好きなPostgreSQLの拡張機能 pg_stat_statements(SQL文の統計情報記録)
4. © NTT DOCOMO SOLUTIONS, Inc. 2026 3 背景 SQL文の統計情報を記録する拡張機能pg_stat_statementsについて 調べていく中で、SQL文の識別子であるquery_idと、 実行計画の識別子であるplan_idに関する 様々な議論・発表をみて興味を持ちました。 多数のSQL文や実行計画の統計情報…たとえば実行時間・実行回数等を 蓄積したり集計したい場合に、識別子があればそれぞれのSQL・実行計画を 一意に識別して効率的に蓄積・集計ができ便利です。 PostgreSQLの性能問題解析において重要な役割を持つ query_id、plan_idの仕組みと、拡張機能における対応状況についてお話します。 まだ開発中の機能に関するお話もふくまれますが、 query_idやplan_idがどのように生成・利用されているのかを知ることで、 PostgreSQLでSQLに関する性能問題が起きた時、 SQL文・実行計画の効率的な情報収集に役立てられれば幸いです。
5. © NTT DOCOMO SOLUTIONS, Inc. 2026 4 PostgreSQLのquery_idとplan_id 同一SQL文を識別するためのIDが query_id PostgreSQL 14以降は本体機能として利用可能です。 同一実行計画を識別するためのIDが plan_id PostgreSQL 18時点では、plan_idを計算する機能は本体に含まれていません。 後述するpg_stat_plansやpg_store_plansといった拡張機能では利用可能です。 SQL文・実行計画は基本的に長文になります。 これらの統計情報を蓄積して集計・解析したい場合に、 SQL文・実行計画をそのまま検索条件とするのは非常に扱いにくいです。 これらの識別子があると、SQL文・実行計画に関する情報収集に便利です。 ただし、これらはあくまでもハッシュ処理されたIDであるため、 同一のSQLや実行計画であっても、 異なる環境・バージョンでは異なるIDが割り当てられる点に注意が必要です。
6. © NTT DOCOMO SOLUTIONS, Inc. 2026 5 PostgreSQLのquery_idとplan_id postgres=# SELECT queryid,query FROM pg_stat_statements; queryid | query ---------------------+------------------------------------------------------------------- -375957924471267726 | SELECT n.nspname as "Schema", | c.relname as "Name", | CASE c.relkind WHEN $1 THEN $2 WHEN $3 THEN $4 WHEN $5 THEN $6 | WHEN $7 THEN $8 WHEN $9 THEN $10 WHEN $11 THEN $12 WHEN $13 | THEN $14 WHEN $15 THEN $16 WHEN $17 THEN $18 END as "Type", | pg_catalog.pg_get_userbyid(c.relowner) as "Owner" | FROM pg_catalog.pg_class c | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam | WHERE c.relkind IN ($19 /*, ... */) | AND n.nspname <> $20 AND n.nspname !~ $21 AND n.nspname <> $22 | AND pg_catalog.pg_table_is_visible(c.oid) | ORDER BY 1,2 たとえば、以下のようなSQLの情報収集をするとき、 queryをそのまま検索条件として指定したり、 他テーブルとの結合条件にSQL全文を指定するのは非常に面倒です。 query_idがあれば、この識別子だけで特定SQLの情報収集が可能となります。
7. © NTT DOCOMO SOLUTIONS, Inc. 2026 6 PostgreSQLのquery_idとplan_id postgres=# SELECT queryid,query FROM pg_stat_statements WHERE query LIKE '%SELECT n.nspname%'; queryid | query ---------------------+------------------------------------------------------------------- -375957924471267726 | SELECT n.nspname as "Schema", | c.relname as "Name", | CASE c.relkind WHEN $1 THEN $2 WHEN $3 THEN $4 WHEN $5 THEN $6 | WHEN $7 THEN $8 WHEN $9 THEN $10 WHEN $11 THEN $12 WHEN $13 | THEN $14 WHEN $15 THEN $16 WHEN $17 THEN $18 END as "Type", | pg_catalog.pg_get_userbyid(c.relowner) as "Owner" | FROM pg_catalog.pg_class c | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam | WHERE c.relkind IN ($19 /*, ... */) | AND n.nspname <> $20 AND n.nspname !~ $21 AND n.nspname <> $22 | AND pg_catalog.pg_table_is_visible(c.oid) | ORDER BY 1,2 あるqueryのquery_idを特定したい場合、最初はquery列にSQLの情報を 検索条件に指定するなどして特定していく必要がありますが、1度確認できれば 同一環境であればこれ以降はquery_idを使って特定SQLの情報収集ができます。 類似のSQLが大量にある場合、query列だけで絞り込むのは中々大変です。
8. © NTT DOCOMO SOLUTIONS, Inc. 2026 7 PostgreSQLのquery_idとplan_id postgres=# SELECT planid,plan FROM pg_stat_plans; planid | plan ----------------------+--------------------------------------------------------------------- 1015162893667589961 | Sort | Sort Key: n.nspname, c.relname | -> Hash Join | Hash Cond: (c.relnamespace = n.oid) | -> Seq Scan on pg_class c | Filter: ((relkind = ANY ('{r,p,v,m,S,f,""}'::"char"[])) AND | pg_table_is_visible(oid)) | -> Hash | -> Seq Scan on pg_namespace n | Filter: ((nspname <> 'pg_catalog'::name) AND | (nspname !~ '^pg_toast'::text) AND | (nspname <> 'information_schema'::name)) plan_idも同様です。以下は拡張機能pg_stat_plans(v2.0)での例です。 このような識別子がないと、同一SQL・実行計画に関する情報収集・蓄積を するために、毎回非常に長いSQL文や実行計画がすべて一致しているか 確認しなければなりませんが、識別子があれば識別子の比較だけで済みます。
9. © NTT DOCOMO SOLUTIONS, Inc. 2026 8 パラメータcompute_query_id compute_query_idは、PostgreSQL 14で追加された機能・パラメータです。 > サーバパラメータcompute_query_idが有効の場合、 > pg_stat_activity、EXPLAIN VERBOSE、csvlog、および、 > 指定に応じてlog_line_prefixで、クエリIDを出力するようにしました。 (出典: PostgreSQL 14.5文書 付録E リリースノート https://www.postgresql.jp/docs/14/release-14.html) pg_stat_statementsも、これを利用してquery_idを計算します。 13以前はpg_stat_statementsがquery_idの計算を行っていましたが、 14で計算処理が本体に移されました。
10. © NTT DOCOMO SOLUTIONS, Inc. 2026 9 Jumbling たとえば列別名だけが異なる場合や、空白・改行の数が異なっていても、 意味的には同一のSQLであれば、同一のquery_idで扱えるようにしたいです。 そのためにPostgreSQLでは「Jumbling」という処理をして、 意味的に同一のSQLからは同一のquery_idが計算できるようにしています。 Jumblingには、SQL文をそのままハッシュ化するのではなく、 SQLの構造解析処理と合わせて重要な情報のみ抽出してハッシュ化することで、 パフォーマンスへの影響を抑える役割もあります。 Jumblingは、区別をつかなくさせる・混ぜ合わせて一緒にする・ ひとくくりにする ごちゃまぜにする…といった意味の言葉です。 日本語ではひとまず「ジャンブリング」と呼ぶのが良いでしょうか。 ひとくくりにする、が最も近いかなと思います。
11. © NTT DOCOMO SOLUTIONS, Inc. 2026 10 Jumbling 列別名が異なっていたり、スペースが含まれていても、 意味的に同一のSQLであれば、ジャンブリングによって query_idは同一となります。 ## 列別名が “A” Postgres=# explain(analyze,buffers,verbose) SELECT id1 AS “A” FROM test1; Query Identifier: -5483179652120623656 ## 列別名が “B” postgres=# explain(analyze,buffers,verbose) SELECT id1 AS "B" FROM test1; Query Identifier: -5483179652120623656 ## SQLにスペースが含まれている postgres=# explain(analyze,buffers,verbose) SELECT id1 AS "B" FROM test1; Query Identifier: -5483179652120623656
12. © NTT DOCOMO SOLUTIONS, Inc. 2026 11 Jumbling PostgreSQL 18からは、IN句内で指定する条件が2つ以上ある場合は 同一のquery_idとなるようジャンブリング処理が変更されています。 PostgreSQL 17以前では、それぞれ異なるquery_idで出力されます。 ## PostgreSQL 18.0 postgres=# explain(analyze,buffers,verbose) SELECT * FROM test1 WHERE id1 IN (1); Query Identifier: 1180084584667567685 postgres=# explain(analyze,buffers,verbose) SELECT * FROM test1 WHERE id1 IN (1,2); Query Identifier: -1145653792731238719 postgres=# explain(analyze,buffers,verbose) SELECT * FROM test1 WHERE id1 IN (1,2,3); Query Identifier: -1145653792731238719 ## PostgreSQL 17.6 postgres=# explain(analyze,buffers,verbose) SELECT * FROM test1 WHERE id1 IN (1); Query Identifier: 1873785992871691032 postgres=# explain(analyze,buffers,verbose) SELECT * FROM test1 WHERE id1 IN (1,2); Query Identifier: 7527615588110704768 postgres=# explain(analyze,buffers,verbose) SELECT * FROM test1 WHERE id1 IN (1,2,3); Query Identifier: -972988021940978450
13. © NTT DOCOMO SOLUTIONS, Inc. 2026 12 plan_idの現状 SQLに対するquery_idと同様、実行計画に対してもplan_idがあります。 PostgreSQL 18時点では、plan_idを計算する機能は本体に含まれていません。 SQLの実行計画の統計情報を記録する拡張機能pg_store_plansは、 独自にplan_idを計算する機能を持ちます。 https://github.com/ossc-db/pg_store_plans ただし、pg_store_plansは記録した実行計画を まるごとハッシュ化してplan_idを計算します。 SQLが実行されるたびにこの計算処理が動作するため、 パフォーマンスへの影響が大きくなっています。 このため、検証環境では有用ですが商用環境での適用には注意が必要です。
14. © NTT DOCOMO SOLUTIONS, Inc. 2026 13 拡張機能pg_stat_plans(v2.0) パフォーマンスに関する問題に対処するため、新たな拡張機能 pg_stat_plans(v2.0)の提案・開発がコミュニティにて進められています。 https://github.com/pganalyze/pg_stat_plans query_idと同様に、実行計画をジャンブリングして重要な情報のみ抽出して ハッシュ化することで、パフォーマンスへの影響を抑えています。 拡張機能pg_stat_plans(v1.0)も存在しますが、 こちらは開発が停止しています。この拡張機能も plan_idを計算する機能を持ちますが、 pg_store_plans同様実行計画を丸ごとハッシュ化するため パフォーマンスへの影響が大きいものでした。 https://github.com/2ndQuadrant/pg_stat_plans
15. © NTT DOCOMO SOLUTIONS, Inc. 2026 14 拡張機能pg_stat_plans(v2.0) ## pg_stat_statements number of transactions actually processed: 665549 latency average = 0.045 ms initial connection time = 2.005 ms tps = 22186.410262 (without initial connection time) ## pg_stat_plans + pg_stat_statements number of transactions actually processed: 652956 latency average = 0.046 ms initial connection time = 2.084 ms tps = 21766.648208 (without initial connection time) ## pg_store_plans + pg_stat_statements number of transactions actually processed: 493990 latency average = 0.061 ms initial connection time = 2.068 ms tps = 16467.420732 (without initial connection time) ## テスト概要 それぞれの拡張機能を有効化した状態で、 pgbench(SELECT-Only)による負荷試験を30秒実施し、 秒あたりのトランザクション数(TPS)を比較します。 ## 環境情報 PostgreSQL 17.6 / pg_stat_plans 2.0 / pg_store_plans 1.9 KVM(CPU 4Core,MEM 4GB) Rocky8.6 ## 実行手順 デフォルト設定で初期構築 ## 30秒間SELECTのみ実行するパターンでテスト pgbench -i pgbench -T 30 –S
16. © NTT DOCOMO SOLUTIONS, Inc. 2026 15 拡張機能pg_stat_plans(v2.0) pg_stat_statements のみ使用時と比較して、 pg_stat_plans 使用時 TPSは 約2%減 pg_store_plans 使用時 TPSは 約25%減 pg_stat_plansではパフォーマンスへの影響を 抑えられていることがわかります。 16467 21766 22186 0 5000 10000 15000 20000 25000 pg_store_plans + pg_stat_statements pg_stat_plans + pg_stat_statements pg_stat_statements TPS
17. © NTT DOCOMO SOLUTIONS, Inc. 2026 16 PostgreSQL 18で追加されたplan_id関連実装 plan_idの計算処理はまだPostgreSQL本体には含まれていませんが、 PostgreSQL 18では、拡張機能との連携を目的として、 「拡張機能によって計算したplan_idをプランナ情報にセットする」 ことができるようになっています。 Allow plugins to set a 64-bit plan identifier in PlannedStmt https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2a0cd38da5ccf70461c51a489ee7d25fcd3f26be これによって、拡張機能pg_stat_plans(v2.0)では、 実行計画・plan_idに関する情報を蓄積するだけでなく、 「特定プロセスで実行中のSQLのplan_idを確認する」ことができます。
18. © NTT DOCOMO SOLUTIONS, Inc. 2026 17 PostgreSQL 18で追加されたplan_id関連実装 「pg_stat_plans_activity」ビューで、各プロセスが実行しているSQLの 実行計画・plan_idを確認できます。pg_stat_plansと pg_stat_statementsを組み合わせることでSQL情報を確認することもできます。 postgres=# SELECT * FROM pg_stat_plans_activity; pid | 385191 plan_id | -8506531700202359186 plan | Aggregate | -> Seq Scan on test001 | Filter: (id001 = 0) postgres=# SELECT psp.*,pss.query postgres-# FROM pg_stat_plans psp, pg_stat_statements pss postgres-# WHERE psp.queryid = pss.queryid AND postgres-# psp.planid = ‘-8506531700202359186’; queryid | 8406517169550618306 planid | -8506531700202359186 calls | 1 total_exec_time | 1049.887692 plan | Aggregate + | -> Seq Scan on test001 + | Filter: (id001 = 0) query | select count(*) from test001 where id001 = $1 ## pid 385191 で以下SQLを実行中に、 ## 別セッションで「pg_stat_plans_activity」 ## を参照することで実行計画・plan_idを確認 select count(*) from test001 where id001 = 0 ## 約1秒で完了するデータ量としています ## 実行完了後、pg_stat_plansと ## pg_stat_statementsに蓄積された ## 情報を組み合わせて参照することで ## SQL統計情報の確認が可能です
19. © NTT DOCOMO SOLUTIONS, Inc. 2026 18 これから 将来的には、query_idと同じくplan_idも本体側で ジャンブリング・生成できるよう議論が進められています。 https://wiki.postgresql.org/wiki/Plan_ID_Jumbling plan_idの生成が本体機能に組み込まれた場合は、 pg_stat_plans(v2.0)だけでなく、様々な拡張機能で plan_idをキーとして、実行計画に関する情報収集・分析が可能となるでしょう。 商用環境で、plan_idをふくむ実行計画を記録・解析できれば、 SQLの性能遅延が発生した際の解析に非常に便利です。 「特定の条件でのみ、大幅に遅延する実行計画が選択されてしまった」 といったケースも追跡することができます。
20. © NTT DOCOMO SOLUTIONS, Inc. 2026 19 これから pg_stat_plans(v2.0)もまだ実験段階の拡張機能であり、 商用環境での利用は推奨されていません。 PostgreSQL 18時点では、拡張機能auto_explainを利用して、 一定以上時間のかかったSQLの実行計画をログに記録するのが便利でしょう。 引き続きplan_idの動向に注視しつつ、 本体機能・拡張機能とも開発に貢献していきたいと思っています。
21. © NTT DOCOMO SOLUTIONS, Inc. 2026 20 その他、記載されている会社名、製品名、サービス名は、各社の商標または登録商標です。