本文へスキップ

PG SQL統計

情報

PG SQL統計は今後SQL統計（DB）に名称が変更される予定です。

PostgreSQLのpg_stat_statementsビューで提供するDB自体のSQL統計です。SQL別の実行回数、実行時間、処理Row数、ブロックI/Oなどのデルタ値をDBに直接照会することなく画面で確認できます。

  • エージェントが1時間間隔でデルタ値を計算して収集します。

  • エージェントオプションstatements_order_by基準（デフォルト値total_time）でstatements_row_limitまで収集します。（デフォルト値：5,000件）

  • DBXエージェント1.6.10バージョン以上が必要です。

ノート

設定完了後、約1時間後にデータ収集が開始されます。

事前設定​

画面にデータを表示するには、DB設定とエージェント設定を必ず設定する必要があります。

DB設定​

  1. pg_stat_statements拡張のインストール

    pg_stat_statementsはPostgreSQLのcontribモジュールに含まれています。インストールされていない場合はパッケージをインストールします。

    # Red Hat / CentOS
    yum install postgresql-contrib

    # Debian / Ubuntu
    apt-get install postgresql-contrib

  2. postgresql.confの設定

    shared_preload_librariesにpg_stat_statementsを追加します。変更後、PostgreSQLの再起動が必要です。

    shared_preload_libraries = 'pg_stat_statements'

  3. 拡張モジュールの作成

    モニタリング対象データベースで拡張を作成します。

    CREATE EXTENSION pg_stat_statements;

  4. インストール確認

    SELECT * FROM pg_stat_statements LIMIT 1;

エージェント設定​

PostgreSQLではstatementsオプションのデフォルト値がtrueのため、別途設定なしでSQL統計がデフォルト収集されます。収集を無効にするにはstatements=falseに設定してください。

設定項目タイプデフォルト値説明
statementsbooleantrueSQL統計の収集有無を設定します。
詳細オプション
設定項目タイプデフォルト値説明
statements_intervalint1SQL統計収集間隔（時間単位）。statements_interval=1に指定した場合、1時間周期で収集します。
statements_interval_minint0SQL統計収集間隔（分単位）。statements_interval_min=10に指定した場合、10分周期で収集します。このオプションを設定するとstatements_intervalより優先されます。
statements_min_rowint10000SQL統計データを収集する基準。下記の収集基準を参照してください。
statements_row_limitint5000収集最大件数。statements_order_by基準の上位件数まで収集します。
statements_order_byString"total_time"SQL統計情報を収集する際のソート基準を指定します。（例：statements_order_by=total_time,temp_blks_read）
statements_schemaString""pg_stat_statements拡張がwhatap.confのdbオプションに記入したDBにインストールされていない場合、拡張がインストールされているDB名を指定します。

収集基準（statements_min_row）

pg_stat_statementsのrows（該当クエリで出力または影響を受ける総Row数）がstatements_min_rowを超える場合に、該当SQLデータを収集します。

ノート

DBワークロードによってSQL収集量が多すぎるまたは少なすぎる場合、statements_min_rowとstatements_row_limitの値を調整して収集量を制御できます。

SQL統計（DB）を分析する​

基本オプション​

上部フィルター領域で照会期間、対象インスタンス、フィルター条件などを設定できます。

  • 時間: 照会する日時を選択

  • インスタンス: 照会対象DBインスタンスを選択

  • フィルター: 条件別データフィルタリング（複数条件はANDロジックを適用）

  • ソート順: データをソートする基準カラムを選択（例：rows、total_time、callsなど）

  • 照会件数: テーブル表示件数を設定

  • 結果内検索: 照会された結果内でクエリキーワード検索

  • カラムアイコン（カラム選択）: 表示するカラムを追加/削除、ドラッグで順序変更可能

  • ダウンロードアイコン（ダウンロード）: CSV形式でエクスポート

カラムガイド​

基本情報​

カラム説明
query正規化されたSQL文（リテラル値が$1、$2などに置換される）
queryid同じ正規化クエリを識別するハッシュコード
usenameクエリを実行したユーザー名
dbクエリが実行されたデータベース名
instanceSQLが実行されたDBインスタンス名

実行および時間​

カラム説明
callsSQL実行回数
total_time総実行時間（単位：ミリ秒）
rowsクエリで出力または影響を受けた総Row数

ブロックI/O（共有バッファ）​

カラム説明
shared_blks_hit共有バッファキャッシュから読み取ったブロック数（キャッシュヒット）
shared_blks_readディスクから読み取った共有ブロック数（キャッシュミス）
shared_blks_dirtied変更された共有ブロック数
shared_blks_writtenディスクに書き込んだ共有ブロック数

