Thread: very slow updates in 8.3?

Hi,

I have noticed that the first time you execute an:

update table1 set params_count=0;

it takes too long to complete: 11000 rows - 100 s.

Postgresql 8.3 configuration on Turion 64 with 1.4 Gb RAM, Windows XP

Which compiler is used to build Postgresql on Windows? Is it 9?

Any comment?


Cheers,
Anton

On Sat, Apr 19, 2008 at 1:33 AM, Anton Andreev <fn30762@fmi.uni-sofia.bg> wrote:
>  update table1 set params_count=0;
>  it takes too long to complete: 11000 rows - 100 s.
>  Postgresql 8.3 configuration on Turion 64 with 1.4 Gb RAM, Windows XP
>  Which compiler is used to build Postgresql on Windows? Is it 9?

Does this table have alot of indexs, or trigger function for deletes?

Remember that in postgresql, an update is really a delete and an
insert since postgreSQL uses the MVCC model.  So you are actually
doing twice the work.

To start, could you post the EXPLAIN ANALYZE for this query?


Also, if you are going to run this query alot, you might want to alter
your FILLFACTOR for you table and associated index to 50%.

ALTER TABLE yourtable SET ( FILLFACTOR = 50 );
ALTER INDEX yourindex1 SET ( FILLFACTOR = 50);

--
Regards,
Richard Broersma Jr.

Anton Andreev wrote:
> Hi,
>
> I have noticed that the first time you execute an:
>
> update table1 set params_count=0;
>
> it takes too long to complete: 11000 rows - 100 s.
>
> Postgresql 8.3 configuration on Turion 64 with 1.4 Gb RAM, Windows XP
>
> Which compiler is used to build Postgresql on Windows? Is it 9?
>
> Any comment?

You really haven't provided any information. What kind of disk do you
have? What does your postgresql.conf look like? How many times have you
run update against that table without vacuuming? What does your update
statement look like?

Joshua D. Drake