|
|
51CTO旗下网站
|
|
移动端

10 个你不知道的 PostgreSQL 功能

如果你曾使用 Postgres 做过一些性能优化,你或许已经使用过 EXPLAIN 。EXPLAIN 向你展示了 PostgreSQL planner 为提供的语句生成的执行计划。

作者:开源中国编译来源:开源中国|2018-07-26 08:30

 

英文原文:The Postgres 10 feature you didn't know about: CREATE STATISTICS

如果你曾使用 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. CREATE TABLE tbl (                                                                         
  2.     col1 int,                                                                              
  3.     col2 int                                                                               
  4. );                                                                                         
  5.  
  6. INSERT INTO tbl SELECT i/10000, i/100000                                                   
  7. FROM generate_series (1,10000000) s(i);                                                    
  8.  
  9. ANALYZE tbl;                                      
  10.  
  11. select * from pg_stats where tablename = 'tbl' and attname = 'col1'
  12. -[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  13. schemaname             | public 
  14. tablename              | tbl 
  15. attname                | col1 
  16. inherited              | f 
  17. null_frac              | 0 
  18. avg_width              | 4 
  19. n_distinct             | 1000 
  20. most_common_vals       | {318,564,596,...} 
  21. most_common_freqs      | {0.00173333,0.0017,0.00166667,0.00156667,...} 
  22. histogram_bounds       | {0,8,20,30,39,...} 
  23. correlation            | 1 
  24. most_common_elems      |  
  25. most_common_elem_freqs |  
  26. elem_count_histogram   | 

单列统计数据不足时

这些单列统计信息可帮助 planner 估算你的条件选择性(这是 planner 用来估算索引扫描将选择多少行的内容)。 当查询中存在多个条件时,planner 假定列(或 where 子句条件)彼此独立。 当列相互关联或相互依赖并导致 planner 低估或高估这些条件将返回的行数时,就不适用。

我们来看下面的几个例子。 为了使查询计划易于阅读,我们通过设置 max_parallel_workers_per_gather  为 0 来关闭每个查询的并行性:

  1. EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1;                             
  2.                                                 QUERY PLAN                                                  
  3. ----------------------------------------------------------------------------------------------------------- 
  4.  Seq Scan on tbl  (cost=0.00..169247.80 rows=9584 width=8) (actual time=0.641..622.851 rows=10000 loops=1) 
  5.    Filter: (col1 = 1) 
  6.    Rows Removed by Filter: 9990000 
  7.  Planning time: 0.051 ms 
  8.  Execution time: 623.185 ms 
  9. (5 rows

正如你看到的那样,planner 估计 col1 的值为 1 的行数是 9584 ,而查询返回的实际行数是 10000 ,所以相当准确。

当你在 column 1 和 column 2 都包含过滤器时会发生什么情况。

  1. EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1 and col2 = 0;                             
  2.                                                 QUERY PLAN                                                 
  3. ---------------------------------------------------------------------------------------------------------- 
  4.  Seq Scan on tbl  (cost=0.00..194248.69 rows=100 width=8) (actual time=0.640..630.130 rows=10000 loops=1) 
  5.    Filter: ((col1 = 1) AND (col2 = 0)) 
  6.    Rows Removed by Filter: 9990000 
  7.  Planning time: 0.072 ms 
  8.  Execution time: 630.467 ms 
  9. (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. CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;  
  2. ANALYZE tbl; 

让我们来看看现在的计划是怎么来的。

  1. EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1 and col2 = 0;                             
  2.                                                 QUERY PLAN                                                  
  3. ----------------------------------------------------------------------------------------------------------- 
  4.  Seq Scan on tbl  (cost=0.00..194247.76 rows=9584 width=8) (actual time=0.638..629.741 rows=10000 loops=1) 
  5.    Filter: ((col1 = 1) AND (col2 = 0)) 
  6.    Rows Removed by Filter: 9990000 
  7.  Planning time: 0.115 ms 
  8.  Execution time: 630.076 ms 
  9. (5 rows

很好!让我们看一下对计划的测量。

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

看这里,我们可以看到, Postgres 意识到 col1 完全决定 col2 ,因此用系数1来捕获这些信息。现在,所有的查询都过滤这些列之后,计划将会得到更好的评估。

ndistinct 统计

函数依赖是你可以在列之间捕获的一种关系。 你可以捕获的另一种统计信息是一组列的不同值。 我们之前指出,planner 可以获取每列不同值的统计数字,但再次合并多列时,这些统计数据往往是错误的。

这些不好的数据是在什么时候影响我们的呢? 下面来看一个例子。

  1. EXPLAIN ANALYZE SELECT col1,col2,count(*) from tbl group by col1, col2;                    
  2.                                                          QUERY PLAN                                                           
  3. ----------------------------------------------------------------------------------------------------------------------------- 
  4.  GroupAggregate  (cost=1990523.20..2091523.04 rows=100000 width=16) (actual time=2697.246..4470.789 rows=1001 loops=1) 
  5.    Group Key: col1, col2 
  6.    ->  Sort  (cost=1990523.20..2015523.16 rows=9999984 width=8) (actual time=2695.498..3440.880 rows=10000000 loops=1) 
  7.          Sort Key: col1, col2 
  8.          Sort Method: external sort  Disk: 176128kB 
  9.          ->  Seq Scan on tbl  (cost=0.00..144247.84 rows=9999984 width=8) (actual time=0.008..665.689 rows=10000000 loops=1) 
  10.  Planning time: 0.072 ms 
  11.  Execution time: 4494.583 ms 

聚合行时,Postgres 选择做散列聚合或组合。 如果它认为散列表合适,则选择散列聚合,否则它会选择对所有行进行排序,然后按照 col1、col2 对它们进行分组。

现在,planner 估计组的数量(等于 col1、col2 的不同值的数量)将为 100000。它预计到它没有足够的 work_mem 将该散列表存储在内存中。 因此,它使用基于磁盘的排序来运行该查询。 但是,正如在查询计划中所看到的那样,实际行数仅为 1001。也许,我们有足够的内存来执行哈希聚合。

让 planner 去捕获 n_distinct 统计信息,重新运行查询并找出结果。

  1. CREATE STATISTICS s2 (ndistinct) on col1, col2 from tbl;                                   
  2. ANALYZE tbl; 
  3.  
  4. EXPLAIN ANALYZE SELECT col1,col2,count(*) from tbl group by col1, col2;                    
  5.                                                       QUERY PLAN                                                        
  6. ----------------------------------------------------------------------------------------------------------------------- 
  7.  HashAggregate  (cost=219247.63..219257.63 rows=1000 width=16) (actual time=2431.767..2431.928 rows=1001 loops=1) 
  8.    Group Key: col1, col2 
  9.    ->  Seq Scan on tbl  (cost=0.00..144247.79 rows=9999979 width=8) (actual time=0.008..643.488 rows=10000000 loops=1) 
  10.  Planning time: 0.129 ms 
  11.  Execution time: 2432.010 ms 
  12. (5 rows

可以看到,现在的估算精度更高了(即 1000 ),查询速度也提高了2倍左右。 通过运行下面的查询,我们可以看到 planner 学到了什么。

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

现实影响

在实际的生产模式中,你总是会有某些与数据库不知道的相互依赖或关系的列。 以下是我们与 Citus 客户见过的一些例子:

  • 有月份,季度和年份的列,因为你希望在报告中显示按所有人分组的统计信息。
  • 地理层次之间的关系。 例如。 具有国家,州和城市的列,并由它们来过滤/分组。

这里的例子仅仅是在数据集中只有 10M 行的情况,并且我们已经看到,在存在相关列的情况下,使用 CREATE 统计信息可显着改善查询计划,并显示性能改进。在 Citus 使用案例中,我们有客户存储数十亿行数据,糟糕查询计划的影响可能非常严重。在上述示例中,当 planner 选择了一个糟糕的查询计划时,我们不得不为 10M 行做一个基于磁盘的分类。想象一下如果是数十亿行,那会有多糟糕。

Postgres一直在变得越来越好

当我们着手构建 Citus 时,我们明确选择了 Postgres 作为构建基础。通过扩展 Postgres ,我们选择了一个坚实的基础,在每个版本迭代中变得更好。由于 Citus 是一个纯粹的扩展,而不是分支,所以每个版本中出现的所有优秀新功能都可以在使用 Citus 时得到充分利用。

【编辑推荐】

  1. 大数据时代十大热门IT岗位
  2. AMO汽车数据架构 引领汽车信息交互革命
  3. 男人养家这件事,从大数据上看压根不成立
  4. 谷歌/微软/推特/Facebook 宣布推出开源数据传输项目
  5. 史上最严重数据车祸:通用丰田特斯拉统统中招
【责任编辑:张燕妮 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

Eclipse Web开发从入门到精通(实例版)

本书由浅入深、循序渐进地介绍了目前流行的基于Eclipse的优秀框架。全书共分14章,内容涵盖了Eclipse基础、ANT资源构造、数据库应用开发、W...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