Re: Execution time of UPDATE raises dramatically! - Mailing list pgsql-hackers

From Jean-Michel POURE
Subject Re: Execution time of UPDATE raises dramatically!
Date
Msg-id 200205051053.03687.jm.poure@freesurf.fr
Whole thread Raw
In response to Execution time of UPDATE raises dramatically!  (Vladimir Zamiussky <zami@chat.ru>)
List pgsql-hackers
> 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


pgsql-hackers by date:

Previous
From: Vladimir Zolotykh
Date:
Subject: help
Next
From: Vladimir Zolotykh
Date:
Subject: Bad timestamp external representation 'Sun 05 May 11:53:44.731416 2002 EEST'