EXPLAIN执行计划分析一文中介绍了如何通过EXPLAIN语句查看SQL的执行计划，本文将进一步介绍统计信息是什么以及优化器是如何利用统计信息进行估算的。
Overview
梧桐数据库为一条查询语句生成计划需要经过以下几个阶段：
- 词法&语法解析 – 语法校验，输入的SQL是否符合语法
- 语义解析 – 将SQL中的对象转换为数据库内部的对象
- RBO – 逻辑优化，比如子查询提升，标量子查询提升为join等
- CBO – 代价优化，根据表的物理信息选取合适的执行方式
- 查询执行
统计信息在CBO阶段起到了至关重要的作用，本文对统计信息的概念和如何在CBO中应用进行一个介绍。
统计信息是指数据库描述表或者索引数据特征的信息，常见的有表记录条数（记录在pg_class的reltuples字段）、页面数等描述表规模的信息，以及描述数据分布特征的MCV(高频非NULL值)、HISTOGRAM（直方图）、CORRELATION等信息。
CREATE TABLE t1(a INT, b INT); INSERT INTO t1 SELECT x,x FROM generate_series(1,10) x; INSERT INTO t1 SELECT x,x FROM generate_series(1,90) x;
1. 单列统计信息
-- ANALYZE t1; wutongdb=# select * from pg_statistic where starelid='t1'::regclass AND staattnum=1; -- 查看a列统计信息 -[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- starelid | 409600 staattnum | 1 stainherit | f stanullfrac | 0 stawidth | 4 stadistinct | -0.9 stakind1 | 1 stakind2 | 2 stakind3 | 3 stakind4 | 0 stakind5 | 0 staop1 | 96 staop2 | 97 staop3 | 97 staop4 | 0 staop5 | 0 stacoll1 | 0 stacoll2 | 0 stacoll3 | 0 stacoll4 | 0 stacoll5 | 0 stanumbers1 | {0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02} stanumbers2 | stanumbers3 | {0.23432343} stanumbers4 | stanumbers5 | stavalues1 | {1,2,3,4,5,6,7,8,9,10} stavalues2 | {11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90} stavalues3 | stavalues4 | stavalues5 |
1.1 字段说明
- starelid（表oid）和staattnum（列号）说明了统计信息对应的基表信息，例子里的staattnum属于1说明该条统计信息对应的是t1表的第一列(a)。
- stanullfrac：表的空值比例。
- stadistinct：正数描述的是distinct值的实际值，负数则是distinct值的比例，例子里共100条数据，但a取值为1-10的行存在重复数据，所以该字段的值为90/100=-0.9。
- stakind1-stakind5：说明staxxx1-staxxx5字段对应的统计信息类型，主要有mcv(1)/直方图(2)/CORRELATION(3)。
- stanumbersx：记录了统计信息的频率，需要结合上下文来查看。
- stavaluesx：具体的统计信息取值，比如stavalues1记录了MCV为1-10,对应的频率均为0.2，mcv值不会出现在直方图中。stavlue2则说明了直方图信息，一般情况下是等频直方图（每两个值之间的概率相等）。
1.2 行数估算
当没有统计信息或者表达式过于复杂的情况下，一般会选取一个默认的选择率作为该条件的选择率。有统计信息的情况下可以总结为一下几个场景：
- 等值条件
1）将条件的常量和stavalues中的MCV对比，如果满足过滤条件，则累加对应的stanumbers字段中的频率；
2）对直方图中数据，按distinct值个数粗略估算选择率； - 范围条件
1）对比MCV对应频率，满足过滤条件则累加选择率；
2）对直方图数据，按边界位置估算选择率；
在没有多列统计信息的情况下，对于多个过滤条件会基于数据完全独立的假设来进行估算，比如restriction1 AND restriction2，那么会认为两个条件完全独立并将两个条件分别的选择率相乘来作为最终的选择率。除此之外，OR和范围条件也会进行相应的修正。
2. 扩展统计信息
梧桐数据库支持扩展统计信息，可以收集依赖性、多列distinct值和多列mcv。
wutongdb=# \h CREATE STATISTIC Command: CREATE STATISTICS Description: define extended statistics Syntax: CREATE STATISTICS [ IF NOT EXISTS ] statistics_name [ ( statistics_kind [, ... ] ) ] ON column_name, column_name [, ...] FROM table_name
多列统计信息记录在pg_statistic_ext_data系统表，数据经过处理，大部分字段无法直接读取，所以就不再展开说明。前面提到梧桐数据库在估算选择率是基于数据完全独立的假设，那么下面这个例子会出现因为数据并不是完全独立而导致的低估
wutongdb=# EXPLAIN SELECT * FROM t1 WHERE a=1 AND b=1; QUERY PLAN ------------------------------------------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.00 rows=1 width=8) -> Seq Scan on t1 (cost=0.00..431.00 rows=1 width=8) Filter: ((a = 1) AND (b = 1)) Optimizer: Orca query Optimizer NewExecutor: ON (5 rows)
此时可以考虑使用依赖性统计信息进行优化，这样在估算时两个条件不再是认为相互独立的，而是存在一定程度的关联，而关联性的强弱决定了整体最终的选择率。
-- CREATE STATISTICS s1 (dependencies) ON a,b FROM t1; ANALYZE t1; wutongdb=# EXPLAIN SELECT * FROM t1 WHERE a=1 AND b=1; QUERY PLAN ------------------------------------------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.00 rows=2 width=8) -> Seq Scan on t1 (cost=0.00..431.00 rows=1 width=8) Filter: ((a = 1) AND (b = 1)) Optimizer: Orca query Optimizer NewExecutor: ON (5 rows)
例子由于数据量较少，估算的准确性虽然提升但并未达到数量级的地步。在一些数据量大且数据特征明显的情况下，扩展统计信息会有更好的效果。多列distinct也可以在涉及多列的聚集场景中起到明显作用，本文就不再额外进行说明。
3. 总结
梧桐数据库提供了丰富的统计信息，绝大多数情况下analyze+单列统计信息可以满足要求，但是在一些特定的场景下仍需要手动进行调优。本文仅对梧桐数据库的统计信息内容和简单条件的估算原理进行了介绍，更深入的原理还是需要大家进一步的探索和学习。
