如果你曾使用 Postgres 做過一些性能優(yōu)化，你或許已經(jīng)使用過 EXPLAIN 。EXPLAIN 向你展示了 PostgreSQL planner 為提供的語句生成的執(zhí)行計劃。它說明了語句涉及到的表將會使用順序掃描、索引掃描等方式進行掃描，在使用多表的情況下將會使用連接算法。但是， Postgres 是如何產(chǎn)生這些規(guī)劃的?

[[224918]]

決定使用哪種規(guī)劃的一個非常重要的輸入是 planner 收集到的數(shù)據(jù)統(tǒng)計。這些統(tǒng)計的數(shù)據(jù)能夠使 planner 評估執(zhí)行規(guī)劃的某一部分會返回多少行，繼而影響到使用哪一種規(guī)劃或連接算法。它們主要是通過運行 ANALYZE 或 VACUUM(和一些 DDL 命令，比如說 CREATE INDEX )來采集或更新的。

這些統(tǒng)計信息由 planner 存儲在 pg_class 和 pg_statistics 中。Pg_class 基本上存儲了每個表和索引中的條目總數(shù)，以及它們所占用的磁盤塊數(shù)。Pg_statistic 存儲關(guān)于每列的統(tǒng)計信息，例如哪些列的 % 值為 nul l，哪些是最常見的值，直方圖邊界等。你可以查看下面的示例，以了解 Postgres 在下表中為 col1 收集的統(tǒng)計信息類型。下面的查詢輸出展示了 planner(正確地)預(yù)估表中列 col1 中有 1000 個不同的值，并且還對最常見的值、頻率等進行了其他預(yù)估。

請注意，我們已經(jīng)查詢了 pg_stats(一個擁有更多可讀版本的列統(tǒng)計信息的視圖)。

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   | 

單列統(tǒng)計數(shù)據(jù)不足時

這些單列統(tǒng)計信息可幫助 planner 估算你的條件選擇性(這是 planner 用來估算索引掃描將選擇多少行的內(nèi)容)。 當查詢中存在多個條件時，planner 假定列(或 where 子句條件)彼此獨立。 當列相互關(guān)聯(lián)或相互依賴并導(dǎo)致 planner 低估或高估這些條件將返回的行數(shù)時，就不適用。

我們來看下面的幾個例子。 為了使查詢計劃易于閱讀，我們通過設(shè)置 max_parallel_workers_per_gather 為 0 來關(guān)閉每個查詢的并行性：

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 的行數(shù)是 9584 ，而查詢返回的實際行數(shù)是 10000 ，所以相當準確。

當你在 column 1 和 column 2 都包含過濾器時會發(fā)生什么情況。

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倍! 讓我們試著理解為什么發(fā)生這種情況。

第一個列的選擇性約為 0.001(1/1000)，第二個列的選擇性為 0.01(1/100)。 要計算將由這兩個“獨立”條件過濾的行數(shù)，planner 會將它們的選擇性相乘。 所以，我們得到：

• 選擇性= 0.001 * 0.01 = 0.00001。

當它乘以我們在表中的行數(shù)即 10000000 時，我們得到 100。這就是 planner 對 100 的估計值的來源。 但是，這些列不是獨立的，那么我們?nèi)绾胃嬷?planner ?

在 PostgreSQL 中創(chuàng)建統(tǒng)計信息

在 Postgres 10 之前，沒有一種簡易的方式去告訴 planner 采集捕捉列之間關(guān)系的數(shù)據(jù)統(tǒng)計。但是， Postgres 10 有一個新特性正好解決了這個問題，可以使用 CREATE STATISTICS 來創(chuàng)建擴展統(tǒng)計的對象，告訴服務(wù)器去采集這些有意思的相關(guān)列的額外的統(tǒng)計信息。

函數(shù)依賴統(tǒng)計

