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.


Mon, 28 Jan 2002 12:12:08 +0300
Vladimir Zamiussky <zami@chat.ru>
Execution time of UPDATE raises dramatically!
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


Fri, 1 Feb 2002 15:58:47 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
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

Sun, 5 May 2002 10:53:03 +0200
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: Execution time of UPDATE raises dramatically!