Postgres 終於要做 query hint——pg_plan_advice 怎麼回應十五年的六條反對

讀完這篇你會知道：Postgres 擋了十五年的不是「讓人控制執行計畫」這個需求，而是「把控制寫成 SQL 註解」這個形式。pg_plan_advice 把需求與形式拆開——你仍可強迫 planner 用某個 index、join 順序、join 方法，但 advice 是一條獨立字串，掛在 GUC 或共享記憶體，query 一字不改。下面從 planner 為什麼 cost-based 講起，走過 advice 語法、六大反對如何被回應，最後落到「該不該開」。

一條查詢，planner 選錯了 join 順序——這是每個 DBA 都認得的場景

場景擺出來：你有一張大的 fact table f、兩張小的 dimension table d1、d2，寫了一條三表 join。EXPLAIN 出來，planner 先把 f 跟 d2 做 hash join、再 join d1，且對 f 走 sequential scan——一個上百萬列的全表掃描，外加兩層 hash：

EXPLAIN SELECT ... FROM f JOIN d1 USING (dim_id) JOIN d2 USING (dim2_id) WHERE ...;

 Hash Join  (cost=... rows=...)
   ->  Hash Join  (cost=... rows=...)
         ->  Seq Scan on f      // ← 全表掃描上百萬列
         ->  Hash
               ->  Seq Scan on d2
   ->  Hash
         ->  Seq Scan on d1

你心裡那個對的計畫，是先用 d1 的條件縮小範圍、對 f 上的 idx_fact_dim_id 走 index 變成 nested loop——掃的是幾百列。執行時間差兩三個數量級，但 cost 帳本上前者反而便宜，因為它的 row estimate 偏掉一個數量級。關鍵的不對稱在這：你知道 planner 錯了，是因為你掌握它沒有的領域知識——某個維度高度傾斜、d1 那個 filter 只命中極少數列，這些「資料的語意」不在統計直方圖裡。planner 估 join 成本靠單一 column 的 selectivity，當兩個 column 有隱性相關（如「城市」與「郵遞區號」）它假設獨立、把 selectivity 相乘，估出的列數小好幾個數量級。這不是 bug：cost model 算得完全正確，只是看不到那層相關性——你要做的是把你知道、它不知道的那點資訊餵回去。

過去十五年，面對這個場景使用者只有一堆「曲線救國」的 workaround。官方從不承認它們是 hint，但每一個都是在偷偷告訴 planner「別這樣做」。最常見的三招全是「借語意副作用達成計畫控制」的變體：

• SET enable_seqscan = off——給 sequential scan 加上天文數字的懲罰逼 planner 找 index。問題是它 session 全域，這條 query 想用 index、另一條小表卻該 seqscan，一刀切兩邊都受影響。
• OFFSET 0——加在子查詢後阻止 flatten，形成 optimization fence；能間接固定 join 邊界，但是副作用不是意圖。
• 把 CTE 物化（pre-12 WITH 預設就是 fence，12 之後要寫 MATERIALIZED）——同樣借語意副作用達成計畫控制。

這三招都不直接表達意圖：你想說「對 f 走 idx_fact_dim_id」，寫出來的卻是「關掉整個 session 的 seqscan」。Kevin Grittner 在 2010 那場辯論裡點破這個悖論：連最反對 hint 的人，都在用 enable_seqscan 這類開關偷偷強迫 index scan——這些其實就是「用別的名字偽裝的 hint」。需求一直都在、也一直被滿足，只是用最糟的形式。pg_plan_advice 的出發點不是「要不要給控制權」，而是「既然控制權早就在行使，能不能給它一個正派的表達形式」。

為什麼 Postgres 擋了十五年——cost-based planner 的立場與那場辯論

Postgres 的 planner 是 cost-based：列舉可行計畫，用統計估成本，挑最低的。它要做三個正交的決定，advice 的詞彙表正好對應這三個維度——join 順序（N 表的順序隨 N 階乘成長）、每個 join 的方法（nested loop / hash join / merge join）、每張表的讀取方式（sequential scan / index scan / bitmap scan）。

