Message-ID: <45D9E646.6060909@modgraph-usa.com>
Date: Mon, 19 Feb 2007 10:02:46 -0800
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Thunderbird 1.5.0.9 (X11/20061206)
MIME-Version: 1.0
To: Andreas Tille <tillea@rki.de>
CC: pgsql-performance@postgresql.org
Subject: Re: How to debug performance problems
References: <Pine.LNX.4.62.0702191150020.15820@wr-linux02>
In-Reply-To: <Pine.LNX.4.62.0702191150020.15820@wr-linux02>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andreas Tille wrote:
> My web application was running fine for years without any problem
> and the performance was satisfying.  Some months ago I added a
> table containing 4500000 data rows ...
> 
> Since about two weeks the application became *drastically* slower
> and I urgently have to bring back the old performance.  As I said
> I'm talking about functions accessing tables that did not increased
> over several years and should behave more or less the same.

Don't assume that the big table you added is the source of the problem.  It might be, but more likely it's something else entirely.  You indicated that the problem didn't coincide with creating the large table.

There are a number of recurring themes on this discussion group:

  * A long-running transaction keeps vacuum from working.

  * A table grows just enough to pass a threshold in the
    planner and a drastically different plan is generated.
  
  * An index has become bloated and/or corrupted, and you
    need to run the REINDEX command.

And several other common problems.

The first thing is to find out which query is taking a lot of time.  I'm no expert, but there have been several explanations on this forum recently how to find your top time-consuming queries.  Once you find them, then EXPLAIN ANALYZE should get you started 

Craig