Re: Database performance post-VACUUM FULL

On Fri, Sep 18, 2009 at 8:44 AM, Karl Wright <kwright(at)metacarta(dot)com> wrote:
> Hi all,
>
> We're using Postgresql 8.3.7 on Debian.  We are seeing a very strange
> performance situation with our application which I am hoping that someone
> can shed light on.
>
> Our tests find that our application runs quite well on 8.3.7 initially.  The
> test consists of database creation followed by 30 cycles of creation and
> removal of approximately 1,000,000 rows (across all tables) per cycle.
>  However, when database maintenance takes place (which consists of a VACUUM
> FULL operation, and some table REINDEX operations), subsequent cycle
> performance is more than 2x worse.  What's more, after one VACUUM FULL
> operation has been done on the database, no subsequent VACUUM FULL
> operations *ever* seem to restore it to proper performance levels.
>
> We used the same general maintenance procedure with 8.2 and found that it
> worked as expected, so we were quite surprised to discover this problem with
> 8.3.7.  Anybody know what's going on?

Can you post to the list all the uncommented settings from your
postgresql.conf, the output of VACUUM VERBOSE, and the output of
EXPLAIN ANALYZE for some representative queries?

...Robert