表多時 planner 從動態規劃窮舉切換成 GEQO（基因演算法），本來就不是找全域最優而是在預算內找夠好的，advice 縮小搜尋空間反而讓它更可能找到好計畫；前一節那個列數估錯在三個決定上都可能誤判，advice 讓你在任一維度覆寫它的選擇。這個信念在 95% 以上的 query 上是對的，pg_plan_advice 的挑戰是只服務剩下那 5%、又不鼓勵濫用——答案藏在「最後手段」定位與 graceful fallback：advice 難用到你不會隨手加，加錯了會乾淨退回而非製造災難。在這個信念下，Oracle 那種 /*+ INDEX(t idx) */ 被視為越權干預；社群列出六條反對 hint 的理由，pg_plan_advice 的設計就是在逐條拆解：

1. 維護負擔：hint 散落在成千上萬條 SQL 裡，沒人記得當初為什麼加、何時該移除。
2. 升級失效：hint 寫死了某個計畫，下一版 planner 變聰明了，這個 hint 反而擋住更好的計畫。
3. 掩蓋根因：加 hint 是治標，真正的問題（統計過期、缺 index、schema 設計）被一條 hint 蓋過去，沒人去修。
4. 規模化差：一兩條 hint 還行，幾千條 query 各自帶 hint，整個系統的行為變得無法整體推理。
5. optimizer 通常比你聰明：人類拍腦袋指定的計畫，在資料分布改變後往往比 cost-based 的選擇更糟。
6. 阻礙 planner 改進：使用者一旦能 hint，就不再回報 planner 的壞計畫，社群失去改進 optimizer 的訊號。

這六條不是稻草人，每一條在 Oracle / MySQL 都真實發生過：一條十年前加的 /*+ ORDERED */ 在資料量長大十倍後變成效能殺手卻沒人敢刪——這就是反對一、二的長相。問題從來不是「反對者太固執」，而是「能不能設計一個形式，得到 hint 的好處又不引入那些病」。2010 年那場 pgsql-performance 討論串把核心立場逐字留了下來：

Robert Haas：「We want hints, or at least many of us do. We just want them to actually work, and to not suck.」

重點在「actually work」（不能默默失效）與「not suck」（不引入那六條病）這兩個高標。pg_plan_advice 的每個決定——Disabled 而非默默忽略、約束而非命令、SQL 外而非 SQL 內——都像在逐條滿足這兩個形容詞。

Tom Lane：「I haven't seen a hinting scheme that didn't suck... I don't say there can't be one.」

被引用最多的是前半句，但 pg_plan_advice 真正接住的是後半句「我不是說不可能有」：那就做一個不爛的給你看。Josh Berkus 當年也給了一個優先序：遇到 planner 選錯，先調 GUC 參數、再設物件層級 cost 參數、再補統計 metadata 讓 planner 自己估準，hint 只能是窮盡這些後的最後手段。pg_plan_advice 走的正是這條路：advice 是 GUC、不進 SQL、定位成最後手段；你還是該先 ANALYZE、先建 extended statistics，advice 是這些都不夠時才上的工具。

有個漂亮的觀察：六條裡有四條（維護負擔、升級失效、規模化差、阻礙改進）的根源都是「hint 嵌在 SQL 裡」，把 advice 移到 SQL 外面集中存放這四條就一起鬆動；剩下兩條（掩蓋根因、optimizer 較聰明）是 hint 行為本身固有的，只能用 graceful fallback 加可稽核性緩解。下面這個 tab widget 把六條逐條攤開。

advice 是什麼——一條從 SQL 外面約束 planner 的字串

advice 是一條字串，描述「我要 planner 在搜尋計畫時遵守哪些約束」，掛在 query 外面。「約束」是關鍵：命令是「你必須這樣做」、做不到就報錯或硬幹爛結果，約束是「你的選擇必須落在這個範圍內」、planner 仍在選只是空間被縮小。SEQ_SCAN(f) 不是命令 planner 對 f 做 seqscan，而是約束它「只考慮 seqscan」，其餘自由度照樣用 cost 去選。你沒取代 optimizer，只給了它額外的邊界條件——這也解釋了為什麼無法滿足的 advice 可以乾淨退回。

