专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

您所不了解的Postgres 10功能:CREATE STATISTICS(译)

 
原文地址: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客户一起看到的一些例子是:
  • 由于要在报表中显示按所有人分组的统计信息,因此具有月,季度和年的列。
  • 地理层次结构之间的关系,例如:具有国家,州和城市列,并按它们进行过滤/分组。
此处的示例在数据集中只有1000万行,我们已经看到,使用CREATE统计信息可以在有相关列的情况下显着改善计划,并且还可以提高性能。
在Citus用例中,我们的客户存储着数十亿行数据,而不良计划的后果可能非常严重。
在我们的示例中,当计划者选择了一个糟糕的计划时,我们不得不对1000万行进行基于磁盘的排序,想像一下数十亿行会是多么糟糕。
 
Postgres越来越好
当我们着手构建Citus时,我们明确选择了Postgres作为基础。通过扩展Postgres,我们选择了一个坚实的基础,可以随着每个发行版的不断完善。
因为Citus是纯粹的扩展,而不是分支,所以使用Citus时可以利用每个发行版中的所有出色新功能。
 
享受您正在阅读的内容吗?
如果您有兴趣阅读我们团队的更多帖子,请注册我们的每月时事通讯,并将最新内容直接发送到您的收件箱。
 
 
 

posted on 2020-07-15 21:56  MSSQL123  阅读(475)  评论(0编辑  收藏  举报