Thread: UPDATE execution time is increasing
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.
On Sun, Oct 7, 2012 at 3:49 PM, <virag@chem.elte.hu> wrote:
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;
-- Valentin