X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 26CB59FA9BD for ; Tue, 2 May 2006 20:28:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 35354-05 for ; Tue, 2 May 2006 20:28:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx00.pub.collaborativefusion.com (mx00.pub.collaborativefusion.com [206.210.89.199]) by postgresql.org (Postfix) with ESMTP id A3DAC9FA2C7 for ; Tue, 2 May 2006 20:28:37 -0300 (ADT) Received: from localhost (monrovll-cuda1-24-53-251-44.pittpa.adelphia.net [24.53.251.44]) (AUTH: LOGIN wmoran, TLS: TLSv1/SSLv3,256bits,AES256-SHA) by wingspan with esmtp; Tue, 02 May 2006 19:28:36 -0400 id 00056407.4457EB24.0000CBED Date: Tue, 2 May 2006 19:28:34 -0400 From: Bill Moran To: "Jim C. Nasby" Cc: bealach_na_bo@hotmail.com, ahodgson@simkin.ca, pgsql-performance@postgresql.org Subject: Re: Why so slow? Message-Id: <20060502192834.6c8e9131.wmoran@collaborativefusion.com> In-Reply-To: <20060502201740.GB97354@pervasive.com> References: <200604281043.16083@hal.medialogik.com> <20060430100346.20f010a9.wmoran@collaborativefusion.com> <20060502201740.GB97354@pervasive.com> Organization: Collaborative Fusion X-Mailer: Sylpheed version 1.0.6 (GTK+ 1.2.10; i386-portbld-freebsd5.4) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/60 X-Sequence-Number: 18847 "Jim C. Nasby" wrote: > On Sun, Apr 30, 2006 at 10:03:46AM -0400, Bill Moran wrote: > > At this point, it seems like you need to do 2 things: > > 1) Schedule lazy vacuum to run, or configure autovacuum. > > 2) Schedule some downtime to run "vacuum full" to recover some disk space. > > > > #2 only needs done once to get you back on track, assuming that #1 is > > done properly. > > You'll also want to reindex since vacuum full won't clean the indexes > up. You might also want to read > http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10087 and > http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10116. Reindexing is in a different class than vacuuming. Neglecting to vacuum creates a problem that gets worse and worse as time goes on. Neglecting to reindex does not create an infinately growing problem, since empty index pages are recycled automatically. It's also conceivable that some usage patterns don't need to reindex at all. http://www.postgresql.org/docs/8.1/interactive/routine-reindex.html -- Bill Moran Potential Technologies http://www.potentialtech.com