PostgreSQL 效能調校實戰：從慢查詢到毫秒級回應

前言

PostgreSQL 是一個功能強大的資料庫，但「功能強大」不等於「自動高效」。在我們的幾個生產專案中，都曾遇到隨著資料量增長而效能急劇下降的問題。

這篇文章整理了我們在實際環境中優化 PostgreSQL 效能的經驗。不是理論性的「教科書知識」，而是真正在生產中驗證過的做法。

案例一：等待時間查詢從 3 秒降到 50ms

問題描述

在我們的 USJ 等待時間專案中，有一個查詢需要取得「過去 24 小時內某設施的等待時間紀錄」：

SELECT ride_id, wait_minutes, recorded_at
FROM wait_times
WHERE ride_id = 'harry-potter'
  AND recorded_at >= NOW() - INTERVAL '24 hours'
ORDER BY recorded_at DESC;

在資料量約 50 萬筆時，這個查詢需要 3.2 秒。

診斷過程

第一步永遠是看 EXPLAIN ANALYZE：

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ride_id, wait_minutes, recorded_at
FROM wait_times
WHERE ride_id = 'harry-potter'
  AND recorded_at >= NOW() - INTERVAL '24 hours'
ORDER BY recorded_at DESC;

結果顯示：

Seq Scan on wait_times  (cost=0.00..12456.00 rows=288 width=24)
  (actual time=1245.123..3201.456 rows=288 loops=1)
  Filter: ((ride_id = 'harry-potter') AND (recorded_at >= ...))
  Rows Removed by Filter: 499712
  Buffers: shared hit=3456 read=5678
Sort  (cost=12456.50..12457.22 rows=288 width=24)
  Sort Method: quicksort  Memory: 32kB
Planning Time: 0.123 ms
Execution Time: 3201.789 ms

問題一目了然：Seq Scan（全表掃描）。50 萬筆資料裡只需要 288 筆，卻掃描了全部。

解決方案

建立複合索引：

CREATE INDEX idx_wait_times_ride_recorded
  ON wait_times (ride_id, recorded_at DESC);

為什麼是 (ride_id, recorded_at DESC) 而不是分開建兩個索引？

• 複合索引可以同時滿足 WHERE ride_id = ? 的過濾和 ORDER BY recorded_at DESC 的排序
• PostgreSQL 只需要在索引中找到 harry-potter 的起始位置，然後按順序讀取直到超出時間範圍
• 避免了排序操作（索引本身就是有序的）

加了索引後的 EXPLAIN ANALYZE：

Index Scan using idx_wait_times_ride_recorded on wait_times
  (cost=0.42..15.67 rows=288 width=24)
  (actual time=0.034..0.412 rows=288 loops=1)
  Index Cond: ((ride_id = 'harry-potter') AND (recorded_at >= ...))
  Buffers: shared hit=5
Planning Time: 0.089 ms
Execution Time: 0.456 ms

從 3201ms 降到 0.456ms，提升 7000 倍。

深入分析：為什麼效果這麼顯著？

關鍵在於「選擇性」（selectivity）。ride_id = 'harry-potter' 大約過濾掉了 95% 的資料（假設有 20 個設施），recorded_at 的時間範圍再過濾掉 97%。兩個條件組合起來，只有不到 0.06% 的資料符合。

在這種高選擇性的查詢中，索引的效果最為顯著。

案例二：租屋資料的地理查詢

問題描述

在租屋地圖專案中，前端拖動地圖時需要查詢特定範圍內的物件：

SELECT id, title, price, lat, lng
FROM listings
WHERE lat BETWEEN 25.01 AND 25.08
  AND lng BETWEEN 121.50 AND 121.58
  AND price BETWEEN 8000 AND 25000
  AND is_active = true;

資料量 15 萬筆時需要 800ms，使用者拖動地圖時明顯卡頓。

解決方案一：B-tree 索引

先嘗試最直覺的做法——建立經緯度的複合索引：

CREATE INDEX idx_listings_location ON listings (lat, lng);

效果：800ms → 200ms。有改善但還不夠流暢。

問題在於 B-tree 索引處理範圍查詢時，只有第一個欄位能用「範圍掃描」，第二個欄位只能在第一個欄位的結果上過濾。

解決方案二：GiST 索引 + PostGIS

引入 PostGIS 擴展，使用空間索引：

-- 新增地理資料型別欄位
ALTER TABLE listings ADD COLUMN geom geometry(Point, 4326);
UPDATE listings SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);

-- 建立 GiST 空間索引
CREATE INDEX idx_listings_geom ON listings USING gist (geom);

-- 查詢改寫
SELECT id, title, price, ST_X(geom) as lng, ST_Y(geom) as lat
FROM listings
WHERE ST_Within(geom, ST_MakeEnvelope(121.50, 25.01, 121.58, 25.08, 4326))
  AND price BETWEEN 8000 AND 25000
  AND is_active = true;

效果：200ms → 35ms。GiST 空間索引對二維範圍查詢的效率遠高於 B-tree。

解決方案三：部分索引

進一步觀察發現，80% 以上的查詢都只查「啟用中」的物件。建立部分索引排除已下架的物件：

CREATE INDEX idx_listings_geom_active
  ON listings USING gist (geom)
  WHERE is_active = true;

效果：35ms → 18ms。索引更小，讀取更快。

查詢計畫解讀指南

看懂 EXPLAIN ANALYZE 是效能調校的基礎。以下是幾個關鍵指標：

