★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

F.32. pg_stat_statements

pg_stat_statementsモジュールは、サーバで実行されたすべてのSQL文のプラン生成時と実行時の統計情報を記録する手段を提供します。

このモジュールは追加の共有メモリを必要とするため、postgresql.confshared_preload_librariespg_stat_statementsを追加してモジュールをロードしなければなりません。 このことは、このモジュールを追加もしくは削除するには、サーバを再起動する必要があるということを意味しています。 さらに、モジュールをアクティブにするには、問い合わせIDの計算を有効にする必要があります。これはcompute_query_idパラメータがautoonに設定されているか、問い合わせIDを計算するサードパーティのモジュールがロードされている場合に自動的に実行されます。

pg_stat_statementsはアクティブになると、サーバのデータベース全体に渡って統計情報を記録します。 この統計情報にアクセスしたり操作したりするために、このモジュールはビューpg_stat_statementspg_stat_statements_infoとユーティリティ関数pg_stat_statements_resetpg_stat_statementsを提供します。 これらは大域的に利用可能ではなく、CREATE EXTENSION pg_stat_statementsで特定のデータベースで可能になります。

F.32.1. pg_stat_statements ビュー

このモジュールによって収集された統計情報は、pg_stat_statementsというビューを通して利用することができます。 このビューは、1行に対して、それぞれ個々のデータベースID、ユーザID、問い合わせID、および最上位レベルの文かどうかの組み合わせを含んでいます(モジュールが記録できるSQL文の最大数まで)。 ビューの列は、表 F.20に示す通りです。

表F.20 pg_stat_statementsの列

列 型

説明

