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

Re: UPDATE execution time is increasing

From: Valentine Gogichashvili <valgog(at)gmail(dot)com>
To: virag(at)chem(dot)elte(dot)hu
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: UPDATE execution time is increasing
Date: 2012-10-08 12:20:09
Message-ID: CAP93muVpgMHf+kREBrctUZGr5eaij-Y2UYh+qZdcdCJxRV44SQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Oct 7, 2012 at 3:49 PM, <virag(at)chem(dot)elte(dot)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;
>

Hello, could you do the same putting VACUUM t; between your updates? What
is the change in UPDATE time?

-- Valentin

In response to

pgsql-performance by date

Next:From: Peter GeogheganDate: 2012-10-08 12:38:47
Subject: Re: [repost] Help me develop new commit_delay advice
Previous:From: Andrzej ZawadzkiDate: 2012-10-08 11:40:31
Subject: Re: Strange behavior after upgrade from 9.0 to 9.2

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