Re: [ADMIN] since when has pg_stat_user_indexes.idx_scan been counting?

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books