postgresql中的统计信息

 pg里面有一个专门的进程 statistics collector 负责对数据库，表，函数的调用次数进行统计，通过socket与执行查询的进程进行通信，当执行语句的进程，在执行一条语句时，会在执行前，把上条语句的统计信息通过socket发送给 statistics collector 进程，这样做是因为上个事务已经 commit 或 rollback 了，统计的是事务已完成的数量。

 

statistics collector 进程等待的timeout是2秒，检查是否有统计数据需要接收。在数据库关闭时会把统计信息记录入文件 PGSTAT_STAT_PERMANENT_FILENAME 里 ，启动时读取

 

statistics collector 进程显示的全部事结束事物的统计信息，不是实时的，这个进程是pg 的必起进程之一。

 

 

/* 统计文件所在的目录 */#define PGSTAT_STAT_PERMANENT_FILENAME "global/pgstat.stat"#define PGSTAT_STAT_PERMANENT_TMPFILE "global/pgstat.tmp" /*统计文件的内容,先写到 PGSTAT_STAT_PERMANENT_TMPFILE, 之后改名成 PGSTAT_STAT_PERMANENT_FILENAME 1。常量 PGSTAT_FILE_FORMAT_ID 2。PgStat_GlobalStats3。每个数据库，后面是表，之后是函数的统计示例如： D PgStat_StatDBEntry （T PgStat_StatTabEntry， T PgStat_StatTabEntry 。。。） （F PgStat_StatFuncEntry， F PgStat_StatFuncEntry 。。。） d*//* * Global statistics kept in the stats collector */typedef struct PgStat_GlobalStats{ TimestampTz stats_timestamp; /* time of stats file update */ PgStat_Counter timed_checkpoints; PgStat_Counter requested_checkpoints; PgStat_Counter buf_written_checkpoints; PgStat_Counter buf_written_clean; PgStat_Counter maxwritten_clean; PgStat_Counter buf_written_backend; PgStat_Counter buf_fsync_backend; PgStat_Counter buf_alloc; TimestampTz stat_reset_timestamp;} PgStat_GlobalStats;
 
/* ---------- * PgStat_StatDBEntry   The collector's data per database * ---------- */typedef struct PgStat_StatDBEntry{ Oid   databaseid; PgStat_Counter n_xact_commit; PgStat_Counter n_xact_rollback; PgStat_Counter n_blocks_fetched; PgStat_Counter n_blocks_hit; PgStat_Counter n_tuples_returned; PgStat_Counter n_tuples_fetched; PgStat_Counter n_tuples_inserted; PgStat_Counter n_tuples_updated; PgStat_Counter n_tuples_deleted; TimestampTz last_autovac_time; PgStat_Counter n_conflict_tablespace; PgStat_Counter n_conflict_lock; PgStat_Counter n_conflict_snapshot; PgStat_Counter n_conflict_bufferpin; PgStat_Counter n_conflict_startup_deadlock; PgStat_Counter n_temp_files; PgStat_Counter n_temp_bytes; PgStat_Counter n_deadlocks;
 TimestampTz stat_reset_timestamp;
 /*  * tables and functions must be last in the struct, because we don't write  * the pointers out to the stats file.  */ HTAB    *tables; HTAB    *functions;} PgStat_StatDBEntry;
 
 
/* ---------- * PgStat_StatTabEntry   The collector's data per table (or index) * ---------- */typedef struct PgStat_StatTabEntry{ Oid   tableid;
 PgStat_Counter numscans;
 PgStat_Counter tuples_returned; PgStat_Counter tuples_fetched;
 PgStat_Counter tuples_inserted; PgStat_Counter tuples_updated; PgStat_Counter tuples_deleted; PgStat_Counter tuples_hot_updated;
 PgStat_Counter n_live_tuples; PgStat_Counter n_dead_tuples; PgStat_Counter changes_since_analyze;
 PgStat_Counter blocks_fetched; PgStat_Counter blocks_hit;
 TimestampTz vacuum_timestamp;  /* user initiated vacuum */ PgStat_Counter vacuum_count; TimestampTz autovac_vacuum_timestamp;  /* autovacuum initiated */ PgStat_Counter autovac_vacuum_count; TimestampTz analyze_timestamp;  /* user initiated */ PgStat_Counter analyze_count; TimestampTz autovac_analyze_timestamp;  /* autovacuum initiated */ PgStat_Counter autovac_analyze_count;} PgStat_StatTabEntry;
/* ---------- * PgStat_StatFuncEntry   The collector's data per function * ---------- */typedef struct PgStat_StatFuncEntry{ Oid   functionid;
 PgStat_Counter f_numcalls;
 PgStat_Counter f_time;  /* times in microseconds */ PgStat_Counter f_time_self;} PgStat_StatFuncEntry;