X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2DFC89DD600 for ; Tue, 6 Dec 2005 16:40:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86896-01 for ; Tue, 6 Dec 2005 16:40:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id B1FD29DD612 for ; Tue, 6 Dec 2005 16:40:07 -0400 (AST) Received: (qmail 23398 invoked by uid 500); 6 Dec 2005 20:44:33 -0000 Date: Tue, 6 Dec 2005 14:44:33 -0600 From: Bruno Wolff III To: Assaf Yaari Cc: pgsql-performance@postgresql.org Subject: Re: Performance degradation after successive UPDATE's Message-ID: <20051206204433.GA22168@wolff.to> Mail-Followup-To: Bruno Wolff III , Assaf Yaari , pgsql-performance@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.017 required=5 tests=[AWL=0.017] X-Spam-Score: 0.017 X-Spam-Level: X-Archive-Number: 200512/190 X-Sequence-Number: 16011 On Tue, Dec 06, 2005 at 11:08:07 +0200, Assaf Yaari wrote: > Thanks Bruno, > > Issuing VACUUM FULL seems not to have influence on the time. That was just to get the table size back down to something reasonable. > I've added to my script VACUUM ANALYZE every 100 UPDATE's and run the > test again (on different record) and the time still increase. Vacuuming every 100 updates should put an upperbound on how slow things get. I doubt you need to analyze every 100 updates, but that doesn't cost much more on top of a vacuum. However, if there is another transaction open while you are doing the updates, that would prevent clearing out the deleted rows, since they are potentially visible to it. This is something you want to rule out. > Any other ideas? Do you have any triggers on this table? Are you updating any other tables at the same time? In particular ones that are referred to by the problem table.