一條慢查詢把我卡了一個下午一條明明走了索引的SQL在數據量漲到千萬級之后突然從幾十毫秒變成幾秒。當時第一反應是統計信息過期了跑完ANALYZE發現沒用索引損壞重建一遍還是沒用。最后用EXPLAIN (ANALYZE, BUFFERS) 一看執行計劃發現優化器選了一個完全出乎意料的Join順序。這事之后我下定決心把PostgreSQL的執行過程徹底啃了一遍才發現網上大多數講執行過程的文章都停在解析-分析-重寫-優化-執行這個流程圖上到了優化器內部就含糊帶過。這篇是執行過程系列的第二篇我打算把優化器到底怎么選執行計劃、執行器每個節點實際在做什么、以及EXPLAIN里那些字段到底代表什么一五一十地拆開講清楚。先說清楚這篇要覆蓋的范圍。上一篇把SQL從文本變成執行計劃的整體流水線過了一遍解析、查詢分析、查詢重寫這些階段屬于前期加工不涉及真正的數據訪問。這篇聚焦兩個最關鍵的環節一個是優化器基于統計信息計算代價并選擇執行計劃的過程另一個是執行器按照計劃樹逐節點讀取和加工數據的細節。理解了這兩塊你才算真正看懂了EXPLAIN輸出遇到慢SQL也才談得上有系統性的排查思路。1. 優化器的決策黑箱從語法樹到執行計劃其實是一道數學題很多開發者對優化器的印象是它很智能能自動選最快的方式但它的本質不是什么人工智能而是一個基于統計信息和代價模型的打分系統。PostgreSQL的查詢優化器Planner做決策的核心邏輯只有一句話枚舉盡可能多的可行計劃算出每個計劃的預計代價選代價最小的那個。1.1 代價模型里的四個數字代表什么EXPLAIN輸出里每個節點都會附帶costxxx..yyy這個數字不是時間單位而是PostgreSQL內部定義的抽象代價單位。它由四個參數加權計算得出seq_page_cost順序頁讀取代價默認1.0表示讀取一個數據頁的平均代價random_page_cost隨機頁讀取代價默認4.0表示通過索引等隨機方式讀取一個頁的代價cpu_tuple_cost處理一行記錄的CPU代價默認0.01cpu_operator_cost執行一個操作符或函數的CPU代價默認0.0025算術上很簡單如果估算要掃描1000個數據頁、處理10000行記錄那最終代價大致就是 1000 * 1.0 10000 * 0.01 1100。實際計算比這個復雜會逐層累加每個節點的IO和CPU開銷但核心思路就是讀頁要花錢算行也要花錢。這個模型最值得注意的地方在于random_page_cost和seq_page_cost的比值。默認4比1意味著優化器默認認為隨機讀比順序讀慢4倍。這個假設在機械硬盤時代基本成立但在SSD上隨機讀和順序讀的差距已經縮小到幾乎沒有。如果你的數據庫跑在SSD上卻不調整這個參數優化器會傾向于選擇順序掃描而避開索引掃描這是很多明明有索引卻不走問題的根源之一。1.2 統計信息是優化器唯一的眼睛優化器的所有估算都建立在pg_statistic和pg_class里的統計信息上。pg_statistic存的是每一列的分布數據最常見值MCVMost Common Values、直方圖邊界、空值比例、平均寬度等pg_class.reltuples和relpages則記錄表的總行數和總頁數。這些統計信息是ANALYZE命令采集的它通過隨機采樣默認default_statistics_target為100即采樣30000行左右來推斷全表分布。這里有個很容易踩的坑分析采樣是隨機的不是全表掃描所以統計信息永遠只是近似值。如果某列數據分布嚴重傾斜比如一個值占了90%采樣可能恰好沒采到或采到過多導致優化器對選擇率的估算嚴重失真。我建議高傾斜的列把statistics_target調高比如ALTER TABLE t ALTER COLUMN status SET STATISTICS 1000;讓采樣的直方圖桶數更多分布刻畫更細致。代價是ANALYZE耗時增加但對幾千萬行的表來說這個代價完全值得。1.3 選擇率估算優化器怎么猜WHERE條件能過濾多少行執行計劃中每個節點的rows字段就是優化器對這個節點要輸出多少行的預測。這個預測決定了下游節點比如Join、Sort的代價所以它的準確性直接決定計劃的優劣。PostgreSQL對WHERE條件的行數估算主要依賴三種手段等值條件直接查pg_statistic里的MCV列表看該值是否在列表里。如果在直接用該值的頻率估算如果不在用查詢頻率最高的幾個值之后剩余概率除以總行數減去MCV覆蓋行數再用直方圖區間估算范圍條件、、BETWEEN用直方圖邊界histogram_bounds來估算位于某個區間的行數比例無統計信息可用時按一個經驗假設——默認選擇率為0.5%或0.33%這通常非常不準但也只能先這么兜底實際排查慢查詢時如果發現EXPLAIN里估算rows和實際actual rows偏差超過一個數量級基本就能認定統計信息失準或采樣沒覆蓋到關鍵值。修正手段有兩個重新ANALYZE或者調高統計目標。2. 執行計劃節點解剖掃描節點不是只有全表掃和索引掃兩種執行計劃是一棵從下往上執行的樹最底層的節點是掃描節點負責從表或索引中取數。PostgreSQL的掃描節點種類遠比初學者以為的多每種掃描適合的場景也完全不同。2.1 Seq Scan順序掃描為什么不是洪水猛獸Seq Scan是全表順序掃描從數據文件的第一頁一直讀到最后一頁。它的特點是IO完全順序預讀友好無論表多大每次讀取的頁都是相鄰的。在下面這幾種場景里Seq Scan其實是最優解表很小比如幾百頁以內全表掃描的成本低到可以忽略查詢需要返回表中大部分行比如超過5%到10%這時用索引反復隨機讀反而更貴統計信息顯示列分布很均勻優化器估算過濾后仍然有大量行需要返回對于順序掃描來說enable_seqscan這個參數的網絡資料經常讓人誤以為關掉它就能強制走索引。這個參數確實可以讓優化器調低順序掃描的優先級但它是按代價權重起作用的不是一票否決。更重要的是你關掉它只是治標不治本——真正的問題要么是統計信息不準要么是索引選擇不當要么是SQL寫法導致無法用索引。真靠關閉參數來優化數據量再漲上去計劃還是會崩。2.2 Index Scan 與 Index Only Scan回表與不回表的本質區別Index Scan先查索引拿到行的物理位置TID即頁號和行號再根據TID去數據文件里讀取那一行。這個過程叫回表。回表一次就要一次隨機IOrandom_page_cost就是為這個動作準備的代價。Index Only Scan則是索引覆蓋的場景查詢需要的所有列都包含在索引列里那么只需要掃描索引頁就能返回結果完全不用回表。判斷計劃里是否真的沒回表要關注Heap Fetches堆頁抓取次數這個字段——如果這個值很高說明VACUUM清理不及時可見性映射Visibility Map沒有標記對應頁為全可見優化器擔心元組可見性無法確認只能回表驗證Index Only Scan就名不副實了。這里有個實戰要點如果你建了一個(a, b)復合索引業務查詢經常只要a和b兩個列那你只要建立這個索引就能在大部分情況下走Index Only Scan。但如果你在WHERE里用了aSELECT出來還需要c列那就必須回表。要想徹底不回表可以把c做成索引列尤其是PostgreSQL 11支持了INCLUDE語法不影響索引本身的選擇性CREATE INDEX idx_t_a_b ON t (a) INCLUDE (b);INCLUDE列只存儲值不參與排序和搜索既享受了覆蓋索引的好處又不會因為多列參與索引查找而膨脹索引樹。2.3 Bitmap Index Scan多條索引的合并考量當查詢條件同時涉及兩個索引列并且各自的選擇率都不算太低時單獨走任何一個索引都要回表大量行PostgreSQL會改用Bitmap Index Scan。它的工作流程分兩步掃描索引把所有滿足條件的行的TID放進一個位圖Bitmap按物理順序從位圖中取出TID去表里讀對應的行這個流程的關鍵優化在于位圖本質上是把隨機IO變成了按物理存儲順序的準順序IO。雖然還是要回表但回表的順序經過重排減少了磁盤磁頭反復跳動的開銷。多個Bitmap還可以做交集AND或并集OR后再回表SELECT * FROM t WHERE a 1 AND b 2;如果a和b上分別有索引PostgreSQL可以掃描兩個索引生成兩個位圖做位圖交集再回表。這比優化器只挑一個索引回表多了很多性能優勢。位圖掃描有兩個前提條件work_mem足夠存放位圖結構否則會退化成Bitmap Heap Scan上的循環重復掃描每個索引頁都去多次以及表足夠大小表本身就不值得用位圖方案。3. 表連接策略的取舍邏輯嵌套循環、哈希連接、歸并連接何時勝出多表連接是SQL最復雜的部分也是優化器決策空間最大的地方。不少人以為連接方式只跟表大小有關實際上它是數據量索引情況內存設置數據分布綜合博弈的產物。3.1 Nested Loop別因為它最笨就小看它嵌套循環是最簡單的連接方式外層表Outer取一行就去內層表Inner找匹配行。時間復雜度是O(N*M)如果內層表有索引則退化為O(N * logM)。它適合的場景很明確外層表行數很少內層表有索引。比如一張用戶表users只有100行訂單表orders有1000萬行查某個用戶的訂單走Nested Loop Join外層掃user內層對每個user在orders上走索引查總共大概執行100次索引查找非常快。換成哈希連接或歸并連接光是構建哈希表或排序的開銷就遠超這個量。3.2 Hash Join無索引場景下的降維打擊哈希連接的原理是把內層表的連接列全部讀出來在內存里建一個哈希表然后外層表每來一行直接去哈希表里查找匹配項。它的時間復雜度是O(NM)建哈希表和查哈希表各一遍無論有沒有索引都一樣。哈希表的構建需要內存這部分內存來自work_mem。如果哈希表超過work_memPostgreSQL會把多余的批次落盤也就是把join分成多個批次batch每個批次分別構建哈希表、然后匹配。注意這個落盤的過程會顯著增加IO導致性能斷崖式下跌。EXPLAIN里可以看到Hash Join節點下的Buckets和Batches如果Batches大于1說明哈希表已經溢出到磁盤了需要調大work_mem。很多人有一個誤解加了enable_hashjoinoff會讓SQL變快。真實情況是如果配置合理整體哈希連接通常遠快于嵌套循環在幾千萬行大表上的表現關閉它往往只是把問題轉移改成歸并或嵌套循環后依然慢甚至更慢。3.3 Merge Join排序好的數據是它的主場歸并連接的前提是兩個表的連接列都已經排好序。然后兩邊各用一個指針從頭往后移動像拉鏈一樣一一配對。如果兩邊數據沒排序優化器會在節點上加Sort操作這時候總代價包括排序的開銷。歸并連接的適用場景是連接列已經有序比如連接列就是索引列索引天然有序數據量很大哈希表放不進內存查詢結果本身需要排序恰好可以利用排序結果而且歸并連接有它的獨特優勢連接結果默認就是有序的。如果你的查詢里有ORDER BY并且排序鍵和連接鍵一致那優化器可以省掉一個顯式的排序節點。3.4 連接順序為什么優化器也會選錯多表連接的順序組合是階乘級的。PostgreSQL為此限制了枚舉數量當連接表數量較少時用動態規劃窮舉所有連接順序超過geqo_threshold默認12后改用遺傳算法搜索次優解。所以實際查詢不要寫太多表關聯連接超過12個表優化器的搜索質量會肉眼可見地下降。這也是為什么那種一條SQL關聯15張表的報表查詢幾乎不可能性能好。與其把所有邏輯堆在一條SQL里不如拆成多條SQL讓應用層聚合。或者用PostgreSQL的WITH子句配合物化AS MATERIALIZED逐步縮小數據量但前提是每步都要有索引支撐。4. 執行器工作過程深入從EXPLAIN輸出看真實執行軌跡執行計劃生成之后執行器開始驅動整棵樹。執行器用的是標準的火山模型Volcano Model每個節點對外暴露一個next()函數上層節點每調用一次下層節點就返回一行元組。這樣一層一層地拉取和加工直到輸出最后的結果集。4.1 一次EXPLAIN實際執行的字段解讀如果你只是在SQL前面加上EXPLAIN看到的是優化器的估算值沒有真實執行信息。要看到執行器真正的運行情況需要在EXPLAIN后面跟上ANALYZEEXPLAIN (ANALYZE, BUFFERS) SELECT u.name, o.amount FROM users u JOIN orders o ON u.id o.user_id WHERE o.created_at 2024-01-01;輸出會分兩列第一列是優化器的估算值第二列actual time是每行真實的耗時單位也是毫秒。actual ... loopsN表示這個節點被循環執行了幾次。一個常見的誤讀是actual rows和actual time——actual time顯示的是該節點被調用的總耗時不是單行耗時。如果loops非常大actual time的累計值會很大這是正常現象不代表每行都慢。BUFFERS選項會把每個節點的緩存讀寫情況打印出來shared hit命中了共享緩沖區shared buffers沒有產生IOshared read從操作系統緩存或磁盤讀入共享緩沖區這里有IO開銷如果一個節點shared read非常高說明表或索引的數據不在內存里物理讀代價很大。這時候應該考慮增大shared_buffers或者用pg_prewarm預熱關鍵表。4.2 過濾條件下推先過濾還是先取數完全不一樣執行計劃里經常看到Filter和Index Cond兩個不同的字段這兩個容易混淆但含義差別很大。Index Cond表示的是索引搜索時用于定位的條件比如Index Cond: (created_at 2024-01-01::timestamp)這意味著優化器利用索引的B樹結構直接跳到第一個滿足條件的葉子節點只掃描滿足區間的部分。Filter則意味著索引幫不了你必須取出行之后逐行過濾。比如Filter: (status active)出現在索引掃描之后說明被索引定位到的行很多但能通過status過濾的只有一部分。如果Filter過濾掉的比例非常高比如一半以上說明這個索引本身沒有覆蓋到status列需要考慮建立復合索引讓過濾條件直接變成查詢條件的一部分。4.3 物化與CTE執行器如何處理WITH子句PostgreSQL 12之前WITH子句默認被當作物化邊界子查詢先執行完結果集落盤到臨時表然后再被外層查詢引用。PostgreSQL 12之后優化器默認可以內聯CTE但它的決策依賴對子查詢大小的估算。如果優化器估算CTE只會被執行一次就會內聯如果它認為子查詢可能被多次引用反而選擇物化一次。從執行器角度看物化節點Materialize是把下層節點的輸出緩存下來而不是每次重新計算。這在Nested Loop中特別重要因為內層表可能被子層循環多次掃描如果內層是計算量很大的子查詢物化一次就能省掉重復計算。這里有個常見的優化手段對只需要執行一次的WITH子查詢顯式加AS MATERIALIZED防止內聯導致的重復計算對只需要跑一次但內聯反而更快的子查詢加AS NOT MATERIALIZED讓優化器內聯。這兩個小關鍵字用好了能避開很多優化器判斷不準的坑。5. 實戰案例一次誤用JOIN導致的全表掃描排查全程理論部分講完用一個真實場景把上面的知識串起來。有一次生產庫突然CPU飆高慢日志里出現了一條之前一直正常的SQLSELECT o.order_no, c.customer_name FROM orders o LEFT JOIN customers c ON o.customer_id c.id WHERE o.paid_at 2024-03-01 AND o.paid_at 2024-04-01 ORDER BY o.paid_at DESC LIMIT 100;orders表5000萬行customers表200萬行兩個表的關聯鍵都有主鍵索引。按道理orders表上應該有paid_at的索引優化器應該用索引定位三月份的數據再逐條回表連customers。但EXPLAIN顯示優化器選擇了Seq Scan掃orders全表然后做Hash Join連接customers最后排序取100行。5.1 排查鏈路先看統計信息再看代價參數第一步用EXPLAIN (ANALYZE, BUFFERS) 跑了完整計劃后發現估算的三月訂單行數是15萬行實際只有8萬行估算偏差在一個數量級以內不算太離譜。那優化器為什么還選全表掃第二步檢查磁盤類型。這臺庫跑在SSD上但random_page_cost還是默認的4.0。我把訂單表按paid_at索引查了一遍數據分布發現三月份訂單占總體的5%左右按默認代價算索引掃描需要回表15萬次隨機讀代價被高估所以優化器選了全表順序掃。第三步把random_page_cost從4.0降到1.1重新EXPLAIN優化器立刻改選了Index Scan Nested Loop Join的計劃總代價下降近一半。5.2 修復效果與后續優化調整完random_page_cost之后SQL執行時間從2.8秒降到120毫秒CPU占用也恢復平穩。但這類調整要謹慎random_page_cost影響的是整個實例的所有查詢如果你的庫里既有SSD又有機械盤表空間就不能簡單地全局調整可以在表級別用ALTER TABLE ... SET (random_page_cost 1.1)做局部覆蓋或者對特定查詢用SET LOCAL在事務內改參數這個案例給到的一個核心教訓是當優化器的選擇和你的直覺不符時先別急著改SQL先檢查統計信息的準確性和代價參數是否匹配硬件特性。這兩個點是最容易出問題也最容易被忽略的。5.3 從執行計劃回推性能瓶頸的三步判斷法根據長期看執行計劃的經驗我總結了一套快速定位瓶頸的方法第一先看actual rows和rows的偏差。偏差超過10倍問題大概率出在統計信息或參數配置上而不是SQL本身。改正方法就是重新ANALYZE或調參而不是盲目加索引。第二看節點里耗時最多的環節。用EXPLAIN (ANALYZE, BUFFERS)跑完后actual time最大的節點就是瓶頸所在。如果瓶頸是Sort排序考慮是否真的需要排序或者能不能用索引排序替代如果是Seq Scan考慮索引是否合適。第三看Buffers的值確認瓶頸在內存還是IO。如果shared hit占比高而shared read很少說明數據已經在內存里瓶頸在CPU計算比如復雜的表達式如果shared read占比高說明IO是瓶頸需要優化IO層次的問題比如提高緩存命中率、優化索引減少訪問的頁數。這套方法適合90%以上的慢查詢排查場景。剩下那10%往往是查詢邏輯本身設計不合理——比如必須跨表關聯做聚合、子查詢嵌套過深、或者一個查詢里包含了太多計算邏輯。這類情況已經不是執行計劃調整能解決的需要從業務邏輯和SQL重構層面下手。6. 常見誤區和優化邊界為什么加了索引還是很慢索引不是萬能的執行計劃也不是越復雜越好。最后把實踐中最常見的幾個認知誤區拿出來挨個拆一遍。6.1 函數包裹索引列索引必然失效很多人寫SQL時不注意條件列的表達式形式比如WHERE DATE(create_time) 2024-01-01或者WHERE amount * 0.9 100。這類條件對優化器來說索引列的原始值被函數或運算改變了B樹索引的排序鍵不再直接可用。PostgreSQL有兩種解法使用WHERE create_time 2024-01-01 AND create_time 2024-01-02這種區間等效寫法如果必須用函數查詢可以建表達式索引Functional IndexCREATE INDEX idx_create_date ON t ((DATE(create_time)));6.2 復合索引列順序的坑最左前綴原則索引(a, b, c)可以支持WHERE a 1、WHERE a 1 AND b 2、以及WHERE a 1 AND b 2 AND c 3但不能支持WHERE b 2或WHERE c 3單獨使用。因為B樹首先按第一列排序第一列不定后面列的有序性就無法利用。但這里有個反直覺的情況PostgreSQL支持SKIP SCAN也就是松散索引掃描可以在某些條件下跳過不匹配的鍵值直接搜索后面的列。不過它只在特定場景能發揮作用不要指望隨時能用。6.3 并行查詢的邊界什么時候該開什么時候不該開PostgreSQL 9.6開始支持并行查詢并行度由max_parallel_workers_per_gather控制。并行計劃的運行邏輯是Gather節點把任務分發給多個并行worker每個worker獨立執行一部分掃描或計算最后匯總結果。并行查詢不是沒有代價的。每個worker都有啟動成本如果表很小或者結果集很小并行帶來的調度開銷可能超過收益。而且并行查詢通常需要work_mem按并行度分配比如work_mem64MB加4個并行worker每個worker可能分到64MB總內存消耗是256MB而不是64MB。這一點極易被忽視一旦并發查詢多起來內存很容易打滿。實際調優的經驗是小查詢不要并行大表的聚合、掃描、連接才開并行。具體閾值可以由parallel_setup_cost和parallel_tuple_cost來控制它們決定了優化器認為多少行才值得啟動并行worker。6.4 超大分頁查詢LIMIT偏移量的隱藏陷阱LIMIT 100 OFFSET 1000000這種寫法會讓執行器老老實實掃描并丟到前一百萬行再返回之后的100行。數據量大時這個操作會越來越慢。合理的替代方案有兩種鍵集分頁Keyset Pagination記錄上一頁最后一條數據的排序鍵下一頁查詢條件追加WHERE id 上一頁最后id ORDER BY id LIMIT 100延遲關聯子查詢里先用索引找出需要的ID再關聯回原表取完整行數據這個優化思路非常管用但要注意排序鍵必須唯一且有索引否則翻頁會漏數據或重復。7. 寫在最后理解執行過程后我的排障思路完全變了把PostgreSQL執行過程的這些細節真正吃透之后我的SQL調優習慣有了明顯轉變。以前遇到慢查詢第一反應是猜加索引改SQL實在不行就加pg_hint_plan強制走某個掃描方式。現在第一反應是打開EXPLAIN (ANALYZE, BUFFERS)看統計信息準不準看代價參數對不對先讓優化器的估算貼近現實再考慮改SQL和索引。絕大多數慢查詢優化器選錯計劃的原因歸根到底就兩個——統計信息失真或者代價模型和硬件不匹配把這兩件事做對至少能解決七成問題。還有一個體會是不要試圖用enable_seqscanoff這類開關來哄騙優化器。優化器做決策依據的是代價模型你關掉一個方案它就換另一個。真正健康和可持續的優化路徑永遠是——保證統計信息準確讓代價參數匹配物理環境然后設計合理的索引和查詢語句讓優化器有更多好計劃可選。這樣即使數據量再漲幾倍執行計劃依然大概率是可靠的。剩下的那些零星的優化器犯傻時刻用手動參數或pg_hint_plan去糾正都不遲但前提是你得先用EXPLAIN把問題徹底看懂。