PostgreSQL 16 查詢效能優化指南，涵蓋 EXPLAIN ANALYZE 解讀、Index 設計、OLAP 參數調優、Vacuum/Autovacuum、Partition 策略與 Logical Replication 健康監控。當使用者遇到慢查詢、需要設計 Index、調整 postgresql.conf 參數、或監控 CDC replication lag 時，使用此技能。
-- 基本用法
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
-- 完整資訊（推薦）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) SELECT ...;
| 關鍵字 | 意義 | 判斷基準 |
|---|---|---|
Seq Scan | 全表掃描 | 大表出現 = 缺 Index |
Index Scan | 走 Index | 通常是好事 |
Bitmap Heap Scan | 批次 Index 掃描 | 多條件時常出現，正常 |
Hash Join | 雜湊 JOIN | 大表 JOIN 通常合理 |
Nested Loop | 巢狀迴圈 JOIN | 外層資料量大時效能差 |
| vs |
actual rowsrows| 估計誤差 |
| 差距 > 10x 代表統計資料過時 |
Buffers: hit / read | 快取命中 | hit 比率高 = 記憶體充足 |
若估計誤差大，執行 ANALYZE <table>; 更新統計資料。
-- 單欄索引（最常用）
CREATE INDEX idx_orders_customer_id ON erp.orders(customer_id);
-- 複合索引（欄位順序：選擇性高的欄放前面）
CREATE INDEX idx_orders_status_date ON erp.orders(status, order_date);
-- 部分索引（只索引特定子集，節省空間）
CREATE INDEX idx_orders_active ON erp.orders(order_date)
WHERE status NOT IN ('cancelled', 'returned');
-- 覆蓋索引（INCLUDE 加入查詢欄，避免回表）
CREATE INDEX idx_orders_covering ON erp.orders(customer_id)
INCLUDE (total_amount, order_date);
-- 表達式索引
CREATE INDEX idx_customers_lower_email ON erp.customers(LOWER(email));
Index 選用規則：
WHERE 欄位 → 建 IndexJOIN ON 欄位 → 必建 Index（兩端都要）ORDER BY 欄位 → 考慮建 Index# 記憶體（依實體 RAM 的比例設定）
shared_buffers = 4GB # 實體 RAM 的 25%
work_mem = 64MB # 複雜排序/Hash Join 的記憶體，per operation
maintenance_work_mem = 512MB # VACUUM、CREATE INDEX 用
# 查詢規劃器
effective_cache_size = 12GB # 估計 OS 快取 = 實體 RAM 的 75%
random_page_cost = 1.1 # SSD 設 1.1（預設 4.0 是針對 HDD）
effective_io_concurrency = 200 # SSD 設 200
# 平行查詢（OLAP 加速）
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
-- 監控 replication lag（在 OLTP/Publisher 執行）
SELECT
pid,
application_name,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
(sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication;
-- 監控 replication slot（lag 累積代表 subscriber 沒在消費）
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;
-- 在 OLAP/Subscriber 確認訂閱狀態
SELECT subname, subenabled, subslotname FROM pg_subscription;
SELECT * FROM pg_stat_subscription;
⚠️
lag_bytes持續增長但active = true→ 檢查 Subscriber 端負載
⚠️active = false→ Subscriber 斷線，需重建連線
| 問題 | 原因 | 解法 |
|---|---|---|
| 全表掃描大表 | 缺少 Index | 建立適當 Index |
| 估計行數嚴重偏差 | 統計資料過時 | ANALYZE table; |
| 大量 Dead Tuples | VACUUM 頻率不足 | 調整 autovacuum_vacuum_scale_factor |
| Hash Join 記憶體溢出 | work_mem 太小 | 调高 work_mem（注意連線數×work_mem 不超過 RAM） |
| Nested Loop 慢 | JOIN 條件缺 Index | 補 Index 或改查詢邏輯 |