PostgreSQL · 特性分析 · 统计信息计算方法

一条SQL在PG中的执行过程是：

PostgreSQL的SQL优化、执行方式为代价模型。而这里的各路径的代价计算，则是依赖于系统表中的统计信息。那么这些统计信息如何得来的？就是这里要讨论的问题。

PostgreSQL是catalog-driven型的数据库，引擎运行过程中所有所需的数据、信息都存放在系统表中，统计信息不例外。这些统计信息，则是通过SQL命令vacuum和analyze分别写入pg_class和pg_statistic中的。

参考官方文档ANALYZE

pg_class的表结构如下：

这里比较关注的是relpages和reltuples两个字段，分别表示这张表占了多少磁盘页和行数。其中行数是估计值。而这两个字段的值是通过vacuum、analyze（或create index)来更新的。

参考官方文档pg_class

pg_statistic的表结构如下：

这里的stanullfrac、stadistinct、stakindN、staopN、stanumbersN、stavaluesN等是我们所关注的值。其中：

stakindN

用于表示后面number、values所表示的数据用途，被用于生成pg_stats。如1则表示是MCV的值；2表示直方图（histogram）的值；3表示相关性（correlation）的值等。kind的取值范围：1~99，內核占用；100~199，PostGIS占用；200~299，ESRI ST_Geometry几何系统占用；300~9999，公共占用。

staopN

用于表示该统计值支持的操作，如’=’或’atttypid, attlen, etc. This is * because some index opclasses store a different type than the underlying * column/expression. Instead use attrtypid, attrtypmod, and attrtype for * information about the datatype being fed to the typanalyze function. */ Form_pg_attribute attr; /* copy of pg_attribute row for column */ Oid attrtypid; /* type of data being analyzed */ int32 attrtypmod; /* typmod of data being analyzed */ Form_pg_type attrtype; /* copy of pg_type row for attrtypid */ MemoryContext anl_context; /* where to save long-lived data */ /* * These fields must be filled in by the typanalyze routine, unless it * returns FALSE. */ AnalyzeAttrComputeStatsFunc compute_stats; /* function pointer */ int minrows; /* Minimum # of rows wanted for stats */ void *extra_data; /* for extra type-specific data */ /* * These fields are to be filled in by the compute_stats routine. (They * are initialized to zero when the struct is created.) */ bool stats_valid; float4 stanullfrac; /* fraction of entries that are NULL */ int32 stawidth; /* average width of column values */ float4 stadistinct; /* # distinct values */ int16 stakind[STATISTIC_NUM_SLOTS]; Oid staop[STATISTIC_NUM_SLOTS]; int numnumbers[STATISTIC_NUM_SLOTS]; float4 *stanumbers[STATISTIC_NUM_SLOTS]; int numvalues[STATISTIC_NUM_SLOTS]; Datum *stavalues[STATISTIC_NUM_SLOTS]; /* * These fields describe the stavalues[n] element types. They will be * initialized to match attrtypid, but a custom typanalyze function might * want to store an array of something other than the analyzed column's * elements. It should then overwrite these fields. */ Oid statypid[STATISTIC_NUM_SLOTS]; int16 statyplen[STATISTIC_NUM_SLOTS]; bool statypbyval[STATISTIC_NUM_SLOTS]; char statypalign[STATISTIC_NUM_SLOTS]; /* * These fields are private to the main ANALYZE code and should not be * looked at by type-specific functions. */ int tupattnum; /* attribute number within tuples */ HeapTuple *rows; /* access info for std fetch function */ TupleDesc tupDesc; Datum *exprvals; /* access info for index fetch function */ bool *exprnulls; int rowstride; } VacAttrStats;

具体的针对字段检查的步骤如下：

确定这个字段是否可以分析，如果不可以，则返回NULL。 一般有两种情况致使这个字段不进行分析：字段已被删除（已删除的字段还存在于系统表中，只是作了标记）；用户指定了字段。

获取数据类型，并决定针对该类型的采样数据量和统计函数 不同的类型，其分析函数也不同，比如array_typanalyze。如果该类型没有对应的分析函数，则采用标准的分析函数std_typanalyze。 以标准分析函数为例，其确定了两个地方：采样后用于统计的函数（compute_scalar_stats或compute_minimal_stats，和采样的记录数（现在默认是300 * 100）。

索引 索引在PG中，是以与表类似的方式存在的。当analyze没有指定字段，或者是继承表的时候，也会对索引进行统计信息的计算。以AccessShareLock打开该表上所有的锁，同样的检查索引的每个字段是否需要统计、如何统计等。

采样 选择表所有字段所需采样数据量的最大值作为最终采样的数据量。当前PG采取的两阶段采样的算法：

这里的采样并不会处理事务中的记录，如正在插入或删除的记录。但如果删除或插入操作是在当前analyze所在的事务执行的，那么插入的是被记为live_tuples并且加入统计的；删除的会被记为dead_tuples而不加入统计。

由此会可能产生两个问题：

以上两种情况，重复执行analyze即可。但有可能因统计信息不准确导致的执行计划异常而造成短时间的性能波动，需要注意！这里也说明了长事务的部分危害。

统计、计算 在获取到相应样本数据后，针对每个字段分别进行分析。 首先会依据当前字段的值，对记录进行排序。因在取出样本数据的时候，按照tuple在磁盘中的位置顺序取出的，因此对值进行排序后即可计算得出相关性。另外，在排序后，也更容易计算统计值的频率，从而得出MCV和MCF。这里采用的快速排序！ 之后，会根据每个值进行分析：

如果是NULL，则计数 NULL概率的计算公式是：stanullfrac = null_number / sample_row_number。

计算出现最多的值，和相应频率

当采样中的值中，存在有唯一值并且存在不唯一值的时候，则依据以下的公式（by Haas and Stokes in IBM Research Report RJ 10025）：

其中，N是指所有的记录数，即pg_class.reltuples；n是指sample_row_number，即采样的记录数；f1则是只出现一次的值的数据；d则是采样中所有的值的数量。

MCV / MCF 并不是所有采样的值都会被列入MCV/MCF。首先是如果可以，则将所有采样的记录放到MCV中，如表所有的记录都已经取作采样的时候；其次，则是选取那些出现频率超过平均值的值，事实上是超过平均值的25%；那些出现频率大于直方图的个数的倒数的时候等。

直方图 计算直方图，会首先排除掉MCV中的值。 意思是直方图中的数据不包含MCV/MCF的部分，两者的值是补充关系而且不会重合，但不一定互补（两种加起来未必是全部数据）。这个也与成本的计算方式有关系，请参考row-estimation-examples 。 其计算公式相对比较简单，如下：

values[(i * (nvals - 1)) / (num_hist - 1)]

i指直方图中的第几列；nvals指当前还有多少个值；num_hist则指直方图中还有多少列。计算完成后，kind的值会被置为2。 到此，采样的统计基本结束。

完成采样的计算后，通过內部函数更新相关的统计信息到pg_statistic，更新relpages和totale rows到pg_class中。即完成了一次统计信息的收集。