pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Engelbrecht <adrianengelbrecht@hotmail.com>
To: bruno@wolff.to
To: valenca@campusvirtual.br
Cc: pgsql-admin@postgresql.org
Subject: Re: Slow Update
Date: Thu, 10 Mar 2005 10:05:03 +1300
Message-ID: <BAY20-F30B38C20311C64B934407CB1510@phx.gbl> (raw)
In-Reply-To: <20050309151918.GA7213@wolff.to>

<html><div style='background-color:'><P>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.</P>
<P>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".<BR><BR></P><BR><BR><BR>
<DIV>
<P>Adrian</P>
<P>ICQ 120480893</P>
<P><A href="https://www.paypal.com/refer/pal=N6T2FQ7WRPHH4">https://www.paypal.com/refer/pal=N6T2FQ7WRPHH...: Bruno Wolff III &lt;bruno@wolff.to&gt; To: Ricardo Valença de Assis &lt;valenca@campusvirtual.br&gt; 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 &lt;valenca@campusvirtual.br&gt; wrote: &gt; Hi everydoby! &gt; &gt; I have a table with more than 26000 rows and I need to use update a &gt; column of this tables on all lines according with a column. So, I need to &gt; use update 26000 times correct? I tried to use a this command: &gt; "UPDATE database SET column1=0 WHERE column2 in (VARIABLES)", where &gt; VARIABLES is the list separeted by commas. But the list has about 26000 &gt; entries, so I got a message of too long parameters. Is there a way 
to use &gt; UPDATE pushing values from a file? Is there a way to run update more faster? &gt; It is taking about 10 seconds for each UPDATE... Does anyone knows another &gt; 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; &gt; &gt; ----- Original Message ----- &gt; From: "Daniel Rubio" &lt;drubior@tinet.org&gt; &gt; To: &lt;pgsql-admin@postgresql.org&gt; &gt; Sent: Wednesday, March 09, 2005 8:33 AM &gt; 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 </div><br clear=all><hr>Looking for love? Check out  <a href="http://g.msn.com/8HMBENNZ/2749??PS=47575"; target="_top">XtraMSN Personals</a> </html>




view thread (3+ messages)

Message-ID: <BAY20-F30B38C20311C64B934407CB1510@phx.gbl>
Permalink:  ../BAY20-F30B38C20311C64B934407CB1510@phx.gbl/
Also on:    postgresql.org/message-id/BAY20-F30B38C20311C64B934407CB1510@phx.gbl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: adrianengelbrecht@hotmail.com, bruno@wolff.to, valenca@campusvirtual.br
  Subject: Re: Slow Update
  In-Reply-To: <BAY20-F30B38C20311C64B934407CB1510@phx.gbl>
  References: <20050309151918.GA7213@wolff.to> <BAY20-F30B38C20311C64B934407CB1510@phx.gbl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox