Hi,

I am updating a big table (90M records) with data from another rather large table (4M entries). Here is my update query:

update links set target_size =
( select size from articles where articles.article_id = links.article_to)

I have built all the indexes one might need, increased shared mem buffers to 400MB, I looked at the query plan and it looks reasonable.
But its taking an eternity to run: I've been running the query for 3 hours now on my new Mac laptop, and looking at the activity monitor I see that postrges is spending all of this time in disk IO (average CPU load of postgres process is about 4-5%).

However, just looking at the query, postgres could cache the articles table and do a single pass over the links table...

Please let me know if there is a good solution to this.

Thanks!
Pavel Velikhov
Institute of Systems Programming
Russian Academy of Sciences



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On 10/19/07, Pavel Velikhov <pvelikhov@yahoo.com> wrote:
>
> Hi,
>
> I am updating a big table (90M records) with data from another rather
> large table (4M entries). Here is my update query:
>
> update links set target_size =
> ( select size from articles where articles.article_id =
> links.article_to)

try:

UPDATE links
SET target_size = size
FROM articles
WHERE articles.article_id = links.article_to;

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend