如果你曾使用 Postgres 做過一些效能優化，你或許已經使用過 EXPLAIN 。EXPLAIN 向你展示了 PostgreSQL planner 為提供的語句生成的執行計劃。它說明了語句涉及到的表將會使用順序掃描、索引掃描等方式進行掃描，在使用多表的情況下將會使用連線演算法。但是， Postgres 是如何產生這些規劃的？
決定使用哪種規劃的一個非常重要的輸入是 planner 收集到的資料統計。這些統計的資料能夠使 planner 評估執行規劃的某一部分會返回多少行，繼而影響到使用哪一種規劃或連線演算法。它們主要是通過執行 ANALYZE 或 VACUUM（和一些 DDL 命令，比如說 CREATE INDEX )來採集或更新的。
這些統計資訊由 planner 儲存在 pg_class 和 pg_statistics 中。Pg_class 基本上儲存了每個表和索引中的條目總數，以及它們所佔用的磁碟塊數。Pg_statistic 儲存關於每列的統計資訊，例如哪些列的 % 值為 nul l，哪些是最常見的值，直方圖邊界等。你可以檢視下面的示例，以瞭解 Postgres 在下表中為 col1 收集的統計資訊型別。下面的查詢輸出展示了 planner（正確地）預估表中列 col1 中有 1000 個不同的值，並且還對最常見的值、頻率等進行了其他預估。
請注意，我們已經查詢了 pg_stats（一個擁有更多可讀版本的列統計資訊的檢視）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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 用來估算索引掃描將選擇多少行的內容）。 當查詢中存在多個條件時，planner 假定列（或 where 子句條件）彼此獨立。 當列相互關聯或相互依賴並導致 planner 低估或高估這些條件將返回的行數時，就不適用。
我們來看下面的幾個例子。 為了使查詢計劃易於閱讀，我們通過設定 max_parallel_workers_per_gather 為 0 來關閉每個查詢的並行性：
|
1 2 3 4 5 6 7 8 9 |
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 ，所以相當準確。
當你在 column 1 和 column 2 都包含過濾器時會發生什麼情況。
|
1 2 3 4 5 6 7 8 9 |
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）。 要計算將由這兩個“獨立”條件過濾的行數，planner 會將它們的選擇性相乘。 所以，我們得到：
選擇性= 0.001 * 0.01 = 0.00001。
當它乘以我們在表中的行數即 10000000 時，我們得到 100。這就是 planner 對 100 的估計值的來源。 但是，這些列不是獨立的，那麼我們如何告知 planner ？
在 PostgreSQL 中建立統計資訊
在 Postgres 10 之前，沒有一種簡易的方式去告訴 planner 採集捕捉列之間關係的資料統計。但是， Postgres 10 有一個新特性正好解決了這個問題，可以使用 CREATE STATISTICS 來建立擴充套件統計的物件，告訴伺服器去採集這些有意思的相關列的額外的統計資訊。
函式依賴統計
回到我們先前評估的問題，col2 的值僅僅是 col1/10 。在資料庫的術語中，我們會說 col2 是函式依賴於 col1 ，也就是說，col1 的值足以決定 col2 的值，並且不存在有兩行資料擁有相同的 col1 值的同時有不同的 col2 值。因此，在 col2 列上的第二個過濾篩選並沒有移除任何行！但是，planner 捕捉到了足夠的統計資訊去知道這件事情。
讓我們來建立一個統計物件去捕獲這些列和執行分析（ANALYZE）所依賴的函式統計。
|
1 2 |
CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl; ANALYZE tbl; |
讓我們來看看現在的計劃是怎麼來的。
|
1 2 3 4 5 6 7 8 9 |
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) |
很好！讓我們看一下對計劃的測量。
|
1 2 3 4 5 6 7 |
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 統計
函式依賴是你可以在列之間捕獲的一種關係。 你可以捕獲的另一種統計資訊是一組列的不同值。 我們之前指出，planner 可以獲取每列不同值的統計數字，但再次合併多列時，這些統計資料往往是錯誤的。
這些不好的資料是在什麼時候影響我們的呢？ 下面來看一個例子。
|
1 2 3 4 5 6 7 8 9 10 11 |
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 統計資訊，重新執行查詢並找出結果。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 學到了什麼。
|
1 2 3 4 5 6 |
SELECT stxkeys AS k, stxndistinct AS nd FROM pg_statistic_ext WHERE stxname = 's2'; k | nd -----+---------------- 1 2 | {"1, 2": 1000} |
現實影響
在實際的生產模式中，你總是會有某些與資料庫不知道的相互依賴或關係的列。 以下是我們與 Citus 客戶見過的一些例子：
- 有月份，季度和年份的列，因為你希望在報告中顯示按所有人分組的統計資訊。
- 地理層次之間的關係。 例如。 具有國家，州和城市的列，並由它們來過濾/分組。
這裡的例子僅僅是在資料集中只有 10M 行的情況，並且我們已經看到，在存在相關列的情況下，使用 CREATE 統計資訊可顯著改善查詢計劃，並顯示效能改進。在 Citus 使用案例中，我們有客戶儲存數十億行資料，糟糕查詢計劃的影響可能非常嚴重。在上述示例中，當 planner 選擇了一個糟糕的查詢計劃時，我們不得不為 10M 行做一個基於磁碟的分類。想象一下如果是數十億行，那會有多糟糕。