題幹與適用場景
一個使用 prepared statement 的 API 上線後出現長尾延遲：小租戶查詢很快，大租戶查詢卻突然走順序掃描。請說明 PostgreSQL 的 custom plan 與 generic plan 如何選擇，如何用 EXPLAIN (GENERICPLAN) 比較計畫，如何驗證統計資訊、參數分布、快取與 plancache_mode，並給出不破壞交易和連線池的修復流程。
面試官考察點
- 能否區分規劃階段、執行階段與結果傳輸成本。
- 是否理解 generic plan 不依賴具體參數值，而 custom plan 可利用參數選擇性。
- 能否安全使用
EXPLAIN ANALYZE，避免把寫入副作用帶到生產環境。 - 能否結合統計資訊、索引、連線池和參數化策略定位回歸。
- 能否用證據選擇
auto、forcegenericplan或forcecustomplan。
回答前需要釐清的問題
- 查詢是透過 prepared statement、ORM 還是代理層執行？連線是否重用？
- 參數分布是否傾斜，租戶規模和資料冷熱是否差異顯著？
- 延遲回歸發生在規劃、執行、鎖等待、IO 還是序列化？
- 是否允許調整索引、統計目標、SQL、連線池或工作階段參數？
30 秒回答框架
先用 EXPLAIN (GENERICPLAN) 查看不依賴參數的計畫，再對代表性參數用 EXPLAIN ANALYZE EXECUTE 查看 custom plan 和實際行數。generic plan 能省規劃時間，但參數選擇性差異大時可能長期低效。先確認統計資訊和計畫快取行為，再用基準資料比較規劃成本、執行成本和尾延遲，最後在受控工作階段調整 plancache_mode 或改寫查詢，並驗證連線池中的所有連線。
分步驟深入解答
1. 拆分規劃與執行
規劃器根據 SQL、統計資訊和參數決定掃描與連接演算法；執行器再讀取頁面、過濾資料列並回傳結果。只看應用總耗時無法判斷 generic plan 是否是根因。
2. 說明 custom plan
custom plan 針對本次參數生成，可利用選擇性估計。例如少量租戶可能適合索引掃描，大租戶可能適合順序掃描或不同的 join 順序；代價是每次或多次重新規劃。
3. 說明 generic plan
generic plan 使用參數佔位符，不依賴本次值。它可以攤薄規劃開銷，但如果參數分布高度傾斜，單一計畫可能對多數值都不理想。EXPLAIN (GENERIC_PLAN) 不能與 ANALYZE 同時使用。
4. 先看 generic plan
EXPLAIN (GENERIC_PLAN)
SELECT sum(amount)
FROM invoices
WHERE tenant_id = $1 AND status = $2;檢查掃描類型、估算行數、索引條件、連接順序和總成本。參數類型無法推斷時明確寫 cast，避免把類型問題誤判成計畫問題。
5. 再看代表性 custom plan
在隔離環境中使用不同規模租戶的參數執行 EXPLAIN (ANALYZE, BUFFERS) EXECUTE。關注 estimated rows 與 actual rows、shared hits/reads、規劃時間、執行時間和是否出現磁碟排序；不要只比較 cost 數字。
6. 檢查統計資訊與分布
確認 autovacuum 或手動 ANALYZE 已涵蓋近期變更，檢查欄位基數、相關性和最常見值。對傾斜欄位可評估提高統計目標，但要以規劃準確度和規劃開銷的實測結果為依據。
7. 選擇修復邊界
工作階段級 plancachemode=forcecustomplan 可驗證 custom plan 是否解決回歸；forcegenericplan 適合計畫穩定且規劃成本高的查詢。長期方案可能是索引、查詢拆分、明確類型或讓 ORM 避免不必要的 prepared statement，不能只改全域參數。
8. 驗證連線池與發布
連線池會讓工作階段設定、prepared statement 生命週期和 PostgreSQL 版本差異變得重要。灰度時按參數分位、租戶規模、連線池實例和資料庫節點比較 p95/p99、規劃時間、緩衝命中和錯誤率，並準備回滾。
設計取捨與邊界
generic plan 的收益是減少規劃工作，代價是失去參數值帶來的選擇性資訊；custom plan 則可能在高頻短查詢中浪費規劃 CPU。EXPLAIN ANALYZE 會實際執行語句，寫入類語句應在交易中回滾或使用唯讀副本。計畫成本是估計單位，不能直接當作毫秒；統計資訊是抽樣結果，計畫會隨資料、版本和 ANALYZE 改變。
落地計畫與證據
- 記錄查詢文字、參數類型、連線池模式、PostgreSQL 版本和計畫快取行為。
- 用
GENERIC_PLAN與多個代表性參數的ANALYZE計畫建立基線。 - 檢查統計資訊更新時間、估算誤差、索引命中、IO 和規劃時間。
- 在單一連線或灰度工作階段試驗
plancachemode，避免直接修改全域設定。 - 以 p95/p99、規劃 CPU、shared reads、鎖等待和錯誤率驗收，並保留回滾開關。
常見誤區與追問
誤區一：看到順序掃描就刪掉 generic plan
順序掃描可能是大範圍結果的正確選擇。先比較代表性參數的實際行數、IO 和尾延遲。
誤區二：把 cost 當作真實時間
cost 是規劃器的相對估計單位。應結合 ANALYZE 的 actual time、buffers 和線上指標判斷。
誤區三：在生產環境直接執行寫入型 EXPLAIN ANALYZE
ANALYZE 會執行語句。寫入操作要在可回滾交易或隔離副本中驗證。
誤區四：只調高統計目標
統計目標會增加分析和規劃成本，也不一定解決連線池或參數類型問題。必須用基準資料證明收益。
誤區五：忽略連線池工作階段邊界
工作階段級 plancachemode 和 prepared statement 可能只影響部分連線。發布前要涵蓋池中所有連線和回收策略。