掃描類型

| 類型 | 含義 | 好壞 | |------|------|------| | Seq Scan | 全表掃描 | 通常不好（除非表很小或需要大部分資料） | | Index Scan | 索引掃描 + 回表 | 好 | | Index Only Scan | 僅索引掃描 | 最好（資料完全從索引取得） | | Bitmap Index Scan | 點陣圖索引掃描 | 好（多條件組合時常見） |

常見的效能問題信號

-- 1. 實際行數遠大於估計行數 → 統計資訊過時
-- (actual rows=50000) vs (rows=100)
-- 解法：ANALYZE table_name;

-- 2. Sort Method: external merge → 記憶體不足，排序寫入磁碟
-- 解法：增加 work_mem 或加索引避免排序

-- 3. Nested Loop 中的 Seq Scan → 內層循環全表掃描
-- 解法：確保 JOIN 條件的欄位有索引

索引策略總結

什麼時候該建索引

• WHERE 條件中頻繁出現的欄位
• JOIN 條件的欄位
• ORDER BY 的欄位
• 高選擇性的欄位（不同值很多的欄位）

什麼時候不該建索引

• 表很小（< 1000 筆）：Seq Scan 反而更快
• 低選擇性的欄位（如 boolean）：除非搭配部分索引
• 頻繁更新的欄位：索引維護成本高
• 很少被查詢的欄位

複合索引的欄位順序

經驗法則：

1. 等值條件的欄位放前面（=）
2. 範圍條件的欄位放後面（>、<、BETWEEN）
3. 排序欄位放最後（如果前面的條件足夠限縮結果）

-- 好的欄位順序
CREATE INDEX idx ON orders (status, customer_id, created_at DESC);
-- 適合查詢：WHERE status = 'active' AND customer_id = 123 ORDER BY created_at DESC

-- 不好的欄位順序
CREATE INDEX idx ON orders (created_at DESC, status, customer_id);
-- 範圍條件放前面會導致後面的欄位無法有效利用索引

連線池管理

問題：連線數爆炸

在使用 Next.js API Routes 時，每次 serverless 函數執行都可能建立新的資料庫連線。在流量尖峰時，PostgreSQL 很快就會達到 max_connections 限制。

解決方案：PgBouncer

在應用和資料庫之間加上 PgBouncer 作為連線池：

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

pool_mode = transaction 是關鍵——連線在 transaction 結束後就會歸還到池中，而不是等到客戶端斷開。這讓 20 個實際連線可以服務 200 個並發客戶端。

效果

導入 PgBouncer 前後的比較：

• 之前：尖峰時段偶爾出現 too many connections 錯誤
• 之後：即使在 3 倍流量下也穩定運作
• PostgreSQL 的 CPU 使用率反而下降了（少了連線建立/銷毀的開銷）

分區表：管理大量時間序列資料

當 wait_times 表累積到 2000 萬筆紀錄時，即使有索引，某些查詢仍然變慢了。問題不在查詢本身，而在 VACUUM 和索引維護。

按月分區

CREATE TABLE wait_times (
    id BIGSERIAL,
    ride_id VARCHAR(50) NOT NULL,
    wait_minutes INTEGER NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (recorded_at);

-- 建立每月的分區
CREATE TABLE wait_times_2025_01 PARTITION OF wait_times
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE wait_times_2025_02 PARTITION OF wait_times
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ...

分區的好處

1. 查詢效能：查詢最近 7 天的資料時，PostgreSQL 直接跳過不相關的分區（partition pruning）
2. 維護效率：VACUUM 只需處理有更新的分區
3. 資料歸檔：要刪除 3 個月前的資料？直接 DROP 那個分區，比 DELETE 快幾百倍
4. 索引更小：每個分區的索引只覆蓋該分區的資料

自動建立分區

用一個 cron job 自動建立未來的分區：

-- 每月 1 號檢查並建立下下個月的分區
DO $$
DECLARE
    next_month DATE := date_trunc('month', NOW()) + INTERVAL '2 months';
    partition_name TEXT;
BEGIN
    partition_name := 'wait_times_' || to_char(next_month, 'YYYY_MM');
    IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = partition_name) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF wait_times FOR VALUES FROM (%L) TO (%L)',
            partition_name,
            next_month,
            next_month + INTERVAL '1 month'
        );
    END IF;
END $$;

監控與預警

效能調校不是一次性的工作，需要持續監控。我們關注的幾個指標：

慢查詢日誌

# postgresql.conf
log_min_duration_statement = 500  # 記錄超過 500ms 的查詢

pg_stat_statements

-- 找出累積執行時間最長的查詢
SELECT
    query,
    calls,
    total_exec_time / 1000 as total_seconds,
    mean_exec_time as avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

索引使用率

-- 找出未被使用的索引（浪費空間和寫入效能）
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique
ORDER BY pg_relation_size(i.indexrelid) DESC;

結語

PostgreSQL 效能調校的核心原則其實很簡單：

1. 先測量，再優化：用 EXPLAIN ANALYZE 確認瓶頸，不要猜
2. 索引是最有效的手段：90% 的慢查詢問題都能用正確的索引解決
3. 理解資料的存取模式：知道「什麼查詢最頻繁」比「什麼查詢最慢」更重要
4. 持續監控：效能問題通常是漸進式的，等到使用者抱怨時已經太晚了

這些經驗都是在真實的生產環境中用血汗換來的。希望對你有幫助。