楊向博-pg_hint_plan技術內幕——解鎖hint優化的核心原理.pdf

《楊向博-pg_hint_plan技術內幕——解鎖hint優化的核心原理.pdf》由會員分享，可在線閱讀，更多相關《楊向博-pg_hint_plan技術內幕——解鎖hint優化的核心原理.pdf（50頁珍藏版）》請在三個皮匠報告上搜索。

1、/2025.01.04/pg_hint_plan技術內幕-解鎖hint優化的核心原理楊向博PostgreSQL ACEhint優化的必要性為何選擇pg_hint_plan內核原理揭秘pg_hint_plan的工作機制總結與展望pg_hint_plan及執行計劃的未來CONTENThint優化的必要性/2025.01.04/hint優化的必要性CBO優化器避免不了cost預估偏差index scan run_cost:對于統計信息不準 -收集統計信息對于計算公式導致的偏差 -guc修正cost（不夠靈活）COST預估=1.統計信息&2.計算公式/2025.01.04/豐富的hint類型，靈活地與

2、SQL相結合hint優化的必要性內核原理揭秘/2025.01.04/pg_hint_plan配置在shared_preload_libraries參數中，Postmaster主進程起動時會加載對應的so，并注冊_PG_init函數中的自定義參數以及hook函數。當使用hint時，是通過hook函數修改plantree來改變執行計劃。接下來我們將主要分析scan method、join method、join order內核原理揭秘-工作機制/2025.01.04/在講具體hint之前，介紹下set guc影響執行計劃的原理。其實在內核中，是利用disable_cost來調整cost的計算。在c

3、ostsize.c中，可以看到所有method對應的cost計算邏輯。Costdisable_cost=1.0e10;以set enable_indexscan to off 為例：在cost_index中當enable_indexscan為false時startup_cost+=disable_cost（10000000000）這樣得到該indexscan的startup_cost會很大在后續優化器計算最小選擇路徑時，當前indexscan會被排除內核原理揭秘-disable_cost/2025.01.04/以IndexScan(table index.)為例：在set_rel_pathli

4、st時進入hook，pg_hint_plan處理rel的indexlist，只保留hint中指定的index，從list中delete其他unused index并將indexscan之外其他的scan method配置為disable_cost最后計算最小代價，確定最終的path因此，會存在一個現象。雖然hint中指定了使用某個index但最終沒走索引掃描，而走了順序掃描。因為hint指定index只是將表的其他索引從plantree中剔除只保留指定的這個索引，但走這個索引預估的代價不一定是最小。內核原理揭秘-Scan method/2025.01.04/如圖這個sql:使用IndexSca

5、n(dba_users dba_users_username_idx)這個hint，最終沒有走index，走了seqscan內核原理揭秘-Scan method/2025.01.04/ScanMethodHintParse:入參hint：IndexScan(dba_users dba_users_username_idx)內核原理揭秘-debug Scan method/2025.01.04/通過parse得到hint_keyword為HINT_KEYWORD_INDEXSCANhint-enforce_mask=2 即ENABLE_INDEXSCAN內核原理揭秘-debug Scan met

6、hod/2025.01.04/restrict_indexes:可以看到rel-indexlist的length為3，為dba_users表的3個索引內核原理揭秘-debug Scan method/2025.01.04/restrict_indexes:對indexlist的成員進行遍歷，比較是否和hint相同，第一個成員和hint相同，標記use_index=true內核原理揭秘-debug Scan method/2025.01.04/restrict_indexes:第二個成員和hint不同，將其append至unused_indexes list內核原理揭秘-debug Scan m

7、ethod/2025.01.04/restrict_indexes:第三個成員和hint不同，將其append至unused_indexes list內核原理揭秘-debug Scan method/2025.01.04/restrict_indexes:unused_indexes 有2個成員，rel-indexlist初始3個成員，循環遍歷rel-indexlist將unused_indexes 剔除，最終rel-indexlist保留了一個成員，即hintname指定的index內核原理揭秘-debug Scan method/2025.01.04/setup_scan_method_e