最小的形狀是設一個 GUC：

SET pg_plan_advice.advice = 'JOIN_ORDER(a (b c) d)';

這條 advice 說：先 join a，再 join「b 與 c 的結果」，最後 join d，括號表示 join 的群組結構。advice 字串可以串接多個子句，每個約束計畫的一個面向。完整的詞彙表大致是：

• JOIN_ORDER(a (b c) d)——以圓括號嚴格指定 join 的順序與群組。另有 JOIN_ORDER(a {b c} d) 用大括號表示這組內部順序可由 planner 自由決定。
• HASH_JOIN(d)——某個 join 用 hash join，d 在 inner 側。HASH_JOIN((d1 d2)) 表示這個 join 的 inner 側是 d1、d2 多個關聯。
• NESTED_LOOP_PLAIN(f)——用 nested loop。
• INDEX_SCAN(f idx_fact_dim_id)——對 alias f 走 index scan，並指定用哪個 index。
• SEQ_SCAN(f)——對 f 強制 sequential scan。
• DO_NOT_SCAN——把某表排除在計畫之外。
• GATHER / GATHER_MERGE / NO_GATHER(f d)——控制平行查詢的 Gather 節點。

詞彙表一對一映射到 planner 的三個決定維度（join 順序、join 方法、scan 方法）外加平行控制。advice 用 alias 而非 table 名指認目標，因為同一張 table 在 self-join 裡可能出現多次、用 table 名無法區分 instance。把幾條串起來就描述了一整個計畫的骨架：

SET pg_plan_advice.advice =
  'JOIN_ORDER(f d1 d2) HASH_JOIN(d1 d2) SEQ_SCAN(f) INDEX_SCAN(d1 idx_d1_pk)';

這跟 Oracle 風格的差別在位置。pg_hint_plan 借用 Oracle 把 hint 寫進 SQL 註解，/*+ HashJoin(d1 d2) */ 直接黏在 query 文字裡；pg_plan_advice 把同樣的表達力搬到 SQL 外面，query 文字保持乾淨。搬到 SQL 外不就把資訊藏起來嗎？答案是 EXPLAIN (PLAN_ADVICE)——任何時候都能對一條 query 跑它，看到哪些 advice 在作用、命中狀態如何。下面這張圖把 advice 字串的解剖攤開。

幾個語法細節值得記。JOIN_ORDER 的圓括號 (b c) 表示「這個子樹的結構固定」，大括號 {b c} 表示「這組必須一起 join、但組內順序 planner 自己挑」。advice 還可以用 dollar quoting 避免引號逃逸：$$JOIN_ORDER(f d1 d2)$$；開了 pg_plan_advice.feedback_warnings = true，無法滿足的 advice 會用 WARNING 講出來而非默默吞掉。

把 advice 貼到計畫樹上——哪個子句約束哪個節點，又在哪裡退回

EXPLAIN 印出來的縮排結構本質是一棵樹：葉節點是對單張表的 scan，內部節點是 join。advice 每一類子句作用在不同層級：SEQ_SCAN / INDEX_SCAN 約束葉節點怎麼讀，HASH_JOIN / NESTED_LOOP_PLAIN 約束內部節點怎麼算，JOIN_ORDER 約束整棵樹的形狀。下面這個 widget 是一棵 query plan tree：點任一節點，看哪一條 advice 子句約束了它，以及約束無法滿足時這個節點怎麼被標成 Disabled。

頂層 Join → d2 · 被 JOIN_ORDER 約束

JOIN_ORDER(f d1 d2) 把 d2 釘在最外層 join，planner 不能把它提前。這一條約束的是樹的形狀，不是任何單一節點的演算法；即使 d2 上有等值條件讓提前 join 更便宜，planner 仍須遵守順序——除非該 advice 無法滿足而被標 Disabled。

Hash Join · 被 HASH_JOIN 約束

HASH_JOIN(d1 d2) 指定這個 join 用 hash join，inner 側是 d1 / d2 的關聯。約束的是這個節點的演算法。

若估計記憶體不足以 hash，傳統上 planner 會自己改用 merge / nested loop；有了 advice，它會優先嘗試滿足 hash，無法滿足才退回並標記。