回到我們先前評估的問題，col2 的值僅僅是 col1/10 。在數(shù)據(jù)庫的術(shù)語中，我們會說 col2 是函數(shù)依賴于 col1 ，也就是說，col1 的值足以決定 col2 的值，并且不存在有兩行數(shù)據(jù)擁有相同的 col1 值的同時有不同的 col2 值。因此，在 col2 列上的第二個過濾篩選并沒有移除任何行!但是，planner 捕捉到了足夠的統(tǒng)計信息去知道這件事情。

讓我們來創(chuàng)建一個統(tǒng)計對象去捕獲這些列和運行分析(ANALYZE)所依賴的函數(shù)統(tǒng)計。

CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;  
ANALYZE tbl; 

讓我們來看看現(xiàn)在的計劃是怎么來的。

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 ，因此用系數(shù)1來捕獲這些信息。現(xiàn)在，所有的查詢都過濾這些列之后，計劃將會得到更好的評估。

ndistinct 統(tǒng)計

函數(shù)依賴是你可以在列之間捕獲的一種關(guān)系。 你可以捕獲的另一種統(tǒng)計信息是一組列的不同值。 我們之前指出，planner 可以獲取每列不同值的統(tǒng)計數(shù)字，但再次合并多列時，這些統(tǒng)計數(shù)據(jù)往往是錯誤的。

這些不好的數(shù)據(jù)是在什么時候影響我們的呢? 下面來看一個例子。

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 對它們進行分組。

現(xiàn)在，planner 估計組的數(shù)量(等于 col1、col2 的不同值的數(shù)量)將為 100000。它預(yù)計到它沒有足夠的 work_mem 將該散列表存儲在內(nèi)存中。 因此，它使用基于磁盤的排序來運行該查詢。 但是，正如在查詢計劃中所看到的那樣，實際行數(shù)僅為 1001。也許，我們有足夠的內(nèi)存來執(zhí)行哈希聚合。

讓 planner 去捕獲 n_distinct 統(tǒng)計信息，重新運行查詢并找出結(jié)果。

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) 

可以看到，現(xiàn)在的估算精度更高了(即 1000 )，查詢速度也提高了2倍左右。 通過運行下面的查詢，我們可以看到 planner 學(xué)到了什么。

SELECT stxkeys AS k, stxndistinct AS nd                                                    
  FROM pg_statistic_ext                                                                    
  WHERE stxname = 's2';  
  k  |       nd        
-----+---------------- 
 1 2 | {"1, 2": 1000} 

現(xiàn)實影響

在實際的生產(chǎn)模式中，你總是會有某些與數(shù)據(jù)庫不知道的相互依賴或關(guān)系的列。 以下是我們與 Citus 客戶見過的一些例子：

• 有月份，季度和年份的列，因為你希望在報告中顯示按所有人分組的統(tǒng)計信息。
• 地理層次之間的關(guān)系。 例如。 具有國家，州和城市的列，并由它們來過濾/分組。

這里的例子僅僅是在數(shù)據(jù)集中只有 10M 行的情況，并且我們已經(jīng)看到，在存在相關(guān)列的情況下，使用 CREATE 統(tǒng)計信息可顯著改善查詢計劃，并顯示性能改進。在 Citus 使用案例中，我們有客戶存儲數(shù)十億行數(shù)據(jù)，糟糕查詢計劃的影響可能非常嚴重。在上述示例中，當 planner 選擇了一個糟糕的查詢計劃時，我們不得不為 10M 行做一個基于磁盤的分類。想象一下如果是數(shù)十億行，那會有多糟糕。

Postgres一直在變得越來越好

當我們著手構(gòu)建 Citus 時，我們明確選擇了 Postgres 作為構(gòu)建基礎(chǔ)。通過擴展 Postgres ，我們選擇了一個堅實的基礎(chǔ)，在每個版本迭代中變得更好。由于 Citus 是一個純粹的擴展，而不是分支，所以每個版本中出現(xiàn)的所有優(yōu)秀新功能都可以在使用 Citus 時得到充分利用。

喜歡以上的內(nèi)容嗎?

如果您有興趣閱讀我們團隊的更多文章，請注冊我們的月刊，我們會將最新內(nèi)容直接發(fā)送到您的收件箱。