Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 10 黑科技 - 自定义统计信息

4 minute read

背景

PostgreSQL和Oracle一样，优化器是基于成本的估算。

成本估算中很重要的一个环节是估计每个执行节点返回的记录数。

例如两张表JOIN的时候，如果走HASH JOIN那么需要选择记录数少的那个作为哈希表。

又比如求多个字段的group by，评估返回多少条记录给上层节点。

对于基于单列统计的的柱状图，估算单个字段条件的选择性是很准确的，而估算多个字段时，PostgreSQL默认使用独立属性，直接以多个字段选择性相乘的方法计算多个字段条件的选择性。不是很准确。

PostgreSQL 10引入了一个黑科技功能，允许用户自定义多个字段的统计信息，目前支持多列相关性和多列唯一值两种统计。

由于多列统计涉及到许多组合（N阶乘种组合），因此默认不会对所有字段进行任意组合的统计，用户可以根据实际的业务需求，对需要and查询，组合group by的字段(例如 where a xx and b xx, group by a,b)。创建对应的自定义统计信息。

例子讲解

1、建表，11个字段。

postgres=# create table tbl(id int, c1 int, c2 text, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int);    
CREATE TABLE

2、写入测试数据，1000万条。

postgres=# insert into tbl select     
postgres-# id,     
postgres-# random()*100, substring(md5(random()::text), 1, 4), random()*900, random()*10000, random()*10000000,     
postgres-# random()*100000, random()*100, random()*200000, random()*40000, random()*90000     
postgres-# from generate_series(1,10000000) t(id);    
INSERT 0 10000000

3、分析表

postgres=# analyze tbl;  
ANALYZE

得到reltuples为1e+07，后面要用于计算。

postgres=# select reltuples from pg_class where relname='tbl';  
-[ RECORD 1 ]----  
reltuples | 1e+07

4、SQL举例

4.1 单个字段条件

postgres=# explain (analyze) select * from tbl where c1=1;  
                                                 QUERY PLAN                                                    
-------------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl  (cost=0.00..218458.08 rows=93865 width=45) (actual time=0.018..755.833 rows=99527 loops=1)  
   Filter: (c1 = 1)  
   Rows Removed by Filter: 9900473  
 Planning time: 0.077 ms  
 Execution time: 763.151 ms  
(5 rows)

可以推算得到c1=1的选择性为: 93865/1e+07 。

postgres=# explain (analyze) select * from tbl where c2='abc';  
                                               QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl  (cost=0.00..218458.08 rows=148 width=45) (actual time=874.473..874.473 rows=0 loops=1)  
   Filter: (c2 = 'abc'::text)  
   Rows Removed by Filter: 10000000  
 Planning time: 0.080 ms  
 Execution time: 874.505 ms  
(5 rows)

可以推算得到c2=’abc’的选择性为: 148/1e+07 。

4.2 多个字段条件

postgres=# explain (analyze) select * from tbl where c1=1 and c2='abc';  
                                              QUERY PLAN                                                 
-------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl  (cost=0.00..243458.09 rows=1 width=45) (actual time=802.347..802.347 rows=0 loops=1)  
   Filter: ((c1 = 1) AND (c2 = 'abc'::text))  
   Rows Removed by Filter: 10000000  
 Planning time: 0.116 ms  
 Execution time: 802.374 ms  
(5 rows)

rows=1是怎么得来的呢，在没有自定义统计信息时，是这么算的，算这两个条件完全不相干，所以选择性直接相乘。

(93865/1e+07) * (148/1e+07) * 1e+07 = 1.389202 ~= 1

4.3 单个字段条件求唯一值

postgres=# explain (analyze) select c1,count(*) from tbl group by c1;  
                                                       QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------  
 HashAggregate  (cost=243458.09..243459.10 rows=101 width=12) (actual time=3256.458..3256.473 rows=101 loops=1)  
   Group Key: c1  
   ->  Seq Scan on tbl  (cost=0.00..193458.06 rows=10000006 width=4) (actual time=0.013..1252.169 rows=10000000 loops=1)  
 Planning time: 0.061 ms  
 Execution time: 3256.518 ms  
