Re: updates (postgreSQL) very slow

On Wed, 2004-03-10 at 15:30, Tom Lane wrote:
> "Fred Moyer" <fred(at)redhotpenguin(dot)com> writes:
> >> This is just a Seq Scan where a numeric field must be updated to
> >> NULL but if I run it you can see that this simple query takes
> >> forever (7628686.23 ms this is over 2 hours for only updating
> >> 747524 records!).
>
> > However updating every row to null with 700k rows is going to take a while
>
> A while, sure, but 2 hours seems excessive to me too. I'm betting that
> there are triggers or foreign keys on the table being updated, and that
> that's where the time is going. It might be possible to improve that,
> but Bobbie hasn't given us enough information.

If there are no foreign keys or triggers and updating each row is taking
one drive seek ( approximately 9 ms with the 80 gig IDE drive being used
here ) then to do 747524 seeks will take 6727716 ms, about 10% less than
the time of 7628686 ms for the update above. Is this is an accurate
estimate or are these numbers just coincidence? It seems like this could
represent the least efficient update scenario.