数据库内核月报

数据库内核月报 - 2016 / 05

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

Author: zhibai

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

----> SQL输入
----> 解析SQL,获取解析后的语法树
----> 分析、重写语法树,获取查询树
----> 根据重写、分析后的查询树计算各路径代价,从而选择一条成本最优的执行树
----> 根据执行树进行执行
----> 获取结果并返回

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

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

参考官方文档ANALYZE

pg_class && pg_statistic

pg_class的表结构如下:

=> \d pg_class
      Table "pg_catalog.pg_class"
     Column     |   Type    | Modifiers
----------------+-----------+-----------
 relname        | name      | not null
 ...
 relpages       | integer   | not null
 reltuples      | real      | not null
 ...
 relkind        | "char"    | not null
 relnatts       | smallint  | not null
 ...
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

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

参考官方文档pg_class

pg_statistic的表结构如下:

=> \d pg_statistic
  Table "pg_catalog.pg_statistic"
   Column    |   Type   | Modifiers
-------------+----------+-----------
 starelid    | oid      | not null
 staattnum   | smallint | not null
 stainherit  | boolean  | not null
 stanullfrac | real     | not null
 stawidth    | integer  | not null
 stadistinct | real     | not null
 stakind1    | smallint | not null
 stakind2    | smallint | not null
 stakind3    | smallint | not null
 stakind4    | smallint | not null
 stakind5    | smallint | not null
 staop1      | oid      | not null
 staop2      | oid      | not null
 staop3      | oid      | not null
 staop4      | oid      | not null
 staop5      | oid      | not null
 stanumbers1 | real[]   |
 stanumbers2 | real[]   |
 stanumbers3 | real[]   |
 stanumbers4 | real[]   |
 stanumbers5 | real[]   |
 stavalues1  | anyarray |
 stavalues2  | anyarray |
 stavalues3  | anyarray |
 stavalues4  | anyarray |
 stavalues5  | anyarray |
Indexes:
    "pg_statistic_relid_att_inh_index" UNIQUE, btree (starelid, staattnum, stainherit)

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

参考官方文档pg_statistic

执行方式

vacuum和analyze的执行可以通过两种方式来触发,一种是DB用户执行,如定时脚本或人工执行;一种是autovacuum。两个操作持有相同类型的锁ShareUpdateExclusiveLock,与DDL互斥。

autovacuum是PostgreSQL提供的一个deamon进程,会在一定时间內或者DML多到一定程度时触发vacuum或analyze。这里的一定时间和一定程度可以通过autovacuum的一系列配置实现,如autovacuum_naptime、autovacuum_max_workers 、autovacuum_vacuum_threshold等;且vacuum和analyze的触发算法和依赖参数并不尽相同。

注:请参考 autovacuum_vacuum_thresholdThe Autovacuum Daemon

analyze

vacuum本身除了负责更新relpages和reltuples等之外,最主要的是:

  1. 回收被更新和删除占用的空间
  2. 回收事务id,冻结老的事务id,以防止这部分老数据丢失

而analyze则主要是收集统计信息,并存储到pg_statistic表中。其主要的步骤如下:

/*
 * These are the valid values of type LOCKMODE for all the standard lock
 * methods (both DEFAULT and USER).
 */

/* NoLock is not a lock mode, but a flag value meaning "don't get a lock" */
#define NoLock					0

#define AccessShareLock			1		/* SELECT */
#define RowShareLock			2		/* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock		3		/* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4		/* VACUUM (non-FULL),ANALYZE, CREATE
										 * INDEX CONCURRENTLY */
#define ShareLock				5		/* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock	6		/* like EXCLUSIVE MODE, but allows ROW
										 * SHARE */
#define ExclusiveLock			7		/* blocks ROW SHARE/SELECT...FOR
										 * UPDATE */
#define AccessExclusiveLock		8		/* ALTER TABLE, DROP TABLE, VACUUM
										 * FULL, and unqualified LOCK TABLE */
typedef struct VacAttrStats
{
	/*
	 * These fields are set up by the main ANALYZE code before invoking the
	 * type-specific typanalyze function.
	 *
	 * Note: do not assume that the data being analyzed has the same datatype
	 * shown in attr, ie do not trust attr->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;

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

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