From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: slow update on 1M rows (worse with indexes)
Date: Thu, 22 Feb 2007 19:25:00 +0100
Message-ID: <20070222182500.GA32361@uio.no> (raw)
In-Reply-To: <45DDDCDE.9050806@beemotechnologie.com>
References: <45DDDCDE.9050806@beemotechnologie.com>
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@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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgunderson@bigfoot.com
Subject: Re: slow update on 1M rows (worse with indexes)
In-Reply-To: <20070222182500.GA32361@uio.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox