Weird performance drop after VACUUM

От: Ümit Öztosun
Тема: Weird performance drop after VACUUM
Дата: ,
Msg-id: 1125093859.9329.30.camel@localhost.localdomain
(см: обсуждение, исходный текст)
Ответы: Re: Weird performance drop after VACUUM  (asif ali)
Re: Weird performance drop after VACUUM  (Tom Lane)
Список: pgsql-performance

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

Weird performance drop after VACUUM  (Ümit Öztosun, )
 Re: Weird performance drop after VACUUM  (asif ali, )
  Re: Weird performance drop after VACUUM  (Philip Hallstrom, )
  Re: Weird performance drop after VACUUM  (Michael Fuhr, )
   Re: Weird performance drop after VACUUM  (asif ali, )
    Re: Weird performance drop after VACUUM  (Michael Fuhr, )
     Re: Weird performance drop after VACUUM  (asif ali, )
      Re: Weird performance drop after VACUUM  (Michael Fuhr, )
       Re: Weird performance drop after VACUUM  (asif ali, )
 Re: Weird performance drop after VACUUM  (Tom Lane, )
  Re: Weird performance drop after VACUUM  (Umit Oztosun, )
  Re: Weird performance drop after VACUUM  ("Steinar H. Gunderson", )
   Re: Weird performance drop after VACUUM  (Tom Lane, )
 Re: Weird performance drop after VACUUM  ("Steinar H. Gunderson", )
  Re: Weird performance drop after VACUUM  (Tom Lane, )

Hello,

We are using PostgreSQL for our business application. Recently, during
testing of our application with large volumes of data, we faced a weird
problem. Our query performance dropped *dramatically* after "VACUUM FULL
ANALYZE" command. We have encountered a similar problem listed on
mailing list archives, but the submitter solved his problem by rewriting
his query, which is unfortunatelly very hard for us.

I am attaching two EXPLAIN ANALYZE outputs, first one is just before the
VACUUM FULL ANALYZE command and the other is the one after. Also
attached is the SQL query, which is simplified to clearify the problem.
In the example query time increases from 1.8 second to > 4.0 secons. The
difference for the complete query is much bigger, query time increases
from 7.8 seconds to > 110 seconds.

Any help is appreciated, we were unable to identify what causes the
query planner to choose a different/poor performing plan.

Notes:
Our production platform is Ubuntu Linux Hoary on i386, PostgreSQL 8.0.3,
compiled from sources. Same tests were carried on Windows XP
Professional and PostgreSQL 8.0.1 with similar results. The queries use
little IO, high CPU. The largest table involved in the sample query has
about 10000 rows. Indexes are used intensively, some tables use > 4
indexes.

Best regards,
Umit Oztosun


Вложения

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

От: Greg Stark
Дата:
Сообщение: Re: Limit + group + join
От: Thomas Ganss
Дата:
Сообщение: Re: Caching by Postgres