On Mon, 2010-09-13 at 16:47 -0700, Josh Berkus wrote: > On 9/13/10 4:41 PM, Joshua D. Drake wrote: > > On Mon, 2010-09-13 at 16:06 -0700, Josh Berkus wrote: > >> All, > >> > >> I've been looking at pg_stat_user_tables (in 8.3, because of a project I > >> have), and it appears that autovacuum, and only autovaccum, updates the > >> data for this view. This means that one can never have data in > >> pg_stat_user_tables which is completely up-to-date, and if autovacuum is > >> off, the view is useless. > > > > As I recall its kept in shared_buffers (in some kind of counter) and > > updated only when it is requested or when autovacuum fires. This was > > done because we used to write stats every 500ms and it was a bottleneck. > > (IIRC) > > Yes, looks like it only gets updated on SELECT or on autovacuum. > > Thing is, a full VACUUM ANALYZE on the database, or even just ANALYZE, > should update some of the counters. And currently it doesnt, resulting > in pg_class.reltuples often being far more up to date than > pg_stat_user_tables.n_live_tup. And frankly, no way to reconcile those > two stats. If you select from pg_stat_user_tables, the counters should be reasonably close unless your default_statistics_target is way off and then pg_class.reltuples would be wrong. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
