題幹與適用情境
orders 資料表有 2 億筆資料，並持續承受每秒 3,000 次新增或狀態更新。全表約 2% 的訂單處於 pending，但不同租戶的占比有明顯差異。營運後台以每秒 40 次的頻率查詢某個租戶最近 30 天的待處理訂單，要求依建立時間由新到舊回傳前 50 筆：
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
AND status = 'pending'
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;資料表上已有 orders(tenantid) 和 orders(createdat) 兩個單欄 B-tree 索引。應用程式監控顯示該查詢的 p95 從 120 毫秒升到 2.8 秒，PostgreSQL 的 CPU、記憶體和連線數都未達容量上限。一次在正式環境規模副本上執行的計畫顯示：最佳化器估算掃描 8,000 筆，實際在排序前產生 420,000 筆，讀取或命中約 120,000 個共享緩衝區塊，最後用 top-N 排序取得 50 筆。
本題限定 PostgreSQL 18。資料表規模、吞吐量和計畫數字都是面試假設，用來驗證推導能否閉環。目標是改善這條高頻讀取，同時控制索引建立風險、儲存空間和寫入放大；分片、快取和硬體擴充暫不進入第一輪方案。
面試官考察點
第一項訊號是先確認負載，再討論 SQL。強回答會區分「單次最慢」和「累積消耗最大」：一個平均 80 毫秒、每秒呼叫 1,000 次的查詢，可能比偶發的 5 秒查詢更值得優先處理。pgstatstatements 提供呼叫次數、總執行時間和平均執行時間；p95 或租戶分位數仍要從應用程式監控或追蹤系統取得。
第二項訊號是能否讀懂執行計畫中的因果鏈。這裡的關鍵線索包括 8,000 與 420,000 的基數偏差、掃描節點輸出的資料筆數、每個節點的 loops、緩衝區塊讀寫、排序方式，以及篩選發生在索引條件還是掃描後的 Filter。只看到 Seq Scan 或「用了索引」都無法直接判定計畫好壞。
第三項訊號是從查詢形狀推導索引順序。tenantid 是等值條件，createdat 同時負責範圍篩選和排序；status='pending' 是固定且稀少的業務狀態。建議索引應讓資料庫定位一個租戶的待處理區間，沿時間順序讀取，並在找到 50 筆後提早停止。
最後看驗證和發布紀律。新增索引會占空間、消耗建立期間的 I/O，並增加新增及狀態變更成本。面試答案要給出正式環境規模資料上的前後計畫、冷熱快取、不同租戶、並行讀寫和回復門檻，不能把一次本機執行變快當成完成。
回答前需要釐清的問題
- 慢的是哪一個統計口徑？ 單租戶 p95、全域 p95、平均值和總資料庫時間會指向不同優先順序。還要確認退化從何時開始，是否對應資料成長、參數分布、發布或統計資訊變化。
- 待處理訂單占比和租戶分布如何？ 若
pending長期只占 1% 到 2%，部分索引很有吸引力；若它占一半，部分索引的尺寸優勢會縮小。大租戶與小租戶混在一個平均值裡，也會掩蓋參數傾斜。 - 查詢是否始終使用常值
status='pending'？ 部分索引只有在規劃階段能證明查詢條件蘊含索引述詞時才可用。把狀態寫成通用參數，可能讓規劃器無法證明匹配。 - 回傳欄位和讀取一致性要求是什麼？ 若還要回傳大段文字、JSON 或關聯十張資料表，涵蓋索引會快速膨脹。先確認這條列表查詢真正需要的欄位。
- 資料表有多熱，允許怎樣上線？ 每秒 3,000 次寫入代表索引寬度和狀態變更成本必須測量。線上通常考慮
CREATE INDEX CONCURRENTLY，同時預留更長建立時間、額外掃描和失敗後清理無效索引的操作窗口。 - 能否在正式環境規模副本執行實際計畫？
EXPLAIN ANALYZE會真正執行陳述式。即使是SELECT，也可能產生明顯負載；寫入陳述式還會執行副作用。應選擇副本、限定參數，或先使用不帶ANALYZE的計畫。
30 秒回答框架
「我會先用應用程式 p95 和 pgstatstatements 確認這條正規化查詢的呼叫量、總資料庫時間及慢租戶，排除鎖定等待和外部相依。接著在正式環境規模副本對代表性參數執行 EXPLAIN (ANALYZE, BUFFERS)，從實際筆數與估算筆數、迴圈次數、緩衝區塊和排序節點定位成本。本例兩個單欄索引仍要產生 42 萬筆候選資料再排序；查詢固定讀取稀少的 pending 狀態，我會先驗證部分涵蓋索引 (tenantid, createdat DESC) INCLUDE (id, totalcents) WHERE status='pending'。它能依租戶和時間直接取前 50 筆。若狀態必須參數化，就比較完整複合索引 (tenantid, status, created_at DESC)。最後用大中小租戶、冷熱快取和並行寫入驗證 p95、緩衝區讀取、索引大小與寫入延遲，再並行建立並保留回復門檻。」
分步深入解答
第一步：從真實工作負載決定優先順序
先把應用程式追蹤中的路由、租戶、參數範圍和 p95 對齊到資料庫的正規化查詢。若已啟用 pgstatstatements，可以先看累積資源消耗：
SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;totalexectime 適合找出大量呼叫累積的資料庫時間，meanexectime 適合找出單次昂貴查詢，calls 說明放大倍數。這個檢視表沒有 p95，也不會自動解釋某個租戶或參數為何更慢，因此還要保留應用程式端分位數和參數分組。若主要時間消耗在鎖定等待、連線排隊、網路或下游服務，單獨改執行計畫無法解決完整延遲。
第二步：安全取得實際執行證據
先用一般 EXPLAIN 查看計畫形狀，再在正式環境規模副本或受控環境執行：
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
AND status = 'pending'
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;閱讀計畫時，從最深且實際耗時高的節點往上走，並把 actual rows × loops 視為節點總工作量的一部分。Buffers: shared read 反映需要從儲存裝置讀取的區塊，shared hit 表示區塊已在共享緩衝區；命中仍會消耗 CPU 和記憶體頻寬。排序落盤時會看到外部排序及暫存區塊讀寫，通常代表輸入筆數或工作記憶體需要進一步分析。
題目中估算 8,000 筆、實際 420,000 筆，相差 52.5 倍。這個偏差可能來自統計資訊過舊，也可能來自 tenant_id 與 status 的相關性沒有被單欄統計表達。先執行合適範圍的 ANALYZE 並重新測試；若相關性穩定且確實影響計畫，可評估針對這組欄位的擴充統計。擴充統計也有收集和規劃成本，只為能改善關鍵估算的強相關欄位建立。
第三步：從查詢形狀推導索引
既有兩個單欄索引可能被 BitmapAnd 組合，但組合結果會失去單一 B-tree 的順序，仍要存取大量 heap 頁面並排序。規劃器也可能只選其中一個索引，再對另一條件進行篩選。兩個索引「都存在」只代表規劃器有候選路徑，不能保證候選路徑符合 WHERE + ORDER BY + LIMIT 的完整形狀。
針對固定且稀少的 pending 狀態，先比較部分索引：
CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';
CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);第一種只保存待處理訂單，索引通常較小；等值租戶定位後，createdat 同時限制 30 天範圍並提供倒序，規劃器可以在取得 50 筆後停止。id 和 totalcents 放入 INCLUDE，不參與搜尋或排序，只提供涵蓋讀取的可能。
第二種完整複合索引適合狀態由參數決定、多個狀態都需要同類查詢的情境。B-tree 的前導等值欄位先縮小範圍，後續時間欄位負責範圍和順序。不能機械套用「選擇性最高的欄位永遠放第一」；索引順序要由等值、範圍、排序和可重用查詢共同決定。
第四步：說清楚部分索引和涵蓋索引的邊界
部分索引只有在規劃時能證明查詢條件包含 status='pending' 才能使用。若通用預先編譯陳述式寫成 status = $2，該參數對所有可能值都不能保證符合索引述詞，規劃器可能放棄部分索引。可以為固定的營運查詢保留常值並獨立封裝，也可以選擇完整複合索引；決定要根據真實查詢樣板驗證，不能靠索引定義猜測。
INCLUDE 也不保證每次得到 Index Only Scan。PostgreSQL 仍要驗證 MVCC 可見性；頁面的 all-visible 位元未設定時，需要回 heap 檢查。頻繁新增和狀態更新的熱資料表會讓更多頁面需要 heap 存取。若計畫顯示 Heap Fetches 仍然很高，應評估移除承載欄位後的較窄索引是否更划算。寬索引還會增加磁碟、快取占用和每次寫入的維護成本。
第五步：驗證收益與代價
用同一組代表性參數比較前後計畫：超大租戶、中位租戶和小租戶；有大量 pending 的租戶和幾乎沒有 pending 的租戶；快取預熱前與預熱後。至少記錄執行時間分布、實際筆數、緩衝區塊、排序、暫存 I/O、Heap Fetches 和索引尺寸。一次執行的毫秒數容易受快取和並行影響，計畫工作量更容易解釋變化原因。
再以真實寫入比例壓測新增以及 pending → paid 狀態更新。部分索引在訂單完成時要刪除索引項目，完整複合索引則會更新狀態鍵；兩者都有成本。驗收門檻可以寫成：讀取 p95 回到目標以內，p99 沒有明顯退步；共享區塊與候選筆數顯著下降；寫入 p95、WAL 量、磁碟和複寫延遲仍在預算內。
上線時先確認磁碟餘量和並行建立監控。CREATE INDEX CONCURRENTLY 允許資料表繼續新增、更新和刪除，但耗時更長，也有失敗後留下無效索引等操作風險。索引建成後用正式查詢樣板確認實際採用，再觀察一個完整尖峰週期。若寫入延遲或複寫延遲超出門檻，先移除新查詢路徑，再按操作規範刪除新索引；保留舊索引，直到新方案通過穩定觀察期且確認沒有其他相依。
高品質示範回答
「我會先確認這條 SQL 值得優先處理。應用程式端提供 p95 和慢租戶，pgstatstatements 提供呼叫次數、總執行時間和平均執行時間。若它每秒 40 次且累積資料庫時間靠前，我會取得真實查詢樣板和幾組不同規模租戶，在正式環境規模副本執行 EXPLAIN (ANALYZE, BUFFERS)。
這份計畫的核心問題是掃描結果太寬：最佳化器估 8,000 筆，實際在 top-N 排序前有 420,000 筆，並觸碰約 120,000 個緩衝區塊。兩個單欄索引可能組合篩選，卻不能直接提供 tenantid + pending + createdat DESC 的有序窄區間。我會先更新統計資訊並重新測試；若租戶與狀態相關性讓估算持續偏差，再評估擴充統計。
因為營運查詢固定讀取稀少的 pending 狀態，我會在副本驗證部分索引 (tenantid, createdat DESC) INCLUDE (id, totalcents) WHERE status='pending'。它先限定索引集合，再依租戶進入時間有序區間，找到 50 筆即可停止。若應用程式要把狀態參數化並查詢多種狀態，我會比較完整複合索引 (tenantid, status, created_at DESC)。INCLUDE 只提供 Index Only Scan 的可能，熱頁面仍可能回 heap，所以我會同時看 Heap Fetches。
驗收涵蓋大中小租戶、冷熱快取和並行寫入。我會比較 p95/p99、候選筆數、緩衝區塊、暫存 I/O、索引尺寸、WAL、寫入延遲和複寫延遲。正式環境使用並行建索引，建成後確認真實樣板採用新計畫，並觀察完整尖峰週期。讀取收益不足或寫入路徑越界時撤回新路徑並刪除新索引，不用硬體擴充掩蓋未解釋的計畫問題。」
常見錯誤
- 看到慢 SQL 就加索引 → 沒有確認頻率、參數和等待類型，可能最佳化了低優先查詢 → 先關聯應用程式分位數、
pgstatstatements和真實參數。 - 把
Seq Scan一律判成錯誤 → 回傳大比例資料或小資料表時，循序掃描可能更便宜 → 比較實際筆數、緩衝區塊和替代計畫的總成本。 - 只看計畫是否出現 Index Scan → 索引掃描仍可能讀取數十萬個項目並大量回 heap → 檢查
actual rows × loops、Filter、Buffers 和 Heap Fetches。 - 忽略估算與實際差異 → 規劃器會根據錯誤基數選擇連接、掃描和排序方式 → 先更新統計，再為穩定相關欄位評估擴充統計。
- 認為多個單欄索引等同複合索引 → Bitmap 組合通常不能直接滿足所需排序，也可能存取大量 heap 頁面 → 依等值、範圍、順序和 LIMIT 推導複合鍵。
- 給所有欄位都加
INCLUDE→ 索引膨脹會降低快取效率並放大寫入成本 → 只涵蓋高頻查詢真正需要且寬度可控的欄位。 - 部分索引建成卻不驗證查詢樣板 → 參數化述詞可能無法在規劃時蘊含索引條件 → 用線上相同的預先編譯方式檢查實際計畫。
- 直接在主資料庫對任意 SQL 跑
EXPLAIN ANALYZE→ 命令會真正執行查詢，重查詢會製造負載，寫入陳述式還會產生副作用 → 先用一般 EXPLAIN，並在副本或受控交易中取得實際證據。 - 只報告一次執行從 2.8 秒降到某個數字 → 快取、參數和並行都可能造成偶然結果 → 比較分布、計畫工作量和完整尖峰週期。
追問及應對
追問一：為什麼循序掃描有時比索引掃描快？
查詢要讀取資料表中很大比例的資料時，循序讀取可以減少隨機存取；索引掃描還要走訪索引並回到分散的 heap 頁面。小資料表可能只占少量頁面，直接掃描也更便宜。應比較真實資料下的緩衝區塊和總時間，不把節點名稱當成評分。
追問二：為什麼 (tenantid) 和 (createdat) 兩個索引不夠？
規劃器可以選擇一個索引後篩選，也可以用 BitmapAnd 組合兩個索引。Bitmap 組合需要收集候選 tuple 位置，結果不保留 created_at 的 B-tree 順序，因此常常還要讀取大量 heap 頁面並排序。複合索引把等值租戶、時間範圍和排序放在同一條有序存取路徑中，LIMIT 50 才能提早停止。
追問三：部分索引為什麼偶爾完全不被使用？
查詢條件必須在規劃階段被識別為蘊含索引述詞。status='pending' 能直接匹配；status=$2 對任意參數都不能保證匹配。運算式寫法不同、資料分布改變或成本估算認為回 heap 更貴，也可能選擇其他計畫。應使用真實預先編譯樣板執行 EXPLAIN，並在需要通用狀態查詢時改用完整複合索引。
追問四：執行 ANALYZE 後估算仍差 50 倍怎麼辦？
先確認取樣覆蓋、欄位的統計目標和資料是否剛發生劇烈變化。若 tenant_id 與 status 強相關，單欄統計會把條件近似當成獨立事件，可以為這組欄位建立 dependencies 或 MCV 擴充統計再測試。擴充統計只改善估算，不會補上缺少的存取路徑；索引和查詢形狀仍要獨立驗證。
追問五：讀查詢變快，但寫入 p95 上升了，怎樣決策？
把收益和成本放回 SLO 與總負載：讀取省下多少資料庫時間，寫入退步影響多少使用者，WAL、磁碟和複寫延遲增加多少。若只有固定稀少狀態需要加速，較窄的部分索引可能比完整涵蓋索引更合適；若承載欄位造成膨脹，移除 INCLUDE 並接受少量回 heap。超過寫入預算時撤回索引方案，繼續從查詢範圍、分頁契約或資料模型尋找更小的存取路徑。