執行計劃個人理解是一個“點”，“線”，“面”的問題，關系資料庫中執行計劃是一個同質化的物件，串聯起來還是比較容易掌握的，對于一條復雜的sql，所謂的點就是其中單個表的訪問方式，線是表之間的連接\驅動順序，面就是表與表之間的具體連接演算法以及中間結果在記憶體緩沖區中的處理（類似于bitmap scan，中間結果集的buffer處理等等），這樣一來，一個sql就的執行計劃就可以逐步拆解開來，可以逐個基于細節來分析，Postgresql的執行計劃，整體上看跟MySQL或者sqlserver都是差不多的，但Postgresql對執行計劃在細節上的描述還是很粗糙的，就索引的訪問形式來說：MySQL中有index 遍歷索引/range 索引范圍查找/ref 非唯一索引查找資料/eq_ref 非唯一索引查找資料，以及回表的標記；sqlserver中也存在著scan和seek是兩個完全不同的概念，以及明顯的“回表”標記，在postgresql執行計劃中是無法直接體現出來的，全部稱之為index scan（index only scan），這一點說實話是比不上MySQL或者sqlserver的，后兩者對執行計劃的描述都很細化，
1 執行計劃中的“點”
1.1 順序掃描Seq Scan
順序掃描實際上就是全表掃描，沒有任何可用索引或者不適合走索引的情況下的一種查詢行為
seq scan的圖形化示例
1.2 索引掃描IndexScan
通過訪問索引獲得元組位置指標后再訪問表資料本身，index scan實際上是包含了通過索引鍵值查找，然后“回表”的程序，這個執行計劃或者字面上，都沒有體現出來，
index scan的圖形化示例
1.3 Index Only Scan
如果索引scan不需要回表的話，執行計劃如果表達這種邏輯？對于 index scan，如果一個查詢不需要回表的場景，比如select c1 from table where c1 =100;查詢的列在索引中就可以直接得到，無需回到基表去得到其他欄位，這種執行計劃叫做Index Only Scan
相比index scan，index only scan就是去掉“回表”這個程序，僅在索引樹上就可以完成的查詢
1.4 位圖索引掃描Bitmap Index Scan
bitmap index scan的詳細介紹見這里：https://www.cnblogs.com/wy123/p/13376991.htmlbitmap的圖形化示例，其中包含了兩部分，第一是bitmap的生成程序，第二是多個bitmap之間的與（或）操作后排序，然后回表的程序，
1.5 預期的index only scan沒有出現
由于Index Only Scan表示僅需要索引就可以找到所需要的資料，無需回表，那么同樣在無需回表的情況下，postgresql如何區分對索引樹（b+）樹的查找（真正的二分法查找）和掃描（掃描整顆B+樹）？這個是一個有意思的問題，這里刻意創建一個抑制索引使用的但是無需回表的查詢： select c2 from myschema.table_test where c2+1 = 1001;，
看看會發生什么，這也是筆者在一開始想不明白的一個問題，它竟然總的是走了一個全表掃描？？？
這兩個問題雖然不完全相同，但還是有一些類似的地方，看來postgresql一些優化措施還是有進步空間的，說白了，官方就是說優化器不夠智能，無法識別類似情況，只能做全表掃描來實作，理論上說是可以index only scan完成的操作，為什么會出現seq scan？
其實這個并不難理解，當where條件時c+1 = 1001的時候，因為c+1 并不是一個直接可用的索引欄位，優化器并不知道這個運算式經過計算后可以轉換成一個索引欄位，因此會走全表掃描，至此，postgresql中僅從索引就可以得到查詢結果的情況下，如何區分對索引樹的二分法查找和索引樹掃描？除此之外，對于其他關系資料庫中的select count(1) from table陳述句的優化，往往可以在一個長度較小的欄位上建立一個索引，然后查詢就自動遍歷這個索引來獲取總行數的優化思路，在Postgresql中是行不通的，類似查詢Postgresql中并不會掃描一個較小的二級索引來實作count計算，而依舊走的是一個全面掃描，
這一點查閱相關資料說是與Postgresql的MVCC，事物可見性映射有關，也就是說在統計表中總行數的時候還要判斷遍歷的行數是不是對當前行可見的，參考這里，個人覺得其實并沒有說清楚，
類似select count(1) from table;沒有任何where條件下，默認一直是走seq scan table 的，究竟如何與事物的可見性關聯起來的？事務可見性以及MVCC，這一點還是比較有搞頭的，埋個坑先：因為事務的可見性只在資料行中標記，對索引是不生效的，難道說通過二級索引回表找到的記錄，都要進行一次可見性判斷？
https://www.postgresql.org/docs/9.4/index-scanning.html
https://www.postgresql.org/docs/current/storage-vm.html
2 執行計劃中的“線”
相對MySQL處理復雜sql能力相對較弱（被吐槽較多的子查詢，盡管MySQL一直在改進他的執行計劃演算法），不太適合相對復雜的sql查詢的場景（網傳淘寶禁止三張以上的表join），postgresql宣稱能夠處理復雜的sql查詢，其實都是其背后的演算法決定的，相比MySQL執行計劃連接路徑的貪心演算法，其最大的問題在于只關心區域，而不關心整體，可能每一步都是最優解，但最終可能不是最優解的情況，
postgresql采用動態規劃演算法和遺傳演算法結合起來生成執行計劃，理論上說postgresql的執行計劃生成演算法是更加優秀的，
類似圖的最短路徑演算法,比如從1到5的最短路徑： 1，對于貪心演算法來說，會走1-》2=》3=》5的路徑,當前的每一步都是最優解，整體上看并不是最優解，
2，對于動態規劃演算法來說，會走1=》4=》3=》5的路徑，其代價明顯優于貪心演算法的結果，
貪心算的問題潛在的問題很明顯，最終的解很可能不是最優的，盡管MySQL在這方面一直在改進，
對于動態規劃演算法可以遍歷所有路徑來獲取一個最短路徑，這種演算法在節點數超過一定程度之后的時間復雜度會呈指數級增長，因此postgresql也會采用折中一些的遺傳演算法來實作（類似遺傳基因改良程序，逐漸退化掉不好的部分）， 不管是貪心演算法，還是動態規劃演算法，遺傳演算法，其本身各有優缺點：
前者實作簡單，時間復雜度低，但存在非最優解的情況；后者盡管可以得到最優解，但是其時間復雜度要大于貪心演算法，
同時也不難理解，為什么MySQL發展至今中沒有執行計劃快取？
就是因為其在相對簡單的場景下，執行計劃的生成代價相對較小，因此考慮可以不快取執行計劃，可以臨時性編譯，貪心演算法同時也決定了MySQL不太適合處理相對復雜的sql查詢場景，其實這恰恰吻合了互聯網專案短平快的特點么，所以MySQL適合這一套，有點野路子的風格（話說mysql的出身就比較野路子，此處野路子是褒義詞），
而postgresql執行計劃的訪問路徑生成代價相對較高，對于復雜的sql查詢每次編譯代價相對較大，因此就保留了執行計劃快取從而達到可重用的目的（pg_prepared_statements），這是典型的學院派的風格（出身于書香門第，加州大學），
3 執行計劃中的“面”
3.1 join方式
這里的“面”是表與表之間的連接處理方式，其實就是經典的loop join，merge join，hash join這三種join方式，
postgresql中的三種join方式與其他資料庫的join在思路上并無二致，原理也很簡單，基本上都有各自適合的場景和前提條件，
3.1.1 loop join
適合處理兩個較小的結果集的場景，同時，盡管是較小的結果集，在有索引驅動的情況下loop join的效率也會相對較高，第二個圖例就代表著基于索引驅動的loop join
3.1.2 merge join
適合處理兩個有序結果集的場景，或者jion雙方本身存在一致的索引鍵
相比loop join只有outer表會前推，merge join在join的時候，outer和inner表同時有一個“前推”的程序，也就是說隨著join的進行，outer表的鍵對inner表的探測次數會越來越少，
要清楚，outer table和inner table的有序是merge join的因，而非果，
3.1.3，hash join
對于無索引且結果集較大的場景，屬于重量級的查詢處理，
其實平時不得見經常出現hash join，如果一個系統的查詢中經常出現hash join，也不見得是一件好事，在前面兩種足夠“輕量級”join方式處理不動時的一種選擇，
相比以上兩種join方式，hash join可能較為難理解一點：hash join簡單說分兩個階段，第一個階段是構建hash桶，對join雙方較小的一個表的連接鍵生成hash桶，第二個階段是對join的另外一張表的鍵值基于hash運算后進行探測，
為什么要這么做？其實還是跟“join條件上沒有索引有關”，相當于間接性地生成了一個hash索引，因此這種情況適合join雙方都變較大，且沒有索引的場景，
那么，為什么在重量級的join情況下為什么不加索引呢，所以上面也說了，經常看到hash join并不代表什么好現象，而是一種不得已的選擇，
并行查詢
并行查詢可以應用在絕大多數上述的點線面中
比如并行Seq Scan，并行Index Scan，并行join等等，其目的就是多個CPU協同作業，然后匯總的一種思路，這一點postgresql還是比較給力的，當然也不是并行執行緒數越多越好（max_parallel_workers），
強制查詢提示
查詢提示作為優化的debug作用，可以嘗試強制按照非默認的執行方式來對比，參考這里：https://blog.csdn.net/jackgo73/article/details/89711523
以上截圖這些有趣的圖片來自于：https://momjian.us/main/writings/pgsql/internalpics.pdf
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1164.html
標籤：PostgreSQL
上一篇：PostgreSQL筆記