Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVN4r-00014j-UD for pgsql-admin@postgresql.org; Mon, 05 Nov 2012 13:50:57 +0000 Received: from mx0.network-studio.com ([62.93.225.99]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVN4p-0005IR-EK for pgsql-admin@postgresql.org; Mon, 05 Nov 2012 13:50:57 +0000 Received: from zimbra-ne01.network-studio.com (zimbra-admin.network-studio.com [192.168.101.80]) by mx0.network-studio.com (Postfix) with ESMTP id 82756408146 for ; Mon, 5 Nov 2012 14:50:53 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by zimbra-ne01.network-studio.com (Postfix) with ESMTP id 7E6D5DA2361 for ; Mon, 5 Nov 2012 14:50:53 +0100 (CET) X-Virus-Scanned: amavisd-new at zimbra-ne01.network-studio.com Received: from zimbra-ne01.network-studio.com ([127.0.0.1]) by localhost (zimbra-ne01.network-studio.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id KuMpz6pYOFsU; Mon, 5 Nov 2012 14:50:53 +0100 (CET) Received: from zimbra-ne01.network-studio.com (zimbra-ne01.network-studio.com [192.168.101.80]) by zimbra-ne01.network-studio.com (Postfix) with ESMTP id 45E70DA1DD5; Mon, 5 Nov 2012 14:50:53 +0100 (CET) Date: Mon, 5 Nov 2012 14:50:53 +0100 (CET) From: Baptiste LHOSTE To: pgsql-admin@postgresql.org Cc: Sylvain CAILLET Message-ID: <216101410.8465931.1352123453261.JavaMail.root@alaloop.com> In-Reply-To: <201211051132.50591.cedric@2ndquadrant.com> Subject: Re: Autoanalyze of the autovacuum daemon ... MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.101.82] X-Mailer: Zimbra 7.2.1_GA_2790 (ZimbraWebClient - GC22 (Linux)/7.2.1_GA_2790) X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/21 X-Sequence-Number: 40266 Hi, > in pg_stat_user_tables, not since the last time ANALYZE run, but you have the > number of reltuples from pg_class that is used to calculate the ratio. So how does the autovacuum daemon to determine the number of obsolete row since its last work on a specific table ? > Everything is relative to the size of the table, what is the content of > pg_class for the second kind of tables ? (relpages/reltuples) relname | last_autoanalyse | reltuples | relpages | n_tup_ins + n_tup_upd + n_tup_del | (from pg_stat_all_tables) | | | (from pg_stat_all_tables) | | | | table1 | 2012-10-15 21:12:04.362647+02 | 2.53296e+06 | 50789 | 28542483 table2 | 2012-10-15 18:27:30.181583+02 | 2.57144e+06 | 54833 | 31127737 table3 | 2012-10-15 13:13:09.569758+02 | 2.58646e+06 | 63208 | 27167944 table4 | 2012-10-15 12:05:39.094652+02 | 2.58655e+06 | 63181 | 27160409 table5 | 2012-10-15 12:05:32.347738+02 | 2.58611e+06 | 57272 | 27171661 table6 | 2012-10-15 10:43:40.558078+02 | 2.57925e+06 | 63145 | 27155875 table7 | 2012-10-15 10:40:25.469625+02 | 2.59551e+06 | 57254 | 27161876 table8 | 2012-10-15 10:25:10.776152+02 | 2.56987e+06 | 63147 | 27177907 table9 | 2012-10-15 09:30:17.407181+02 | 2.5923e+06 | 57231 | 27152953 table10 | 2012-10-15 08:29:25.427207+02 | 2.56237e+06 | 63085 | 31770581 table11 | 2012-10-15 08:26:14.550742+02 | 2.56503e+06 | 63065 | 31769609 table12 | 2012-10-15 06:39:01.737914+02 | 2.57833e+06 | 63023 | 31767543 table13 | 2012-10-15 06:35:36.43683+02 | 2.57217e+06 | 63047 | 31759954 table14 | 2012-10-15 05:08:02.858623+02 | 2.59068e+06 | 57119 | 27154555 table15 | 2012-10-15 04:40:48.695142+02 | 2.57449e+06 | 63002 | 31768034 table16 | 2012-10-10 07:51:49.625226+02 | 2.48584e+06 | 54014 | 22110223 table17 | 2012-10-09 10:03:36.649331+02 | 2.58047e+06 | 63742 | 23423257 table18 | 2012-10-09 10:02:13.549362+02 | 2.57854e+06 | 63585 | 23364035 table19 | 2012-10-09 09:18:55.447895+02 | 2.58851e+06 | 63516 | 23375180 Regards, Baptiste Lhoste