10個你不知道的PostgreSQL功能：創建統計信息
如果你曾使用 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(一個擁有更多可讀版本的列統計信息的視圖)。
- 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 來關閉每個查詢的并行性：
- 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 都包含過濾器時會發生什么情況。
- 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)所依賴的函數統計。
- CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;
- ANALYZE tbl;
讓我們來看看現在的計劃是怎么來的。
- 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)
很好!讓我們看一下對計劃的測量。
- 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 可以獲取每列不同值的統計數字，但再次合并多列時，這些統計數據往往是錯誤的。
這些不好的數據是在什么時候影響我們的呢? 下面來看一個例子。
- 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}
現實影響
在實際的生產模式中，你總是會有某些與數據庫不知道的相互依賴或關系的列。 以下是我們與 Citus 客戶見過的一些例子：
- 有月份，季度和年份的列，因為你希望在報告中顯示按所有人分組的統計信息。
- 地理層次之間的關系。 例如。 具有國家，州和城市的列，并由它們來過濾/分組。
這里的例子僅僅是在數據集中只有 10M 行的情況，并且我們已經看到，在存在相關列的情況下，使用 CREATE 統計信息可顯著改善查詢計劃，并顯示性能改進。在 Citus 使用案例中，我們有客戶存儲數十億行數據，糟糕查詢計劃的影響可能非常嚴重。在上述示例中，當 planner 選擇了一個糟糕的查詢計劃時，我們不得不為 10M 行做一個基于磁盤的分類。想象一下如果是數十億行，那會有多糟糕。
Postgres一直在變得越來越好
當我們著手構建 Citus 時，我們明確選擇了 Postgres 作為構建基礎。通過擴展 Postgres ，我們選擇了一個堅實的基礎，在每個版本迭代中變得更好。由于 Citus 是一個純粹的擴展，而不是分支，所以每個版本中出現的所有優秀新功能都可以在使用 Citus 時得到充分利用。
喜歡以上的內容嗎?
如果您有興趣閱讀我們團隊的更多文章，請注冊我們的月刊，我們會將最新內容直接發送到您的收件箱。
