X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 38F5F566B1 for ; Mon, 21 Mar 2005 18:52:24 +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 99845-10 for ; Mon, 21 Mar 2005 18:52:21 +0000 (GMT) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id 6F803566AB for ; Mon, 21 Mar 2005 18:52:20 +0000 (GMT) Received: from ool-4350c7ad.dyn.optonline.net ([67.80.199.173] helo=[192.168.0.66]) by outbound.mailhop.org with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.44) id 1DDS0h-000Dg9-RI; Mon, 21 Mar 2005 13:52:19 -0500 Message-ID: <423F17D7.7040904@zeut.net> Date: Mon, 21 Mar 2005 13:52:07 -0500 From: "Matthew T. O'Connor" Organization: Terrie O'Connor Realtors User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Steve Crawford Cc: pgsql-admin@postgresql.org Subject: Re: Excessive growth of pg_attribute and other system tables References: <200503171515.38214.scrawford@pinpointresearch.com> <200503171536.10389.scrawford@pinpointresearch.com> In-Reply-To: <200503171536.10389.scrawford@pinpointresearch.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Mail-Handler: MailHop Outbound by DynDNS.org X-Originating-IP: 67.80.199.173 X-Report-Abuse-To: abuse@dyndns.org (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: zeut X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/329 X-Sequence-Number: 16827 Steve Crawford wrote: >On Thursday 17 March 2005 3:15 pm, Steve Crawford wrote: > > >>I'm having trouble with physical growth of postgresql system >>tables.... >> >> > >Additional info. The most recent autovacuum entries for the >pg_attribute table are: > >[2005...] Performing: VACUUM ANALYZE "pg_catalog"."pg_attribute" >[2005...] table name: tati."pg_catalog"."pg_attribute" >[2005...] relid: 1249; relisshared: 0 >[2005...] reltuples: 9334.000000; relpages: 82282 >[2005...] curr_analyze_count: 6647115; curr_vacuum_count: 861454 >[2005...] last_analyze_count: 6647115; last_vacuum_count: 861454 >[2005...] analyze_threshold: 9834; vacuum_threshold: 19668 > >and > >[2005...] Performing: ANALYZE "pg_catalog"."pg_attribute" >[2005...] table name: foo."pg_catalog"."pg_attribute" >[2005...] relid: 1249; relisshared: 0 >[2005...] reltuples: 4843240.000000; relpages: 82284 >[2005...] curr_analyze_count: 6657041; curr_vacuum_count: 862897 >[2005...] last_analyze_count: 6657041; last_vacuum_count: 861454 >[2005...] analyze_threshold: 4843740; vacuum_threshold: 19668 > >(Both within past 1 day - dates truncated to avoid line-wrap.) The >table currently has just over 9,000 tuples and I have no reason to >believe that should have changed substantially. The thresholds and >counts seem way off - especially in the second pass. > > I believe this discrepancy has to do with the fact that ANALYZE can return some very bogus values for reltuples, where as vacuum always returns an accurate count. I'm not sure how to best handle this.