Message-ID: <4DCC96BB.1030609@2ndquadrant.com>
Date: Thu, 12 May 2011 22:26:03 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Tomas Vondra <tv@fuzzy.cz>
CC: pgsql-performance@postgresql.org
Subject: Re: [ADMIN] since when has pg_stat_user_indexes.idx_scan
	been counting?
References: <BANLkTikhNLPhCjE819=RFG-+CB6UV4GbUA@mail.gmail.com>
	<BANLkTimeo-P+=fxKzui1Md2Siq_YJ7ASug@mail.gmail.com>
	<BANLkTinTsg18Wk9+JPrpfOXCkn-W0D2TNw@mail.gmail.com>
	<4DCC4718.8000409@fuzzy.cz>
In-Reply-To: <4DCC4718.8000409@fuzzy.cz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tomas Vondra wrote:
> Stats about access to the data (index/seq scans, cache hit ratio etc.)
> are stored in pg_stat_* and pg_statio_* catalogs, and are updated after
> running each query. AFAIK it's not a synchronous process, but when a
> backend finishes a query, it sends the stats to the postmaster (and
> postmaster updates the catalogs).
>   

Description in the docs goes over this in a little more detail 
http://www.postgresql.org/docs/current/static/monitoring-stats.html :

"The statistics collector communicates with the backends needing 
information (including autovacuum) through temporary files. These files 
are stored in the pg_stat_tmp subdirectory...When using the statistics 
to monitor current activity, it is important to realize that the 
information does not update instantaneously. Each individual server 
process transmits new statistical counts to the collector just before 
going idle; so a query or transaction still in progress does not affect 
the displayed totals. Also, the collector itself emits a new report at 
most once per PGSTAT_STAT_INTERVAL milliseconds (500 unless altered 
while building the server). So the displayed information lags behind 
actual activity. However, current-query information collected by 
track_activities is always up-to-date."

It's not synchronous at all.  The clients create a temporary file for 
the statistics collector and move on.  The actual statistics don't get 
updated until the statistics collector decides enough time has passed to 
bother, which defaults to at most every 500ms.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books