作者
digoal
日期
2017-09-02
标签
PostgreSQL , 多列统计信息 , 多列唯一值 , 多列依赖度 , 任意维度TOP N
背景
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 abc';
QUERY PLAN
Seq Scan on tbl (cost=0.00..218458.08 rows=148 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 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 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 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 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 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
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
京公网安备 11010802041100号