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