Scan f · 被 SEQ_SCAN 或 INDEX_SCAN 約束

這是場景裡的關鍵節點。原本 planner 對 f 選 sequential scan；INDEX_SCAN(f idx_fact_dim_id) 強迫它走指定 index。約束的是葉節點的讀取方式。

若 idx_fact_dim_id 不存在或不適用（例如 query 沒有可用的 index 條件），這條 advice 無法滿足，節點被標 Disabled，planner 退回 seqscan——graceful fallback。

Scan d1 · 被 INDEX_SCAN 指名

INDEX_SCAN(d1 idx_d1_pk) 不只說「走 index」，還指名走哪一個。當一張表有多個可用 index，這條把選擇權從 cost model 手上拿過來。

指名一個語意上不對的 index，advice 一樣會被判 inapplicable 而退回——advice 是約束，不是無視正確性的命令。

Scan d2 · 未被 advice 涵蓋

advice 字串裡沒有提到 d2 的 scan 方法，所以這個節點完全由 planner 自由決定。這正是 advice 的「部分約束」性質：你只釘住你在意的維度，其餘交給 cost-based optimizer，EXPLAIN (PLAN_ADVICE) 會把它標成 not matched。

比較 Scan f（被 advice 覆寫）跟 Scan d2（沒談到）兩格，就看到「部分約束」的精髓：你只需釘住確定 planner 估錯的那幾個節點。注意關鍵字 Disabled——這是跟舊式 hint 最重要的差異：傳統 hint 寫 /*+ INDEX(t idx) */ 但 index 用不上時 Oracle 要嘛默默忽略、要嘛硬走爛計畫；pg_plan_advice 走第三條路，無法滿足的子句被標成 Disabled，planner 退回剩餘約束下的最佳計畫，而 EXPLAIN (PLAN_ADVICE) 把這個狀態講給你聽。這也讓 advice 跨環境共用安全：搬到 production 萬一少了某 index，最壞只是部分 advice 退回預設。

EXPLAIN 的輸出會在計畫旁邊註記每條 advice 的命中狀態：

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT ... FROM f JOIN d1 ... JOIN d2 ...;

/* matched */                          // advice 命中且生效
/* matched, inapplicable, failed */    // 命中目標但無法套用（如 index 不存在）
/* not matched */                      // advice 提到的目標不在這條 query
/* matched, conflicting, failed */     // 兩條 advice 互相矛盾
/* failed */                           // 最終計畫沒有遵守這條 advice

這套註記是判斷「advice 生不生效」的唯一可靠依據。matched 是你想看到的；not matched 是中性的（advice 寫了某 alias 但 query 沒有）。真正要警覺的是帶 failed 的兩種：matched, inapplicable, failed（找到目標但無法滿足，最典型是 INDEX_SCAN(f some_idx) 那個 index 不存在）與 matched, conflicting, failed（兩條 advice 互相打架），兩種都會讓對應節點被標 Disabled。

下面這個 widget 讓你親手玩 graceful fallback：拖動「row estimate 偏差」slider，看 planner 的選擇何時與 advice 一致、何時被覆寫、何時變成 Disabled 退回。這條 slider 掃的是 planner 唯一真正會錯的東西——它對中間結果列數的估計。

row estimate 偏差倍數 = ×1.0

那麼 advice 字串本身從哪來？你不必手寫。EXPLAIN (PLAN_ADVICE) 還能把目前這個計畫逆向描述成 advice 字串。第一步，用傳統手段逼出好計畫，例如暫時 SET enable_seqscan = off；第二步，對這條 query 跑 EXPLAIN (PLAN_ADVICE) 導出 advice：

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT ... FROM f JOIN d1 USING (dim_id) JOIN d2 USING (dim2_id);

                   QUERY PLAN
------------------------------------------------------
 Nested Loop
   ->  Index Scan using idx_fact_dim_id on f
   ->  ...
 Plan advice:
   JOIN_ORDER(f d1 d2) NESTED_LOOP_PLAIN(f)
   INDEX_SCAN(f idx_fact_dim_id)