8、nforcement:hint-enforce_mask=2即enable_indexscan=on，將其余scan Method配置為off，設置disable_cost這樣就對于index_scan只保留了hint指定的index，以及將其余掃描方式startup_cost設置為disable_cost內核原理揭秘-debug Scan method進而去計算最小代價，確定最終的path/2025.01.04/set_rel_pathlist:經過set_cheapest(rel)確定最優path為SeqScan內核原理揭秘-debug Scan method/2025.01.04/以Me

9、rgeJoin(table table table.)為例：在make_rel_from_joinlist時進入hookset_join_config_options，將hint指定的enable_mergejoin 置為on，并將其他的scan method配置為disable_cost生成Mergejoin和Nestloop的joinpath最后計算最小代價，確定最終的joinpath內核原理揭秘-Join method/2025.01.04/sql默認走Nestloop使用hint/*+Mergejoin(du pu)*/可走Mergejoin內核原理揭秘-Join method/202

10、5.01.04/JoinMethodHintParse:經過parse hint_keyword為HINT_KEYWORD_MERGEJOIN，hint-enforce_mask=2 即enable_mergejoin內核原理揭秘-debug Join method/2025.01.04/set_join_config_options:enforce_mask=2 即set merge_join to on，set enable_nestloop to off，set enable_hashjoin to off即Nestloop和Hashjoin的startup_cost為disable_c

11、ost 內核原理揭秘-debug Join method/2025.01.04/add_paths_to_joinrel:這個函數會被調用兩次：rel1和rel2分別為innerrel，enble_mergejoin=on，因此為joinrel設置mergejoin的path內核原理揭秘-debug Join method/2025.01.04/add_paths_to_joinrel:mergejoin_allow總為true，為joinrel設置Nestloop的path內核原理揭秘-debug Join method/2025.01.04/add_paths_to_joinrel:ena

12、ble_hashjoin為false，不涉及Hashjoin的path內核原理揭秘-debug Join method/2025.01.04/pg_hint_plan_standard_join_search:由于Nestloop的startup_cost被設置為disable_cost，因此set_cheapest(rel)為T_MergePath內核原理揭秘-debug Join method/2025.01.04/以Leading()為例：在make_rel_from_joinlist時進入hookOuterInnerJoinCreate獲取Innerrel并將傳遞給hint-inner

13、_nrels,hint-inner_joinrelidsadd_paths_to_joiner_wraper會執行兩次，第一次rel2為inner，第二次rel1為inner；每次執行時比較join_hint-inner_joinrelids和innerrel-relids的bitmapset是否相同。若相同，說明和Leading hint相匹配，則set join_hint-enforce_mask也就是將選擇的join method置為on，對other join Method配置disable_cost若不同，則將所有join method的startup_cost 配置為disable

14、_cost最終set_cheapest(rel)計算最小代價，確定最終joinpath內核原理揭秘-Join order/2025.01.04/如圖SQL默認走Nestloop并且pu為inner使用Leading(pu du)指定du為inner內核原理揭秘-Join order/2025.01.04/transform_join_hints:leading hint為Leading(pu du)內核原理揭秘-debug Join order/2025.01.04/transform_join_hints:根據aliasname獲得表的relid，pu為2，du為1內核原理揭秘-debug

15、Join order/2025.01.04/OuterInnerJoinCreate:outerrel為pu，innerrel為du內核原理揭秘-debug Join order/2025.01.04/OuterInnerJoinCreate:hint-inner_nrels賦值為1，bitmapset為(b 1)內核原理揭秘-debug Join order/2025.01.04/populate_joinrel_with_paths:第一次調用add_paths_to_joinrel（由于宏定義實際將調用add_paths_to_joinrel_wrapper）將rel1以outer入參，