(5 rows)

rows=101来自pg_stats.n_distinct , tbl.c1列的统计。

n_distinct             | 101

4.4 多个字段条件求唯一值

postgres=# explain (analyze) select c1,c2,count(*) from tbl group by c1,c2;  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 GroupAggregate  (cost=1561215.43..1671215.50 rows=1000001 width=17) (actual time=11414.144..16549.549 rows=5147139 loops=1)  
   Group Key: c1, c2  
   ->  Sort  (cost=1561215.43..1586215.45 rows=10000006 width=9) (actual time=11414.132..13905.616 rows=10000000 loops=1)  
         Sort Key: c1, c2  
         Sort Method: external merge  Disk: 185984kB  
         ->  Seq Scan on tbl  (cost=0.00..193458.06 rows=10000006 width=9) (actual time=0.008..1567.909 rows=10000000 loops=1)  
 Planning time: 0.082 ms  
 Execution time: 16952.301 ms  
(8 rows)

5、自定义统计信息语法讲解

Command:     CREATE STATISTICS  
Description: define extended statistics  
Syntax:  
CREATE STATISTICS [ IF NOT EXISTS ] statistics_name  
    [ ( statistic_type [, ... ] ) ]  
    ON column_name, column_name [, ...]  
    FROM table_name

创建自定义统计信息，指定需要自定义统计的字段名，需要统计依赖性、唯一性（不指定则都统计）。

6、创建自定义统计信息

我们创建c1 c2 c3这三个字段的自定义统计信息。

postgres=# create statistics s1 on c1,c2,c3 from tbl;  
CREATE STATISTICS

自定义统计信息创建好之后，需要分析表，才会生成。

postgres=# analyze tbl;  
ANALYZE

7、自定义统计信息解读

postgres=# select * from pg_statistic_ext where stxname='s1';  
-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------------  
stxrelid        | 16384   -- 表  
stxname         | s1  
stxnamespace    | 2200  
stxowner        | 10  
stxkeys         | 2 3 4  -- 表示第2,3,4列创建自定义统计信息。  
stxkind         | {d,f}   -- 统计 字段之间的依赖度（相关性）、唯一值个数。  
stxndistinct    | {"2, 3": 3747653, "2, 4": 87662, "3, 4": 9001205, "2, 3, 4": 10000006}   -- 组合唯一值个数  
stxdependencies | {"3 => 2": 0.642100, "3 => 4": 0.639567, "2, 3 => 4": 0.995000, "2, 4 => 3": 0.712033, "3, 4 => 2": 0.999667}    
-- 字段之间的依赖性，当使用多个字段AND条件时，用于代替多个孤立条件的选择性相乘。选择性乘以依赖度，选出最后计算结果最低的，作为最终选择性。

stxndistinct，很好理解，就是字段组合后的唯一值个数。

stxdependencies，当一个字段确定后，另一个字段是唯一值的比例有多少？例如a=1, b={1,2,3,4,5,….}; a=2,b=1，这里只有后面这条算b依赖a。 依赖条数除以总数即a => b的依赖值。可以用于评估两个字段都是等值条件时的选择性。生成stxdependencies的算法很简单，a => b 等于 count(distinct a)/count(distinct a,b)；b=>a 等于 count(distinct b)/count(distinct a,b)；

postgres=# \d t  
                 Table "public.t"  
 Column |  Type   | Collation | Nullable | Default   
--------+---------+-----------+----------+---------  
 c1     | integer |           |          |   
 c2     | integer |           |          |   
Statistics objects:  
    "public"."s1" (ndistinct, dependencies) ON c1, c2 FROM t  
  
postgres=# select * from pg_statistic_ext where stxname='s2';  
 stxrelid | stxname | stxnamespace | stxowner | stxkeys | stxkind |   stxndistinct   |             stxdependencies                
----------+---------+--------------+----------+---------+---------+------------------+------------------------------------------  
    16394 | s2      |         2200 |       10 | 1 2     | {d,f}   | {"1, 2": 105358} | {"1 => 2": 0.083733, "2 => 1": 0.916200}  
