PostgreSQL 查詢最佳化深度指南：EXPLAIN ANALYZE、索引策略與實戰調校

那條拖垮生產環境的 SQL

凌晨 2:47。警報響起。一個通常 12ms 回應的 API 端點超時到 30 秒。資料庫 CPU 飆到 100%。排查後發現：一條原本掃描 500 行的查詢現在在掃 4700 萬行。一個 8 億行的表上 ORDER BY created_at DESC LIMIT 20——三天前的一次遷移中，支撐它的索引被誤刪了。直到表資料跨過臨界點，才有人注意到。

PostgreSQL 查詢最佳化不是背清單。而是理解查詢規劃器怎麼想、它為什麼做某些決定、怎麼給它正確的資訊。這篇文章涵蓋原理、工具，和五個來自生產系統的真實最佳化案例。

讀 EXPLAIN ANALYZE：唯一重要的事

每個 PostgreSQL 最佳化從 EXPLAIN ANALYZE 開始。不是 EXPLAIN——是 EXPLAIN ANALYZE。區別在於：EXPLAIN 估算成本。EXPLAIN ANALYZE 實際執行查詢並報告真實時間。

EXPLAIN 輸出的解剖

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

Limit  (cost=12543.28..12543.31 rows=10 width=44)
       (actual time=342.112..342.115 rows=10 loops=1)
  ->  Sort  (cost=12543.28..12554.78 rows=4600 width=44)
            (actual time=342.110..342.112 rows=10 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  HashAggregate  (cost=12320.50..12366.50 rows=4600 width=44)
                  (actual time=338.450..340.120 rows=4523 loops=1)
              Group Key: u.id
              Batches: 1  Memory Usage: 1169kB
              ->  Hash Right Join  (cost=4560.20..11980.30 rows=68040 width=40)
                        (actual time=125.340..310.280 rows=67200 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..6540.00 rows=500000 width=8)
                              (actual time=0.015..85.400 rows=500000 loops=1)
                    ->  Hash  (cost=4520.20..4520.20 rows=3200 width=36)
                            (actual time=125.110..125.112 rows=3200 loops=1)
                          Buckets: 4096  Batches: 1  Memory Usage: 253kB
                          ->  Seq Scan on users u
                                    (cost=0.00..4520.20 rows=3200 width=36)
                                    (actual time=0.020..124.500 rows=3200 loops=1)
                                Filter: (created_at > '2025-01-01'::date)
                                Rows Removed by Filter: 96800
Planning Time: 1.234 ms
Execution Time: 342.356 ms

每個數字的含義

欄位	含義	關注點
`cost=x..y`	啟動成本..總成本（任意單位）	估算行數與實際差距大時
`actual time=x..y`	返回首行 ms..返回全部 ms	首行時間偏高 = 產出慢
`rows=N`（估算）	規劃器的行數估算	與實際相差 10 倍以上 = 有問題
`rows=N`（實際）	實際處理行數	與估算對比
`loops=N`	節點執行次數	Nested Loop 內部節點：高迴圈數 + 高單次成本
`Buffers: shared hit=N`	快取命中頁數	數值高 = 記憶體壓力？
`Buffers: shared read=N`	磁碟讀取頁數	數值高 = 缺索引或冷快取
`Rows Removed by Filter`	掃描後被過濾的行	數值高 = 缺少有效索引

最關鍵的三個數字

估算行數 vs 實際行數比率：如果規劃器估算 100 行但實際是 50000 行，下游每個節點都會做錯決定。大多數糟糕的查詢計畫都源於此。
Rows Removed by Filter：每一行「被過濾掉」的都被從磁碟讀取、處理、然後丟棄。如果這個數是百萬級而實際行數是百級，你需要索引。
Buffers: shared read：磁碟讀取。在調校良好的系統中，大部分應當是 shared hit（快取命中）。在高頻查詢上看到高 read 說明 shared_buffers 太小或工作集超過了記憶體。

索引策略：了解 PostgreSQL 有什麼

PostgreSQL 的索引類型比多數開發者意識到的多。選錯了，規劃器就會棄用你的索引。

索引類型決策矩陣

索引類型	最佳場景	儲存開銷	寫入代價	侷限
B-tree	等值、範圍、排序、LIKE 'abc%'	中等（約 2 倍索引列）	低	前導萬用字元 LIKE '%abc' 失效
Hash	僅等值（`=`）	低	極低	不支援範圍查詢和排序
GIN	全文搜尋、陣列、JSONB 包含	高（倒排索引）	高	精確匹配慢，寫入代價大
GiST	幾何資料、全文、範圍類型	中等	中等	對簡單類型讀速低於 B-tree
BRIN	物理相關的超大表	極小	極小	僅資料物理有序時有效
SP-GiST	非平衡樹結構（IP、點）	中等	中等	小眾——確定需要再用

各索引類型最佳實踐

B-tree——解決 90% 問題：

-- 標準 B-tree：支援 =, <, >, BETWEEN, ORDER BY, LIKE 'prefix%'
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- 複合索引：列順序很重要
-- 查詢：WHERE status = 'active' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- status 在前（等值條件），created_at 在後（範圍條件）——最優

-- 覆蓋索引（INCLUDE）：實現 index-only scan
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
  INCLUDE (total_amount, created_at);
-- 查詢：SELECT user_id, status, total_amount FROM orders WHERE user_id = 123
-- 完全從索引中獲取資料——無需回表

GIN——JSONB 和全文搜尋：

-- JSONB 索引：@> (包含), ? (鍵存在), ?| (任一鍵)
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- 全文搜尋
CREATE INDEX idx_posts_search ON posts
  USING GIN (to_tsvector('english', title || ' ' || body));

-- 使用該索引的查詢
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & optimization');

BRIN——十億行表的救星：

-- BRIN：Block Range INdex —— 按區塊範圍存 min/max
-- 極小索引（通常 < 表大小的 1%），適用於超大追加型表

CREATE INDEX idx_events_timestamp_brin ON events
  USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- 因為資料按時間戳順序插入，BRIN 工作極佳
-- 1TB 表 → 50MB BRIN 索引 → 亞秒級範圍查詢
SELECT * FROM events
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-02';

部分索引——只索引需要查的：

-- 只索引未處理的訂單（佔表的 5%）
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

-- 索引縮小 95%，寫入更快，掃描更快
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50;

案例一：N+1 生成器

症狀

一條 ORM 生成的查詢耗時 8.2 秒。開發者不理解——「就是查 100 篇文章和它們作者而已。」

診斷

-- ORM 實際生成的 SQL（簡化）
-- 1 次查文章，然後 100 次單獨查作者
SELECT * FROM articles ORDER BY published_at DESC LIMIT 100;
-- 對每篇文章：
SELECT * FROM users WHERE id = $1;  -- 執行了 100 次

EXPLAIN ANALYZE 顯示總共 101 次查詢。每次作者查詢雖然快（2ms），但 100 次加起來是 200ms 網路往返加規劃開銷。

修復

-- 把 101 次查詢變成 2 次
SELECT * FROM articles ORDER BY published_at DESC LIMIT 100;

-- 批量查作者
SELECT * FROM users WHERE id = ANY($1);
-- $1 = [author_id_1, author_id_2, ..., author_id_100]

最佳化後：42ms。答案是 ORM 的 eager loading / preload 功能——開發者之前沒用。永遠要檢查你的 ORM 生成的 SQL。開發環境打開日誌。

案例二：OFFSET 陷阱

症狀

分頁在第 1 頁很快（20ms），第 10 頁變慢（200ms），第 500 頁逾時（30s+）。

診斷

-- 問題查詢
SELECT * FROM events
WHERE user_id = 456
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

EXPLAIN ANALYZE 揭示：PostgreSQL 掃描了 10020 行然後丟棄前 10000 行。頁碼越深就掃描丟棄更多。

修復：Keyset 分頁

-- 第 1 頁
SELECT * FROM events
WHERE user_id = 456
ORDER BY created_at DESC, id DESC
LIMIT 21; -- 多取一條判斷是否有下頁

-- 最後一行：created_at = '2025-03-15 14:23:01', id = 88234

-- 第 2 頁（用第 1 頁最後一行作為游標）
SELECT * FROM events
WHERE user_id = 456
  AND (created_at, id) < ('2025-03-15 14:23:01', 88234)
ORDER BY created_at DESC, id DESC
LIMIT 21;

-- 支撐游標的複合索引
CREATE INDEX idx_events_user_cursor ON events(user_id, created_at DESC, id DESC);

最佳化後：穩定 3-5ms/頁，不受頁碼深淺影響。索引用游標直接定位，只讀需要的行。

案例三：索引列上函數阻斷

症狀

一個約會應用的「附近使用者」查詢，在有 location 索引的情況下仍全表掃描 5000 萬行。

診斷

-- 實際查詢
SELECT * FROM profiles
WHERE ST_DWithin(
  location,
  ST_MakePoint($1, $2)::geography,
  10000  -- 10 公里
);

location 上建了 B-tree 索引，但 ST_DWithin() 是 GiST 操作。B-tree 索引加速不了空間查詢。規劃器沒得選，只能順序掃描。

修復

-- 把 B-tree 換成 GiST 空間索引
CREATE INDEX idx_profiles_location_gist ON profiles
  USING GIST (location);

-- 查詢現在走索引
EXPLAIN ANALYZE SELECT * FROM profiles
WHERE ST_DWithin(location, ST_MakePoint(139.65, 35.67)::geography, 10000);

-- Index Scan using idx_profiles_location_gist
-- Rows: 1,247（原來是全表 5000 萬順序掃描）

最佳化後：12ms（原來 4.2s）。用 PostgreSQL 擴展（PostGIS、pg_trgm、ltree）時，確保索引類型與運算子匹配。

案例四：十億行表的時間範圍查詢

症狀

一個分析儀表板的「最近 7 天」查詢在 8 億行的 events 表上耗時 45 秒。

診斷

-- 目前查詢
SELECT event_type, COUNT(*), AVG(duration_ms)
FROM analytics_events
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY event_type;

-- EXPLAIN 顯示：Parallel Seq Scan on analytics_events
-- Rows Removed by Filter: 790,000,000

created_at 上有常規 B-tree 索引，但表有 450GB。索引自身 38GB——太大，放不進記憶體。每次查詢都要從磁碟讀取索引頁。規劃器判斷順序掃描比隨機索引讀取更便宜。

修復：分區 + BRIN

-- 第一步：按月分區
CREATE TABLE analytics_events (
  id BIGSERIAL,
  event_type TEXT NOT NULL,
  duration_ms INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 建立月分區
CREATE TABLE analytics_events_2025_07
  PARTITION OF analytics_events
  FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');

-- 第二步：每個分區上建 BRIN 索引（極小、極快）
CREATE INDEX idx_events_brin_created
  ON analytics_events USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- 第三步：啟用分區修剪
SET enable_partition_pruning = on; -- PG 12+ 預設開啟

最佳化後：800ms（原來 45s）。分區修剪立刻排除了 24 個月分區中的 23 個。目標分區上的 BRIN 索引只有 2MB 而非 38GB——完全快取在記憶體中。

分區決策指南

表大小	分區粒度	原因
<1000 萬行	別分區	開銷 > 收益
1000 萬-1 億	可能不需要	索引調校通常足夠
1 億-10 億	按月或按週	分區修剪 + 可控索引大小
>10 億	按天（寫入量大）	保持每個分區 < 5000 萬行

案例五：統計資訊盲區

症狀

在 staging（20ms）跑得完美的查詢，生產環境耗時 8 秒。同樣的表結構、同樣的索引、同樣的查詢。

診斷

SELECT * FROM shipments
WHERE status = 'delivered'
  AND warehouse_id = 42
ORDER BY created_at DESC
LIMIT 50;

-- Staging：1 萬行，9 千行是 'delivered'
-- 生產：5000 萬行，4800 萬行是 'delivered'

-- Staging 裡的規劃器："status = 'delivered' 過濾掉大部分行，走 status 上的索引"
-- Index Scan using idx_shipments_status → 20ms ✓

-- 生產裡的規劃器："status = 'delivered' 匹配了 96% 的行，索引掃描比順序掃描慢"
-- Seq Scan on shipments → 8s ✗

PostgreSQL 在 pg_stats 中儲存列統計資訊。當資料分佈改變，統計資訊就過時了。生產環境的規劃器不知道 warehouse_id = 42 把範圍縮小到 200 行。

修復

-- 執行 ANALYZE 更新統計資訊
ANALYZE shipments;

-- 或建立擴展統計資訊處理關聯列
CREATE STATISTICS shipments_status_warehouse (dependencies)
  ON status, warehouse_id FROM shipments;

ANALYZE shipments;

-- 更好：建一個匹配兩個條件的複合索引
CREATE INDEX idx_shipments_warehouse_status_created
  ON shipments(warehouse_id, status, created_at DESC);

最佳化後：2ms（原來 8s）。複合索引加上新鮮統計資訊，讓規劃器選擇了 index-only scan。

何時 ANALYZE

批量插入後（超過表的 5%）
批量刪除後
建立新索引後
定期執行：autovacuum_analyze_scale_factor = 0.05。大表（>1000 萬行）下調到 0.01。

PostgreSQL 版本特性改進

特性	版本	影響
並列查詢執行	PG 9.6+	多個 worker 參與 seq scan、hash join
並列 B-tree 索引掃描	PG 11+	worker 共用索引掃描進度
增量排序	PG 13+	對部分有序資料排序更快
擴展統計資訊	PG 10+（13+ 增強）	關聯列感知
LZ4 壓縮（TOAST）	PG 14+	比 PGLZ 解壓更快
並列 hash join	PG 14+	多 worker 構建 hash 表
MERGE 命令	PG 15+	單語句 upsert
SQL/JSON 建構器	PG 15+	SQL 中原生 JSON 建構
並列 full & right hash join	PG 16+	更多並列 join 類型
pg_stat_io	PG 16+	I/O 時間統計
VACUUM 獨立 maintenance_work_mem	PG 17+	自動/手動 vacuum 獨立記憶體

連線池：缺失的效能層

我在生產環境常看到：max_connections = 500。這幾乎總是錯的。

PostgreSQL 使用程序-per-連線模型。每個連線佔用 5-10MB 記憶體，每次查詢都發生上下文切換。500 個連線意味著 2.5-5GB 僅用於連線開銷，還有毀滅性的 CPU 上下文切換。

PgBouncer 設定

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# 關鍵：使用交易級池化
pool_mode = transaction

# 服務端連線保持少量
default_pool_size = 25
max_client_conn = 500

# 為關鍵操作預留連線
reserve_pool_size = 5
reserve_pool_timeout = 3

pool_mode	行為	最佳場景
`session`	每個用戶端會話一個服務端連線	使用 session 狀態的遺留應用
`transaction`	每次交易後釋放連線	REST API、無狀態服務
`statement`	每次語句後釋放連線	極端擴展、無交易場景

經驗法則：default_pool_size = (CPU 核心數 * 2)（交易池化模式）。16 核伺服器，pool_size 設 25-32 綽綽有餘。

最佳化除錯工具箱

-- 目前有哪些查詢在跑？
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- 哪些查詢最慢？（需要 pg_stat_statements）
SELECT queryid, calls,
       mean_exec_time::numeric(10,1) AS avg_ms,
       total_exec_time::numeric(10,1) AS total_ms,
       rows,
       left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 索引使用統計
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- 未被使用的索引！
ORDER BY pg_relation_size(indexrelid) DESC;

-- 表膨脹檢查
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 快取命中率
SELECT sum(heap_blks_read) AS heap_read,
       sum(heap_blks_hit) AS heap_hit,
       round(100.0 * sum(heap_blks_hit) /
         NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 1) AS cache_hit_ratio
FROM pg_statio_user_tables;
-- 目標是 >99% 的快取命中率

生產設定速查

# postgresql.conf —— 16GB/8 核 伺服器的修改項

# 記憶體
shared_buffers = 4GB              # 記憶體的 25%
effective_cache_size = 12GB       # 記憶體的 75%
work_mem = 64MB                   # 每次排序操作的記憶體
maintenance_work_mem = 512MB      # VACUUM、CREATE INDEX
wal_buffers = 64MB

# 規劃器
random_page_cost = 1.1            # 預設 4.0 基於 HDD。SSD 用 1.0-1.5
effective_io_concurrency = 200    # SSD 併發 I/O 深度
default_statistics_target = 500   # 更多取樣，更好計畫

# 並列
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 12         # 含並列 + 邏輯複製

# WAL 與檢查點
max_wal_size = 8GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9   # 分散檢查點寫入

總結：PostgreSQL 查詢最佳化是一個迴圈：用 EXPLAIN ANALYZE 測量 → 定位瓶頸（磁碟 I/O、行數估算偏差、缺索引、統計資訊過時）→ 最小化修復 → 再測量。按影響力排序的最有效最佳化：加對索引 → 修復 N+1 查詢 → 更新統計資訊 → 對大表分區 → 設定連線池 → 調校伺服器參數。每個最佳化都要用最佳化前後的 EXPLAIN ANALYZE (BUFFERS) 輸出來證明。不測量就是在瞎改。

線上工具

JSON 格式化 — 效能除錯時檢查 JSON 格式的查詢結果
雜湊計算器 — 為不適合 B-tree 的大文字列生成雜湊索引值