X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 98B82528AD for ; Fri, 3 Jun 2005 11:50:47 -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 82204-05 for ; Fri, 3 Jun 2005 14:50:45 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 6747F52874 for ; Fri, 3 Jun 2005 11:50:44 -0300 (ADT) Received: (qmail 10896 invoked by uid 500); 3 Jun 2005 14:49:51 -0000 Date: Fri, 3 Jun 2005 09:49:51 -0500 From: Bruno Wolff III To: Bahadur Singh Cc: pgsql-bugs@postgresql.org, pgsql-novice@postgresql.org, pgsql-performance@postgresql.org Subject: Re: BUG #1697: Select getting slower on continously updating data Message-ID: <20050603144951.GA10195@wolff.to> Mail-Followup-To: Bruno Wolff III , Bahadur Singh , pgsql-bugs@postgresql.org, pgsql-novice@postgresql.org, pgsql-performance@postgresql.org References: <20050602130259.GB9716@wolff.to> <20050603070900.62046.qmail@web50805.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050603070900.62046.qmail@web50805.mail.yahoo.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/31 X-Sequence-Number: 11990 On Fri, Jun 03, 2005 at 00:09:00 -0700, Bahadur Singh wrote: > > Many thanks for this tip ! > But is this good idea to analyse/vacuuming the > database tables while updates are taking place.. > Since, I update continuously say (100,000 ) times or > more the same data set. > > This is the result of analyze command. > > INFO: analyzing "public.salesarticle" > INFO: "salesarticle": scanned 3000 of 20850 pages, > containing 62 live rows and 134938 dead rows; 62 rows > in sample, 431 estimated total rows > > Gesamtlaufzeit der Abfrage: 5531 ms. > Total Time Taken : 5531 ms. > > Can you suggest me some clever way to so, because I > would prefer to do vaccumming while database is not > loaded with queries/transactions. While that may be a nice preference, under your usage pattern that does not appear to be a good idea. As long as your disk I/O isn't saturated you want to be running vacuums a lot more often than you are. (Analyze should only be needed if the distrution of values is changing constantly. An example would be timestamps indicating when an update occured.)