最もコストの高いクエリを特定(pg_stat_statements: 合計時間と平均時間)

コマンド

-- By total time: queries consuming the most server time
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_total,
       rows / NULLIF(calls, 0) AS rows_per_call,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- By mean time per call: individually slow queries
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2) AS max_ms,
       round(mean_plan_time::numeric, 2) AS mean_plan_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE calls >= 50
ORDER BY mean_exec_time DESC
LIMIT 20;

概要

現在のデータベースで最も多くのサーバー時間を消費している正規化されたクエリを、合計に対する割合とともに一覧表示する。2つ目のクエリは呼出しあたりで最も遅いものを一覧表示する。

使用する場面

CPU/IO負荷が上昇したとき、またはユーザーが「データベースが遅い」と報告したときの最初の確認先として。チューニングをどこから始めるかの判断に。

注意

pg_stat_statementsは、shared_preload_librariesに追加してサーバーを再起動し、データベースでCREATE EXTENSIONを実行するまで何も収集しない。値は最後のリセット以降の累積値。mean_msに比べてstddev_msが高い場合、呼出しごとに計画またはデータ分布が変動していることを示す。mean_plan_timeはpg_stat_statements.track_planning = on(デフォルトoff、有効化するとオーバーヘッドが加わる)の場合のみ設定される。他のユーザーのクエリテキストを見るには、スーパーユーザーまたはpg_read_all_statsロールが必要。