Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5QDXPa52503 for ; Tue, 26 Jun 2001 09:33:25 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 2.12 #1) id 15EsyQ-0009gl-0W; Tue, 26 Jun 2001 14:33:47 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 17E1613C41; Tue, 26 Jun 2001 14:32:59 +0100 (BST) Message-ID: <007101c0fe44$83877de0$1001a8c0@archonet.com> From: "Richard Huxton" To: "David M. Richter" Cc: References: <3B36FB6E.1039F0D4@DKFZ-heidelberg.de> <005601c0fd60$db576740$1001a8c0@archonet.com> <3B3872E5.65A6B538@DKFZ-heidelberg.de> Subject: Re: Plsql Function with error: No space left on device. Date: Tue, 26 Jun 2001 14:32:55 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/414 X-Sequence-Number: 3711 From: "David M. Richter" > Hallo Richard! > > Thanks a lot for Your hint. It works. Yeah. > But only in a little database of 1000 rows. > In my case with 4 milions of rows I was waiting 17 hours without any > success by using the processor 97%. > So I canceled the Job. Patience is a virtue, but nobody is *that* virtuous. > What could i optimize in this query? Not much as it stands, it couldn't be much simpler. > Do You perhaps have another great hint? Well - if you've been drinking all night, have a glass of water before going to bed - always served me well ;-) We're going to need some more information. Try EXPLAIN and post the results along with the \d for the two tables and how many records are in each. Oh - and how many distinct values for each field there are. I'm assuming for any childoid there is only one corresponding parentoid in relseries_image? If so, and values of childoid are repeated many times, try selecting unique pairs into a temporary table. Join against that for the update. It could be that PG is ending up with a zillion rows in the join. - Richard Huxton