Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5ACCE9FBCD1 for ; Wed, 2 May 2007 12:50:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76689-01-4 for ; Wed, 2 May 2007 12:50:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 6CD399FBCC1 for ; Wed, 2 May 2007 12:50:32 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-217-243.bk12-dsl.surnet.cl [201.221.217.243]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l42FoOed030409; Wed, 2 May 2007 08:50:24 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id ACC1732A10; Wed, 2 May 2007 11:50:20 -0400 (CLT) Date: Wed, 2 May 2007 11:50:20 -0400 From: Alvaro Herrera To: Peter Koczan Cc: Gabriele Bartolini , pgsql-admin@postgresql.org Subject: Re: VACUUM FULL ANALYSE hanging Message-ID: <20070502155020.GQ4585@alvh.no-ip.org> Mail-Followup-To: Peter Koczan , Gabriele Bartolini , pgsql-admin@postgresql.org References: <910CF843580B3C40A25CD0D04B3908E213050E@exchange4.comune.prato.local> <4638AA6E.2090506@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <4638AA6E.2090506@gmail.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Wed, 02 May 2007 08:50:25 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/24 X-Sequence-Number: 25344 Peter Koczan escribió: > I've noticed in my own experiments and experiences with VACUUM FULL that > it tries to reindex all the indexes to compress them. While a good idea, > this unfortunately takes a *long* time. Huh, this is not an accurate description of what happens. VACUUM FULL tries to keep indexes up to date, but it doesn't *reindex* them (like a REINDEX command would do). There was another thread about VACUUM FULL in which Tom Lane described exactly what happened to indexes. > In my experiences, doing a dump/restore was far faster, but this method > creates downtime. (e.g. a 10 GB database took 2 hours to restore, while > reindexing/vacuuming full was still on the same table after 4 hours). It has been reported that CLUSTER is also faster than VACUUM FULL, and it leaves the indexes more compact than VACUUM FULL to boot. So it may be the best option. It is also easier to do than dump/restore. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support