資料面試題：如何診斷 PostgreSQL 參數化查詢的 generic plan？

題幹與適用場景

一個使用 prepared statement 的 API 上線後出現長尾延遲：小租戶查詢很快，大租戶查詢卻突然走順序掃描。請說明 PostgreSQL 的 custom plan 與 generic plan 如何選擇，如何用 EXPLAIN (GENERIC_PLAN) 比較計畫，如何驗證統計資訊、參數分布、快取與 plan_cache_mode，並給出不破壞交易和連線池的修復流程。

面試官考察點

• 能否區分規劃階段、執行階段與結果傳輸成本。
• 是否理解 generic plan 不依賴具體參數值，而 custom plan 可利用參數選擇性。
• 能否安全使用 EXPLAIN ANALYZE，避免把寫入副作用帶到生產環境。
• 能否結合統計資訊、索引、連線池和參數化策略定位回歸。
• 能否用證據選擇 auto、force_generic_plan 或 force_custom_plan。

回答前需要釐清的問題

• 查詢是透過 prepared statement、ORM 還是代理層執行？連線是否重用？
• 參數分布是否傾斜，租戶規模和資料冷熱是否差異顯著？
• 延遲回歸發生在規劃、執行、鎖等待、IO 還是序列化？
• 是否允許調整索引、統計目標、SQL、連線池或工作階段參數？

30 秒回答框架

先用 EXPLAIN (GENERIC_PLAN) 查看不依賴參數的計畫，再對代表性參數用 EXPLAIN ANALYZE EXECUTE 查看 custom plan 和實際行數。generic plan 能省規劃時間，但參數選擇性差異大時可能長期低效。先確認統計資訊和計畫快取行為，再用基準資料比較規劃成本、執行成本和尾延遲，最後在受控工作階段調整 plan_cache_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. 選擇修復邊界

工作階段級 plan_cache_mode=force_custom_plan 可驗證 custom plan 是否解決回歸；force_generic_plan 適合計畫穩定且規劃成本高的查詢。長期方案可能是索引、查詢拆分、明確類型或讓 ORM 避免不必要的 prepared statement，不能只改全域參數。

8. 驗證連線池與發布

連線池會讓工作階段設定、prepared statement 生命週期和 PostgreSQL 版本差異變得重要。灰度時按參數分位、租戶規模、連線池實例和資料庫節點比較 p95/p99、規劃時間、緩衝命中和錯誤率，並準備回滾。

設計取捨與邊界

generic plan 的收益是減少規劃工作，代價是失去參數值帶來的選擇性資訊；custom plan 則可能在高頻短查詢中浪費規劃 CPU。EXPLAIN ANALYZE 會實際執行語句，寫入類語句應在交易中回滾或使用唯讀副本。計畫成本是估計單位，不能直接當作毫秒；統計資訊是抽樣結果，計畫會隨資料、版本和 ANALYZE 改變。

落地計畫與證據

1. 記錄查詢文字、參數類型、連線池模式、PostgreSQL 版本和計畫快取行為。
2. 用 GENERIC_PLAN 與多個代表性參數的 ANALYZE 計畫建立基線。
3. 檢查統計資訊更新時間、估算誤差、索引命中、IO 和規劃時間。
4. 在單一連線或灰度工作階段試驗 plan_cache_mode，避免直接修改全域設定。
5. 以 p95/p99、規劃 CPU、shared reads、鎖等待和錯誤率驗收，並保留回滾開關。

常見誤區與追問

誤區一：看到順序掃描就刪掉 generic plan

順序掃描可能是大範圍結果的正確選擇。先比較代表性參數的實際行數、IO 和尾延遲。

誤區二：把 cost 當作真實時間

cost 是規劃器的相對估計單位。應結合 ANALYZE 的 actual time、buffers 和線上指標判斷。

誤區三：在生產環境直接執行寫入型 EXPLAIN ANALYZE

ANALYZE 會執行語句。寫入操作要在可回滾交易或隔離副本中驗證。

誤區四：只調高統計目標

統計目標會增加分析和規劃成本，也不一定解決連線池或參數類型問題。必須用基準資料證明收益。

誤區五：忽略連線池工作階段邊界

工作階段級 plan_cache_mode 和 prepared statement 可能只影響部分連線。發布前要涵蓋池中所有連線和回收策略。