need help with a query - Mailing list pgsql-performance

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