X-Original-To: pgsql-performance@postgresql.org Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id B6EA44758F1 for ; Thu, 13 Mar 2003 15:05:42 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h2DKbf6P030427; Thu, 13 Mar 2003 15:37:41 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h2DK5Up03833; Thu, 13 Mar 2003 15:05:30 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id F3HTJCH7; Thu, 13 Mar 2003 15:05:29 -0500 Subject: Re: speeding up COUNT and DISTINCT queries From: Robert Treat To: Greg Stark Cc: Max Baker , Joe Conway , Manfred Koizar , PostgreSQL Performance Mailing List In-Reply-To: <874r67ff28.fsf@stark.dyndns.tv> References: <20030312223811.GM30411@warped.org> <20030313015540.GP30411@warped.org> <3E6FE59E.1050602@joeconway.com> <20030313020546.GQ30411@warped.org> <874r67ff28.fsf@stark.dyndns.tv> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 13 Mar 2003 15:05:30 -0500 Message-Id: <1047585930.23128.913.camel@camel> Mime-Version: 1.0 X-Archive-Number: 200303/95 X-Sequence-Number: 1393 On Thu, 2003-03-13 at 10:42, Greg Stark wrote: > Max Baker writes: > > On Wed, Mar 12, 2003 at 05:57:50PM -0800, Joe Conway wrote: > > That would explain why once a night isn't enough. Thanks. > > The contents of this table get refreshed every 4 hours. I'll add a > > vacuum after every refresh and comapre the results in a couple days. > > If it gets completely refreshed, ie, every tuple is updated or deleted and > re-inserted in a big batch job then VACUUM might never be enough without > boosting some config values a lot. You might need to do a VACUUM FULL after > the refresh. VACUUM FULL locks the table though which might be unfortunate. > hmm... approx 35,000 records, getting updated every 4 hours. so.. 35000 / (4*60) =~ 145 tuples per minute. Lets assume we want to keep any overhead at 10% or less, so we need to lazy vacuum every 3500 updates. so... 3500 tuples / 145 tpm =~ 25 minutes. So, set up a cron job to lazy vacuum every 20 minutes and see how that works for you. Robert Treat