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

[[224918]]

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

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

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

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)計(jì)數(shù)據(jù)不足時(shí)

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

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

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 估計(jì) col1 的值為 1 的行數(shù)是 9584 ，而查詢返回的實(shí)際行數(shù)是 10000 ，所以相當(dāng)準(zhǔn)確。

當(dāng)你在 column 1 和 column 2 都包含過(guò)濾器時(shí)會(huì)發(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 的估計(jì)減少了100倍! 讓我們?cè)囍斫鉃槭裁窗l(fā)生這種情況。

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

• 選擇性= 0.001 * 0.01 = 0.00001。

當(dāng)它乘以我們?cè)诒碇械男袛?shù)即 10000000 時(shí)，我們得到 100。這就是 planner 對(duì) 100 的估計(jì)值的來(lái)源。 但是，這些列不是獨(dú)立的，那么我們?nèi)绾胃嬷?planner ?

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

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

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

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

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

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

讓我們來(lái)看看現(xiàn)在的計(jì)劃是怎么來(lái)的。

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) 

很好!讓我們看一下對(duì)計(jì)劃的測(cè)量。

SELECT stxname, stxkeys, stxdependencies                                                   
  FROM pg_statistic_ext                                                                    
  WHERE stxname = 's1';    
stxname | stxkeys |   stxdependencies     
---------+---------+---------------------- 
 s1      | 1 2     | {"1 => 2": 1.000000} 
(1 row) 

看這里，我們可以看到， Postgres 意識(shí)到 col1 完全決定 col2 ，因此用系數(shù)1來(lái)捕獲這些信息?，F(xiàn)在，所有的查詢都過(guò)濾這些列之后，計(jì)劃將會(huì)得到更好的評(píng)估。

ndistinct 統(tǒng)計(jì)

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

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

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 

聚合行時(shí)，Postgres 選擇做散列聚合或組合。 如果它認(rèn)為散列表合適，則選擇散列聚合，否則它會(huì)選擇對(duì)所有行進(jìn)行排序，然后按照 col1、col2 對(duì)它們進(jìn)行分組。

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

讓 planner 去捕獲 n_distinct 統(tǒng)計(jì)信息，重新運(yùn)行查詢并找出結(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倍左右。 通過(guò)運(yùn)行下面的查詢，我們可以看到 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)實(shí)影響

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

• 有月份，季度和年份的列，因?yàn)槟阆Ｍ趫?bào)告中顯示按所有人分組的統(tǒng)計(jì)信息。
• 地理層次之間的關(guān)系。 例如。 具有國(guó)家，州和城市的列，并由它們來(lái)過(guò)濾/分組。

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

Postgres一直在變得越來(lái)越好

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

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

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