From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | fred(at)redhotpenguin(dot)com |
Cc: | "Bobbie van der Westhuizen" <Bobbie(at)idpi1(dot)agric(dot)za>, pgsql-general(at)postgresql(dot)org, tomcat(at)designmagick(dot)com |
Subject: | Re: updates (postgreSQL) very slow |
Date: | 2004-03-10 15:30:33 |
Message-ID: | 6781.1078932633@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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.
Another thing that jumps out at me is that this table hasn't been
vacuumed or analyzed recently. The planner thinks there are 3491 rows
when really there are 747524. That's a bit of a big difference. It
won't matter for the UPDATE itself --- a seqscan is a seqscan --- but
it might matter for planning foreign-key queries.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | wespvp | 2004-03-10 15:33:42 | More Deadlock Detection on Insert |
Previous Message | Frank van Vugt | 2004-03-10 15:18:25 | Re: does this look more like a possible bug or more like a possible hardware problem...? (long) |