X-Original-To: pgsql-performance@postgresql.org Received: from cat (adsl-66-123-169-52.dsl.sntc01.pacbell.net [66.123.169.52]) by postgresql.org (Postfix) with ESMTP id 4207747580B for ; Thu, 13 Mar 2003 15:22:56 -0500 (EST) Received: by cat (Postfix, from userid 501) id 5151EB6D69; Thu, 13 Mar 2003 12:22:54 -0800 (PST) Date: Thu, 13 Mar 2003 12:22:54 -0800 From: Max Baker To: Robert Treat Cc: Greg Stark , Joe Conway , Manfred Koizar , PostgreSQL Performance Mailing List Subject: Re: speeding up COUNT and DISTINCT queries Message-ID: <20030313202254.GD30411@warped.org> References: <20030312223811.GM30411@warped.org> <20030313015540.GP30411@warped.org> <3E6FE59E.1050602@joeconway.com> <20030313020546.GQ30411@warped.org> <874r67ff28.fsf@stark.dyndns.tv> <1047585930.23128.913.camel@camel> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1047585930.23128.913.camel@camel> User-Agent: Mutt/1.5.3i X-Archive-Number: 200303/96 X-Sequence-Number: 1394 On Thu, Mar 13, 2003 at 03:05:30PM -0500, Robert Treat wrote: > 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. I'm not starting with fresh data every time, I'm usually checking for an existing record, then setting a timestamp and a boolean flag. I've run some profiling and it's about 8000-10,000 UPDATEs every 4 hours. These are accompanied by about 800-1000 INSERTs. > 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. I'm now having VACUUM ANALYZE run after each of these updates. The data comes in in spurts -- a 90 minute batch job that runs every 4 hours. thanks folks, -m