查詢規劃器與執行計畫：PostgreSQL 如何選擇最優路徑 | PostgreSQL

-- SSD 環境下的典型設定
SET random_page_cost = 1.1;
SET seq_page_cost = 1.0;
-- 讓 Planner 更願意選擇 Index Scan

-- 查看特定欄位的統計資訊
SELECT
    attname,
    null_frac,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'status';

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- 若 pending 佔比很高（>10%），會選擇 Seq Scan
-- Seq Scan on orders  (cost=0.00..2500.00 rows=50000 width=120)
--   Filter: (status = 'pending'::text)

EXPLAIN SELECT * FROM users WHERE user_id = 12345;
-- Index Scan using users_pkey on users  (cost=0.43..8.45 rows=1 width=200)
--   Index Cond: (user_id = 12345)

-- 建立 Covering Index
CREATE INDEX idx_orders_status_covering
ON orders(status) INCLUDE (order_id, created_at, total_amount);

EXPLAIN SELECT order_id, created_at, total_amount
FROM orders WHERE status = 'shipped';
-- Index Only Scan using idx_orders_status_covering on orders
--   Index Cond: (status = 'shipped'::text)
--   Heap Fetches: 0  ← 完全不碰 Heap

EXPLAIN SELECT * FROM orders
WHERE status = 'processing' AND region = 'TW';
-- Bitmap Heap Scan on orders
--   Recheck Cond: ((status = 'processing') AND (region = 'TW'))
--   ->  BitmapAnd
--       ->  Bitmap Index Scan on idx_orders_status
--       ->  Bitmap Index Scan on idx_orders_region

-- 典型 Nested Loop：主鍵連接
EXPLAIN SELECT o.*, u.name
FROM orders o JOIN users u ON o.user_id = u.user_id
WHERE o.order_id = 99999;
-- Nested Loop  (cost=0.86..17.89 rows=1 width=220)
--   ->  Index Scan on orders  (cost=0.43..8.45 rows=1)
--   ->  Index Scan on users   (cost=0.43..8.45 rows=1)

SET work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, p.product_name
FROM orders o JOIN products p ON o.product_id = p.product_id;
-- Hash Join  (cost=450.00..12500.00 rows=200000 width=50)
--   Hash Cond: (o.product_id = p.product_id)
--   ->  Seq Scan on orders
--   ->  Hash
--       Buckets: 65536  Batches: 1  Memory Usage: 7825kB
--                        ^^^^^^^^ Batches=1 表示全在記憶體中

-- 診斷用：強制停用特定 Join 策略（不建議長期使用）
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
-- 觀察 Planner 在受限條件下的選擇
-- 使用後記得 RESET

EXPLAIN SELECT * FROM orders WHERE user_id = 100;

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'processed' WHERE user_id = 100;
ROLLBACK;  -- 撤銷實際修改

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.user_id
WHERE o.created_at > NOW() - INTERVAL '7 days';

-- JSON 格式（適合程式解析，可貼入視覺化工具）
EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) SELECT ...;

-- PG16+：顯示記憶體使用
EXPLAIN (ANALYZE, MEMORY) SELECT ...;

-- PG16+：顯示通用計畫（Prepared Statements 診斷）
EXPLAIN (GENERIC_PLAN) SELECT $1::int + $2::int;

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- HashAggregate  ← 記憶體中建 hash table 統計
-- 或 GroupAggregate  ← 依賴已排序的輸入

EXPLAIN SELECT SUM(amount) FROM orders;
-- Aggregate（無 GROUP BY）

EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Sort  Sort Key: created_at DESC
--   ->  Seq Scan on orders

-- PG13+ Incremental Sort（利用部份排序的索引）
-- 假設有索引 ON orders(user_id)
EXPLAIN SELECT * FROM orders ORDER BY user_id, created_at DESC;
-- Incremental Sort
--   Sort Key: user_id, created_at DESC
--   Presorted Key: user_id  ← 利用索引已排好 user_id
--   ->  Index Scan using idx_orders_user_id on orders

SET max_parallel_workers_per_gather = 4;
EXPLAIN SELECT COUNT(*) FROM very_large_table;
-- Finalize Aggregate
--   ->  Gather  (workers=4)
--         ->  Partial Aggregate
--               ->  Parallel Seq Scan on very_large_table

-- 問題：500 萬行表對低選擇率條件全表掃描
EXPLAIN SELECT * FROM orders WHERE order_number = 'ORD-2024-00001';
-- Seq Scan on orders  (cost=0.00..125000.00 rows=1 width=200)

-- 解決：建立索引
CREATE INDEX idx_orders_order_number ON orders(order_number);

-- 驗證：成本從 125000 降至 8.58
EXPLAIN SELECT * FROM orders WHERE order_number = 'ORD-2024-00001';
-- Index Scan using idx_orders_order_number  (cost=0.56..8.58 rows=1)

-- 問題：Planner 的 rows 估算嚴重偏低，選了錯誤策略
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN order_items i ON o.order_id = i.order_id
WHERE o.created_at > '2024-01-01';
-- Nested Loop  (actual time=0.025..48230.000 rows=2000000 loops=1)

-- 解決方案一：更新統計資訊
ANALYZE orders;
ANALYZE order_items;

-- 解決方案二：增加 work_mem 讓 Hash Join 有足夠記憶體
SET work_mem = '256MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_a JOIN large_b ON large_a.id = large_b.a_id;
-- Hash Join
--   Buckets: 131072  Batches: 32  Memory Usage: 8192kB
--                    ^^^^^^^^ Batches > 1 = spill to disk

-- 解決：增加 work_mem
SET work_mem = '512MB';
-- Batches: 1  ← 不再溢出，效能大幅提升

