題幹與適用場景
同一查詢在測試環境正常，在生產環境卻出現高延遲。請設計一套 PostgreSQL 18 查詢診斷方案，利用 EXPLAIN (ANALYZE, BUFFERS) 以及新增的記憶體、磁碟和 I/O 細節，區分計畫錯誤、排序溢出、快取未命中和儲存延遲。
這道題適合資料工程、後端和資料庫維運職位。PostgreSQL 18 release notes 擴展 EXPLAIN 的節點記憶體/磁碟資訊，並預設展示執行時的 buffer 存取細節；官方 EXPLAIN 文件定義估算行數、實際行數、loops、BUFFERS 與 ANALYZE 的含義。本文基於公開資料整理，不聲稱是公司真題。
面試官考察點
面試官關注你是否能把執行計畫讀成證據鏈，而不是看到某個耗時節點就盲目加索引。高品質回答會區分估算誤差與資源耗盡，解釋 shared hit/read/dirtied/written、排序/雜湊記憶體和 I/O 時序，並說明生產採樣、權限與回滾。
回答前需要釐清的問題
- 查詢是否可在唯讀副本或去識別資料上重放？
- 變慢是平均延遲、尾延遲，還是特定參數導致的計畫分化？
- 生產允許多大的
EXPLAIN ANALYZE額外執行成本？ - 是否有查詢指紋、統計資訊刷新和磁碟/快取監控可交叉驗證？
30 秒回答框架
「先保存生產參數和查詢指紋，在副本上執行 EXPLAIN (ANALYZE, BUFFERS, VERBOSE)，比較估算與實際行數、loops、buffer hit/read 和新版本的記憶體/磁碟欄位。若估算偏差大，檢查統計資訊；若 sort/hash 溢出，檢查工作記憶體與資料傾斜；若 read 高，結合 I/O 指標判斷快取或儲存問題。任何改動先在副本驗證，再灰度索引、統計資訊或參數，並監控尾延遲。」
分步驟深入解答
先固定樣本。記錄 SQL、繫結參數、計畫時間、執行時間、回傳行數和資料庫版本；同一 SQL 不同參數可能選擇不同計畫。EXPLAIN 預設只估算，ANALYZE 才執行真實語句，因此寫查詢必須在交易中回滾或使用唯讀副本，避免診斷改變資料。
讀計畫時先找估算行數與實際行數的數量級差異，再看 loops 放大後的總成本。BUFFERS 把存取拆成 shared hit、read、dirtied、written 等，hit 高不代表查詢一定快，read 還要結合資料量和儲存延遲。PostgreSQL 18 對更多節點補充記憶體與磁碟使用資訊，可幫助識別排序、視窗彙總、CTE 或 Materialize 的工作集。
若 sort/hash 節點使用磁碟，先確認是 workmem 不足、並行過高還是單次資料傾斜；不能簡單全域調大，因為 workmem 按算子和並行消耗。若 buffer read 多而 I/O 延遲高，檢查快取容量、表膨脹、索引選擇性和底層儲存；若 read 多但延遲低，可能只是冷快取，應透過穩定重放與多次採樣確認。
估算誤差通常指向過期統計資訊、相關欄位缺少擴展統計、參數敏感或資料分布變化。先用 ANALYZE、擴展統計或查詢改寫驗證，避免直接強制 join 順序。索引改動需比較寫放大、維護成本和覆蓋率；執行計畫變好不等於整體吞吐變好。
生產診斷設定採樣和權限邊界。限制 EXPLAIN ANALYZE 次數與並行，去識別字面量和結果，使用 pgstatstatements 聚合指紋。將計畫、記憶體、buffer 和 I/O 指標關聯到 p95/p99 延遲；變更採用 canary，出現鎖等待、記憶體壓力或尾延遲回歸立即撤銷。
高品質示範回答
我會先在唯讀副本固定參數重放，收集估算/實際行數、loops、buffer hit/read/dirtied/written 和 PostgreSQL 18 新增的節點記憶體/磁碟欄位。估算差異大先查統計資訊，sort/hash 磁碟溢出再分析 work_mem、並行與傾斜，read 高則結合快取和儲存延遲判斷。索引、統計或參數變更均先在副本驗證，再小流量灰度並觀察 p99、鎖等待、記憶體與 I/O。
常見錯誤
- 錯誤表現 → 生產主庫直接跑
EXPLAIN ANALYZE；失敗原因 → 會執行真實語句並增加負載或副作用；修正方法 → 唯讀副本、唯讀交易或安全回滾。 - 錯誤表現 → 看到 buffer read 就立刻加索引；失敗原因 → 可能是冷快取、統計偏差或儲存延遲；修正方法 → 與多次採樣和 I/O 指標交叉驗證。
- 錯誤表現 → 全域把
work_mem調到很大；失敗原因 → 每個算子和並行都會消耗記憶體；修正方法 → 估算並行預算，按工作階段或查詢灰度。 - 錯誤表現 → 只比較執行時間；失敗原因 → 忽略尾延遲、寫放大和計畫穩定性；修正方法 → 聯合 p99、資源指標和回歸樣本評估。
追問及應對
shared hit 很高，為什麼查詢仍然慢？
命中只說明頁面來自共享緩衝區，不代表 CPU、排序、鎖等待或算子處理成本低。要結合 loops、節點記憶體/磁碟、執行時間分布和等待事件判斷瓶頸。
為什麼不把 work_mem 設成實體記憶體的一半？
work_mem 可能被同一查詢的多個算子以及並行工作階段分別消耗，簡單按實體記憶體設定會造成峰值超額和 OOM。應按並行、算子數量、連線池和節點預算計算，並用監控驗證。
何時應該刷新統計資訊而不是改寫 SQL？
當估算行數長期偏離實際分布，且資料變化或相關欄位未被統計覆蓋時，先刷新統計資訊或增加擴展統計；若估算已準確而算子仍不合適，再考慮查詢改寫或索引。