資料面試：如何用 PostgreSQL 擴充統計資訊修復基數誤估？
題干與適用場景
一個 PostgreSQL 查詢在單欄過濾時很快，但同時過濾 customer_tier、region 和 status 時選擇了錯誤的連接順序。請診斷基數誤估，並說明何時使用擴充統計資訊、如何驗證收益以及它的邊界。
PostgreSQL 文件指出，預設統計資訊主要按欄收集；當多個欄位存在相關性時，獨立性假設會讓選擇率相乘並產生誤估。CREATE STATISTICS 可以收集依賴關係、最常見值組合或多變量直方圖，但它不會取代索引，也不會讓所有謂詞自動變準。
面試官考察點
- 是否先用
EXPLAIN (ANALYZE, BUFFERS)對比 estimated 與 actual rows。 - 是否能解釋多欄相關性導致的獨立性假設錯誤。
- 是否區分
dependencies、mcv和ndistinct的適用場景。 - 是否知道擴充統計物件需要
ANALYZE才會取得資料。 - 是否用工作負載和回歸查詢驗證計畫改變，而不是憑感覺加入統計物件。
- 是否說明採樣、維護成本、表達式和跨表相關性的邊界。
回答前需要釐清的問題
- 誤估發生在過濾、連接還是分組？不同階段需要不同診斷。
- 表大小、資料偏斜、更新頻率和
defaultstatisticstarget是什麼？ - 三個欄位是否同表、同一查詢謂詞，且相關性是否穩定？
- 計畫問題是延遲、記憶體溢出、錯誤連接演算法，還是資源成本？
- 是否已有合適索引、分區和新鮮的單欄統計資訊？
30 秒回答框架
我會先用實際執行計畫確認估算行數在哪一步偏離，並檢查統計資訊新鮮度和資料分布。若同表多欄有穩定相關性，再選擇 dependencies、mcv 或 ndistinct 建立最小統計物件，執行 ANALYZE 後用代表性查詢比較估算誤差、連接演算法、緩衝讀和尾延遲。擴充統計只改善規劃器資訊，不取代索引、分區或資料建模；若相關性跨表、隨時間變化或採樣不足，就降低承諾並繼續治理資料與計畫。
分步驟深入解答
1. 定位估算誤差
比較 EXPLAIN (ANALYZE, BUFFERS) 中每個節點的 estimated rows 與 actual rows，找出第一個數量級偏差。記錄謂詞、連接順序、計畫時間、執行時間和緩衝命中，避免只看總耗時。
2. 檢查單欄統計與新鮮度
確認最近一次 ANALYZE 覆蓋相關資料表，查看 pg_stats 的最常見值、直方圖和非空比例。資料剛大量變更、強烈偏斜或統計目標過低時，先修復採樣和更新節奏，再判斷是否需要擴充統計。
3. 選擇統計類型
dependencies 描述欄位之間的功能依賴，適合一個欄位強烈暗示另一個欄位的場景；mcv 描述多欄最常見組合，適合少數組合主導選擇率；ndistinct 估計多欄組合的不同值數量，適合分組或去重基數問題。一個物件可以指定多個類型，但應以實際誤差為依據。
CREATE STATISTICS orders_customer_region_stats
(dependencies, mcv, ndistinct)
ON customer_tier, region, status
FROM orders;
ANALYZE orders;4. 重新驗證計畫
在接近生產的參數、快取狀態和並發下重跑查詢，比較每個關鍵節點的行數誤差、連接方法、記憶體、磁碟臨時檔案和 p95/p99。計畫改變不等於一定更好；要確認總資源和穩定性都改善，並觀察不同參數的查詢。
5. 處理採樣與統計目標
擴充統計仍基於採樣，稀有組合或快速變化的資料可能沒有被捕捉。提高熱點欄位統計目標前，先測量 ANALYZE 時間、系統負載和收益；不要把全域目標盲目調到最大。統計物件的欄位集合也應盡量小，避免維護無用組合。
6. 說明邊界和替代方案
擴充統計只描述同一資料表的欄位關係，不能直接建模跨表相關性，也不會改變索引存取路徑。跨表問題可能需要重寫查詢、預聚合、分區、物化結果或改進資料模型；相關性隨租戶、季節或狀態轉移變化時，需要持續監控而非一次性修復。
7. 建立回歸與清理機制
把代表性計畫和估算誤差寫入回歸集合，在 PostgreSQL 升級、資料遷移和 schema 變更後複測。若統計物件長期沒有改善、只服務已刪除查詢或增加維護成本，應刪除並記錄原因。用查詢指紋關聯統計物件、計畫變化和線上延遲。
高品質示範回答
我會先找出計畫中第一個 estimated rows 與 actual rows 相差數量級的節點，並確認單欄統計已經新鮮。假設三個欄位在同一張訂單表中存在穩定組合，我會先建立最小的 dependencies 或 mcv 物件，執行 ANALYZE，再用代表性參數比較估算誤差、連接順序、緩衝讀和尾延遲。若是分組或去重的組合基數問題，再評估 ndistinct。
我不會把擴充統計當成索引替代品，也不會承諾跨表相關性自動解決。對稀有組合、快速變化資料或採樣不足，我會測量更高統計目標的成本，必要時改寫查詢、預聚合或調整模型。最終將計畫和估算誤差納入回歸集合，持續檢查收益是否仍存在。
常見錯誤
- 只看總耗時不看節點誤差 → 找不到誤估源頭 → 逐節點比較 estimated 與 actual rows。
- 把三種統計類型全部預設開啟 → 增加維護而未必有收益 → 依據誤差形態選擇最小集合。
- 建立物件後不執行
ANALYZE→ 規劃器沒有新資料 → 明確刷新和驗證步驟。 - 認為擴充統計會建立索引 → 仍可能掃描大量資料 → 分開討論統計資訊和存取路徑。
- 用一次參數的計畫證明普遍有效 → 資料分布和參數會變化 → 做多參數、並發和回歸驗證。
- 忽略跨表相關性 → 單表統計無法解決連接誤估 → 改寫查詢或治理模型。
追問及應對
什麼時候優先選 dependencies？
當一個欄位幾乎決定另一個欄位，例如區域與受限狀態有穩定函數關係時。先用計畫誤差和資料檢查證明依賴，再建立物件。
mcv 與 ndistinct 如何區分？
mcv 關注多欄最常見組合對過濾選擇率的影響；ndistinct 關注組合值數量，常用於分組、去重或連接基數估計。
擴充統計物件會自動更新嗎？
統計資料在 ANALYZE 時收集，需依靠自動或手動 ANALYZE 觸發。物件定義存在不等於資料已經新鮮。
為什麼提高統計目標可能仍無效？
採樣仍可能遺漏稀有組合，資料關係也可能隨時間變化。應觀察估算誤差和 ANALYZE 成本，必要時改用模型或查詢策略。
如何驗證沒有回歸？
保存多組參數的計畫，比較估算誤差、資源、p95/p99 和臨時檔案，並在版本、資料量和 schema 變更後重跑。
什麼時候刪除擴充統計？
當查詢已消失、誤差沒有改善或維護成本超過收益時刪除，並保留原因和前後指標，避免統計物件無限累積。