POSTGRESQL 统计信息与数据查询的准确性与多种统计信息类型

开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群。

自己做了一个统计分析有关的，思维导图

之前有一期说过，对于一些特殊的查询中的优化，可以在不建立索引和SQL优化的情况下，我们通过统计分析来进行优化的操作，效果也是显而易见的。本期，咱们的详细的说说统计分析的信息，以及作用，以及如何更加的个性化的调配我们的统计信息等。基于统计信息的门类和PG中的统计信息的表众多，我们这里只谈与查询有关的部分，也就是 table statistics ,而不是其他的统计信息的收集等。

每个表的状态的基本的信息包括于

1  表的行数

2  每个数据页面存储的数据

3  多少数据页面被标记在 visibility map 中

这些信息被保存在 pg_class 中，通过简单的语句我们可以确认以上的一些信息。

那么这些统计信息的主要作用在哪里，主要的作用就在于在数据查询中，可以通过统计信息分析出查询需要执行的计划是那些，怎么执行等问题的基础数据提供的部分。

在我们执行VACUUM FULL， Autovacuum, index ,reindex 或者手动analyze的情况下，可以收集相关的表的统计分析信息。

那么是不是有一些表，从来就没有过统计分析的情况，的确是有的，由一些表可能从来就没有进行过统计分析的数据收集，举例我们看一下

create table no_statistics (id int) with (autovacuum_enabled = false);

没有统计分析的表的数据查询是相当的混乱的，数据库并不知道这个表的具体的情况，所以查询中并不能给出一个具体的查询的好的评估。在我们针对这个表进行了相关的分析后，查询分析器才能准确的对这个表进行正确的查询评估。

我们在针对这个表进行了 vacuum后，也能对这个表进行了analyze 相关的工作，得到了表更新的行状态信息。

除此以外，在查询中我们还依赖与每个表中列的统计分析，这里就需要看PG中的另外一个表pg_stats.

通过表中关键的字段 n_distinct ，如果看到 -1 则说明表中每列的行值都不一样。正直的数字表明当前值的不同的值的数量的特性。

下面我们在PEOPLE表中的age 字段中来看看到底有多少种值，并且值的分布情况，都可以通过语句来分析出来。

 SELECT most_common_vals AS mcv,

  left(most_common_freqs::text,1000) || '...' AS mcf

FROM pg_stats

WHERE tablename = 'people' and attname = 'age';

而空值的在一个表里面的获取，也可以通过语句来处理，下面的语句就可以

SELECT round(reltuples * s.null_frac) AS rows

FROM pg_class

JOIN pg_stats s ON s.tablename = relname

WHERE s.tablename = 'people' and s.attname = 'comments';

可以看到通过上面的语句可以获得NULL 列的行数。

说到这里有几个词汇，需要注意 mcv , mcf , mcv 是most common value, mcf 是 most common freqs  这两个值直接可以形成我们数据库的列值分布的图，对于我们查询数据的指导是一个重要的指标。

而如果出现了数据量过多，在一个数组无法存放的状态，此时POSTGRESQL 会祭出直方图的方式来处理这样的情况，因为直方图存储的是值的边界，所以这样的处理的统计分析的数据，不会过于臃肿。

在对于POSTGRESQL 的统计分析中，可以参看下面这篇对于个性化的统计分析的创建和使用。

这里在上一篇中没有提到的关于建立自定义统计分析后，如何查询出自己的统计分析，可以通过select * from pg_stats_ext ; 的扩展表来查询你的自定义的统计分析的信息。

另外关于统计分析在PG 中我有一个想法就是，自动+手动+细化的工作方式，这里详细解释一下

1  对于常用的查询的表，尤其是经常UPDATE的数据表，不要等待AUTOVACUUM 去轮训他，我们实际上是可以通过手写一个程序来去定期的对他 IUD的，尤其是 U D ，较高的表定时的去判断他的数据的live 和  delete row 之间的差别，并且在超过你经验上认为应该去做统计分析的情况下，进行触发式统计分析。

2  另外经常有人问和提出的关于AUTOVACUUM 是否可以在业务繁忙期关闭的问题，个人认为不能，可以通过提高AUTOVACUUM 的一些参数达到在VACUUM 中尽量较少占用资源的，将时间拉长的方式来进行处理，而不是直接将这个表的AUTOVACUUM 给关闭掉。

最后给出一些常用的与统计分析调整列裁员值的语句

1    修改列的统计分析估值的采样（PG13以上版本）

alter table tablename alter column column_name set statistics 10000; 

默认是100，通过调整大小来存储更多的样本

2   针对一个表产生修改所有列的估算值的执行语句

 SELECT format(

          'ALTER TABLE %s ALTER %I SET STATISTICS 1000',

          attrelid::regclass,

          attname

       )

FROM pg_attribute

WHERE attrelid = 'people'::regclass

  AND attnum > 0 ;

3  对于数据表中的统计分析进行人性化展示的语句

SELECT attname, inherited, n_distinct,

       array_to_string(most_common_vals, E'\\n') as most_common_vals

FROM pg_stats

WHERE tablename = 'people';

———————————————————————————————

接着上几期，说完高尔夫 GTI，和领克 03+ ，本期必须的说说高性能钢炮中不能被不提及的 思域， 什么思域，鬼火少年，NO NO NO，我们说的思域可不是一般的思域，不是街上随处可见的那个东西，我们要说的是  思域的两厢版本，和 思域TYPE R 。

玩车业界有一句顺口溜，蓝针大众你追不起，红标本田你赶不上，银耳的奥迪你惹不起。（如果你不懂那么 额...  ）当然这些车都是进口，没有国产的

如果你是本田的爱好者，并且是 TYPE-R的死忠粉，那么你可以买一辆东风本田的思域两厢版，当然如果要买也必然是买  手动版本，曾经座过一次这个国产思域的两厢手动版本，OMG ，只要你敢踩，只要你敢，过山车的赶脚一定是有的，就是那种你被钉到座位上的感觉（当然时间很短，并且的1 -2档猛踩），过弯的时候你不注意，你的脸可能会和侧窗来一个亲密的接触。

可如果你就要要红标的本田TYPE-R ，进口42万的平行进口应该能满足你对速度的信仰。你将获得一个2.0T 的发动机 320匹的马力，400牛米的扭矩，和一个六速手动变速箱以及 245/30的大宽胎，和百公里 5.7秒的加速力。

开上这个车你必然不是鬼火少年，你就是鬼王 ！

动力总成方面，全新思域TYPE R将继续搭载上一代车型的2.0T发动机，其最大功率提升到了320马力，峰值扭矩为400牛·米，传动系统匹配6速手动变速箱，并采用前置前驱布局。悬架部分，全新思域TYPE R配备有自适应减振器、Brembo制动系统、20英寸黑色轻量化轮圈及245/30 R20规格的轮胎。此外，新车还配备了仿碳纤维小包围。（消息来源：caradvice；编译/汽车之家 刁昊）