Thread: need help with a query

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/

Pavel Velikhov <pvelikhov@yahoo.com> writes:
> Got a very different query plan this time, with a hash join between links and articles. At least now postgres is
usingboth shared memory buffers and working mem, but its still completely IO bound, only getting in 5-6% CPU once in a
while.I guess I can't squeeze more out of the laptop, but I also have a machine with 16GB RAM that I'll try this on
next.Should I allocate tons of memory into shared buffers or into the working memory? 

For a hash join, I think you want to raise work_mem as high as you can
(without driving the system into swapping).  It won't read any block of
the tables more than once, so there's no point in having lots of
buffers.

            regards, tom lane

On 10/20/07, Pavel Velikhov <pvelikhov@yahoo.com> wrote:
> Left the query running for 10+ hours and had to kill it. I guess there
> really was no need to have lots of
> shared buffers (the hope was that postgresql will cache the whole table). I
> ended up doing this step inside
> the application as a pre-processing step. Can't have postgres running with
> different fsych options since this
> will be part of an "easy to install and run" app, that should just require a
> typical PosgreSQL installation.

Is the size always different?  If not, you could limit the updates:

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

Since this is a huge operation, what about trying:

CREATE TABLE links_new AS SELECT l.col1, l.col2, a.size as
target_size, l.col4, ... FROM links l, articles a WHERE a.article_id =
l.article_to;

Then truncate links, copy the data from links_new.  Alternatively, you
could drop links, rename links_new to links, and recreate the
constraints.

I guess the real question is application design.  Why doesn't this app
store size at runtime instead of having to batch this huge update?

--
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/