Re: Identical query slower on 8.4 vs 8.3

От: Kevin Grittner
Тема: Re: Identical query slower on 8.4 vs 8.3
Дата: ,
Msg-id: 4C3EE8A00200002500033637@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Identical query slower on 8.4 vs 8.3  (Patrick Donlin)
Список: pgsql-performance

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

Identical query slower on 8.4 vs 8.3  (Patrick Donlin, )
 Re: Identical query slower on 8.4 vs 8.3  (Thom Brown, )
 Re: Identical query slower on 8.4 vs 8.3  ("Kevin Grittner", )
  Re: Identical query slower on 8.4 vs 8.3  (Patrick Donlin, )
   Re: Identical query slower on 8.4 vs 8.3  (Alvaro Herrera, )
   Re: Identical query slower on 8.4 vs 8.3  (Andy Colson, )
   Re: Identical query slower on 8.4 vs 8.3  ("Kevin Grittner", )
   Re: Identical query slower on 8.4 vs 8.3  (Merlin Moncure, )
    Re: Identical query slower on 8.4 vs 8.3  (Patrick Donlin, )
   Re: Identical query slower on 8.4 vs 8.3  ("Igor Neyman", )
  Re: Identical query slower on 8.4 vs 8.3  (, )
 Re: Identical query slower on 8.4 vs 8.3  (Jon Nelson, )
 Re: Identical query slower on 8.4 vs 8.3  ("Joshua D. Drake", )
 Re: Identical query slower on 8.4 vs 8.3  ("Joshua D. Drake", )

Patrick Donlin <> wrote:

> I have run vacuumdb --full --analyze, it
> actually runs as a nightly cron job.

That's usually not wise -- VACUUM FULL can cause index bloat, and is
not normally necessary.  If you have autovacuum turned on and run a
database vacuum each night, you can probably avoid ever running
VACUUM FULL.  A long-running transaction or mass deletes might still
make aggressive cleanup necessary on occasion, but you should
consider using CLUSTER instead of VACUUM FULL.  So, you should
probably change your crontab job to vacuum --all --analyze.

Also, after a bulk load of a database like this you might consider a
one-time VACUUM FREEZE of the database.  Without that there will
come a time when autovacuum will need to rewrite all rows from the
bulk load which haven't subsequently been modified, in order to
prevent transaction ID wraparound problems.

-Kevin


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

От: Ben Chobot
Дата:
Сообщение: Re: performance on new linux box
От: "Pierre C"
Дата:
Сообщение: Re: Question of using COPY on a table with triggers