Date: Sat, 13 Jan 2007 22:33:07 -0800
From: Ken <postgres@kwasnicki.com>
Subject: update query taking 24+ hours
To: pgsql-sql@postgresql.org
Message-id: <45A9CEA3.3020903@kwasnicki.com>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
User-Agent: Thunderbird 1.5.0.8 (X11/20061025)

Hello,
I have postgres 8.1 on a linux box: 2.6Ghz P4, 1.5GB ram, 320GB hard 
drive.  I'm performing an update between two large tables and so far 
it's been running for 24+ hours.

I have two tables:
Master:
x int4
y int4
val1 int2
val2 int2

Import:
x int4
y int4
val int2

Each table has about 100 million rows.  I want to populate val2 in 
Master with val from Import where the two tables match on x and y.
So, my query looks like:
UPDATE Master SET val2=Import.val WHERE Master.x=Import.x AND 
Master.y=Import.y;

Both tables have indexes on the x and y columns.  Will that help?

Is there a better way to do this?  In each table x,y are unique, does 
that make a difference?  ie: would it be faster to run some kind of 
query, or loop, that just goes through each row in Import and updates 
Master (val2=val) where x=x and y=y?
If this approach would be better how to construct such a SQL statement?

The other weird thing is that when I monitor the system with xload it 
shows two bars of load, and the hard drive is going nuts,  so far my 
database directory has grown by 25GB,  however when I run "top" the 
system shows 98% idle and the postmaster process is usually only between 
1-2% CPU, although it is using 50% (750MB) ram.  Also the process shows 
up with a "D" status in the "S" column. 

Not sure what is going on.  If the size of the tables makes what I'm 
trying to do insane, or if I just have a bad SQL approach, or if 
something is wrong with my postgres configuration.

Really appreciate any help!
Thanks!
Ken