Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id EDF492E3276 for ; Fri, 18 Jan 2008 19:05:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 08626-05 for ; Fri, 18 Jan 2008 19:05:01 -0400 (AST) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 7AEBC2E2F72 for ; Fri, 18 Jan 2008 19:05:05 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Fri, 18 Jan 2008 17:05:04 -0600 Message-Id: <4790DC36.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Fri, 18 Jan 2008 17:04:54 -0600 From: "Kevin Grittner" To: , "Thomas Markus" Subject: Re: update performance 8.1.4 References: <478C7DEC.1020806@proventis.net> In-Reply-To: <478C7DEC.1020806@proventis.net> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/215 X-Sequence-Number: 27989 >>> On Tue, Jan 15, 2008 at 3:33 AM, in message <478C7DEC.1020806@proventi= s.net>, Thomas Markus wrote:=20 =20 > update tablename set datecol=3Dnull > ~ 2.3 mio rows >=20 > After 6 hours, this was still not finished. selects and inserts are ok. > System is a dual xeon, 8gb ram, debian 64bit, pg 8.1.4 =20 You do realize that every row updated is a delete and an insert, right? So this should take as long as a single database transaction which deleted all the rows in the table and inserted them again. =20 If datecol is null in any rows, you should try: =20 update tablename set datecol=3Dnull where datecol is not null; =20 If you have canceled the update because of the long run time, you will have left a lot of dead rows in the table, which will make the next attempt run even longer. Be sure you are doing vacuums. =20 -Kevin =20