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

От: Steinar H. Gunderson
Тема: Re: slow update on 1M rows (worse with indexes)
Дата: ,
Msg-id: 20070222182500.GA32361@uio.no
(см: обсуждение, исходный текст)
Ответ на: slow update on 1M rows (worse with indexes)  (Gabriel Biberian)
Список: pgsql-performance

Скрыть дерево обсуждения

slow update on 1M rows (worse with indexes)  (Gabriel Biberian, )
 Re: slow update on 1M rows (worse with indexes)  ("Steinar H. Gunderson", )
 Re: slow update on 1M rows (worse with indexes)  ("Joshua D. Drake", )
 Re: slow update on 1M rows (worse with indexes)  (, )

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/


В списке pgsql-performance по дате сообщения:

От: Mark Stosberg
Дата:
Сообщение: Using the 8.2 autovacuum values with 8.1
От: ismo.tuononen@solenovo.fi
Дата:
Сообщение: Re: slow update on 1M rows (worse with indexes)