16、rel2以inner入參。rel1的bitmapset為(b 1)即du，rel2的bitmapset為(b 2)即pu內核原理揭秘-debug Join order/2025.01.04/add_paths_to_joinrel_wrapper:hint指定的inner的bitmapset為(b 1)即du，入參inner（rel2）的bitmapset為(b 2)即pu，兩者不匹配因此給所有join Method startup_cost賦值disable_cost內核原理揭秘-debug Join order/2025.01.04/add_paths_to_joinrel:enable_

17、mergejoin=false,不會生成Mergejoin對應的joinpath內核原理揭秘-debug Join order/2025.01.04/final_cost_nestloop:enble_nestloop=false但是默認永遠生成Nestloop joinpath，不過startup_cost被配置為disable_cost內核原理揭秘-debug Join order/2025.01.04/add_paths_to_joinrel:enable_hashjoin=false,不會生成Hashjoin對應的joinpath內核原理揭秘-debug Join order/2025

18、.01.04/populate_joinrel_with_paths:第二次調用add_paths_to_joinrel（由于宏定義實際將調用add_paths_to_joinrel_wrapper）將rel2以outer入參，rel1以inner入參。rel1的bitmapset為(b 1)即du，rel2的bitmapset為(b 2)即pu內核原理揭秘-debug Join order/2025.01.04/add_paths_to_joinrel_wrapper:hint指定的inner的bitmapset為(b 1)即du，入參inner（rel1）的bitmapset為(b 1)即

19、du，兩者匹配因此給除Nestloop 之外的join Method startup_cost賦值disable_cost內核原理揭秘-debug Join order/2025.01.04/add_paths_to_joinrel:enable_mergejoin和enable_hashjoin都為false，因此不會對這兩種join Method生成joinpath。enable_nestloop為true，startup_cost不會設置disable_cost內核原理揭秘-debug Join order/2025.01.04/final_cost_nestloop:enble_nes

20、tloop=true 經過計算startup_cost為970.59內核原理揭秘-debug Join order/2025.01.04/pg_hint_plan_standard_join_search:經過set_cheapest(rel)最小代價計算，確定最終的joinpath內核原理揭秘-debug Join order/2025.01.04/當不能修改sql加hint時，如果想綁定執行計劃可以將query_id及對應的hint文本寫入hint table；執行sql時通過SPI方式從hint table查詢到對應的hint，根據hint改變執行計劃 內核原理揭秘-hint table

21、/2025.01.04/如圖sql默認inner為pu，將query_id及hint寫入hint tableset pg_hint_plan.enable_hint_table to on(可以設置默認打開)執行計劃就按照hint指定du為inner內核原理揭秘-hint table總結與展望/2025.01.04/pg_hint_plan是通過hook函數，結合內核disable_cost 對 plantree進行修改，從而按預期指定執行計劃?；Ahint類型已完備，目前我們可以利用hint table共享執行計劃。hint table的處理邏輯可以有進一步提升。目前是通過SPI方式去查詢h

22、int table，并發場景下會存在性能隱患?？梢钥紤]使用專用的cache，就像其他系統表都是先訪問syscache去獲取表數據。hint只是矯正執行計劃的一種途徑，就是為了解決cbo優化器cost預估出現偏差，某些特殊場景下的性能劣化。對于執行計劃的矯正還有其他途徑，不防參考下Oracle已具備的能力。Oracle 8（1997年6月）：hintOracle 8i&9i（2001年6月）:stored outlineOracle 10g（2003年9月）:sql profileOracle 11g（2007年7月）:sql plan manangement、adaptive cursor s

23、haringOracle 12c（2013年6月）:sql plan manangement、adaptive cursor sharing、Adaptive Execution Plans總結與展望-pg_hint_plan及執行計劃的未來/2025.01.04/sql profile:是一個數據庫對象，其中包含特定于 SQL SQL 語句的輔助統計信息。從概念上講，SQL 配置文件對于 SQL 語句的作用相當于對象級統計信息對于表或索引的作用。在分析 SQL 語句時，SQL Tuning Advisor 使用一組特定的綁定值作為輸入。顧問將優化器估計值與對數據樣本執行語句片段所獲得的值進行