第三步，把 enable_seqscan 設回 on，把導出的 advice 貼進 pg_plan_advice.advice（或存進 stash）讓它持久。現在這條 query 不關閉 seqscan 也仍走你要的計畫，而你動的不是 SQL 是 advice。EXPLAIN 只輸出最小約束集；反過來也能拿它當計畫快照：升級前導出、升級後再導一次 diff，就是一種輕量的「planner 回歸檢測」。

pg_stash_advice、query-id 與跨重啟保留——advice 怎麼從一次性變成基礎設施

GUC 版的 advice 生命週期跟 session 一樣短，且套用到 session 裡的每一條提到該 alias 的 query，粒度太粗。pg_stash_advice 補的就是這兩段：把 advice 變成持久、可跨重啟的基礎設施，同時把套用粒度收細到「特定 query-id」。GUC 版適合「探索」（在 psql 反覆試、看 EXPLAIN），stash 版適合「固化」（探索定案後釘進 stash 永久生效）。

它的機制建立在 query-id 上：Postgres 對每條 query 算一個跟動態參數無關的 hash（算 hash 前把常數參數化掉），所以 WHERE x = 1 跟 WHERE x = 999 算出同一個 query-id；pg_stash_advice 就是一張 query-id → advice 的表，存在共享記憶體裡、每個 backend 在 planning 時直接查、幾乎零成本。這跟 pg_stat_statements 是同一把 hash：你在那裡看到某條 query 的 queryid 很慢，直接拿那個 id 去 stash 掛 advice 就對得起來。用起來三步，先建一塊命名的 stash：

CREATE EXTENSION pg_stash_advice;
SELECT pg_create_advice_stash('production_tuning');

然後對某個 query-id 寫入 advice：

SELECT pg_set_stashed_advice(
    'production_tuning',
    5424487836266966148,
    'INDEX_SCAN(f idx_fact_dim_id) NESTED_LOOP_PLAIN(f)'
);

那串 5424487836266966148 就是 query-id——一個 64-bit 的 hash。最後把 stash 啟用到某個範圍：

SET pg_stash_advice.stash_name = 'production_tuning';
ALTER DATABASE mydb SET pg_stash_advice.stash_name = 'production_tuning';
ALTER ROLE reporting_user SET pg_stash_advice.stash_name = 'production_tuning';

第二、三行是關鍵——你可以讓整個 database 或某個特定角色自動套用一份 advice 集合，application code 一行都不改。這在讀寫分離架構裡特別有用：OLTP 走 app_user 讓 planner 自由發揮，分析報表走 reporting_user 跑容易估錯的大 join，你只在 reporting_user 上掛 stash、OLTP 路徑完全不受影響——舊世界你得在 application 層 SET enable_seqscan = off 且那還是 session 全域的。持久化由 pg_stash_advice.persist = on（預設）負責，advice 寫到磁碟撐過重啟，把計畫控制變成一份集中、可稽核、可版控的 stash，正面回應「規模化差」那條反對。

「與動態參數無關」是雙面的。好處是一條 advice 自動套用到所有參數變體；風險是如果最佳計畫其實隨參數值而變（x = 1 該走 index、x = 999 該走 seqscan），一條 by-query-id 的 advice 就把兩種情況綁死——這正是「optimizer 通常比你聰明」那條反對的具體形態（Postgres 自己的 generic vs custom plan 會自適應，by-query-id advice 等於繞過它）。所以掛 advice 前，先確認最佳計畫對所有合理參數值都一致。

該不該開——它換來什麼、代價是什麼、跟 pg_hint_plan 怎麼選

pg_plan_advice 是 contrib 模組、不是核心預設開啟，所以「開不開」是個明確選擇。planner 在絕大多數 query 上表現良好，advice 是為那 1% 估錯的 query 準備的。真正的問題不是「要不要裝」，而是「我手上有沒有那種 planner 反覆估錯、又無法靠 ANALYZE 或 extended statistics 修好的 query」——有，它值得；沒有，先別急。下面這張表把它跟既有的兩條路並排：

click column header to sort · 4 columns × 5 rows

