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

Re: slow update on 1M rows (worse with indexes)

From: "Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow update on 1M rows (worse with indexes)
Date: 2007-02-22 18:25:00
Message-ID: 20070222182500.GA32361@uio.no (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Feb 22, 2007 at 07:11:42PM +0100, Gabriel Biberian wrote:
> Create a new DB and load a dump of the above database with 976009 rows, 
> then i perform updates on the whole table.  I recorded the time taken 
> for each full update and the amount of extra disk space used.  Each 
> consecutive update of the table is slower than the previous
> beebox(at)evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=2"
> UPDATE 976009
> real    0m41.542s

You're creating a huge amount of dead rows by this kind of procedure. Try a
VACUUM in-between, or enable autovacuum. (Adjusting your WAL and
checkpointing settings might help too.)

Apart from that, do you really have a scenario that requires updating _all_
rows in your table regularly?

/* Steinar */
-- 
Homepage: http://www.sesse.net/

In response to

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2007-02-22 18:42:39
Subject: Re: slow update on 1M rows (worse with indexes)
Previous:From: Gabriel BiberianDate: 2007-02-22 18:11:42
Subject: slow update on 1M rows (worse with indexes)

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