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 314FD3B4790 for ; Thu, 23 Dec 2004 19:00:33 +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 28580-02 for ; Thu, 23 Dec 2004 19:00:28 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 655943B4783 for ; Thu, 23 Dec 2004 19:00:28 +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 iBNJ0SGj000383; Thu, 23 Dec 2004 14:00:28 -0500 (EST) To: ogjunk-pgjedan@yahoo.com Cc: pgsql-admin@postgresql.org Subject: Re: VACUUMing for 30 minutes In-reply-to: <20041223181555.25535.qmail@web12706.mail.yahoo.com> References: <20041223181555.25535.qmail@web12706.mail.yahoo.com> Comments: In-reply-to message dated "Thu, 23 Dec 2004 10:15:55 -0800" Date: Thu, 23 Dec 2004 14:00:27 -0500 Message-ID: <382.1103828427@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/229 X-Sequence-Number: 15815 writes: > Now, I run VACUUM religiously every night, across all tables, but maybe > that's an overkill for th number of updates and inserts in this DB. Or maybe it's not enough? How many updates/deletes do you do in an average day? > INFO: Pages 303359: Changed 23, Empty 0; Tup 114182: Vac 47, Keep 273, > UnUsed 11798506. You've got 303359 pages holding only 114182 tuples, which means this table is *at least* two-thirds empty, and probably a lot more; there's no way to tell from this output what the average tuple size is, but I bet it's a lot less than a page. The indexes look pretty bloated too. I would recommend CLUSTERing the table on one index or another as the easiest way of cleaning it up. A somewhat faster way would be to drop the indexes, VACUUM FULL, re-make the indexes, but there's more chance of mistakes that way. Once you've got the tables de-bloated, take another look at your FSM settings; it's a good bet they are not high enough for your database size. regards, tom lane