維度	pg_plan_advice (PG19)	pg_hint_plan (3rd party)	舊式 GUC / 副作用
控制寫在哪	SQL 外面（GUC / stash）	SQL 註解 /*+ ... */	session GUC / OFFSET 0 等副作用
粒度	per-query-id，可精準到單條	per-statement	session 全域，一刀切
無法滿足時	標 Disabled、graceful 退回	多半默默忽略	不適用（本就是粗工具）
持久 / 跨重啟	stash 寫磁碟，撐過重啟	隨 SQL 走，跟 code 綁死	session 結束即失效
可否自動產生	EXPLAIN (PLAN_ADVICE) 產 advice	需人工撰寫	需人工試錯

先把運作邊界釐清。advice 是約束、不是目標函數：你只能表達「join 順序是這個、scan 方法是那個」，不能表達「我希望這個計畫便宜一點」；而且不保證被遵守——每一條都可能被標 Disabled。由此引出最重要的實務紀律：把關鍵 query 的 advice 命中狀態納入測試，在 CI 跑 EXPLAIN (PLAN_ADVICE) 並 assert 輸出裡沒有 failed，這樣有人改 schema、刪 index、或重命名 alias 導致 advice 默默退回時測試會紅。新手最常踩的坑就是 alias：INDEX_SCAN(f idx) 裡的 f 是 alias 不是 table 名（FROM fact AS f 就寫 f），寫錯得到的是 not matched——不報錯也沒生效。再開 feedback_warnings 讓退回的 advice 在 log 留痕、讓 pipeline 對「advice failed」告警。

它沒換來的正好對應六條反對裡無法用「位置」解決的兩條。第一，它沒讓「掩蓋根因」消失——advice 仍是治標，發現某 query 需要 advice 該去查為什麼 planner 估錯（統計過期就 ANALYZE、column 有相關性就建 extended statistics CREATE STATISTICS）；差別在於那份 stash 清單本身就是你的待辦清單，變長就是警訊。第二，它沒讓「optimizer 通常比你聰明」失效——你釘住一個計畫等於賭你此刻比 cost model 懂這條 query，分布漂移後可能輸；graceful Disabled 救得了「index 消失」的硬失效，救不了「advice 仍可滿足、但已不再是好計畫」的軟退化，所以 advice 需要定期複查、且應該少而精，當手術刀用在那幾條 planner 確實估錯的 query 上，而不是「幾百條各掛一條」那套「沒人敢刪的計畫債」。

至於跟 pg_hint_plan 怎麼選，四個差異：in-tree 與否（pg_hint_plan 是第三方 extension，release 必須追著每個 Postgres 大版本跑，過去很多團隊延後升級的真實理由；pg_plan_advice 是 contrib，跟著本體一起升）；污染 SQL 與否（pg_hint_plan 把 hint 寫進 SQL 註解、改 hint 要動 code，pg_plan_advice 改 advice 是改一筆 stash，對「SQL 埋在 ORM 生成字串裡碰不到」這種處境是唯一可行的路）；失敗行為（pg_hint_plan 多半默默忽略，pg_plan_advice 把命中狀態做成 first-class 的 EXPLAIN 輸出與可選 WARNING）；能否自動產生（pg_hint_plan 靠人工撰寫，pg_plan_advice 的 EXPLAIN (PLAN_ADVICE) 能把任何現有計畫導成 advice）。

結論不複雜：已重度依賴 pg_hint_plan 且運作良好就沒有非遷不可的理由；但要從零建立計畫控制，pg_plan_advice 是更對的起點——in-tree 的 contrib、advice 不污染 SQL、無法滿足時退得乾淨，而且 EXPLAIN (PLAN_ADVICE) 能替你產出初版 advice。Tom Lane 當年那句「我不是說不可能有不爛的 hinting scheme」，pg_plan_advice 的回答是把 hint 從 SQL 裡搬出來、加一條 graceful 退路、再讓 EXPLAIN 自己產生它。

Take-away：query hint 之爭從來不是「能不能控制 planner」，而是「控制放在哪、出錯怎麼退」——pg_plan_advice 把控制搬到 SQL 外面、給無法滿足的 advice 一條標記 Disabled 的退路，於是十五年前那個「要 hint、但要能用、且不能爛」的願望，第一次有了不爛的形狀。