系統目錄與統計視圖：PostgreSQL 的自我描述資料庫 | PostgreSQL

-- \d tablename 等同於查詢：
SELECT
    a.attname AS "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS "Type",
    CASE WHEN a.attnotnull THEN 'not null' ELSE '' END AS "Nullable"
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
WHERE c.relname = 'your_table'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

-- 查詢特定資料表的基本資訊（pg_class）
SELECT
    c.oid,
    c.relname        AS table_name,
    c.relkind,
    c.relpages,
    c.reltuples::bigint AS estimated_rows,
    n.nspname        AS schema_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'users'
  AND n.nspname = 'public';

-- 查詢資料表的欄位詳細資訊（pg_attribute）
SELECT
    attnum,
    attname               AS column_name,
    atttypid::regtype     AS data_type,
    attnotnull            AS not_null,
    atthasdef             AS has_default
FROM pg_attribute
WHERE attrelid = 'public.users'::regclass
  AND attnum > 0          -- 排除系統欄位（attnum <= 0）
  AND NOT attisdropped    -- 排除已刪除欄位
ORDER BY attnum;

-- 查詢資料表的所有索引
SELECT
    i.relname                AS index_name,
    ix.indisprimary          AS is_primary,
    ix.indisunique           AS is_unique,
    ix.indisvalid            AS is_valid,
    array_to_string(
        array_agg(a.attname ORDER BY k.ordinality),
        ', '
    )                        AS columns,
    am.amname                AS index_method
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_am am ON am.oid = i.relam
JOIN LATERAL unnest(ix.indkey) WITH ORDINALITY AS k(attnum, ordinality)
    ON true
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum
WHERE t.relname = 'users'
  AND t.relnamespace = 'public'::regnamespace
GROUP BY i.relname, ix.indisprimary, ix.indisunique, ix.indisvalid, am.amname
ORDER BY i.relname;

-- 使用 pg_stats 視圖（比直接查 pg_statistic 更易讀）
SELECT
    attname            AS column_name,
    null_frac          AS null_fraction,
    avg_width          AS avg_byte_width,
    n_distinct,        -- 正數=確切值，負數=佔總行數比例
    most_common_vals   AS mcv,
    most_common_freqs  AS mcf,
    correlation        -- 欄位值與實體位置的相關性（-1到1）
FROM pg_stats
WHERE tablename = 'users'
  AND schemaname = 'public'
  AND attname = 'email';

-- 查詢當前所有活躍的後端程序
SELECT
    pid,
    usename          AS username,
    application_name,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS duration,
    left(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()  -- 排除自己
ORDER BY query_start;

-- 找出執行超過 5 分鐘的查詢
SELECT
    pid,
    usename,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;

-- 資料表健康監控：dead tuples、vacuum 時間、DML 統計
SELECT
    schemaname,
    relname                  AS table_name,
    n_live_tup               AS live_tuples,
    n_dead_tup               AS dead_tuples,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze,
    seq_scan,
    idx_scan,
    n_tup_hot_upd            -- HOT UPDATE 比例越高越好
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 找出從未被掃描的索引（idx_scan = 0）
SELECT
    schemaname,
    relname        AS table_name,
    indexrelname   AS index_name,
    idx_scan       AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 資料庫整體的 Buffer Cache 命中率
SELECT
    datname,
    blks_read,
    blks_hit,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
    temp_files,
    deadlocks
FROM pg_stat_database
WHERE datname = current_database();

-- 個別資料表的 Buffer Cache 命中率
SELECT
    relname,
    heap_blks_read,
    heap_blks_hit,
    round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS cache_hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read + heap_blks_hit > 0
ORDER BY heap_blks_read DESC
LIMIT 20;

-- 搜尋與 VACUUM 相關的所有設定
SELECT
    name,
    setting,
    unit,
    context,         -- 何時生效：user/superuser/sighup/postmaster
    boot_val,        -- 預設值
    pending_restart  -- 是否需要重啟才能生效
FROM pg_settings
WHERE name LIKE '%vacuum%'
ORDER BY name;

-- 查看非預設的設定（被明確修改過的）
SELECT name, setting, unit, source, sourcefile
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY name;

-- 啟用步驟（需要 superuser）
-- 1. postgresql.conf 加入：
-- shared_preload_libraries = 'pg_stat_statements'
-- 2. 重啟後建立擴展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 找出最耗時的前 10 個查詢
SELECT
    userid::regrole        AS username,
    calls,
    round(total_exec_time::numeric, 2)  AS total_ms,
    round(mean_exec_time::numeric, 2)   AS mean_ms,
    rows,
    round(100.0 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct_of_total,
    left(query, 120)       AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 找出 I/O 最密集的查詢
SELECT
    calls,
    round(blk_read_time::numeric, 2)  AS blk_read_ms,
    round(blk_write_time::numeric, 2) AS blk_write_ms,
    shared_blks_read,
    shared_blks_hit,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE calls > 100
ORDER BY (blk_read_time + blk_write_time) DESC
LIMIT 10;

-- 查詢各後端類型的 I/O 統計
SELECT
    backend_type,
    object,       -- relation/temp_relation/wal 等
    context,      -- normal/vacuum/bulkread/bulkwrite
    reads,
    read_time,
    writes,
    write_time,
    hits          -- buffer 命中次數
FROM pg_stat_io
ORDER BY backend_type, object, context;

-- 提高特定欄位的統計目標（適合高基數欄位）
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;

-- 降低不重要欄位的統計目標以加速 ANALYZE
ALTER TABLE logs ALTER COLUMN raw_message SET STATISTICS 0;

-- 更新統計後確認效果
ANALYZE orders;
SELECT n_distinct, correlation FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

-- 建立跨欄位擴展統計（記錄相關性）
CREATE STATISTICS orders_status_region ON status, region FROM orders;

-- 收集統計
ANALYZE orders;

-- 查看擴展統計定義
SELECT stxname, stxkeys, stxkind FROM pg_statistic_ext;

-- 錯誤：會破壞資料庫一致性
UPDATE pg_class SET relname = 'new_name' WHERE relname = 'old_name';

-- 正確：使用 DDL 指令
ALTER TABLE old_name RENAME TO new_name;

-- 重置當前資料庫的所有統計
SELECT pg_stat_reset();

-- 重置全域統計
SELECT pg_stat_reset_shared('bgwriter');
SELECT pg_stat_reset_shared('wal');        -- PG14+

-- 重置單一資料表的統計
SELECT pg_stat_reset_single_table_counters('public.orders'::regclass);

-- 使用 regclass 型別轉換（比字串拼接更安全）
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_relation_size(oid))       AS table_size,
    pg_size_pretty(pg_indexes_size(oid))        AS indexes_size
FROM pg_class
WHERE relname = 'orders';