X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 75A27535EF for ; Fri, 25 Mar 2005 19:55:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73060-03 for ; Fri, 25 Mar 2005 19:55:27 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id C6D4353598 for ; Fri, 25 Mar 2005 19:55:26 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2PJtRml011992; Fri, 25 Mar 2005 14:55:27 -0500 (EST) To: "Matthew T. O'Connor" Cc: Otto Blomqvist , pgsql-performance@postgresql.org Subject: Re: pg_autovacuum not having enough suction ? In-reply-to: <42446A66.6010504@zeut.net> References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> <42435408.30303@zeut.net> <00f201c53168$96903480$e602a8c0@blomqvist> <42446A66.6010504@zeut.net> Comments: In-reply-to "Matthew T. O'Connor" message dated "Fri, 25 Mar 2005 14:45:42 -0500" Date: Fri, 25 Mar 2005 14:55:27 -0500 Message-ID: <11991.1111780527@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/556 X-Sequence-Number: 11261 "Matthew T. O'Connor" writes: > hmm.... the value in reltuples should be accurate after a vacuum (or > vacuum analyze) if it's not it's a vacuum bug or something is going on > that isn't understood. If you or pg_autovacuum are running plain > analyze commands, that could explain the invalid reltules numbers. > Was reltuples = 113082 correct right after the vacuum? Another thing to check is whether the reltuples (and relpages!) that autovacuum is reporting are the same as what's actually in the pg_class row for the relation. I'm wondering if this could be a similar issue to the old autovac bug where it wasn't reading the value correctly. If they are the same then it seems like it must be a backend issue. One thing that is possibly relevant here is that in 8.0 a plain VACUUM doesn't set reltuples to the exactly correct number, but to an interpolated value that reflects our estimate of the "steady state" average between vacuums. I wonder if that code is wrong, or if it's operating as designed but is confusing autovac. Can autovac be told to run the vacuums in VERBOSE mode? It would be useful to compare what VERBOSE has to say to the changes in reltuples/relpages. regards, tom lane