Skip site navigation (1) Skip section navigation (2)

UPDATE execution time is increasing

From: virag(at)chem(dot)elte(dot)hu
To: pgsql-performance(at)postgresql(dot)org
Subject: UPDATE execution time is increasing
Date: 2012-10-07 13:49:23
Message-ID: 20121007154923.10716wu6pawsdi03@webmail.chem.elte.hu (view raw or flat)
Thread:
Lists: pgsql-performance
Hello!

I would like to ask following question:
I have created a table and I updated all records.
And I executed this update command again and again....
Execution time was growing after each step.
I cannot understand this behavior.
First update command took 6 sec, 30th update (same) command took 36  
sec (6x times greater value!!!).
Can somebody explain me why increasing this update time?

-- 1st update: 6175 ms
-- 5th update: 9265 ms
-- 10th update: 15669 ms
-- 20th update: 26940 ms
-- 20th update: 36198 ms

PGSQL version: 9.1.5, parameters: default install used

Thanks your answer in advance!

SCRIPT:

DROP SCHEMA IF EXISTS tempdb CASCADE;
CREATE SCHEMA tempdb;
SET search_path TO tempdb;

DROP TABLE IF EXISTS t;
CREATE  TABLE t (
   id SERIAL ,
   num int NOT NULL,
   PRIMARY KEY (id)
);


insert into t
   SELECT *,0 FROM generate_series(1,100000);

update t set num=num+1;  -- 1st update: 6175 ms
update t set num=num+1;
update t set num=num+1;
update t set num=num+1;
update t set num=num+1;

update t set num=num+1;  -- 5th update: 9265 ms
.....


update t set num=num+1;  -- 10th update: 15669 ms
.....


update t set num=num+1;  -- 20th update: 26940 ms
.....


update t set num=num+1;  -- 30th update: 36198 ms
.....


----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.



Responses

pgsql-performance by date

Next:From: Ants AasmaDate: 2012-10-07 14:27:02
Subject: Re: Same query doing slow then quick
Previous:From: Anibal David AcostaDate: 2012-10-05 18:33:49
Subject: Re: how to avoid deadlock on masive update with multiples delete

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group