(1 row)  
  
postgres=# select count(distinct c1) from t;  
 count   
-------  
 10000  
(1 row)  
  
postgres=# select count(distinct c2) from t;  
 count    
--------  
 100001  
(1 row)  
  
postgres=# select count(distinct (c1,c2)) from t;  
s count    
--------  
 109999  
(1 row)  
  
postgres=# select 10000/109999.0;  
        ?column?          
------------------------  
 0.09090991736288511714  
(1 row)  
  
postgres=# select 100001/109999.0;  
        ?column?          
------------------------  
 0.90910826462058745989  
(1 row)  
  
postgres=# select 0.083733+0.916200;  
 ?column?   
----------  
 0.999933  
(1 row)

评估例子

a = ? and b = ? 的选择性   
=   
min( "选择性(a) * (a=>b)" , "选择性(b) * (b=>a)" )

8、SQL举例

8.1 多个字段条件

postgres=# explain (analyze) select * from tbl where c1=1 and c2='abc';  
                                               QUERY PLAN                                                 
--------------------------------------------------------------------------------------------------------  
 Seq Scan on tbl  (cost=0.00..243458.09 rows=96 width=45) (actual time=802.182..802.182 rows=0 loops=1)  
   Filter: ((c1 = 1) AND (c2 = 'abc'::text))  
   Rows Removed by Filter: 10000000  
 Planning time: 0.098 ms  
 Execution time: 802.203 ms  
(5 rows)

创建了多字段统计信息后，这两个条件在统计信息之列，所以可以用他们的依赖度来算组合AND条件的选择性。

算法：选择性最低的条件的选择性 * 与另一个字段的依赖度，得到组合选择性。

0.642100 * (148/1e+07) * 1e+07 = 95.0308 (这里反推的选择性有一点失真，大概原理就是这样)

8.2 多个字段条件求唯一值

postgres=# explain (analyze) select c1,c2,count(*) from tbl group by c1,c2;  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 GroupAggregate  (cost=1561215.43..1698692.02 rows=3747653 width=17) (actual time=11632.613..16843.873 rows=5147139 loops=1)  
   Group Key: c1, c2  
   ->  Sort  (cost=1561215.43..1586215.45 rows=10000006 width=9) (actual time=11632.597..14202.457 rows=10000000 loops=1)  
         Sort Key: c1, c2  
         Sort Method: external merge  Disk: 185984kB  
         ->  Seq Scan on tbl  (cost=0.00..193458.06 rows=10000006 width=9) (actual time=0.008..1567.024 rows=10000000 loops=1)  
 Planning time: 0.215 ms  
 Execution time: 17246.889 ms  
(8 rows)

直接使用了多列统计信息中的唯一值统计信息3747653。

"2, 3": 3747653

9、如何利用自定义统计信息统计多个字段 唯一值、多列依赖性。

9.1 PostgreSQL已有了单列唯一值的统计，我们可以通过pg_stats.n_distinct以及pg_class.reltuples查询到。

通过create statistic，数据库会自动收集多列值的统计信息，我们查询pg_statistic_ext.stxndistinct，可以得到多列唯一值的估计值。

9.2 多列依赖性指的是列与列之间值的依赖强度，是一个小于等于1的系数。1表示强依赖，

小结

1、PostgreSQL 10支持自定义多列统计信息，目前支持 多列组合唯一值、列与列的相关性。

2、多列唯一值可用于评估group by, count(distinct)等。

3、列与列相关性可用于估算多个列AND条件的选择性。算法

a = ? and b = ? 的选择性   
=   
min( "选择性(a) * (a=>b)" , "选择性(b) * (b=>a)" )

4、由于多列统计信息的组合很多，因此数据库默认只统计单列的柱状图。当用户意识到某些列会作为组合查询列时，再创建自定义多列统计信息即可。

参考

https://www.postgresql.org/docs/10/static/multivariate-statistics-examples.html

https://www.postgresql.org/docs/10/static/sql-createstatistics.html

https://www.postgresql.org/docs/10/static/planner-stats.html#planner-stats-extended

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...