Re: Why Does UPDATE Take So Long?

Sorry for the hyperbole; I should have qualified that ridiculous
statement with "...on my machines." No doubt the problem has something
to do with configuration, because I don't know much about that. One of
my machines is running PG 8.1 on Linux Fedora Core 5. It's got an AMD
64bit CPU with a GB RAM and plenty of normal disk space (not running
RAID 5). The other machine is running Linux FC9 and PG 8.3. It's got a
i686 cpu with a GB RAM and also not using RAID.

Since I don't understand much about configuring PostgreSQL, both of
these machines use the default PostgreSQL configuration. I figured that
it was optimized for general use but maybe since my files are large-ish
(in the low multi-million record ranges) mayb ethta doesn't qualify as
general use. Anyway, here's the configuration settings you mentioned.
Shared_buffers are = 1000
#checkpoint_segments = 3
#checkpoint_timeout = 300
#checkpoint_warning = 30

What should I be looking for in the configuration to improve UPDATE
performance?

Thanks,
- Bill Thoen

Bill Moran wrote:
> In response to Bill Thoen <bthoen(at)gisnet(dot)com>:
>
>
>> Doesn't look like that's the problem. I moved my table over to another
>> Linux box running PG 8.3 and update performance was pretty bad there as
>> well. In the time that PG 8.3 was struggling with update there I created
>> a copy of my table on my PG 8.1 machine and inserted all columns with
>> one containing the altered values I wanted and that took less than two
>> minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still
>> thrashing away trying to update that one column that's not even part of
>> any index..
>>
>> Something is really wrong with UPDATE in PostgreSQL I think.
>>
>
> That's an interesting theory, although it's completely wrong and founded
> in ridiculosity. If something were "really wrong with UPDATE" in every
> version of PostgreSQL, you'd be reading about it on the mailing lists,
> and you won't.
>
> What I suspect is that the typical tuning advice applies here. I don't
> see any information about your configuration or your hardware setup.
> * What are shared_buffers set at?
> * What do the checkpoint configs look like?
> * In general, what does your postgresql.conf look like, how much tuning
> have you done?
> * What is your hardware setup? You're not running RAID 5 are you?
>
>