摘要： 孫子兵法雲：“謀定而後動，知止而有得”，做任何事一定要進行謀劃部署，做好準備，這樣才能利於這件事的成功，切不可莽撞而行。同樣，GaussDB（DWS）執行查詢語句也會按照預定的計劃來執行，給定硬體環境的情況下，執行的快慢全憑計劃的好壞，那麼一條查詢語句的計劃是如何制定的呢，本文將為大家解讀計劃生成中行數估算和路徑生成的奧祕。
本文分享自華為雲社群《GaussDB（DWS）計劃生成原理揭祕（一）》，原文作者：Jugg 。
GaussDB（DWS）優化器的計劃生成方法有兩種，一是動態規劃，二是遺傳演算法，前者是使用最多的方法，也是本系列文章重點介紹物件。一般來說，一條SQL語句經語法樹（ParseTree）生成特定結構的查詢樹（QueryTree）後，從QueryTree開始，才進入計劃生成的核心部分，其中有一些關鍵步驟：
- 設定初始並行度（Dop）
- 查詢重寫
- 估算基錶行數
- 估算關聯表（JoinRel）
- 路徑生成，生成最優Path
- 由最優Path建立用於執行的Plan節點
- 調整最優並行度
本文主要關注3、4、5，這些步驟對一個計劃生成影響比較大，其中主要涉及行數估算、路徑選擇方法和代價估算（或稱Cost估算），Cost估算是路徑選擇的依據，每個運算元對應一套模型，屬於較為獨立的部分，後續文章再講解。Plan Hint會在3、4、5等諸多步驟中穿插干擾計劃生成，其詳細的介紹讀者可參閱博文：GaussDB(DWS)效能調優系列實現篇六：十八般武藝Plan hint運用。
先看一個簡單的查詢語句：
select count(*) from t1 join t2 on t1.c2 = t2.c2 and t1.c1 > 100 and (t1.c3 is not null or t2.c3 is not null);
GaussDB（DWS）優化器給出的執行計劃如下：
postgres=# explain verbose select count(*) from t1 join t2 on t1.c2 = t2.c2 and t1.c1 > 100 and (t1.c3 is not null or t2.c3 is not null); QUERY PLAN -------------------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+--------------------------------------------------+--------+------------+----------+---------+--------- 1 | -> Aggregate | 1 | | | 8 | 111.23 2 | -> Streaming (type: GATHER) | 4 | | | 8 | 111.23 3 | -> Aggregate | 4 | | 1MB | 8 | 101.23 4 | -> Hash Join (5,7) | 3838 | | 1MB | 0 | 98.82 5 | -> Streaming(type: REDISTRIBUTE) | 1799 | 112 | 2MB | 10 | 46.38 6 | -> Seq Scan on test.t1 | 1799 | | 1MB | 10 | 9.25 7 | -> Hash | 1001 | 25 | 16MB | 8 | 32.95 8 | -> Streaming(type: REDISTRIBUTE) | 1001 | | 2MB | 8 | 32.95 9 | -> Seq Scan on test.t2 | 1001 | | 1MB | 8 | 4.50 Predicate Information (identified by plan id) ----------------------------------------------------------------- 4 --Hash Join (5,7) Hash Cond: (t1.c2 = t2.c2) Join Filter: ((t1.c3 IS NOT NULL) OR (t2.c3 IS NOT NULL)) 6 --Seq Scan on test.t1 Filter: (t1.c1 > 100)
通常一條查詢語句的Plan都是從基表開始，本例中基表t1有多個過濾條件，從計劃上看，部分條件下推到基表上了，部分條件沒有下推，那麼它的行數如何估出來的呢？我們首先從基表的行數估算開始。
一、基錶行數估算
如果基表上沒有過濾條件或者過濾條件無法下推到基表上，那麼基表的行數估算就是統計資訊中顯示的行數，不需要特殊處理。本節考慮下推到基表上的過濾條件，分單列和多列兩種情況。
1、單列過濾條件估算思想
基錶行數估算目前主要依賴於統計資訊，統計資訊是先於計劃生成由Analyze觸發收集的關於表的樣本資料的一些統計平均資訊，如t1表的部分統計資訊如下：
postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct, avg_width, most_common_vals, most_common_freqs from pg_stats where tablename = 't1'; tablename | attname | null_frac | n_distinct | n_dndistinct | avg_width | most_common_vals | most_common_freqs -----------+---------+-----------+------------+--------------+-----------+------------------+------------------- t1 | c1 | 0 | -.5 | -.5 | 4 | | t1 | c2 | 0 | -.25 | -.431535 | 4 | | t1 | c3 | .5 | 1 | 1 | 6 | {gauss} | {.5} t1 | c4 | .5 | 1 | 1 | 8 | {gaussdb} | {.5} (4 rows)
各欄位含義如下：
- null_frac：空值比例
- n_distinct：全域性distinct值，取值規則：正數時代表distinct值，負數時其絕對值代表distinct值與行數的比
- n_dndistinct：DN1上的distinct值，取值規則與n_distinct類似
- avg_width：該欄位的平均寬度
- most_common_vals：高頻值列表
- most_common_freqs：高頻值的佔比列表，與most_common_vals對應
從上面的統計資訊可大致判斷出具體的資料分佈，如t1.c1列，平均寬度是4，每個資料的平均重複度是2，且沒有空值，也沒有哪個值佔比明顯高於其他值，即most_common_vals（簡稱MCV）為空，這個也可以理解為資料基本分佈均勻，對於這些分佈均勻的資料，則分配一定量的桶，按等高方式劃分了這些資料，並記錄了每個桶的邊界，俗稱直方圖（Histogram），即每個桶中有等量的資料。
有了這些基本資訊後，基表的行數大致就可以估算了。如t1表上的過濾條件"t1.c1>100"，結合t1.c1列的均勻分佈特性和資料分佈的具體情況：
postgres=# select histogram_bounds from pg_stats where tablename = 't1' and attname = 'c1'; histogram_bounds ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------ {1,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550,560,570,580,590,600,610,62 0,630,640,650,660,670,680,690,700,710,720,730,740,750,760,770,780,790,800,810,820,830,840,850,860,870,880,890,900,910,920,930,940,950,960,970,980,990,1000} (1 row)
可知，t1.c1列的資料分佈在1~1000之間，而每兩個邊界中含有的資料量是大致相同的（這裡是根據樣本統計的統計邊界），先找到100在這個直方圖中的大概位置，在這裡它是某個桶的邊界（有時在桶的內部），那麼t1.c1>100的資料佔比大約就是邊界100之後的那些桶的數量的佔比，這裡的佔比也稱為選擇率，即經過這個條件後，被選中的資料佔比多少，因此由“t1.c >100“過濾之後的行數就可以估算出來了。
以上就是估算基錶行數的基本思想。一般地，
有統計資訊：
- 等值條件
1）對比MCV，如果滿足過濾條件，則選擇率（即most_common_freqs）累加；
2）對Histogram資料，按distinct值個數粗略估算選擇率； - 範圍條件
1）對比MCV資料，如果滿足過濾條件，則選擇率累加；
2）對Histogram資料，按邊界位置估算選擇率； - 不等值條件：可轉化為等值條件估算
無統計資訊：
- 等值條件：比如過濾條件是：“substr(c3, 1, 5) = 'gauss'”，c3列有統計資訊，但substr(c3, 1, 5)沒有統計資訊。那如何估算這個條件選擇率呢？一個簡單的思路是，如果substr(c3, 1, 5) 的distinct值已知的話，則可粗略假設每個distinct值的重複度一致，於是選擇率也可以估算出來；在GaussDB（DWS）中，可通過設定cost_model_version=1開啟表示式distinct值估算功能；
- 範圍條件：此時僅僅知道substr(c3, 1, 5)的distinct值是無法預估選擇率的，對於無法估算的表示式，可通過qual_num_distinct進行設定指定相應distinct值；
- 不等值條件：可轉化為等值條件估算
2. 多列過濾條件估算思想
比如t1表有兩個過濾條件：t1.c1 = 100 and t1.c3 = 'gauss'，那麼如何估算該兩列的綜合選擇率？在GaussDB（DWS）中，一般性方法有兩個：
僅有單列統計資訊
該情況下，首先按單列統計資訊計算每個過濾條件的選擇率，然後選擇一種方式來組合這些選擇率，選擇的方式可通過設定cost_param來指定。為何需要選擇組合方式呢？因為實際模型中，列與列之間是有一定相關性的，有的場景中相關性比較強，有的場景則比較弱，相關性的強弱決定了最後的行數。
該引數的意義和使用介紹可參考：GaussDB(DWS)效能調優系列實戰篇五：十八般武藝之路徑干預。
有多列組合統計資訊
如果過濾的組合列的組合統計資訊已經收集，則優化器會優先使用組合統計資訊來估算行數，估算的基本思想與單列一致，即將多列組合形式上看成“單列”，然後再拿多列的統計資訊來估算。
比如，多列統計資訊有：((c1, c2, c4))，((c1, c2))，雙括號表示一組多列統計資訊：
- 若條件是：c1 = 7 and c2 = 3 and c4 = 5，則使用((c1, c2, c4))
- 若條件是：c1 = 7 and c2 = 3，則使用((c1, c2))
- 若條件是：c1 = 7 and c2 = 3 and c5 = 6，則使用((c1, c2))
多列條件匹配多列統計資訊的總體原則是：
- 多列統計資訊的列組合需要被過濾條件的列組合包含；
- 所有滿足“條件1”的多列統計資訊中，選取“與過濾條件的列組合的交集最大“的那個多列統計資訊。
對於無法匹配多列統計資訊列的過濾條件，則使用單列統計資訊進行估算。
3. 值得注意的地方
- 目前使用多列統計資訊時，不支援範圍類條件；如果有多組多列條件，則每組多列條件的選擇率相乘作為整體的選擇率。
- 上面說的單列條件估算和多列條件估算，適用範圍是每個過濾條件中僅有表的一列，如果一個過濾條件是多列的組合，比如 “t1.c1 < t1.c2”，那麼一般而言單列統計資訊是無法估算的，因為單列統計資訊是相互獨立的，無法確定兩個獨立的統計資料是否來自一行。目前多列統計資訊機制也不支援基表上的過濾條件涉及多列的場景。
- 無法下推到基表的過濾條件，則不納入基錶行數估算的考慮範疇，如上述：t1.c3 is not null or t2.c3 is not null，該條件一般稱為JoinFilter，會在建立JoinRel時進行估算。
- 如果沒有統計資訊可用，那就給預設選擇率了。
二、JoinRel行數估算
基錶行數估算完，就可以進入表關聯階段的處理了。那麼要關聯兩個表，就需要一些資訊，如基錶行數、關聯之後的行數、關聯的方式選擇（也叫Path的選擇，請看下一節），然後在這些方式中選擇代價最小的，也稱之為最佳路徑。對於關聯條件的估算，也有單個條件和多個條件之分，優化器需要算出所有Join條件和JoinFilter的綜合選擇率，然後給出估算行數，先看單個關聯條件的選擇率如何估算。
1. 一組Join條件估算思想
與基表過濾條件估算行數類似，也是利用統計資訊來估算。比如上述SQL示例中的關聯條件：t1.c2 = t2.c2，先看t1.c2的統計資訊：
postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct, avg_width, most_common_vals, most_common_freqs from pg_stats where tablename = 't1' and attname = 'c2'; tablename | attname | null_frac | n_distinct | n_dndistinct | avg_width | most_common_vals | most_common_freqs -----------+---------+-----------+------------+--------------+-----------+------------------+------------------- t1 | c2 | 0 | -.25 | -.431535 | 4 | | (1 row)
t1.c2列沒有MCV值，平均每個distinct值大約重複4次且是均勻分佈，由於Histogram中保留的資料只是桶的邊界，並不是實際有哪些資料（重複收集統計資訊，這些邊界可能會有變化），那麼實際拿邊界值來與t2.c2進行比較不太實際，可能會產生比較大的誤差。此時我們堅信一點：“能關聯的列與列是有相同含義的，且資料是儘可能有重疊的”，也就是說，如果t1.c2列有500個distinct值，t2.c2列有100個distinct值，那麼這100個與500個會重疊100個，即distinct值小的會全部在distinct值大的那個表中出現。雖然這樣的假設有些苛刻，但很多時候與實際情況是較吻合的。回到本例，根據統計資訊，n_distinct顯示負值代表佔比，而t1表的估算行數是2000：
postgres=# select reltuples from pg_class where relname = 't1'; reltuples ----------- 2000 (1 row)
於是，t1.c2的distinct是0.25 * 2000 = 500，類似地，根據統計資訊，t2.c2的distinct是100：
postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct from pg_stats where tablename = 't2' and attname = 'c2'; tablename | attname | null_frac | n_distinct | n_dndistinct -----------+---------+-----------+------------+-------------- t2 | c2 | 0 | 100 | -.39834 (1 row)
那麼，t1.c2的distinct值是否可以直接用500呢？答案是不能。因為基表t1上還有個過濾條件"t1.c1 > 100"，當前關聯是發生在基表過濾條件之後的，估算的distinct應該是過濾條件之後的distinct有多少，不應是原始表上有多少。那麼此時可以採用各種假設模型來進行估算，比如幾個簡單模型：Poisson模型（假設t1.c1與t1.c2相關性很弱）或完全相關模型（假設t1.c1與t1.c2完全相關），不同模型得到的值會有差異，在本例中，"t1.c1 > 100"的選擇率是 8.995000e-01，則用不同模型得到的distinct值會有差異，如下：
- Poisson模型（相關性弱模型）：500 * (1.0 - exp(-2000 * 8.995000e-01 / 500)) = 486
- 完全相關模型：500 * 8.995000e-01 = 450
- 完全不相關模型：500 * (1.0 - pow(1.0 - 8.995000e-01, 2000 / 500)) = 499.9，該模型可由概率方法得到，感興趣讀者可自行嘗試推導
- 實際過濾後的distinct：500，即c2與c1列是不相關的
postgres=# select count(distinct c2) from t1 where c1 > 100; count ------- 500 (1 row)
估算過濾後t1.c2的distinct值，那麼"t1.c2 = t2.c2"的選擇率就可以估算出來了： 1 / distinct。
以上是任一表沒有MCV的情況，如果t1.c2和t2.c2都有MCV，那麼就先比較它們的MCV，因為MCV中的值都是有明確佔比的，直接累計匹配結果即可，然後再對Histogram中的值進行匹配。
2. 多組Join條件估算思想
表關聯含有多個Join條件時，與基表過濾條件估算類似，也有兩種思路，優先嚐試多列統計資訊進行選擇率估算。當無法使用多列統計資訊時，則使用單列統計資訊按照上述方法分別計算出每個Join條件的選擇率。那麼組合選擇率的方式也由引數cost_param控制，詳細參考GaussDB(DWS)效能調優系列實戰篇五：十八般武藝之路徑干預。
另外，以下是特殊情況的選擇率估算方式：
- 如果Join列是表示式，沒有統計資訊的話，則優化器會嘗試估算出distinct值，然後按沒有MCV的方式來進行估算；
- Left Join/Right Join需特殊考慮以下一邊補空另一邊全輸出的特點，以上模型進行適當的修改即可；
- 如果關聯條件是範圍類的比較，比如"t1.c2 < t2.c2"，則目前給預設選擇率：1 / 3；
3. JoinFilter的估算思想
兩表關聯時，如果基表上有一些無法下推的過濾條件，則一般會變成JoinFilter，即這些條件是在Join過程中進行過濾的，因此JoinFilter會影響到JoinRel的行數，但不會影響基表掃描上來的行數。嚴格來說，如果把JoinRel看成一箇中間表的話，那麼這些JoinFilter是這個中間表的過濾條件，但JoinRel還沒有產生，也沒有行數和統計資訊，因此無法準確估算。然而一種簡單近似的方法是，仍然利用基表，粗略估算出這個JoinFilter的選擇率，然後放到JoinRel最終行數估算中去。
三、路徑生成
有了前面兩節的行數估算的鋪墊，就可以進入路徑生成的流程了。何為路徑生成？已知表關聯的方式有多種（比如 NestLoop、HashJoin）、且GaussDB（DWS）的表是分散式的儲存在叢集中，那麼兩個表的關聯方式可能就有多種了，而我們的目標就是，從這些給定的基表出發，按要求經過一些操作（過濾條件、關聯方式和條件、聚集等等），相互組合，層層遞進，最後得到我們想要的結果。這就好比從基表出發，尋求一條最佳路徑，使得我們能最快得到結果，這就是我們的目的。本節我們介紹Join Path和Aggregate Path的生成。
1. Join Path的生成
GaussDB（DWS）優化器選擇的基本思路是動態規劃，顧名思義，從某個開始狀態，通過求解中間狀態最優解，逐步往前演進，最後得到全域性的最優計劃。那麼在動態規劃中，總有一個變數，驅動著過程演進。在這裡，這個變數就是表的個數。本節，我們以如下SQL為例進行講解：
select count(*) from t1, t2 where t1.c2 = t2.c2 and t1.c1 < 800 and exists (select c1 from t3 where t1.c1 = t3.c2 and t3.c1 > 100);
該SQL語句中，有三個基表t1, t2, t3，三個表的分佈鍵都是c1列，共有兩個關聯條件：
- t1.c2 = t2.c2， t1與t2關聯
- t1.c1 = t3.c2， t1與t3關聯
為了配合分析，我們結合日誌來幫助大家理解，設定如下引數，然後在執行語句：
set logging_module='on(opt_join)'; set log_min_messages=debug2;
第一步，如何獲取t1和t2的資料
首先，如何獲取t1和t2的資料，比如 Seq Scan、Index Scan等，由於本例中，我們沒有建立Index，那選擇只有Seq Scan了。日誌片段顯示：
我們先記住這三組Path名稱：path_list，cheapest_startup_path，cheapest_total_path，後面兩個就對應了動態規劃的區域性最優解，在這裡是一組集合，統稱為最優路徑，也是下一步的搜尋空間。path_list裡面存放了當前Rel集合上的有價值的一組候選Path（被剪枝調的Path不會放在這裡），cheapest_startup_path代表path_list中啟動代價最小的那個Path，cheapest_total_path代表path_list裡一組總代價最小的Path（這裡用一組主要是可能存在多個維度分別對應的最優Path）。t2表和t3表類似，最優路徑都是一條Seq Scan。有了所有基表的Scan最優路徑，下面就可以選擇關聯路徑了。
第二步，求解(t1, t2)關聯的最優路徑
t1和t2兩個表的分佈鍵都是c1列，但Join列都是c2列，那麼理論上的路徑就有：（放在右邊表示作為內表）
- Broadcast(t1) join t2
- t1 join Broadcast(t2)
- Broadcast(t2) join t1
- t2 join Broadcast(t1)
- Redistribute(t1) join Redistribute(t2)
- Redistribute(t2) join Redistribute(t1)
然後每一種路徑又可以搭配不同的Join方法（NestLoop、HashJoin、MergeJoin），總計18種關聯路徑，優化器需要在這些路徑中選擇最優路徑，篩選的依據就是路徑的代價（Cost）。優化器會給每個運算元賦予代價，比如 Seq Scan，Redistribute，HashJoin都有代價，代價與資料規模、資料特徵、系統資源等等都有關係，關於代價如何估算，後續文章再分析，本節只關注由這些代價怎麼選路徑。由於代價與執行時間成正比，優化器的目標是選擇代價最小的計劃，因此路徑選擇也是一樣。路徑代價的比較思路大致是這樣，對於產生的一個新Path，逐個比較該新Path與path_list中的path，若total_cost很相近，則比較startup cost，如果也差不多，則保留該Path到path_list中去；如果新路徑的total_cost比較大，但是startup_cost小很多，則保留該Path，此處略去具體的比較過程，直接給出Path的比較結果：
由此看出，總代價最小的路徑是兩邊做重分佈、t1作為內表的路徑。
第三步，求解(t1, t3)關聯的最優路徑
t1和t3表的關聯條件是：t1.c1 = t3.c2，因為t1的Join列是分佈鍵c1列，於是t1表上不需要加Redistribute；由於t1和t3的Join方式是Semi Join，外表不能Broadcast，否者可能會產生重複結果；另外還有一類Unique Path選擇（即t3表去重），那麼可用的候選路徑大致如下：
- t1 semi join Redistribute(t3)
- Redistribute(t3) right semi join t1
- t1 join Unique(Redistribute(t3))
- Unique(Redistribute(t3)) join t1
由於只有一邊需要重分佈且可以進行重分佈，則不選Broadcast，因為相同資料量時Broadcast的代價一般要高於重分佈，提前剪枝掉。再把Join方法考慮進去，於是優化器給出了最終選擇：
此時的最優計劃是選擇了內表Unique Path的路徑，即t3表先去重，然後在走Inner Join過程。
第四步，求解(t1，t2，t3)關聯的最優路徑
有了前面兩步的鋪墊，三個表關聯的思路是類似的，形式上是分解成兩個表先關聯，然候在與第三個表關聯，實際操作上是直接取出所有兩表關聯的JoinRel，然後逐個增加另一個表，嘗試關聯，選擇的方式如下：
- JoinRel(t1, t2) join t3：
(t1, t2)->(cheapest_startup_path + cheapest_total_path) join t3->(cheapest_startup_path + cheapest_total_path) - JoinRel(t1, t3) join t2：
(t1, t3)->(cheapest_startup_path + cheapest_total_path) join t2->(cheapest_startup_path + cheapest_total_path) - JoinRel(t2, t3) join t1：由於沒有(t2, t3)關聯，所以此種情況不存在
每取一對內外表的Path進行Join時，也會判斷是否需要重分佈、是否可以去重，選擇關聯方式，比如JoinRel(t1, t2) join t3時，也會嘗試對t3表進行去重的Path，因為這個Join本質仍然是Semi Join。下圖是選擇過程中產生的部分有價值的候選路徑（篇幅所限，只擷取了一部分）：
優化器在這些路徑中，選出瞭如下的最優路徑：
對比實際的執行計劃，二者是一樣的（對比第4層HashJoin的“E-costs“是一樣的）：
從這個過程可以大致感受到path_list有可能會發生一些膨脹，如果path_list中路徑太多了，則可能會導致cheapest_total_path有多個，那麼下一級的搜尋空間也就會變的很大，最終會導致計劃生成的耗時增加。關於Join Path的生成，作以下幾點說明：
- Join路徑的選擇時，會分兩個階段計算代價，initial和final代價，initial代價快速估算了建hash表、計算hash值以及下盤的代價，當initial代價已經比path_list中某個path大時，就提前剪枝掉該路徑；
- cheapest_total_path有多個原因：主要是考慮到多個維度下，代價很相近的路徑都有可能是下一層動態規劃的最佳選擇，只留一個可能得不到整體最優計劃；
- cheapest_startup_path記錄了啟動代價最小的一個，這也是預留了另一個維度，當查詢語句需要的結果很少時，有一個啟動代價很小的Path，但總代價可能比較大，這個Path有可能會成為首選；
- 由於剪枝的原因，有些情況下，可能會提前剪枝掉某個Path，或者這個Path沒有被選為cheapest_total_path或cheapest_startup_path，而這個Path是理論上最優計劃的一部分，這樣會導致最終的計劃不是最優的，這種場景一般概率不大，如果遇到這種情況，可嘗試使用Plan Hint進行調優；
- 路徑生成與叢集規模大小、系統資源、統計資訊、Cost估算都有緊密關係，如叢集DN數影響著重分佈的傾斜性和單DN的資料量，系統記憶體影響下盤代價，統計資訊是行數和distinct值估算的第一手資料，而Cost估算模型在整個計劃生成中，是選擇和淘汰的關鍵因素，每個JoinRel的行數估算不準，都有可能影響著最終計劃。因此，相同的SQL語句，在不同叢集或者同樣的叢集不同統計資訊，計劃都有可能不一樣，如果路徑發生一些變化可通過分析Performance資訊和日誌來定位問題，Performance詳解可以參考博文：GaussDB(DWS)的explain performance詳解。
- 如果設定了Random Plan模式，則動態規劃的每一層cheapest_startup_path和cheapest_total_path都是從path_list中隨機選取的，這樣保證隨機性。
2. Aggregate Path的生成
一般而言，Aggregate Path生成是在表關聯的Path生成之後，且有三個主要步驟（Unique Path的Aggregate在Join Path生成的時候就已經完成了，但也會有這三個步驟）：先估算出聚集結果的行數，然後選擇Path的方式，最後建立出最優Aggregate Path。前者依賴於統計資訊和Cost估算模型，後者取決於前者的估算結果、叢集規模和系統資源。Aggregate行數估算主要根據聚集列的distinct值來組合，我們重點關注Aggregate行數估算和最優Aggregate Path選擇。
2.1 Aggregate行數估算
以如下SQL為例進行說明：
select t1.c2, t2.c2, count(*) cnt from t1, t2 where t1.c2 = t2.c2 and t1.c1 < 500 group by t1.c2, t2.c2;
該語句先是兩表關聯，基表上有過濾條件，然後求取兩列的GROUP BY結果。這裡的聚集列有兩個，t1.c2和t2.c2，在看一下系統表中給出的原始資訊：
postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct from pg_stats where (tablename = 't1' or tablename = 't2') and attname = 'c2'; tablename | attname | null_frac | n_distinct | n_dndistinct -----------+---------+-----------+------------+-------------- t1 | c2 | 0 | -.25 | -.431535 t2 | c2 | 0 | 100 | -.39834 (2 rows)
統計資訊顯示t1.c2和t2.c2的原始distinct值分別是-0.25和100，-0.25轉換為絕對值就是0.25 * 2000 = 500，那它們的組合distinct是不是至少應該是500呢？答案不是。因為Aggregate對JoinRel(t1, t2)的結果進行聚集，而系統表中統計資訊是原始資訊（沒有任何過濾）。這時需要把Join條件和過濾條件都考慮進去，如何考慮呢？首先看過濾條件 “t1.c1<500“可能會過濾掉一部分t1.c2，那麼就會有個選擇率（此時我們稱之為FilterRatio），然後Join條件"t1.c2 = t2.c2"也會有一個選擇率（此時我們稱之為JoinRatio），這兩個Ratio都是介於[0, 1]之間的一個數，於是估算t1.c2的distinct時這兩個Ratio影響都要考慮。如果不同列之間選擇Poisson模型，相同列之間用完全相關模型，則t1.c2的distinct大約是這樣：
distinct(t1.c2) = Poisson(d0, ratio1, nRows) * ratio2
其中d0表示基表中原始distinct，ratio1代表使用Poisson模型的Ratio，ratio2代表使用完全相關模型的Ratio，nRows是基錶行數。如果需要定位分析問題，這些Ratio可以從日誌中查閱，如下設定後在執行SQL語句：
set logging_module='on(opt_card)'; set log_min_messages=debug3;
本例中，我們從日誌中可以看到t1表上的兩個Ratio：
在看t2.c2，這一列原始distinct是100，而從上面日誌中可以看出t2表的資料全匹配上了（沒有Ratio），那麼Join完t2.c2的distinct也是100。此時不能直接組合t1.c2和t2.c2，因為"t1.c2 = t2.c2“暗含了這兩個列的值是一樣的，那就是說它們等價，於是只需考慮Min(distinct(t1.c2), distinct(t2.c2))即可，下圖是Performance給出的實際和估算行數：
postgres=# explain performance select t1.c2, t2.c2, count(*) cnt from t1, t2 where t1.c2 = t2.c2 and t1.c1 < 500 group by t1.c2, t2.c2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs ----+-----------------------------------------------+------------------+--------+--------+------------+----------------+----------+---------+---------+--------- 1 | -> Streaming (type: GATHER) | 48.500 | 99 | 100 | | 80KB | | | 16 | 89.29 2 | -> HashAggregate | [38.286, 40.353] | 99 | 100 | | [28KB, 31KB] | 16MB | [24,24] | 16 | 79.29 3 | -> Hash Join (4,6) | [37.793, 39.920] | 1980 | 2132 | | [6KB, 6KB] | 1MB | | 8 | 75.04 4 | -> Streaming(type: REDISTRIBUTE) | [0.247, 0.549] | 1001 | 1001 | 25 | [53KB, 53KB] | 2MB | | 4 | 32.95 5 | -> Seq Scan on test.t2 | [0.157, 0.293] | 1001 | 1001 | | [12KB, 12KB] | 1MB | | 4 | 4.50 6 | -> Hash | [36.764, 38.997] | 998 | 1000 | 62 | [291KB, 291KB] | 16MB | [20,20] | 4 | 29.88 7 | -> Streaming(type: REDISTRIBUTE) | [36.220, 38.431] | 998 | 999 | | [53KB, 61KB] | 2MB | | 4 | 29.88 8 | -> Seq Scan on test.t1 | [0.413, 0.433] | 998 | 999 | | [14KB, 14KB] | 1MB | | 4 | 9.25
2.2 Aggregrate Path生成
有了聚集行數，則可以根據資源情況，靈活選擇聚集方式。Aggregate方式主要有以下三種：
- Aggregate + Gather (+ Aggregate)
- Redistribute + Aggregate (+Gather)
- Aggregate + Redistribute + Aggregate (+Gather)
括號中的表示可能沒有這一步，視具體情況而定。這些聚集方式可以理解成，兩表關聯時選兩邊Redistribute還是選一邊Broadcast。優化器拿到聚集的最終行數後，會嘗試每種聚集方式，並計算相應的代價，選擇最優的方式，最終生成路徑。這裡有兩層Aggregate時，最後一層就是最終聚集行數，而第一層聚集行數是根據Poisson模型推算的。Aggregate方式選擇預設由優化器根據代價選擇，使用者也可以通過引數best_agg_plan指定。三類聚集方式大致適用範圍如下：
- 第一種，直接聚集後行數不太大，一般是DN聚集，CN收集，有時CN需進行二次聚集
- 第二種，需要重分佈且直接聚集後行數未明顯減少
- 第三種，需要重分佈且直接聚集後行數減少明顯，重分佈之後，行數又可以減少，一般是DN聚集、重分佈、再聚集，俗稱雙層Aggregate
四、結束語
本文著眼於計劃生成的核心步驟，從行數估算、到Join Path的生成、再到Aggregate Path的生成，介紹了其中最簡單過程的基本原理。而實際的處理方法遠遠比描述的要複雜，需要考慮的情況很多，比如多組選擇率如何組合最優、分佈鍵怎麼選、出現傾斜如何處理、記憶體用多少等等。權衡整個計劃生成過程，有時也不得不有所舍，這樣才能有所得，而有時計劃的一點劣勢也可以忽略或者通過其他能力彌補上來，比如SMP開啟後，並行的效果會淡化一些計劃上的缺陷。總而言之，計劃生成是一項複雜而細緻的工作，生成全域性最優計劃需要持續的發現問題和優化，後續博文我們將繼續探討計劃生成的祕密。