userid oid (参照先 pg_authid.oid

SQL文を実行したユーザのOID

dbid oid (参照先 pg_database.oid

SQL文が実行されたデータベースのOID

toplevel bool

問い合わせが最上位レベルのSQL文として実行された時は真 (pg_stat_statements.tracktopに設定されている場合は常に真)

queryid bigint

同一の正規化された問い合わせを識別するためのハッシュコード

query text

代表的なSQL文の文字列

plans bigint

SQL文がプラン生成された回数 (pg_stat_statements.track_planningが有効な場合。無効であればゼロ)

total_plan_time double precision

SQL文のプラン生成に費やした総時間(ミリ秒単位) (pg_stat_statements.track_planningが有効な場合。無効であればゼロ)

min_plan_time double precision

SQL文のプラン生成に費やした最小時間(ミリ秒単位) (pg_stat_statements.track_planningが有効な場合。無効であればゼロ)

max_plan_time double precision

SQL文のプラン生成に費やした最大時間(ミリ秒単位) (pg_stat_statements.track_planningが有効な場合。無効であればゼロ)

mean_plan_time double precision

SQL文のプラン生成に費やした平均時間(ミリ秒単位) (pg_stat_statements.track_planningが有効な場合。無効であればゼロ)

stddev_plan_time double precision

SQL文のプラン生成に費やした時間の母標準偏差(ミリ秒単位) (pg_stat_statements.track_planningが有効な場合。無効であればゼロ)

calls bigint

SQL文が実行された回数

total_exec_time double precision

SQL文の実行に費やした総時間(ミリ秒単位)

min_exec_time double precision

SQL文の実行に費やした最小時間(ミリ秒単位)

max_exec_time double precision

SQL文の実行に費やした最大時間(ミリ秒単位)

mean_exec_time double precision

SQL文の実行に費やした平均時間(ミリ秒単位)

stddev_exec_time double precision

SQL文の実行に費やした時間の母標準偏差(ミリ秒単位)

rows bigint

SQL文によって取得された、あるいは影響を受けた行の総数

shared_blks_hit bigint

SQL文によってヒットした共有ブロックキャッシュの総数

shared_blks_read bigint

SQL文によって読み込まれた共有ブロックの総数

shared_blks_dirtied bigint

SQL文によってダーティ状態となった共有ブロックの総数

shared_blks_written bigint

SQL文によって書き込まれた共有ブロックの総数

local_blks_hit bigint

SQL文によってヒットしたローカルブロックキャッシュの総数

local_blks_read bigint

SQL文によって読み込まれたローカルブロックの総数

local_blks_dirtied bigint

SQL文によってダーティ状態となったローカルブロックの総数

local_blks_written bigint

SQL文によって書き込まれたローカルブロックの総数

temp_blks_read bigint

SQL文によって読み込まれた一時ブロックの総数

temp_blks_written bigint

SQL文によって書き込まれた一時ブロックの総数

blk_read_time double precision

SQL文がデータファイルブロックの読み取りに費やした総時間(ミリ秒単位) (track_io_timingが有効な場合。無効であればゼロ)

blk_write_time double precision

SQL文がデータファイルブロックの書き出しに費やした総時間(ミリ秒単位) (track_io_timingが有効な場合。無効であればゼロ)

temp_blk_read_time double precision

SQL文が一時ファイルブロックの読み取りに費やした総時間(ミリ秒単位) (track_io_timingが有効な場合。無効であればゼロ)

temp_blk_write_time double precision

SQL文が一時ファイルブロックの書き出しに費やした総時間(ミリ秒単位) (track_io_timingが有効な場合。無効であればゼロ)

wal_records bigint

SQL文により生成されたWALレコードの総数

wal_fpi bigint

SQL文により生成されたWALフルページイメージの総数

wal_bytes numeric

SQL文により生成されたバイト単位のWAL総量

jit_functions bigint

SQL文がJITコンパイルされた関数の総数

jit_generation_time double precision

SQL文のJITコードの生成に費やした総時間(ミリ秒単位)

jit_inlining_count bigint

関数がインライン化された回数

jit_inlining_time double precision

SQL文が関数のインライン化に費やした総時間(ミリ秒単位)

jit_optimization_count bigint

SQL文が最適化された回数

jit_optimization_time double precision

SQL文の最適化に費やした総時間(ミリ秒単位)

jit_emission_count bigint

コードが出力された回数

jit_emission_time double precision

SQL文のコードを出力するのに費やした総時間(ミリ秒単位)


セキュリティ上の理由から、スーパーユーザとpg_read_all_statsロールの権限を持つメンバだけが、他のユーザによって実行されたSQLテキストや問い合わせのqueryidを見ることができます。 ただし、ユーザのデータベースにビューがインストールされている場合、統計情報については他のユーザから見ることができます。

計画作成が可能な問い合わせ(つまりSELECTINSERTUPDATEDELETEMERGE)は、内部のハッシュ計算に従った、同一の問い合わせ構造を持つ限り、1つのpg_stat_statements項目に組み合わせられます。 典型的には、2つの問い合わせは、問い合わせの中に現れるリテラル定数の値以外、意味的に等価である場合、この目的では同一とみなされます。 しかし、ユーティリティコマンド(つまりこの他のコマンドすべて)は問い合わせ文字列のテキストを基に厳密に比較されます。

注記

定数置換とqueryidに関する以下の詳細は、compute_query_idが有効な場合のみ適用されます。 queryidの計算に外部モジュールを使用する場合、詳細はその外部モジュールのドキュメントを参照する必要があります。

他の問い合わせと合致させるために定数値が無視された場合、pg_stat_statementsの表示の中で定数は$1のようなパラメータ記号に置換されます。 問い合わせの残りのテキストは、pg_stat_statements項目に関連付いた特定のqueryidハッシュ値を持つ、1つ目の問い合わせのテキストです。

一部の状況では、見た目上異なるテキストを持つ問い合わせが1つのpg_stat_statements項目にまとめられることがあります。 通常これは意味的に等しい問い合わせでのみ発生しますが、関連がない問い合わせが1つの項目にまとめられるハッシュ競合の可能性がわずかながら存在します。 (しかしこれは別のユーザまたは別のデータベースに属する問い合わせでは発生することはあり得ません。)

queryidハッシュ値は問い合わせの解析後の表現に対して計算されますので、search_pathの設定が異なる等の要因の結果として異なる意味を持つ場合、同じテキストを持つ問い合わせが別の項目として現れるという、反対もまたあり得ます。

pg_stat_statementsの消費者は、問い合わせテキストよりもより安定で信頼できる各項目への識別子として(おそらくdbiduseridと組み合わせて)queryidを使いたいかもしれません。 しかし、queryidハッシュ値の安定性には限定された保証しかないのを理解することは重要です。 識別子は解析後の木から得られますので、その値は、とりわけ、この表現に現れる内部オブジェクト識別子の関数です。 これは少々直観に反する結果です。 例えば、pg_stat_statementsは見たところは同一の問い合わせを、それらが2つの問い合わせの実行の間に削除され再作成されたテーブルを参照しているのであれば、別個のものとみなします。 ハッシュ処理はプラットフォームのマシンアーキテクチャやその他の面の違いにも敏感です。 その上、PostgreSQLのメジャーバージョンをまたがってqueryidが安定であるとみなすのは安全ではありません。

経験上、queryid値は、基礎となるサーバのバージョンとカタログメタデータの詳細が全く同じである限り、安定していて比較可能とみなすことができます。 物理WAL再生に基づくレプリケーションに参加する2つのサーバでは、同じ問い合わせに対して同一のqueryid値を持つことが期待できます。 しかし、論理レプリケーションの仕組みは、レプリカが対応する詳細すべてで同一であることを約束しません。そのため、論理レプリカの集まりで増えるコストを識別するのにqueryidは有用ではありません。 疑わしければ、直接テストすることを薦めます。

代表的な問い合わせテキストの定数を置き換えるのに使われたパラメータ記号は、元の問い合わせテキストの最も大きな$nパラメータの次の数字から、もしそれがなければ$1から始まります。 ある場合には、この番号付けに影響する隠れたパラメータ記号があるかもしれないことに言及しておく価値はあります。 例えば、PL/pgSQLは関数の局所変数の値を問い合わせに挿入するために隠れたパラメータ記号を使います。そのため、SELECT i + 1 INTO jのようなPL/pgSQL文はSELECT i + $2のような代表的なテキストになります。

代表的な問い合わせテキストは外部ディスクファイルに保持され、共有メモリを消費しません。 そのため、非常に長い問い合わせテキストであっても保存に成功します。 しかし、数多くの長い問い合わせテキストが蓄積されると、外部ファイルは手に負えないくらい大きくなるかもしれません。 もしそのようなことが起きれば、回復手法として、pg_stat_statementsは問い合わせテキストを破棄することを選ぶでしょう。その結果、各queryidに関連する統計は保存されるものの、pg_stat_statementsビュー内に存在するエントリはすべてqueryフィールドがヌルになります。 もしこのようなことが起きたら、再発防止のためpg_stat_statements.maxを減らすことを検討してください。

プラン生成時と実行時の統計情報はそれぞれの終了フェーズで更新され、操作に成功した場合にのみ更新されるため、planscallsは常に一致するとは限りません。 例えば、SQL文のプラン生成に成功しても実行フェーズの間に失敗した場合、そのプラン生成時の統計情報のみが更新されます。 キャッシュされたプランが使用されプラン生成がスキップされた場合、実行時の統計情報のみが更新されます。

F.32.2. pg_stat_statements_info ビュー

pg_stat_statementsモジュール自体の統計は記録され、pg_stat_statements_infoというビューを通して利用することができます。 このビューは、1行のみが含まれます。 ビューの列は、表 F.21に示す通りです。

表F.21 pg_stat_statements_infoの列

列 型

説明

dealloc bigint

pg_stat_statements.maxよりも多くの異なるSQL文が検出されたため、実行回数が最も少ないSQL文のpg_stat_statementsエントリが割り当て解除された合計回数

stats_reset timestamp with time zone

pg_stat_statementsビューのすべての統計情報が最後にリセットされた時刻


F.32.3. 関数

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

pg_stat_statements_resetは指定されたuseriddbidqueryidに対応するpg_stat_statementsによってこれまでに収集したすべての統計情報を削除します。 いずれかのパラメータを指定しないのであれば、デフォルト値0(無効)を使ってください。他のパラメータに一致する統計情報がリセットされます。 どのパラメータも指定しない、または、すべての指定されたパラメータが0(無効)ならば、すべての統計情報を削除します。 pg_stat_statementsビューのすべての統計情報が破棄された場合、pg_stat_statements_infoビューの統計情報もリセットされます。 デフォルトでは、この関数はスーパーユーザのみ実行できます。 GRANTを使ってアクセス権を他のユーザに付与できます。

pg_stat_statements(showtext boolean) returns setof record

pg_stat_statementsビューは同じくpg_stat_statementsという名前の関数の項で定義されています。 クライアントがpg_stat_statements関数を直接呼び出し、showtext := falseと指定することで問い合わせテキストを省略することが可能です(すなわち、ビューのquery列に対応するOUT引数はNULLを返します)。 この機能は不定長の問い合わせテキストを繰り返し取得するオーバーヘッドを避けたいと考える外部のツールをサポートすることを意図したものです。 そのようなツールは代わりに、それがpg_stat_statements自身が行なっていることのすべてですので、各項目で最初に観察された問い合わせテキストをキャッシュし、必要とされる問い合わせテキストのみを取得できます。 サーバは問い合わせテキストをファイルに格納しますので、この方法はpg_stat_statementsデータの繰り返しの検査に対する物理I/Oを減らすでしょう。

F.32.4. 設定パラメータ

pg_stat_statements.max (integer)

pg_stat_statements.maxは、このモジュールによって記録されるSQL文の最大数(すなわち、pg_stat_statementsビューにおける行の最大数)です。 これを超えて異なるSQL文を検出した場合は、最も実行回数の低いSQL文の情報が捨てられます。 このような情報が破棄された回数は、pg_stat_statements_infoビューで確認できます。 デフォルトは5000です。 このパラメータはサーバの起動時にのみ指定できます。

pg_stat_statements.track (enum)

pg_stat_statements.trackは、どのSQL文をモジュールによって計測するかを制御します。 topを指定した場合は(直接クライアントによって発行された)最上層のSQL文を記録します。 allは(関数の中から呼び出された文などの)入れ子になった文も記録します。 noneは文に関する統計情報収集を無効にします。 デフォルトはtopです。 この設定はスーパーユーザだけが変更できます。

pg_stat_statements.track_utility (boolean)

pg_stat_statements.track_utilityは、このモジュールがユーティリティコマンドを記録するかどうかを指定します。 ユーティリティコマンドとは、 SELECTINSERTUPDATEDELETE、およびMERGE以外のすべてです。 デフォルトはonです。 この設定はスーパーユーザのみが変更できます。

pg_stat_statements.track_planning (boolean)

pg_stat_statements.track_planningは、このモジュールがプラン生成の操作と時間を記録するかどうかを指定します。 このパラメータを有効にすると、特に同一の問い合わせ構造を持つSQL文が、少数のpg_stat_statementsエントリ更新のために競合する多数の同時接続によって実行される場合にパフォーマンスが著しく低下する可能性があります。 デフォルトはoffです。 この設定はスーパーユーザのみが変更できます。

pg_stat_statements.save (boolean)

pg_stat_statements.saveは、サーバを終了させる際に文の統計情報を保存するかどうかを指定します。 offの場合、統計情報は終了時に保存されず、サーバ開始時に再読み込みもされません。 デフォルト値はonです。 このパラメータはpostgresql.confファイル、またはサーバコマンドラインでのみ設定できます。

このモジュールは、pg_stat_statements.maxに比例する追加の共有メモリを必要とします。 pg_stat_statements.tracknoneが設定されていても、モジュールがロードされている限り常にこのメモリが消費されることに注意してください。

これらのパラメータはpostgresql.confの中で設定しなければなりません。 典型的な使用方法は以下のようになります。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all

F.32.5. サンプル出力

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench
$ pgbench -c10 -t300 bench

bench=# \x
bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
calls           | 3000
total_exec_time | 25565.855387
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------​------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 4 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 5 ]---+--------------------------------------------------​------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000


bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
            WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------​------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 4 ]---+--------------------------------------------------​------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000
-[ RECORD 5 ]---+--------------------------------------------------​------------------
query           | vacuum analyze pgbench_accounts
calls           | 1
total_exec_time | 136.448116
rows            | 0
hit_percent     | 99.9201915403032721

bench=# SELECT pg_stat_statements_reset(0,0,0);

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------​---------------------------
query           | SELECT pg_stat_statements_reset(0,0,0)
calls           | 1
total_exec_time | 0.189497
rows            | 1
hit_percent     |
-[ RECORD 2 ]---+--------------------------------------------------​---------------------------
query           | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit /          +
                |                nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
                |           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
calls           | 0
total_exec_time | 0
rows            | 0
hit_percent     |

F.32.6. 作者

板垣 貴裕 。 Peter Geoghegan により問い合わせの正規化が追加されました。