Date: Sun, 14 Jan 2007 20:47:46 -0800
From: Ken <postgres@kwasnicki.com>
Subject: Re: update query taking 24+ hours
In-reply-to: <12574.1168803032@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Message-id: <45AB0772.3010109@kwasnicki.com>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <45A9CEA3.3020903@kwasnicki.com> <12574.1168803032@sss.pgh.pa.us>
User-Agent: Thunderbird 1.5.0.8 (X11/20061025)

Thanks Tom!
explain output:
 Merge Join  (cost=60454519.54..70701005.93 rows=682951183 width=22)
   Merge Cond: (("outer".y = "inner".y) AND ("outer".x = "inner".x))
   ->  Sort  (cost=41812068.08..42304601.78 rows=197013479 width=20)
         Sort Key: Master.y, Master.x
         ->  Seq Scan on Master  (cost=0.00..3129037.79 rows=197013479 
width=20)
   ->  Sort  (cost=18642451.46..18879400.92 rows=94779784 width=10)
         Sort Key: Import.y, Import.x
         ->  Seq Scan on Import  (cost=0.00..1460121.84 rows=94779784 
width=10)

Don't really understand all those numbers but they look big, to me.

work_mem is set to 262144.  should it be bigger?  i have 1.5GB ram on 
the system.  also i set /proc/sys/kernel/shmmax to 256000000.  too big, 
too small?

There are no foreign key constraints on either table. 

I don't know what hashjoin or sort-and-mergejoin are but I will look 
into them.

Thanks!
Ken

Tom Lane wrote:
> Ken <postgres@kwasnicki.com> writes:
>   
>> 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.
>> UPDATE Master SET val2=Import.val WHERE Master.x=Import.x AND 
>> Master.y=Import.y;
>>     
>
> What does EXPLAIN say about that?  (Don't try EXPLAIN ANALYZE,
> but a plain EXPLAIN should be quick enough.)
>
>   
>> Both tables have indexes on the x and y columns.  Will that help?
>>     
>
> A two-column index would have helped a lot more, probably, although
> with so many rows to process I'm not sure that indexes are useful
> anyway.  For really bulk updates a hashjoin or sort-and-mergejoin
> plan is probably the best bet.
>
> BTW, both of those would require plenty of work_mem to run fast
> ... what have you got work_mem set to?
>
> And possibly even more to the point, do you have any foreign key
> constraints leading into or out of the Master table?
>
> 			regards, tom lane
>
>