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 29E0FD1B174 for ; Thu, 15 Jul 2004 01:36:50 -0300 (ADT) 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 77523-08 for ; Thu, 15 Jul 2004 04:36:47 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id F336ED1B18C for ; Thu, 15 Jul 2004 01:36:40 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i6F4ahYq016533; Thu, 15 Jul 2004 00:36:43 -0400 (EDT) To: Christopher Browne Cc: pgsql-performance@postgresql.org Subject: Re: vacuum full 100 mins plus? In-reply-to: References: Comments: In-reply-to Christopher Browne message dated "Wed, 14 Jul 2004 22:21:29 -0400" Date: Thu, 15 Jul 2004 00:36:43 -0400 Message-ID: <16532.1089866203@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/93 X-Sequence-Number: 7484 Christopher Browne writes: > A long time ago, in a galaxy far, far away, PHatcher@macys.com (Patrick Hatcher) wrote: >> Answered my own question. I gave up the vacuum full after 150 mins. I was >> able to export to a file, vacuum full the empty table, and reimport in less >> than 10 mins. I suspect the empty item pointers and the sheer number of >> removable rows was causing an issue. > In that case, you'd be a little further better off if the steps were: > - drop indices; > - copy table to file (perhaps via pg_dump -t my_table); > - truncate the table, or drop-and-recreate, both of which make > it unnecessary to do _any_ vacuum of the result; > - recreate indices, probably with SORT_MEM set high, to minimize > paging to disk > - analyze the table (no need to vacuum if you haven't created any > dead tuples) > - cut SORT_MEM back down to "normal" sizes Rather than doing all this manually, you can just CLUSTER on any handy index. In 7.5, another possibility is to issue one of the forms of ALTER TABLE that force a table rewrite. The range of usefulness of VACUUM FULL is really looking narrower and narrower to me. I can foresee a day when we'll abandon it completely. regards, tom lane