24、比較。當發現顯著差異時，SQL Tuning Advisor 將糾正措施捆綁在 SQL 配置文件中，然后建議采納。SQL SQL profileprofile中更正后的統計信息可以改善優化器costcost估計，從而幫助優化器選擇更好的計劃。與hint不同的是，使用 SQL profile時無需更改應用程序源代碼。數據庫對 SQL 配置文件的使用對用戶來說是透明的。不會綁定特定計劃或子計劃。SQL profile可修復不正確的預估，同時讓優化器能夠靈活地在不同情況下選擇最佳計劃。sql plan manangement:是一種預防機制，使優化器能夠自動管理執行計劃，確保數據庫僅使用已知或經過驗

25、證的計劃。SPM使用一種稱為SQL 計劃基線（SQL plan baseline）的機制，它是優化器允許對 SQL 語句使用的一組可接受的計劃。在此上下文中，計劃包括優化器重現執行計劃所需的所有計劃相關信息（例如，SQL 計劃標識符、hint集、綁定值和優化器環境）?；€實現為一組計劃行和重現計劃所需的大綱（outline）。outline是一組用于強制執行特定計劃的優化器hint。SPM主要組成部分如下：計劃捕獲：該組件存儲一組 SQL 語句的計劃的相關信息。方案選擇：該組件是優化器根據存儲的計劃歷史檢測計劃變化，并使用 SQL 計劃基線選擇適當的計劃，以避免潛在的性能回歸。計劃演變：此組件

26、是將新計劃添加到現有 SQL 計劃基線的過程，可以手動添加，也可以自動添加。在典型用例中，數據庫僅在驗證計劃執行良好后才將計劃接受到計劃基線中。SPM可防止因計劃變更而導致的性能下降?？偨Y與展望-pg_hint_plan及執行計劃的未來/2025.01.04/SQL profile和 SQL 計劃基線均可確保優化器僅使用最佳計劃，從而幫助提高 SQL 語句的性能。profile和基線都是使用hint在內部實現的。但是，這些機制存在顯著差異，包括以下內容：一般來說，SQL 計劃基線是主動的，而 SQL profile是被動的。通常，您會在出現嚴重性能問題之前創建 SQL 計劃基線。SQL 計劃基

27、線可防止優化器將來使用次優計劃。當您調用 SQL Tuning Advisor 時，數據庫會創建 SQL profile，通常只有在SQL 語句出現高負載癥狀后才會執行此操作。SQL profile的主要作用是持續解決導致計劃不理想的優化器錯誤。由于 SQL profile機制是被動的，因此它無法保證在發生劇烈數據庫變化時保持穩定的性能。SQL 計劃基線可以重現特定計劃，而 SQL profile可以更正優化器成本估算。SQL 計劃基線是一組已接受的計劃。每個計劃都使用一組大綱提示來實現，這些提示完全指定了特定的計劃。SQL 配置文件也是使用hint來實現的，但這些提示不指定任何特定的計劃。相

28、反，提示會糾正優化器估計中的錯誤計算，這些錯誤計算會導致計劃不理想。例如，hint可能會糾正表的基數估計。由于profile不會將優化器限制到任何一項計劃，因此 SQL profile比 SQL 計劃基線更靈活。例如，初始化參數和優化器統計信息的變化使優化器能夠選擇更好的計劃。Oracle建議使用 SQL Tuning Advisor。這樣，就可以遵循顧問對 SQL profile和計劃基線的建議，而不是嘗試確定哪種機制最適合每個 SQL 語句.so.so.期待PGPG執行計劃管理未來可以參考sqlsql profileprofile和SPMSPM這樣的能力，對執行計劃有更好的把控，減少一些突發性能劣化問題?？偨Y與展望-pg_hint_plan及執行計劃的未來THANK YOUhttps:/