Subject: Re: Observation about db response time
From: "Jeffrey W. Baker" <jwb@gghcwest.com>
To: Frank Wiles <frank@wiles.org>
Cc: Akshay Mathur <akshay.mathur@airtightnetworks.net>,
	pgsql-performance@postgresql.org
In-Reply-To: <20050830081322.20a13d4f.frank@wiles.org>
References: <003401c5ad63$8420d940$9701a8c0@pune.wibhu.com>
	<20050830081322.20a13d4f.frank@wiles.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Tue, 30 Aug 2005 09:39:17 -0700
Message-Id: <1125419957.2658.0.camel@kane>
Mime-Version: 1.0

On Tue, 2005-08-30 at 08:13 -0500, Frank Wiles wrote:
> On Tue, 30 Aug 2005 18:35:30 +0530
> "Akshay Mathur" <akshay.mathur@airtightnetworks.net> wrote:
> 
> > Hello Friends,
> >  
> > We were having a database in pgsql7.4.2 The database was responding
> > very slowly even after full vacuum analyze (select count(*) from
> > some_table_having_18000_records was taking 18 Sec).
> >  
> > We took a backup of that db and restored it back. Now the same db on
> > same PC is responding fast (same query is taking 18 ms).
> >  
> > But we can't do the same as a solution of slow response. Do anybody
> > has faced similar problem? Is this due to any internal problem of
> > pgsql? Is there any clue to fasten the database?
> 
>   This could be because you don't have max_fsm_pages and
>   max_fsm_relations setup correctly or are not doing full vacuums 
>   often enough. 
> 
>   If your database deletes a ton of data as a matter of course then
>   sometimes a full vacuum will not clear up as much space as it could.
> 
>   Try increasing those configuration values and doing vacuums more
>   often. 
> 
>   If you should also explore upgrading to the latest 8.0 as you will
>   no doubt see noticeable speed improvements. 

This can also be caused by index bloat.  VACUUM does not clear out the
index.  You must use REINDEX for that.

-jwb