X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C17DE564A3 for ; Wed, 9 Mar 2005 21:05:21 +0000 (GMT) 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 46950-09 for ; Wed, 9 Mar 2005 21:05:04 +0000 (GMT) Received: from hotmail.com (bay20-f30.bay20.hotmail.com [64.4.54.119]) by svr1.postgresql.org (Postfix) with ESMTP id 496C15654C for ; Wed, 9 Mar 2005 21:05:03 +0000 (GMT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 9 Mar 2005 13:05:04 -0800 Message-ID: Received: from 222.152.172.251 by by20fd.bay20.hotmail.msn.com with HTTP; Wed, 09 Mar 2005 21:05:03 GMT X-Originating-IP: [222.152.172.251] X-Originating-Email: [adrianengelbrecht@hotmail.com] X-Sender: adrianengelbrecht@hotmail.com In-Reply-To: <20050309151918.GA7213@wolff.to> From: "Adrian Engelbrecht" To: bruno@wolff.to, valenca@campusvirtual.br Cc: pgsql-admin@postgresql.org Subject: Re: Slow Update Date: Thu, 10 Mar 2005 10:05:03 +1300 Mime-Version: 1.0 Content-Type: text/html; format=flowed X-OriginalArrivalTime: 09 Mar 2005 21:05:04.0171 (UTC) FILETIME=[AA3917B0:01C524EB] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.82 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, HTML_10_20, HTML_MESSAGE, MIME_HTML_ONLY, MSGID_FROM_MTA_HEADER X-Spam-Level: X-Archive-Number: 200503/131 X-Sequence-Number: 16629

If it is necessary for you to comapre each row with a list of 26 possible values (VARIABLES) in the IN clause and not do a replacement of all values in column1 with "0" as suggested previously, you might want to place the 26000 variables in a temporary table that is either sorted physically in the required search order, or indexed, then place a select on the IN clause from that table.

I'm not surprised it's taking a long time to run the update. Firstly, it's an update, and secondly, for each of the 26000 rows in the table, it is comparing with 26000 variables, so it is doing 26000 x 26000 "selects".




From: Bruno Wolff III <bruno@wolff.to> To: Ricardo Valença de Assis <valenca@campusvirtual.br> CC: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Slow Update Date: Wed, 9 Mar 2005 09:19:18 -0600 On Wed, Mar 09, 2005 at 11:44:33 -0300, Ricardo Valença de Assis <valenca@campusvirtual.br> wrote: > Hi everydoby! > > I have a table with more than 26000 rows and I need to use update a > column of this tables on all lines according with a column. So, I need to > use update 26000 times correct? I tried to use a this command: > "UPDATE database SET column1=0 WHERE column2 in (VARIABLES)", where > VARIABLES is the list separeted by commas. But the list has about 26000 > entries, so I got a message of too long parameters. Is there a way to use > UPDATE pushing values from a file? Is there a way to run update more faster? > It is taking about 10 seconds for each UPDATE... Does anyone knows another > form to do this task? If you really want to do this for all rows in the table just do: UPDATE tablename SET column1=0; > > ----- Original Message ----- > From: "Daniel Rubio" <drubior@tinet.org> > To: <pgsql-admin@postgresql.org> > Sent: Wednesday, March 09, 2005 8:33 AM > Subject: Re: [ADMIN] Too many clients----A big problem for my team Why did you include this message that had nothing to do with your question? ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Looking for love? Check out XtraMSN Personals