pg_stat_* ビューによる取得とセクション定義

-- 待機イベント別のセッション数（Oracle AWR Top 5 Timed Events 相当）
SELECT
    wait_event_type,
    wait_event,
    state,
    COUNT(*)            AS session_count
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY wait_event_type, wait_event, state
ORDER BY session_count DESC;

-- アクティブセッションの詳細一覧
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    now() - query_start   AS query_duration,
    left(query, 80)        AS query_preview
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    ROUND(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted,
    conflicts,
    temp_files,
    temp_bytes,
    deadlocks,
    stats_reset
FROM pg_stat_database
WHERE datname = current_database();

SELECT
    schemaname,
    relname                                     AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins                                   AS inserts,
    n_tup_upd                                   AS updates,
    n_tup_del                                   AS deletes,
    n_tup_hot_upd                               AS hot_updates,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read + idx_tup_fetch DESC
LIMIT 10;

-- インデックス使用統計
SELECT
    schemaname,
    relname       AS table_name,
    indexrelname  AS index_name,
    idx_scan      AS scans,
    idx_tup_read  AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

-- 未使用インデックスの検出（主キー・一意制約除く）
SELECT
    s.schemaname,
    s.relname         AS table_name,
    s.indexrelname    AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
    s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisprimary
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- テーブル I/O 統計（キャッシュヒット率）
SELECT
    schemaname,
    relname                                                         AS table_name,
    heap_blks_read,
    heap_blks_hit,
    ROUND(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2) AS heap_hit_ratio,
    idx_blks_read,
    idx_blks_hit,
    ROUND(idx_blks_hit::numeric / NULLIF(idx_blks_hit + idx_blks_read, 0) * 100, 2) AS idx_hit_ratio,
    toast_blks_read,
    toast_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read + idx_blks_read DESC
LIMIT 20;

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint,
    buffers_clean,
    maxwritten_clean,
    buffers_backend,
    buffers_backend_fsync,
    buffers_alloc,
    stats_reset
FROM pg_stat_bgwriter;

-- ロック待ちとブロッキングセッションの特定
SELECT
    blocked.pid                              AS blocked_pid,
    blocked.usename                          AS blocked_user,
    blocked.query                            AS blocked_query,
    blocking.pid                             AS blocking_pid,
    blocking.usename                         AS blocking_user,
    blocking.query                           AS blocking_query,
    now() - blocked.query_start              AS wait_duration
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY wait_duration DESC;

-- ロック種別の集計
SELECT
    locktype,
    relation::regclass   AS table_name,
    mode,
    granted,
    COUNT(*)             AS count
FROM pg_locks
WHERE relation IS NOT NULL
GROUP BY locktype, relation, mode, granted
ORDER BY count DESC;

-- 実行中の VACUUM の進捗確認
SELECT
    p.pid,
    p.relid::regclass       AS table_name,
    p.phase,
    p.heap_blks_total,
    p.heap_blks_scanned,
    ROUND(p.heap_blks_scanned::numeric / NULLIF(p.heap_blks_total, 0) * 100, 1) AS progress_pct,
    p.num_dead_item_ids
FROM pg_stat_progress_vacuum p;

-- バキュームが必要なテーブルの優先度確認
SELECT
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_ratio DESC
LIMIT 20;

-- データベース全体の統計をリセット（計測開始点のリセット）
SELECT pg_stat_reset();

-- バックグラウンドライタ統計のみリセット
SELECT pg_stat_reset_shared('bgwriter');

-- pg_stat_statements のリセット（PART 03 参照）
SELECT pg_stat_statements_reset();

-- リセット日時の確認
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();
SELECT stats_reset FROM pg_stat_bgwriter;