X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EEEB3529E2 for ; Thu, 31 Mar 2005 21:44:23 +0100 (BST) 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 68404-05 for ; Thu, 31 Mar 2005 20:44:15 +0000 (GMT) Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net [66.7.238.130]) by svr1.postgresql.org (Postfix) with ESMTP id E45F053739 for ; Thu, 31 Mar 2005 21:44:13 +0100 (BST) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 5DBFEFE0A; Thu, 31 Mar 2005 12:44:15 -0800 (PST) From: Steve Crawford Organization: Pinpoint Research To: Tom Lane Subject: Re: [ADMIN] Excessive growth of pg_attribute and other system tables Date: Thu, 31 Mar 2005 12:44:15 -0800 User-Agent: KMail/1.5.1 Cc: "Matthew T. O'Connor" , Jan Wieck , pgsql-hackers@postgresql.org References: <200503171515.38214.scrawford@pinpointresearch.com> <7739.1112295848@sss.pgh.pa.us> <14686.1112299573@sss.pgh.pa.us> In-Reply-To: <14686.1112299573@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503311244.15055.scrawford@pinpointresearch.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.08 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/1066 X-Sequence-Number: 65868 On Thursday 31 March 2005 12:06 pm, Tom Lane wrote: > I wrote: > > The light just went on ... system catalog updates don't generate > > statistics reports. Hence, autovacuum doesn't know any work is > > needed. > > The above claim is too strong --- they do normally generate stats > updates. However, in a simple test I observed that > pg_stat_all_tables.n_tup_del did not seem to increment for the > deletes that occur when a temp table is dropped during backend > exit. (Most likely we aren't flushing out the final stats > messages...) > > Steve, is your app in the habit of creating lots of temp tables > that are not dropped explicitly? That would explain why you are > getting bit more than other people. Yes, various processes create in total well over 100 temporary tables every hour. None of them are explicitly dropped. Cheers, Steve