Re: slow full table update

От: Tomas Vondra
Тема: Re: slow full table update
Дата: ,
Msg-id: 491B7123.3090309@fuzzy.cz
(см: обсуждение, исходный текст)
Ответ на: Re: slow full table update  ("Scott Marlowe")
Список: pgsql-performance

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

slow full table update  (<>, )
 Re: slow full table update  (, )
  Re: slow full table update  (<>, )
   Re: slow full table update  (, )
    Re: slow full table update  (<>, )
     Re: slow full table update  (Richard Huxton, )
      Re: slow full table update  ("Vladimir Sitnikov", )
      Re: slow full table update  (Tom Lane, )
       Re: slow full table update  (Tomas Vondra, )
 Re: slow full table update  ("Scott Marlowe", )
  Re: slow full table update  (<>, )
   Re: slow full table update  ("Scott Marlowe", )
    Re: slow full table update  (Tomas Vondra, )
   Re: slow full table update  (PFC, )

> This is the critical point.  You have this line:
>
> There were 132969 unused item pointers.
>
> Which says there's 132k or so dead rows in your table.  Which means
> vacuum / autovacuum isn't keeping up.  Did you try and stop the update
> several times?  Each time it starts then gets killed it creates dead
> rows.

Try to run just ANALYZE on the table and then run the

SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'

again. It should report about 20k of pages, i.e. 160MB. That might slow
the things down ;-)

> Try doing a vacuum full followed by a reindex OR a cluster on this
> table and see if that helps.

Well, maybe the vacuum will fix the problem - have you executed the
query that took 167ms (according to the explain analyze output posted by
you) over a clean table? But I doubt the growth from 6.000 to 20.000
alone might cause degradation from 170ms to several minutes ...

regards
Tomas


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

От: "Scott Marlowe"
Дата:
Сообщение: Re: Performance Question
От: "Dave Page"
Дата:
Сообщение: Re: Performance Question