Skip site navigation (1) Skip section navigation (2)

Update from other tables slow

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Update from other tables slow
Date: 2008-01-29 01:18:04
Message-ID: BAYC1-PASMTP10E75613B2D319AEBA902196350@CEZ.ICE (view raw or flat)
Thread:
Lists: pgsql-novice
This question is so silly, that I must hang my head in shame and admit it's
a novice question.

Frequently on our DB we need to run large updates on a table, typically
involving over 100K rows. These nearly ALWAYS involve values from other
tables. But the updates take so long that we think the process has stalled
on some sort of deadly-embrace locking scenario and we cancel it.

Is there anything wrong with this UPDATE statement?

update mdx_core.provider
set 
   middle_name = fix.middle_name, 
   display_name = fix.display_name
from mdx_import._fix_mnames as fix
where provider.provider_id = fix.provider_id;

Even though _fix_mnames has an index on provider_id, it took so long that I
cancelled it and rewrote it, replacing it with this absolutely silly-looking
statement:

update mdx_core.provider
set 
   middle_name = (
      select middle_name 
      from mdx_import._fix_mnames as fix 
      where fix.provider_id = provider.provider_id),
   display_name = (
      select display_name 
      from mdx_import._fix_mnames as fix 
      where fix.provider_id = provider.provider_id)
where provider.provider_id in (
   select provider_id from mdx_import._fix_mnames)

This worked, after running for 25 minutes on 276K rows. TWENTY FIVE MINUTES?

The performance of our server on other operations are great, but this can't
be normal for an enterprise-scale SQL server, can it? What am I doing wrong?

TIA

Carlo


Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2008-01-29 01:32:37
Subject: Re: maths functions and spreadsheet
Previous:From: e-letterDate: 2008-01-28 20:42:49
Subject: maths functions and spreadsheet

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group