原文地址：https://www.citusdata.com/blog/2018/03/06/postgres-planner-and-its-usage-of-statistics/，本文統一將原文中的“planner”譯做“優化器” 如果您對Postgres進行了一些性能優化，則可能使用過EXP ...
原文地址：https://www.citusdata.com/blog/2018/03/06/postgres-planner-and-its-usage-of-statistics/，本文統一將原文中的“planner”譯做“優化器” 如果您對Postgres進行了一些性能優化，則可能使用過EXPLAIN。 EXPLAIN向您顯示PostgreSQL計劃程式為提供的語句生成的執行計劃。 它顯示瞭如何掃描語句引用的表（使用順序掃描，索引掃描等），以及如果使用多個表，將使用哪種聯接演算法。但是，Postgres是依據什麼信息給出執行計劃的？
優化器（planner）收集統計數據是決定使用哪種計劃的非常重要的參考信息。 這些統計信息使優化器（planner）可以估計執行計劃的特定部分後將返回多少行，這將影響執行計劃將要使用的掃描或聯接演算法的類型。 統計主要通過運行ANALYZE或VACUUM（以及一些DDL命令，如CREATE INDEX）來收集/更新它們。
這些統計信息由存儲在pg_class和pg_statistics中。 Pg_class基本上存儲每個表和索引中的條目總數，以及它們所占用的磁碟塊數。 Pg_statistic存儲有關每個列的統計信息，例如該列的值的為空的百分比，最常見的值是什麼，直方圖範圍等。 您可以在下麵的表格中查看以下示例，該示例針對針對col1收集的Postgres統計類型。 下麵的查詢輸出顯示，planner（正確）估計表中的col1列有1000個不同的值，並且還對最常見的值，頻率等進行其他估計。
請註意，我們已經查詢了pg_stats（該視圖保存了更易讀的列統計信息。）
CREATE TABLE tbl ( col1 int, col2 int ); INSERT INTO tbl SELECT i/10000, i/100000 FROM generate_series (1,10000000) s(i); ANALYZE tbl; select * from pg_stats where tablename = 'tbl' and attname = 'col1'; -[ RECORD 1 ]----------+-------------------------------- schemaname | public tablename | tbl attname | col1 inherited | f null_frac | 0 avg_width | 4 n_distinct | 1000 most_common_vals | {318,564,596,...} most_common_freqs | {0.00173333,0.0017,0.00166667,0.00156667,...} histogram_bounds | {0,8,20,30,39,...} correlation | 1 most_common_elems | most_common_elem_freqs | elem_count_histogram |
單個欄位統計信息的不足 這些單列統計信息可幫助優化器（planner）預計篩選條件的選擇性（這是計劃程式用來估計索引掃描將選擇多少行的方法）。 當查詢中提供多個條件時，優化器（planner）將假定列（或where子句條件）彼此獨立。 當列之間相互關聯或存在相互依賴時，情況並非如此，這會使計劃者估算或低估了這些條件將返回的行數。（譯者註：對於相關性列，優化器預估的比實際數據行數要少）
讓我們看下麵的幾個例子。為了使計劃易於閱讀，我們通過將max_parallel_workers_per_gather設置為0來關閉每個查詢的並行性；
EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Seq Scan on tbl (cost=0.00..169247.80 rows=9584 width=8) (actual time=0.641..622.851 rows=10000 loops=1) Filter: (col1 = 1) Rows Removed by Filter: 9990000 Planning time: 0.051 ms Execution time: 623.185 ms (5 rows)如您在此處看到的，優化器（planner）估計col1的值為1的行數為9584，查詢返回的實際行數為10000。因此，非常準確。 但是，當您在第1列和第2列中都包含過濾器時，會發生什麼情況。
EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1 and col2 = 0; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Seq Scan on tbl (cost=0.00..194248.69 rows=100 width=8) (actual time=0.640..630.130 rows=10000 loops=1) Filter: ((col1 = 1) AND (col2 = 0)) Rows Removed by Filter: 9990000 Planning time: 0.072 ms Execution time: 630.467 ms (5 rows)優化器（planner）的估算已經降低了100倍！讓我們嘗試瞭解為什麼會發生這種情況。 第一列的選擇性約為0.001（1/1000），第二列的選擇性為0.01（1/100），為了計算將被這兩個“獨立”條件過濾的行數，計劃器將其選擇性乘以。 因此，我們得到：選擇性= 0.001 * 0.01 = 0.00001。
將其乘以表中的行數，即10000000，我們得到100。這就是計劃者估計的100的來源。
如果這些列不是獨立的（有多個列之間存在依賴關係），我們如何告訴優化器（planner）呢？ 譯者註：
早些年曾經執著地研究過SQLServer對非相關列預估的演算法，
類似於pg，SQLServer從預估行數從2012版的p0*p1*p2*p3……*RowCount，演變為P0*P11/2 * P21/4 * P31/8……* RowCount，https://www.cnblogs.com/wy123/p/5790855.html 在PostgreSQL創建統計表信息 在Postgres 10之前，沒有一種簡單的方法可以告訴優化器（planner）收集統計數據，這些統計數據捕獲了列之間的這種關係。 但是，在Postgres 10中，有一個新功能可以解決此問題。 CREATE STATISTICS可用於創建擴展的統計對象，這些對象告訴伺服器收集有關這些有趣的相關列的額外統計信息。
相關列的統計信息(Functional dependency statistics)
回到我們先前的估計問題，問題在於col2的值實際上只是col的1/10。 譯者註：一個表中有兩個欄位c1和c2，比如c1代表“省份Id”，c2代表“縣Id”，這樣c1和c2就存在依賴關係。 在資料庫術語中，我們可以說col2在功能上取決於col1。這意味著col1的值足以確定col2的值，並且沒有兩行具有相同的col1值但具有不同的col2值。 因此，col2上的第二個過濾器實際上不會刪除任何行！但是，優化器（planner）可以捕獲足夠的統計信息來瞭解這一點。 我們創建一個統計對象以捕獲有關這些列的功能依賴性統計並運行ANALYZE。
CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl; ANALYZE tbl;讓我們看看planner現在提出了什麼。
EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1 and col2 = 0; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Seq Scan on tbl (cost=0.00..194247.76 rows=9584 width=8) (actual time=0.638..629.741 rows=10000 loops=1) Filter: ((col1 = 1) AND (col2 = 0)) Rows Removed by Filter: 9990000 Planning time: 0.115 ms Execution time: 630.076 ms (5 rows)好多了！讓我們來看看是什麼幫助優化器（planner）做出了這一決定。
SELECT stxname, stxkeys, stxdependencies FROM pg_statistic_ext WHERE stxname = 's1'; stxname | stxkeys | stxdependencies ---------+---------+---------------------- s1 | 1 2 | {"1 => 2": 1.000000} (1 row)綜上所述，我們可以看到Postgres意識到col1完全確定col2，因此捕獲該信息的繫數為1。現在，所有在這兩個列上都具有過濾器的查詢將具有更好的估計。 非相關列的統計信息（ndistinct statistics） 功能依賴性是可以在列之間捕獲的一種關係。您可以捕獲的另一種統計數據是一組列的不同值的數量。 前面我們曾提到，計劃者為每一列捕獲了不同值數量的統計信息，但是當組合多個列時，這些統計信息常常是錯誤的 譯者註：比如一個訂單表中有兩個欄位c1和c2，比如c1代表“UserId”，c2代表訂單類型“OrderType”（假如有服飾，食品，3C產品等），很明顯，一個用戶可以隨意購買任何類型的商品，UserId和OrderType之間沒有任何依賴關係 糟糕的統計數據何時會傷害我們？讓我們來看一個例子。
EXPLAIN ANALYZE SELECT col1,col2,count(*) from tbl group by col1, col2; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1990523.20..2091523.04 rows=100000 width=16) (actual time=2697.246..4470.789 rows=1001 loops=1) Group Key: col1, col2 -> Sort (cost=1990523.20..2015523.16 rows=9999984 width=8) (actual time=2695.498..3440.880 rows=10000000 loops=1) Sort Key: col1, col2 Sort Method: external sort Disk: 176128kB -> Seq Scan on tbl (cost=0.00..144247.84 rows=9999984 width=8) (actual time=0.008..665.689 rows=10000000 loops=1) Planning time: 0.072 ms Execution time: 4494.583 ms彙總行時，Postgres選擇進行哈希彙總或組彙總。如果它適合哈希表在記憶體中，則選擇哈希聚合，否則選擇對所有行進行排序，然後根據col1，col2將它們分組。 現在，優化器(planner)估計的數量（等於col1和col2的不同值的數量）將為100000。 它發現它沒有足夠的work_mem將該哈希表存儲在記憶體中。因此，它使用基於磁碟的排序來運行查詢。 但是，正如您在計劃的實際部分中看到的那樣，實際行數僅為1001。也許，我們有足夠的記憶體來將它們容納在記憶體中，併進行哈希聚合。 讓我們要求優化器(planner)捕獲n_distinct統計信息，然後重新運行查詢並找出答案。
CREATE STATISTICS s2 (ndistinct) on col1, col2 from tbl; ANALYZE tbl; EXPLAIN ANALYZE SELECT col1,col2,count(*) from tbl group by col1, col2; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=219247.63..219257.63 rows=1000 width=16) (actual time=2431.767..2431.928 rows=1001 loops=1) Group Key: col1, col2 -> Seq Scan on tbl (cost=0.00..144247.79 rows=9999979 width=8) (actual time=0.008..643.488 rows=10000000 loops=1) Planning time: 0.129 ms Execution time: 2432.010 ms (5 rows)您可以看到估算值現在更加準確（即1000），查詢現在快了2倍。通過運行下麵的查詢，我們可以看到優化器(planner)學到了什麼。
SELECT stxkeys AS k, stxndistinct AS nd FROM pg_statistic_ext WHERE stxname = 's2'; k | nd -----+---------------- 1 2 | {"1, 2": 1000} Real-world implications
實際情況下的影響
在實際的生產模式中，您總是會擁有某些列，而這些列之間具有資料庫不知道的相互依存關係。我們與Citus客戶一起看到的一些例子是：
- 由於要在報表中顯示按所有人分組的統計信息，因此具有月，季度和年的列。
- 地理層次結構之間的關係,例如:具有國家，州和城市列，並按它們進行過濾/分組。
當我們著手構建Citus時，我們明確選擇了Postgres作為基礎。通過擴展Postgres，我們選擇了一個堅實的基礎，可以隨著每個發行版的不斷完善。
因為Citus是純粹的擴展，而不是分支，所以使用Citus時可以利用每個發行版中的所有出色新功能。 享受您正在閱讀的內容嗎？
如果您有興趣閱讀我們團隊的更多帖子，請註冊我們的每月時事通訊，並將最新內容直接發送到您的收件箱。