ブロックI/O（ローカルバッファ）​

カラム説明
local_blks_hitローカルバッファキャッシュから読み取ったブロック数
local_blks_readディスクから読み取ったローカルブロック数
local_blks_dirtied変更されたローカルブロック数
local_blks_writtenディスクに書き込んだローカルブロック数

一時ブロック​

カラム説明
temp_blks_read読み取った一時ブロック数
temp_blks_written書き込んだ一時ブロック数

I/O時間​

カラム説明
blk_read_timeブロック読み取りに費やした時間（単位：ミリ秒）。track_io_timingの有効化が必要。
blk_write_timeブロック書き込みに費やした時間（単位：ミリ秒）。track_io_timingの有効化が必要。

テーブルFetch​

カラム説明
table fetch by rowidインデックスを通じてテーブルRowを直接照会した回数
table fetch continued rowRowが複数のブロックにまたがって保存されている場合に追加ブロックを読み取った回数

データ解釈ガイド​

チューニング対象SQLの識別​

  • total_timeが高くcallsも高いSQL

    頻繁に実行されながら遅い → 最優先チューニング対象

  • rowsが高いSQL

    大量のRowを処理 → 不必要な照会でないか確認

  • total_time / calls（平均実行時間）が高いSQL

    個別実行が遅い → SQLチューニング、実行計画の確認

キャッシュ効率の確認​

  • キャッシュヒット率 = shared_blks_hit / (shared_blks_hit + shared_blks_read)

    • 90%以下の場合、shared_buffersの増設または非効率なクエリの確認

  • shared_blks_readが高いSQL: ディスクI/Oを多く引き起こす → インデックス確認、shared_buffers増設を検討

  • shared_blks_dirtiedが高いSQL: データを多く変更 → チェックポイント負荷を引き起こす可能性あり

一時ブロックの確認​

  • temp_blks_read、temp_blks_writtenが高い場合、ソートやハッシュジョイン時にメモリが不足してディスクを使用中

  • work_memの増設を検討

I/O時間の確認​

  • blk_read_timeが高いSQL: ディスク読み取り待ちが大きい → ストレージパフォーマンスまたはキャッシュ効率を確認

  • blk_read_timeの確認のためにpostgresql.confにtrack_io_timing = onの設定が必要

実践活用シナリオ​

シナリオ1. 遅いSQLを探す​

  1. total_time基準で降順ソート

  2. 上位SQLのcallsを確認 → 頻繁に実行されながら遅いSQLが最優先チューニング対象

シナリオ2. キャッシュ効率が低いSQLを探す​

  1. shared_blks_readが高いSQLを確認

  2. shared_blks_hitと比較してキャッシュヒット率を計算

  3. キャッシュヒット率が低い場合、該当SQLのPlan（実行計画）を確認してインデックス追加の有無を決定

シナリオ3. ディスクI/O過多のSQLを探す​

  1. blk_read_timeが高いSQLを確認（track_io_timing = onが必要）

  2. → ディスク読み取りに時間を多く消費するSQL → インデックス確認、不必要なシーケンシャルスキャンを除去

追加機能​

SQL詳細表示​

SQLカラムをクリックすると詳細情報ウィンドウが開きます。SQL詳細ウィンドウの説明は以下を参照してください。

DB参考情報​

pg_stat_statements​

この画面のデータはDBのpg_stat_statementsビューから取得するため、DB設定変更や統計初期化が収集データに直接影響します。

pg_stat_statements.max​

このパラメータは、異なるSQLパターン（queryid）を最大何個まで保存するかを決定します。デフォルト値は5000です。

-- 例：以下の2つのSQLは同じqueryidに正規化されます。
SELECT * FROM users WHERE id = 10;
SELECT * FROM users WHERE id = 20;
→ SELECT * FROM users WHERE id = $1

保存可能なSQLパターン数がlimitを超えると、最も実行回数が少ないSQLから削除されます。この場合、一部のSQLの統計が欠落する可能性があります。

-- 現在のlimitを確認
SHOW pg_stat_statements.max;

-- 現在保存されているSQLパターン数を確認
SELECT COUNT(*) FROM pg_stat_statements;

SQLパターン数がlimitに近づいたらpg_stat_statements.maxの値を増やしてください。変更後、PostgreSQLの再起動が必要です。

pg_stat_statements_reset()初期化​

pg_stat_statements_reset()関数で統計を初期化すると、既存データがすべて削除され、新しいデータが記録されます。エージェントが収集するデルタ値も初期化されるため注意してください。

SELECT pg_stat_statements_reset();
最終修正：