Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 386B46327B0 for ; Mon, 13 Sep 2010 20:42:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 99265-02 for ; Mon, 13 Sep 2010 23:42:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from spinlock.commandprompt.com (host-215.commandprompt.net [207.173.203.215]) by mail.postgresql.org (Postfix) with ESMTP id B16CE63550E for ; Mon, 13 Sep 2010 20:41:50 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by spinlock.commandprompt.com (Postfix) with ESMTP id 2B696348013; Mon, 13 Sep 2010 16:41:48 -0700 (PDT) X-Virus-Scanned: Debian amavisd-new at commandprompt.com Received: from spinlock.commandprompt.com ([127.0.0.1]) by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Xo-LUale9npE; Mon, 13 Sep 2010 16:41:45 -0700 (PDT) Received: from [192.168.1.132] (unknown [75.142.155.118]) by spinlock.commandprompt.com (Postfix) with ESMTPSA id 3F2FD348011; Mon, 13 Sep 2010 16:41:45 -0700 (PDT) Subject: Re: Where does data in pg_stat_user_tables come from? From: "Joshua D. Drake" Reply-To: jd@commandprompt.com To: Josh Berkus Cc: pgsql-performance@postgresql.org In-Reply-To: <4C8EAE5E.70201@agliodbs.com> References: <4C8EAE5E.70201@agliodbs.com> Content-Type: text/plain; charset="UTF-8" Organization: Command Prompt, Inc. Date: Mon, 13 Sep 2010 16:41:28 -0700 Message-ID: <1284421288.28610.501.camel@jd-desktop.unknown.charter.com> Mime-Version: 1.0 X-Mailer: Evolution 2.28.3 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201009/64 X-Sequence-Number: 40267 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) 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