-- 問題：批量匯入後統計資訊未更新
INSERT INTO events SELECT * FROM events_staging;  -- 匯入 100 萬筆

EXPLAIN SELECT COUNT(*) FROM events WHERE type = 'click';
-- actual rows=150000  vs  估算 rows=1500  ← 100 倍誤差

-- 檢查最後 ANALYZE 時間
SELECT tablename, last_analyze, last_autoanalyze, n_live_tup
FROM pg_stat_user_tables WHERE tablename = 'events';

-- 解決：手動更新統計
ANALYZE events;

-- 問題：WHERE 中對索引欄使用函式
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';
-- Seq Scan  ← 無法使用 idx_users_email

-- 解決方案一：Expression Index
CREATE INDEX idx_users_email_upper ON users(UPPER(email));

-- 解決方案二：調整查詢（應用層預處理）
SELECT * FROM users WHERE email = LOWER('USER@EXAMPLE.COM');

-- 針對特定欄位調整（估算一直不準時）
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders(status);

-- 資料分布均勻的欄位可降低
ALTER TABLE logs ALTER COLUMN log_level SET STATISTICS 50;

-- 找出統計資訊最舊的大表（高風險）
SELECT
    tablename,
    n_live_tup,
    last_autoanalyze,
    EXTRACT(EPOCH FROM (NOW() - last_autoanalyze))/3600 AS hours_ago
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY last_autoanalyze NULLS FIRST
LIMIT 20;

-- 確認 autoanalyze 正常運作
SHOW autovacuum;                       -- on
SHOW autovacuum_analyze_threshold;     -- 50（行數觸發閾值）
SHOW autovacuum_analyze_scale_factor;  -- 0.2（20% 變動觸發）
-- 觸發條件：dead tuples > threshold + scale_factor × n_live_tup

-- 在 EXPLAIN ANALYZE 輸出中觀察 JIT
EXPLAIN (ANALYZE) SELECT COUNT(*), SUM(amount)
FROM very_large_orders
WHERE created_at > '2023-01-01' AND status IN ('completed', 'shipped');

-- 輸出末尾：
-- JIT:
--   Functions: 8
--   Options: Inlining true, Optimization true, Expressions true, Deforming true
--   Timing: Generation 2.5 ms, Inlining 15.2 ms, Optimization 32.1 ms,
--           Emission 12.5 ms, Total 62.4 ms

-- 針對 OLTP 角色停用 JIT
ALTER ROLE oltp_user SET jit = off;

-- JIT 啟用門檻（預設值通常足夠）
SHOW jit_above_cost;           -- 100000
SHOW jit_inline_above_cost;    -- 500000
SHOW jit_optimize_above_cost;  -- 500000

SHOW max_parallel_workers;              -- 8（系統總並行 Worker 上限）
SHOW max_parallel_workers_per_gather;   -- 2（單一 Gather 的 Worker 數）
SHOW min_parallel_table_scan_size;      -- 8MB（表超過此大小才考慮並行）
SHOW min_parallel_index_scan_size;      -- 512kB

-- 允許更多並行 Worker
SET max_parallel_workers_per_gather = 4;

-- 1. 查詢包含 parallel-unsafe 的函式
-- 需明確標記 PARALLEL SAFE
CREATE FUNCTION my_func() RETURNS void
AS $$ ... $$ LANGUAGE plpgsql PARALLEL SAFE;

-- 2. DML 操作（UPDATE/DELETE/INSERT）
-- 3. SERIALIZABLE 隔離級別
-- 4. 使用 nextval()、currval() 等序列函式

-- 載入擴展
LOAD 'auto_explain';

SET auto_explain.log_min_duration = 1000;   -- 超過 1 秒才記錄
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;
SET auto_explain.log_nested_statements = on;

-- 永久啟用（postgresql.conf）
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '1s'

-- 找出最耗時的查詢
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round((total_exec_time / calls)::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 找出平均最慢的查詢（至少執行 100 次）
SELECT query, calls,
    round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 輔助診斷：找出使用 Seq Scan 最多的表（可能需要索引）
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       CASE WHEN seq_scan > 0
            THEN round(seq_tup_read::numeric / seq_scan, 0)
       END AS avg_seq_tup
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_tup_read DESC
LIMIT 20;

PREPARE stmt AS SELECT * FROM orders WHERE status = $1;

-- 前 5 次：Custom Plan（針對實際參數值優化）
-- 第 6 次起：可能切換為 Generic Plan（通用計畫）
-- 若資料分佈不均，Generic Plan 可能效能差很多

-- 強制使用 Custom Plan
SET plan_cache_mode = force_custom_plan;

-- 強制使用 Generic Plan（參數分布均勻時）
SET plan_cache_mode = force_generic_plan;

-- PG11：CTE 是 optimization fence，強制 materialize
-- PG12+：CTE 預設可被 inline，Planner 能優化整個查詢

-- 強制 materialize（保證 CTE 只執行一次）
WITH recent AS MATERIALIZED (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT * FROM recent WHERE user_id = 100;

-- 明確允許 inline 優化
WITH recent AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT * FROM recent WHERE user_id = 100;

-- 欄位為 integer，傳入字串可能導致索引失效
SELECT * FROM orders WHERE user_id = '12345';

-- 更危險：ORM 傳入錯誤型別
-- 解決：確保查詢參數型別與欄位型別完全匹配

-- 問題：相關子查詢每行執行一次
SELECT o.order_id,
    (SELECT COUNT(*) FROM order_items WHERE order_id = o.order_id)
FROM orders o;
-- 若 orders 有 100 萬行，子查詢執行 100 萬次

-- 解決：改用 JOIN
SELECT o.order_id, COUNT(i.item_id) AS item_count
FROM orders o LEFT JOIN order_items i ON o.order_id = i.order_id
GROUP BY o.order_id;