Re: Execution time of UPDATE raises dramatically!

> Help

You are welcome.

> create table some_table (
> id int UNIQUE,
> value int
> );
> INSERT INTO some_table values(1,0);
> INSERT INTO some_table values(50,0);

I would prefer :

CREATE TRABLE table_foo (
foo_oid serial,
foo_value int
);

foo_oid will become a primary key, thus it is being indexed. Which is not the
case of your example.

> When I do UPDATE some_table set value=... where id=...,
> query execution time raises in arithmetic progression!
> After about 50 updates on every row query consumes ~3 sec against 0.3
> sec as it was at the beginning.
> psql takes ~80% of CPU time (acording to top).
> VACUUM helps to restore execution speed, but i think it is not the way out.
> Is it BUG or FEATURE?

You need to create an index OR to add a primary key.

> Postgres: 7.1.3;
> System: Debian woody (kernel 2.4.17) on K6/450 with 128Mb RAM.

If you are starting developement, it is highly recommanded you upgraded to
PostgreSQL 7.2.1. It is the most stable PostgreSQL release, with many bug
fixes and speed improvement.

Also, if you have a Windows workstation, try install pgAdmin2
(http://pgadmin.postgresql.com). This will speed-up your developements.

Do not hesitate to come back to us to tell if it solved your problem.

Cheers,
Jean-Michel POURE