Aboutstatscollected - 数据库编程

pg_class.relpages pg_class.reltuples只是近似值，和实际数据会有点误差；新建空表，首次insert对自动收集和更新统计信息，影响的表pg_class\pg_stats；对于insert操作: analyze会更新表pg_class\pg_stats，而vacuum或者acuum full只会更新pg_class，并不会更新任何统计信息pg_stats；对于update\delete操作: analyze会更新表pg_class\pg_stats，而vacuum或者acuum full只会更新pg_class，并不会更新任何统计信息pg_stats；但是vacuum full对于索引对象的pg_class.relpages貌似存在bug，只有再次analyze或者vacuum才会正确更新pg_class; gp_autostats_mode可以配置参数有： NONE ON_NO_STATS ON_CHANGE 默认是ON_NO_STATS gp_autostats_on_change_threshold参数配置阀值，与gp_autostats_mode配合使用，该值为临界值下限，比如80000，那么在超过80000也即等于80001的时候才会生效。

详细测试如下：
[gpadmin@wx60 ~]$ psql gtlions
psql (8.2.15)
Type "help" for help.
 
gtlions=# \timing on
Timing is on.
gtlions=# select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.15 (Greenplum Database 4.2.7.2 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 25 2014 18:05:04
(1 row)
 
Time: 2.020 ms
gtlions=# \pset x
Expanded display is on.
gtlions=# select * from pg_settings where name ~ 'gp_autostats';
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------
name       | gp_autostats_mode
setting    | ON_NO_STATS
unit       | 
category   | Developer Options
short_desc | Sets the autostats mode.
extra_desc | Valid values are NONE, ON_CHAN




GE, ON_NO_STATS. ON_CHANGE requires setting gp_autostats_on_change_threshold.
context    | user
vartype    | string
source     | configuration file
min_val    | 
max_val    | 
-[ RECORD 2 ]---------------------------------------------------------------------------------------------------------------------------------
name       | gp_autostats_on_change_threshold
setting    | 2147483647
unit       | 
category   | Developer Options
short_desc | Threshold for number of tuples added to table by CTAS or Insert-to to trigger autostats in on_change mode. See gp_autostats_mode.
extra_desc | 
context    | user
vartype    | integer
source     | configuration file
min_val    | 0
max_val    | 2147483647
 
Time: 4.464 ms
gtlions=# \q
[gpadmin@wx60 ~]$ gpconfig -c gp_autostats_on_change_threshold -v 80000
20141017:16:45:27:008114 gpconfig:wx60:gpadmin-[INFO]:-completed successfully
[gpadmin@wx60 ~]$ gpstop -u
20141017:16:45:32:008229 gpstop:wx60:gpadmin-[INFO]:-Starting gpstop with args: -u
20141017:16:45:32:008229 gpstop:wx60:gpadmin-[INFO]:-Gathering information and validating the environment...
20141017:16:45:32:008229 gpstop:wx60:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20141017:16:45:32:008229 gpstop:wx60:gpadmin-[INFO]:-Obtaining Segment details from master...
20141017:16:45:33:008229 gpstop:wx60:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.2.7.2 build 1'
20141017:16:45:33:008229 gpstop:wx60:gpadmin-[INFO]:-Signalling all postmaster processes to reload
. 
[gpadmin@wx60 ~]$ psql gtlions
psql (8.2.15)
Type "help" for help.
 
gtlions=# \pset x
Expanded display is on.
gtlions=# select * from pg_settings where name ~ 'gp_autostats';
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------

Aboutstatscollected(一)