POSTGRESQL analyze table 到底做了什么与扩展统计

PostgreSQL 中对表的状态是有单独的命令来进行状态的收集的，到底怎么对表来进行状态的收集，并且都做了什么，我们怎么来依靠这些信息来对查询进行有益的帮助。这些都将在这篇文章里面探讨。

首先我们对PG12 中，关于Analyze 的注释来仔细的阅读一遍

ANALYZE collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

Without a table_and_columns list, ANALYZE processes every table and materialized view in the current database that the current user has permission to analyze. With a list, ANALYZE processes only those table(s). It is further possible to give a list of column names for a table, in which case only the statistics for those columns are collected.

分析收集数据库中表中关于内容的状态，并将结果存储到pg_statistic 系统目录中，随后查询执行计划的工作中会利用这些数据来帮助查询更有效率的执行，节省查询时间。Analyze命令对于当前数据库中的每一个表或者物化视图进行分析, 前提是操作的用户必须具有这些表的权限,或者是这个数据库的OWNER,或SUPERUSER. 进一步讲,对于表中的某些列进行状态信息的收集.

并且对于表的分析,中外部的表也是被支持的,但也要看你的外部的表是否支持analyzed命令,部分不支持analyze的外部数据的封装器. 在postgresql 默认的设置中, autovacuum 进程,会自动去分析表,当然您也可以根据ORACLE的经验,来对表定期的进行analyze 命令的执行,来收集表中的数据变化后的状态, 这对于加速查询是十分有帮助的. 可以在比较低的工作时间中去运行analyze来保证统计信息的更新性.

这里需要提及的analyze 需要一个读锁来，此时这个工作可以和其他的工作并行工作。这里的工作包含统计表中大部分的列的值的分布，并且可以通过直方图展示每列值的分布的情况。

对于大表，analyze 会对大表取一个随机的表的内容，而不会傻到对每一个行进行一个扫描.这样做的好处是节省数据判断的时间. 但这样的结果是很可能每次运行ANALYZE会有不同的结果,当然一般这样的变化是细微的. 这样的情况下我们可以提高analyze 的手动的次数,提高整体查询计划的平稳性.

而这些数据到底存到了哪里, pg_statistic, pg_statistic 是存储analyze 命令执行后或者autovacuum 执行后统计的内容存储到了这个表. 这里注意存储的数据的值基本都是近似值.

starelid: starelid 是pg_statistic中关于这条记录是表的oid信息

staattnum: 所属表的列的编号

stainherit : 这个列标明这个列是否包含继承的子列,并且是否被统计,TRUE为统计

stanumber1：是我们这列存储的值的分布情况

stavalues1： 存储列中存储的值的明细

select * from pg_stats; pg_stats 是一个类似于可以提供相关数据库方便人类阅读的VIEW ，实际的数据还是通过pg_statistic 获得的。

对数据库表的状态的收集，一般通过autovacuum来就可以了，对于一些比较重要的业务大表，我们也可以学习类似ORACLE 定期对数据进行统计数据分析的方法来进行。

尤其我们还可以针对特定的字段来进行数据的分析和数据的收集，类似我们有一个比较大的表，并且列比较多拿此时我们对于这个表的某个字段查询时频繁，并且是复杂的，同时这个表的这个列的值还是经常变化的。那么此时我们可以针对这个表的这个列来进行分析。

SELECT relname, relkind, reltuples, relpages FROM pg_class order by reltuples desc;

我们可以看到这个表的数据的行数比较大，并且值的分布也比较广泛，也就说明、并且这个字段是bid ，有此我们可以得到两个信息

1 我们如果对这个表的统计信息进行收集，通过普通的方式来收集统计信息，是通过评估的方式，而不会是完全的方式。

2 我们分析可以对单独的这个bid 字段进行分析

以上是建立在我们的查询的where 条件是带有bid 这个条件的， 反过来我们很多的查询并不是单列查询，查询的条件和语句都比较复杂，例如 group by,

这样的情况下我们需要的统计信息可能就需要更有关系性，也就是GROUP BY 中的字段的之间的关系，通过一个统计信息能进行展示。

那我们可以做什么以下以 pgbench_accounts 中有两个字段 bid和 abalance 那么我们需要对这个表经常的使用group by 语句 ,那我们可以通过以下的方式来给查询提供更有效的查询的统计信息。

create statistics bid_ab (ndistinct) on bid,abalance from pgbench_accounts;

analyze pgbench_accounts;

SELECT stxkeys AS k, stxdndistinct AS nd test-# FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) test-# WHERE stxname = 'bid_ab';

以上我们其实就建立了一个扩展的统计信息，来帮助某些特殊的查询得到更加准确的统计分析信息。