X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 71E475283C for ; Mon, 13 Jun 2005 05:54:40 -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 65732-07 for ; Mon, 13 Jun 2005 08:54:31 +0000 (GMT) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [202.0.58.21]) by svr1.postgresql.org (Postfix) with ESMTP id 152C452839 for ; Mon, 13 Jun 2005 05:54:26 -0300 (ADT) Received: from smtp-1.paradise.net.nz (smtp-1a.paradise.net.nz [202.0.32.194]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0II000DYAM2QJW@linda-2.paradise.net.nz> for pgsql-performance@postgresql.org; Mon, 13 Jun 2005 20:54:27 +1200 (NZST) Received: from [192.168.1.11] (218-101-14-40.paradise.net.nz [218.101.14.40]) by smtp-1.paradise.net.nz (Postfix) with ESMTP id 07ED282B40; Mon, 13 Jun 2005 20:54:26 +1200 (NZST) Date: Mon, 13 Jun 2005 20:54:23 +1200 From: Mark Kirkwood Subject: Re: Updates on large tables are extremely slow In-reply-to: To: Yves Vindevogel Cc: pgsql-performance@postgresql.org Message-id: <42AD49BF.7050706@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050511) References: <579bc853ef3957c736c2773f13c444f7@implements.be> <42ACF2DD.8090409@paradise.net.nz> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.147 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/279 X-Sequence-Number: 12916 Apologies - I should have said output of 'VACUUM VERBOSE mytable'. (been using 8.1, which displays dead tuple info in ANALYZE...). Mark Yves Vindevogel wrote: > rvponp=# analyze verbose tblPrintjobs ; > INFO: analyzing "public.tblprintjobs" > INFO: "tblprintjobs": 19076 pages, 3000 rows sampled, 588209 estimated > total rows > ANALYZE > > > On 13 Jun 2005, at 04:43, Mark Kirkwood wrote: > > Yves Vindevogel wrote: > > I'm trying to update a table that has about 600.000 records. > The update query is very simple : update mytable set pagesdesc = > - pages ; > The query takes about half an hour to an hour to execute. I have > tried a lot of things. > > > Half an hour seem a bit long - I would expect less than 5 minutes on > reasonable hardware. > > You may have dead tuple bloat - can you post the output of 'ANALYZE > VERBOSE mytable' ?