Thread: Execution time of UPDATE raises dramatically!

Hello.

There is small table:
----------
create table some_table (
id int UNIQUE,
value int
);
INSERT INTO some_table values(1,0);
....
INSERT INTO some_table values(50,0);
-------------

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?

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

Vladimir Zamiussky writes:

> 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?

It's just a fact of how the system works.

-- 
Peter Eisentraut   peter_e@gmx.net

> 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