pg_stat_statements による取得とセクション定義

# shared_preload_libraries に追加（再起動が必要）
shared_preload_libraries = 'pg_stat_statements'

# オプション設定
pg_stat_statements.max = 10000       # 追跡する SQL の最大数（デフォルト：5000）
pg_stat_statements.track = all       # all=全クエリ, top=トップレベルのみ, none=無効
pg_stat_statements.track_utility = on  # COPY・VACUUM などのユーティリティも追跡
pg_stat_statements.save = on          # 再起動後も統計を保持

-- 拡張をインストール（superuser 権限が必要）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- インストール確認
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- バージョン確認
SELECT pg_stat_statements_info();

SELECT
    ROUND(total_exec_time::numeric, 2)                                AS total_exec_ms,
    calls,
    ROUND((total_exec_time / calls)::numeric, 2)                      AS avg_exec_ms,
    ROUND(min_exec_time::numeric, 2)                                  AS min_exec_ms,
    ROUND(max_exec_time::numeric, 2)                                  AS max_exec_ms,
    ROUND(stddev_exec_time::numeric, 2)                               AS stddev_exec_ms,
    rows,
    ROUND((100 * total_exec_time / SUM(total_exec_time) OVER())::numeric, 2) AS pct_total,
    left(query, 100)                                                  AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
    calls,
    ROUND(total_exec_time::numeric, 2)               AS total_exec_ms,
    ROUND((total_exec_time / calls)::numeric, 2)     AS avg_exec_ms,
    rows,
    ROUND((rows::numeric / calls), 2)                AS avg_rows,
    left(query, 100)                                 AS query_preview
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

SELECT
    calls,
    shared_blks_hit + shared_blks_read              AS total_blks,
    shared_blks_hit,
    shared_blks_read,
    ROUND(shared_blks_hit::numeric /
        NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 2) AS hit_ratio,
    shared_blks_dirtied,
    shared_blks_written,
    local_blks_hit + local_blks_read                AS local_blks_total,
    temp_blks_read,
    temp_blks_written,
    left(query, 100)                                AS query_preview
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

-- スナップショット保存テーブル
CREATE TABLE pgss_snapshot AS
SELECT
    now()         AS snap_time,
    queryid,
    query,
    calls,
    total_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
WHERE 1 = 0;  -- 構造だけ作成

-- スナップショット取得（計測開始時）
INSERT INTO pgss_snapshot
SELECT now(), queryid, query, calls, total_exec_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements;

-- 計測終了時にもう一度 INSERT してから差分クエリ
WITH
  s1 AS (SELECT * FROM pgss_snapshot WHERE snap_time = (SELECT MIN(snap_time) FROM pgss_snapshot)),
  s2 AS (SELECT * FROM pgss_snapshot WHERE snap_time = (SELECT MAX(snap_time) FROM pgss_snapshot))
SELECT
    s2.query,
    s2.calls - s1.calls                       AS calls_diff,
    ROUND((s2.total_exec_time - s1.total_exec_time)::numeric, 2) AS exec_ms_diff,
    ROUND(((s2.total_exec_time - s1.total_exec_time) /
        NULLIF(s2.calls - s1.calls, 0))::numeric, 2)             AS avg_exec_ms,
    s2.rows - s1.rows                         AS rows_diff,
    s2.shared_blks_read - s1.shared_blks_read AS phys_reads_diff
FROM s1 JOIN s2 USING (queryid)
WHERE s2.calls > s1.calls
ORDER BY exec_ms_diff DESC
LIMIT 20;

-- 全統計をリセット（計測開始点のリセット）
SELECT pg_stat_statements_reset();

-- 特定のクエリのみリセット（PostgreSQL 12+）
SELECT pg_stat_statements_reset(userid, dbid, queryid)
FROM pg_stat_statements
WHERE query LIKE '%target_table%';